Scaling an aggregation pipeline: from 41,000 queries a cycle to a handful
Designing a data-ingestion pipeline whose cost scales with what changed, not with the size of the dataset — batch loading, delta filtering, and cursor-based fan-out.
Nothing about for each pair, check if it exists, check if it changed, write if needed looks wrong in a code review. It's three queries per item, and three
queries per item is a pattern every backend engineer has written a hundred
times. The problem only shows up once you multiply it by the actual size of
the collection you're iterating — in this case, over 13,000 trading pairs
across nine exchanges, three queries each, every polling cycle. Roughly 41,000
database round-trips, repeating on a timer.
The cost was invisible until it wasn't
At small scale, this pattern is genuinely fine — the query count is low enough that latency and database load don't register as a problem worth fixing. It becomes a problem exactly when the thing it's iterating over grows, which is also exactly when nobody is looking at that code anymore, because it shipped, it worked, and it moved off the top of everyone's mental model of "code that might be slow."
The visible symptom wasn't a crash. It was a cycle time creeping toward 50 seconds for what should have been a routine poll — long enough to start overlapping with the next scheduled cycle if nothing else intervened.
Batch load, then filter — the fix is almost always this shape
The rewrite didn't change what the aggregator was checking, only how much work it did to check it. Existing pairs and base assets for a given exchange are now loaded in two bulk queries instead of one query per pair, and the result is filtered down to only the pairs that actually changed before any per-pair processing happens. Everything downstream of that filter — new listing detection, delisting detection, exchange-expansion detection — now runs against a small, already-relevant set instead of the full 13,000.
The result: query count per cycle dropped from tens of thousands to a handful, and cycle time went from roughly 50 seconds to 5–8 seconds. Nothing about the detection logic changed. Only the amount of database round-trips required to reach it did.
The same fix, applied a second time, in a different place
The same review pass found an equivalent problem on the notification side: fanning alerts out to subscribers by loading every subscriber into memory on every send. The fix mirrors the aggregation fix exactly — a cursor-based stream instead of a full in-memory load — because it's the same underlying mistake wearing a different name: doing O(n) expensive work per item instead of O(1) expensive work up front and O(n) cheap work after.
Why this is worth writing about
Neither fix required new infrastructure, a new database, or a rewrite. They required noticing that a pattern which is correct in isolation stops being free once the collection it operates over crosses a size nobody originally designed for. The lesson isn't "always batch your queries" — sometimes N+1 is fine, when N is small and stays small. It's that the moment a collection's size becomes a business number instead of a test-fixture number, the query pattern deserves a second look, on a schedule, not just when it becomes a 5-alarm incident.