3s+ to Sub-Second: Write-Time Denormalisation

An endpoint on the main dashboard of the ESG platform took over three seconds to respond. It was the first request after login — the screen everyone landed on, every morning, before doing anything else. Three seconds is a particular kind of bad. It is not an outage, so it never became an incident. It is slow enough that people stopped trusting the page and started refreshing it, which made it slower. And it sat on the critical path of a product whose whole pitch was that it was faster than doing the analysis by hand. The platform is around 240,000 lines of code and I am its largest contributor, so there was nobody else to hand this to.

The instinct with a slow MongoDB endpoint is to add an index and move on. I ran `explain()` first, and the shape of the problem was not what I expected. The aggregation pipeline behind that dashboard contained 14 `$lookup` stages and 8 `$unwind` stages. It had grown that way honestly. The data was normalised across facilities, reporting periods, metrics, emission factors, suppliers and users — a reasonable schema. Every new dashboard widget needed one more related collection, so it got one more `$lookup`. No single addition was unreasonable. The cumulative result was a pipeline that reassembled most of the database on every page load. The `$unwind` stages were the expensive part. Each one multiplies the document count flowing through the rest of the pipeline before a later `$group` collapses it again. The pipeline was inflating its working set several times over, in memory, only to reduce it back down — and doing that work identically on every request, for data that changes when someone submits a reading, which is rare. There was a second, independent problem underneath it: a classic N+1. The handler fetched a list of entities, then looped and queried per entity for its latest reading. Fine with the twelve rows in the dev seed. Not fine with a real customer's facility count, where it became hundreds of sequential round trips inside one request.

The reads were expensive because they were recomputing, on every request, a relationship that had already been decided at write time. So I denormalised at write time. When a reading is submitted, the write path now also updates a materialised document that holds the joined, pre-aggregated shape the dashboard actually reads — facility name, period, rolled-up metric totals, the fields the 14 lookups were reaching for. The read becomes a query against one collection instead of a fourteen-way join. The trade is the one denormalisation always makes: writes get slower and more complex, and you take on the duty of keeping the copy correct. That trade is worth making when reads vastly outnumber writes and the read is on a latency-critical path — which is exactly this case. A reading is submitted occasionally; the dashboard is loaded constantly. Two things made it safe to live with: → The materialised document is derived, never authoritative. Source records remain the truth. If the derived shape is ever wrong, it can be rebuilt from source, and a rebuild is a routine operation rather than a recovery. → Updates happen in the same path as the write that causes them, so the copy cannot silently drift behind the data it summarises. The N+1 got the fix it actually needed rather than a cache in front of it: the per-entity loop collapsed into a single query over all the entities at once, backed by a compound index matching the query's equality-then-sort shape so the database could satisfy it with one index scan instead of sorting the result set in memory. Field order in a compound index is not cosmetic — an index on the wrong ordering serves the equality match and then sorts anyway, which looks like an index that "did not help". Getting that order right is the difference between the index being used and being decorative.

The endpoint went from 3s+ to sub-second. • 14 `$lookup` stages removed from the read path • 8 `$unwind` stages removed with them • N+1 loop replaced by a single indexed query • Dashboard load moved off the critical path of the morning workflow The measurement that mattered was not the benchmark — it was that people stopped refreshing the page.

1. Read `explain()` before you optimise. The pipeline shape was the problem, not a missing index. An index on that aggregation would have made almost no difference, and I would have shipped it and believed I had fixed something. 2. Normalisation is a write-time virtue. It keeps writes correct and cheap. On a read-heavy path it is a cost you pay on every request, and when reads outnumber writes by orders of magnitude, paying it once at write time is simply the better trade. 3. `$unwind` is where aggregation pipelines go to die. It multiplies documents mid-pipeline. Several of them compound. Watch the document count between stages, not just the total runtime. 4. Denormalised data must be derived, never authoritative. The moment a materialised copy becomes the only place a fact lives, a stale write becomes data loss instead of a rebuild. 5. Fix the N+1, don't cache it. A cache in front of hundreds of sequential round trips hides the problem until the cache misses — usually under exactly the load that made it matter. 6. Compound index order follows the query, not the schema. Equality fields first, then sort fields. Get it backwards and the database will quietly sort in memory while you look at an index it technically used.