A Cache Is Not a Fix for a Slow Query

Three to seven seconds on a vehicle listing endpoint

The vehicle listing endpoint on the platform I worked on ran at three to seven seconds. That is the number I measured with New Relic, the APM in use on that project, and it is the number this post is built around. New Relic did two things at once: it showed the latency, and it showed where that latency actually lived. The trace was dominated by database time, not application time. Almost none of those three to seven seconds was React, serialisation, middleware or anything running inside the Node process. It was the query.

That distinction should decide what you do next, and in what order. A slow endpoint with database time dominating has a database problem, and a database problem gets fixed at the database: by giving the query an index it can use and a shape it can execute cheaply. It does not get fixed by putting a cache in front of it, because a cache in front of a scan does not make the scan faster. It only makes the scan happen less often, for whichever requests happen to repeat, and every request that does not repeat still pays the full cost.

That is a deliberate choice, not an oversight. A before and after number is easy to make persuasive and easy to make meaningless, because it depends entirely on what changed between the two measurements and how the second one was taken. The order of the work is the part that holds up without a number attached to it: measure, index, reshape, then cache. Get the order wrong and a cache is the fastest way to make a slow query look fixed while leaving it exactly as slow as it always was for the request that misses.

Why the index comes before the cache

The index comes before the cache because it addresses the cause. A cache addresses the symptom, and only for the fraction of requests that happen to hit it. The rule for building the index is older than any of this and it still holds: put the fields the query filters on equality first, in the order the query filters on them, the field it sorts on second, and any field it filters on a range last. A database engine can use a compound index as a prefix, so a query that filters on two equality fields and sorts on a third can be answered by walking the index in order, with no separate sort step and no scan.

A scan costs roughly in proportion to the size of the collection, not to the size of the result. A listing query that returns twenty rows out of a much larger table still has to examine a large share of that table if no index matches the filter and sort together, and that share gets worse, not better, as the collection grows. A cache in front of that query does nothing about the share. It only decides how often you pay it. Traffic spread across many different filter combinations, which a vehicle listing endpoint with several search parameters usually sees, will keep missing the cache at close to the same rate no matter how large you make it, because a cache trades repetition for speed, and a diverse set of queries has little repetition to trade.

// Fields in the order the query actually uses them: equality filters
// first, in the order they are filtered on, the sort field second.
await db.collection('vehicles').createIndex({
  emirateCode: 1,
  status: 1,
  createdAt: -1
});

const plan = await db
  .collection('vehicles')
  .find({emirateCode, status: 'available'})
  .sort({createdAt: -1})
  .explain('executionStats');

// A scan reports a stage of COLLSCAN, with totalDocsExamined close to
// the size of the collection. An index that matches reports IXSCAN,
// with totalDocsExamined close to nReturned.

Read the explain output before you trust the index, not after. totalDocsExamined close to nReturned is what a matching index looks like. Anywhere near the size of the collection is a scan wearing an index’s name.

Then the query shape

The index alone was not enough on that endpoint. The listing query also sorted on a field that could not use an index efficiently, so even with the compound index in place, the ordering step still fell back to an in memory sort for part of the result. I changed the ordering to use the createdAt timestamp, a field written once, never recalculated, and placed cleanly at the end of the compound index. The sort became free: the index already produced the rows in that order, so there was nothing left to sort in memory.

That is one instance of a general problem: the shape of a query matters as much as the presence of an index. Four shapes are worth checking on any endpoint that is still slow after indexing.

• Sorting on a computed or unindexed field. If the value being sorted on is derived at query time, or lives outside the compound index, the database has to gather every matching row into memory and sort it there, no matter how good the filter’s index is.
• Projecting fields you do not render. Returning a full document when the response only uses six fields moves bytes across the network and through serialisation for no reason; project only what the response actually needs.
• A filter that cannot use the index prefix. A compound index is only useful in the order it was built. A query that filters on the second field without the first cannot use the index the same way, and often cannot use it at all.
• Offset pagination on a large collection. skip(10000) still has to walk and discard ten thousand documents before it returns the next page. A cursor keyed on the last seen value of the sort field costs about the same on page two as it does on page two hundred.

None of these four are cache problems. They are shapes a database has to fight regardless of what sits in front of it, and a cache in front of a badly shaped query only hides how badly shaped it is.

Two caches, two invalidation models

Only once the index and the query shape were fixed did I add a cache, and the endpoint ended up needing two of them, each solving a different problem with a different invalidation model.

The first is the search and booking response cache: a Redis cache for the endpoint’s own response, keyed by the request URL. It works as a key because every field that changes the answer, the dates, the duration, the branch and the vehicle class, is already present in the query string, so the URL is a complete description of the request. It carries a 30 minute TTL, matched deliberately to how long the booking journey holds a customer’s search: once someone starts searching, that hold lasts 30 minutes, and there is no reason for the cached answer to outlive the window in which the customer is still acting on it.

The second is an internal entity cache, for the service to service call the booking flow makes when it needs vehicle data: code, imagery, time to ready, emirate mapping. That data does not go stale on a schedule the way a search result does. It is cached with no TTL at all, keyed by vehicle code, model year and emirate code together, the combination that is genuinely unique for that lookup. Instead of an expiry, the key is invalidated at the point of write: when a vehicle record changes, the write path removes that specific key, and the next read repopulates it.

function vehicleCacheKey(vehicleCode: string, modelYear: number, emirateCode: string) {
  return `vehicle:${vehicleCode}:${modelYear}:${emirateCode}`;
}

// On write, invalidate the exact key rather than a broader pattern
// or a wildcard scan of the keyspace.
async function invalidateVehicle(vehicleCode: string, modelYear: number, emirateCode: string) {
  await redis.del(vehicleCacheKey(vehicleCode, modelYear, emirateCode));
}

Two caches on the same endpoint, two different answers to the same question: what happens when the underlying data changes before the cache would otherwise expire. One accepts staleness for a bounded window because it can. The other cannot accept any staleness it can avoid, so it does not use a window at all.

TTL or write invalidation: how do you choose?

The rule that decides between the two is simple to state and easy to get backwards under deadline pressure: use a TTL when you cannot know that the data changed, and use write invalidation when you can. A TTL is an admission that you are not watching for the change, so you are willing to serve a stale answer for a bounded time instead. Write invalidation is a claim that you are watching, so the cache never needs to guess.

Getting this backwards in either direction costs you something specific. Using a TTL where you could invalidate on write is choosing to serve stale data for no reason, on a schedule you picked instead of on the event that actually changed the answer. Using invalidation where you cannot observe every writer to the data is worse: it is choosing to serve stale data forever, because nothing will ever tell that cache to update, and a stale entry with no TTL just sits there being wrong until someone notices in production.

The practical test is whether you can enumerate every writer to the data behind a key. That is a harder question than it sounds, because the writers are rarely all in the same service, or even in the same codebase. A batch importer, an admin panel, a nightly sync job and a support tool that lets a person fix one record by hand are four separate writers to the same entity, and each one has to know to invalidate the cache, or the cache has to be invalidated somewhere all four paths pass through. Miss one, and that is the writer whose change never shows up.

The key is the contract

The response cache works with the request URL as its key for a specific reason, not by convention: every field that changes the answer is already in the query string. Dates, duration, branch and vehicle class all have to be present for the search to run at all, so the URL is not an approximation of the request, it is a complete description of it. Two identical URLs will always produce the same answer, which is the only property a cache key actually needs.

The failure mode is a field that changes the answer but does not live in the URL: something read from a header, a cookie, a session or a locale setting instead. Cache a response keyed only by URL when the answer also depends on one of those, and the cache will serve one customer’s answer to a different customer who happens to request the same URL under a different header, cookie, session or locale. That is not a slow bug. It is a wrong answer bug, and it tends to surface as a support ticket long before anyone finds it in a log.

The internal entity cache uses a composite key instead, vehicle code, model year and emirate code together, because none of those three fields is unique on its own but the combination is. A composite key only works if the uniqueness is genuine: if two different vehicles could ever share all three fields, the cache would serve one vehicle’s data under the other’s key, the same wrong answer failure as the URL case, arrived at from the entity side rather than the request side.

• Every field the answer actually depends on, and nothing that does not change the answer
• A value read the same way on every request, not one that depends on load order or which instance happens to handle it
• A combination that is genuinely unique for what it identifies, verified rather than assumed
• Nothing that changes per viewer unless the cache is meant to be per viewer, in which case that field belongs in the key too

Three things that break a working cache

Three failure modes are worth naming even though none of them were problems on this particular endpoint. They are refinements worth adding to a cache like this, not a description of what shipped, and the distinction matters: a working cache and a cache that has been hardened against these three are not the same thing, and it is worth knowing which one you actually have.

A cache stampede happens when a hot key expires under load: the TTL runs out, and every request that was relying on that key misses at the same moment, so they all reach the database together instead of one request refreshing it while the rest wait. A short lock around the refresh, so only one request repopulates the key while the others either wait briefly or receive the stale value until it lands, prevents the pile up without giving up the TTL.

Versioned key prefixes replace a delete with a switch: instead of removing a key, or a related set of keys, which can complete partially and leave some invalidated and some not, the write path increments a version number and every subsequent read for that entity starts using the new prefix. The old entries simply age out unread. Invalidation becomes atomic because there is nothing left to half finish.

Negative caching is caching the no availability answer with its own, shorter TTL. Without it, the most expensive queries, the ones that scan the most and still return nothing, are the only ones never cached, because a cache that only stores non empty results skips exactly the requests that most need the help.

The order of work

None of this is complicated once the order is fixed. It only gets expensive when the order is inverted, and a cache is reached for first because it is the fastest thing to ship under pressure and the one that makes a graph look better by tomorrow morning.

1. 1

   Measure first

   Find out whether the endpoint is slow because of database time or application time, before you decide what to fix. An APM trace answers this in minutes; guessing does not.

2. 2

   Index for the query you actually run

   Build a compound index that matches the fields the query filters, sorts and ranges on, in that order: equality first, sort second, range last.

3. 3

   Reshape the query

   Sort on a field the index can use, project only what the response renders, and replace offset pagination with a cursor once the offset gets large.

4. 4

   Cache what is still expensive

   Only once the query itself is fast is a cache worth adding, and only for the calls still costly enough to justify one.

5. 5

   Choose TTL or invalidation, per cache

   Decide for each cache separately, by whether you can observe every writer to the data, and write the invalidation rule down where the writer that triggers it can see it.

That is what performance work usually turns out to be: the unglamorous half of the fix, done in order, before a single Redis key gets written. The same discipline sits behind the platform work referenced throughout this post.