The N+1 you can't see, and a cache that must never lie

Two of my favourite kinds of fix are the ones where a number drops by three orders of magnitude and the ones where you make a whole category of outage impossible. This is a short tour of both, from a Node backend talking to Postgres through Prisma with Redis in front of the slow bits.

The N+1 hiding in a helper#

A report was taking about twelve seconds. Not crashing, just slow enough to feel broken. The code looked completely innocent: a loop over the rows, and inside it a call to a helper that resolved a bit of mapping for each row.

for (const row of rows) {
  const mapping = await getMappingForCategory(row.category); // <- here
  // ...
}

The problem is that the helper was not free. Each call fanned out to a few database queries. Over a 500-row report that is roughly 1,500 queries, run one after another because they are awaited in the loop. Then a twelve-period trend ran those reports in parallel and the worst case ballooned to around 6,000 sequential awaits. Hence twelve seconds.

The fix is the classic one: stop asking per row, ask once. A single bulk method did two findMany calls up front, assembled the results in memory into a Map keyed by id (with the specific rows overriding the general ones), and then each row resolved its mapping with an O(1) Map.get. Per-report cost went from about a second to about five milliseconds.

The detail that made it maintainable: I did not delete the old per-item helper. I rewrote it to call the bulk method, so the actual resolution logic lives in exactly one place. Two entry points, one implementation.

The test that pins the fix#

Here is the part I have learned the hard way. A performance fix is not done when it is fast. It is done when it cannot silently become slow again.

An output test does not help you here. The report returns the same rows whether it runs one query or six thousand, so a test that checks the output will happily pass the day someone reintroduces the N+1 in a refactor.

So the test asserts the call count, not the output: the bulk method is called exactly once, and the old per-row path is called exactly zero times, no matter how many rows you throw at it. Reintroduce the loop-of-queries and the test goes red immediately, with a message that points straight at what happened.

If a regression would not change your output, only your query count, then your test has to watch the query count. Assert the shape of the work, not just the result.

A cache that fails safe#

Redis sat in front of an expensive aggregate. The temptation with a cache is to treat it as just another data store. It is not. It is an optimisation, and the moment it becomes load-bearing you have built a second source of truth that can disagree with the first. Get this wrong and a Redis hiccup takes the whole feature down, or the app cheerfully serves numbers that were already stale, which is worse than being slow. A few rules kept it honest:

• Write with one atomic command. Use SETEX(key, ttl, value), not SET followed by EXPIRE. The two-step version has a gap: crash in between and you have left a key with no expiry that lives forever. One command, no gap.
• Every cache read falls back to the database. The whole thing is wrapped so that a Redis outage degrades to a direct query, never an error. A cache being down should make you slower, not broken.
• Writes and invalidations are non-fatal. Computing the value and returning it to the user matters; caching it does not. So the cache write is fire-and-forget, and an invalidation failure on a data change is logged and swallowed, because a cache-invalidation failure must never fail the actual data mutation. A bounded TTL is the safety net if an invalidation is ever missed.

One more thing worth saying out loud: invalidating with KEYS pattern:* is a production footgun. KEYS walks the entire keyspace and blocks Redis while it does it. It is fine at ten keys and a genuine incident at ten thousand. The plan, before that scale arrives, is a SCAN cursor loop instead.

The shared idea#

The N+1 fix and the caching rules are the same lesson pointing two ways. Know the real cost of the thing you are calling, and never let the fast path quietly become the fragile one. A helper that hides a database call, and a cache that quietly becomes a source of truth, are the same mistake: trusting something to be cheap and reliable that was never promised to be either.