2026-05-06 · 4 min read

A while back, one of our seemingly simple APIs started causing big headaches. It was just supposed to fetch a paginated list of leads, along with contact details, organization info, and some other metadata. On paper it sounded easy. In reality it was dragging the whole system down.

The Pain Point

Every request was running a query with 4–5 nested joins per row. At low traffic it seemed fine. But as soon as load increased, things got real bad:

• p95 latency jumped between 800ms to 1.5s
• Database connection pool kept getting exhausted
• Other unrelated parts of the app started slowing down too

It wasn't just a slow endpoint anymore — it was becoming a system-wide bottleneck. Here's what the query roughly looked like:

SELECT l.*, c.*, o.*, ls.*
FROM leads l
LEFT JOIN contacts c ON c.id = l.contact_id
LEFT JOIN organizations o ON o.id = l.org_id
LEFT JOIN lead_sources ls ON ls.id = l.source_id
WHERE ...
ORDER BY l.created_at DESC
LIMIT 20 OFFSET 0;

Pagination didn't help much. Every request still paid the full price of these joins, especially with complex filters on top.

The Fixes That Didn't Work

We tried a bunch of the usual stuff first:

• Scaling the database — helped a bit, but was expensive and didn't solve the root issue.
• Bigger connection pool — just meant more heavy queries running simultaneously, making contention worse.
• Redis caching — a nightmare to invalidate because the data came from multiple tables. And with all the different filter + pagination combinations, we hit cache explosion fast.

After a while it became clear: the problem wasn't our infra. It was how we were fetching the data.

The Idea That Saved Us

Instead of doing all the heavy lifting every time someone loaded the list, we decided to pre-compute the data.

We created a new materialized table — basically a flat, denormalized version of the leads data with all joins already resolved. Built specifically for fast reads.

How We Built It

Write Path: Whenever something changed (lead created/updated, contact updated, org updated, etc.), we emitted an event to RabbitMQ. A background worker picked it up, fetched the latest data, flattened it, and updated the materialized table.

Read Path: The API now just queries the materialized table directly — no joins.

The worker logic was pretty simple:

async function handleEvent(event) {
  const leadIds = findAffectedLeads(event);

  for (const leadId of leadIds) {
    const data = await fetchFullLeadData(leadId);
    await upsertIntoMaterializedTable(data);
  }
}

An Interesting Race Condition We Hit

Even after we got the pipeline working smoothly, we ran into a tricky problem.

The denormalization was happening too fast. Sometimes the worker would pick up the event and update the materialized table before the original transaction on the main table was fully committed. This caused weird inconsistencies — the materialized table would be "one query behind," or stale. It was subtle but annoying.

How we fixed it

We changed the event emission timing. Instead of firing the event as soon as the change started, we only emitted it after the transaction was successfully committed in the main database.

That small change eliminated the race condition entirely and made the system much more reliable.

The Messy Parts

This approach wasn't "set it and forget it." We ran into several real challenges:

• Fanout: One organization update could affect dozens of leads. We had to find all affected leads and update them efficiently.
• Idempotency: Workers could retry, so we used UPSERT statements everywhere.
• Out-of-order events: A contact update might arrive before the lead was even created. We had to handle missing data gracefully and retry when needed.
• Failures: Dead-letter queues, retry logic, the works.
• Backfilling: We had to run a one-time job to populate the table with all existing data before going live.

The Tradeoffs

This solution isn't perfect — as nothing is in systems. We accepted eventual consistency: the materialized table might lag by a few seconds sometimes. We also added more moving parts (queue + workers) to reason about.

But for our use case it was worth it. We traded strict consistency for speed and stability.

We also considered other options along the way:

• Database materialized views — too rigid for our needs
• Heavy Redis caching — invalidation hell
• Full CQRS — overkill for our scale

The Results

The improvements were dramatic:

• p95 latency dropped from ~1.2s down to ~150ms
• Database load dropped significantly
• Connection pool stabilized
• The whole system felt fast and predictable again