A count that scaled with the wrong thing

The fix that stopped working

I run a lead distribution platform for a client. Their team picks a set of filters, runs a count to see how many leads match, and if the number looks right, pulls those leads into a file to deliver. The count is what people wait on, dozens of times a day.

I wrote about making that count fast in making a slow count fast. It went from 88 seconds to 3.4 by moving the whole thing into one aggregate query, with the exclusion lists as NOT EXISTS anti-joins instead of a fetch-then-filter step in application code.

Three months later the same counts were timing out. The worst came back after 142 seconds as "cannot commit an expired transaction", so it never finished at all.

Nothing about the query had changed. One client's suppression list had grown to about six million rows.

Where the cost was

Every count subtracts two things: the client's suppression list, the numbers and emails they've said never to contact, and everything already delivered to them. Both are per client and both can be large. Simplified, the count looked like this:

SELECT count(*)
FROM leads p
WHERE <filters>
  AND NOT EXISTS (
    SELECT 1 FROM suppression s
    WHERE s.client = $1
      AND s.kind = 'phone'
      AND s.value = p.phone
  )
  AND NOT EXISTS (
    SELECT 1 FROM suppression s
    WHERE s.client = $1
      AND s.kind = 'email'
      AND s.value = lower(p.email)
  )
  AND NOT EXISTS (
    SELECT 1 FROM delivered d
    WHERE d.client = $1 AND d.hash = p.hash
  );

Each of those clauses builds a hash of the client's entire list and probes it for every candidate lead. The cost scales with the size of the list. So the more a client suppressed, the slower their counts got, and six million rows sat right at the edge of spilling that hash to disk, which is where a fast query becomes a slow one and then a timed out one. I wrote about that threshold separately in work_mem changes the algorithm.

What struck me is that it's the wrong number to scale with. The leads we could ever keep for a client are bounded by the pool, a few million rows, no matter how big the suppression list is. A list with twenty million entries can't exclude more than the few million leads that exist. We were paying for the size of the question instead of the size of the answer.

Resolving it once

The suppression list and the delivery history change at three moments: when a client uploads suppression, when we deliver them leads, and when new leads arrive. They don't change between counts, so there's no reason to recompute the match on every one.

I added a resolved set, one row per client and excluded lead id. When a suppression file lands, I resolve its values against the pool once and record which lead ids it hits:

INSERT INTO excluded (client, lead_id, source)
SELECT $1, p.id, 'suppression_phone'
FROM suppression s
JOIN leads p ON p.phone = s.value
WHERE s.client = $1
  AND s.kind = 'phone'
  AND <this file's rows>
ON CONFLICT DO NOTHING;

The same happens on delivery and on ingest. The expensive value matching moves out of the path people wait on and into the moments the data actually changes. The count then stops hashing lists and does one lookup:

SELECT count(*)
FROM leads p
WHERE <filters>
  AND NOT EXISTS (
    SELECT 1 FROM excluded x
    WHERE x.client = $1 AND x.lead_id = p.id
  );

Now the count's cost tracks the pool, not the list. I tested that directly instead of trusting the theory. I held a client's real matches fixed and grew their suppression list from under a million rows to thirty-one million. The old query climbed and then spiked past four minutes. The new one sat flat at about fourteen seconds the whole way. The 142 second count now runs in six or seven.

The resolve had an index lesson in it, and it's one I'd already written down. The first version matched phone and email as a single OR condition, which the planner can't satisfy from either index, so it scanned the whole pool and took a hundred seconds for one client. Splitting it into two equi-joins, one per field, dropped it to seconds. That's the same rule as the separate NOT EXISTS clauses in the earlier note. Knowing it didn't stop me writing it the slow way again.

Keeping the set in sync

The speed was the easy part. The resolved set is a cache, and a cache that drifts from the truth is worse than no cache. Here a stale row means a lead wrongly withheld, or worse, one wrongly sent to somebody who asked never to be contacted again.

So four events have to maintain it, every time, without exception: suppression upload, de-suppression, delivery, and ingest. De-suppression is the awkward one. Releasing values means re-resolving that client, and that has to happen atomically, delete and rebuild inside one transaction, so a count running at the same instant never sees the set half built. A count that reads a half rebuilt exclusion set doesn't fail. It returns a number, and the number's wrong.

Proving it matched

The part I spent most time on wasn't the index or the sync. It was proving the fast path and the old path give the same count. Not approximately. Exactly, for every client and every combination of filters.

A faster count that's wrong is worse than a slow one that isn't, and this one decides who gets contacted. Nobody double checks a number that looks about right and arrives quickly.

So before it went near production I ran both paths against a full copy of the live data and diffed them: 120 clients, thirty three million resolved rows, every filter shape including the awkward de-duplication ones. Every one matched to the row. Then it shipped behind a flag I can turn off in seconds, with the old path left intact underneath it.

The client asked the most useful question anyone can ask on a performance change, which is whether I'm sure it's fixed. It isn't a challenge, it's an invitation to show the evidence, and twice on this build it sent me back to measure and find I'd been half right at best.

The speedup is the headline. A count that timed out now runs in single digits and stays there no matter how much a client suppresses. The real work was making sure the number never changed on the way there, not even once, not even for a single commit.