Five-minute queries behind a sixty-second timeout: unwinding a cache cascade

The tool was a data visualization product: clients opened it to explore charts and graphs built on their own employee data. Vue on the front, Go behind it, and the analytics layer was Redash, which caches query results in Redis so an expensive query doesn't have to run again for the next person who asks for it.

Then it stopped loading at all.

Why subquery caching looks like the right answer

The dashboard's queries were layered. Expensive base aggregations at the bottom, and on top of them, queries that read those results and sliced them further for each chart.

Caching the bottom layer is the obvious move. Compute the costly aggregation once, let every query above it read a cached result, and a page full of charts costs one hard computation instead of a dozen. For a while that is exactly what happens, which is what makes the failure mode so easy to walk into.

How it collapsed

The cache was keyed to queries, and the queries had dependencies the cache understood only crudely. Any change to a table sitting underneath a cached subquery invalidated it — and everything built on top of that subquery had to recompute too.

On a live product, those base tables change constantly. So the cached layer that was supposed to absorb the expensive work was being torn down and rebuilt continuously, and every query stacked above it inherited the cost. The main queries drifted out to around five minutes.

They never got to finish. The request timeout was sixty seconds, so what users experienced wasn't a slow dashboard — it was a dashboard that failed.

The frontend made the worst case the first case

Compounding it, the page fetched everything before rendering anything. Many separate result sets, all requested up front, all blocking the first paint.

That meant the very first visit — a cold cache, every query computing from scratch, nothing allowed to render until all of them returned — was the single worst path through the system. It was also the path every new client took. The load that most needed to succeed was the one guaranteed to time out.

Three fixes, in the order they mattered

Preload the relationships. The queries were doing per-row lookups that could be satisfied in one pass. Eager-loading what the aggregation actually needed removed a whole class of repeated round-trips from inside the expensive layer, which is the cheapest kind of win available: same results, far less work.

Split the fetch and make it asynchronous. Rendering no longer waited on everything. Each chart requested its own data, and the page painted as soon as the parts it needed were ready rather than when the slowest query in the set finished. This didn't make anything faster. It stopped the slowest thing on the page from setting the speed of the whole page, which users experience as roughly the same thing.

Warm the cache before anyone asks. A scheduled job calls the API with each client's parameters on a cadence, so the expensive queries run on the cron's time rather than a user's. A cold cache stops being a customer-facing event and becomes a background one.

The combination took the p95 load down to about thirty seconds.

Thirty seconds is not fast

That number is the honest end of the story, and it's worth not dressing up. Going from times out and shows nothing to loads in half a minute is the difference between a broken product and a usable one, and it was the right thing to chase first. It is not the same as making the thing quick.

The remaining thirty seconds are structural. Caching query results — however well you warm them — is still a bet that someone asked the same question recently. The queries themselves are still computing aggregations over raw rows every time they run, and the cache is only hiding that latency, not removing it.

Where a different shape fits

• Materialized views, or a rollup table. Precompute the aggregation as data, refreshed on a schedule or incrementally on write, and the dashboard reads rows instead of triggering computation. That converts the problem from "how long does this query take" to "how fresh is this table," which is a much easier question to answer.
• A warehouse rather than the operational database. Analytical queries over the same tables serving live product traffic will always contend. Separating them removes the constraint that makes aggressive caching necessary in the first place.
• Invalidation that follows relevance, not dependency. The cascade happened because the cache knew which tables a query touched but not whether the specific change mattered to it. A cache keyed on the inputs that actually affect a result invalidates far less — at the cost of having to state those inputs explicitly, which nothing does for you.

The lesson I'd carry forward isn't that caching was the wrong tool. It's that a cache layered on top of a dependency graph inherits that graph's fan-out, and if you haven't looked at the fan-out you haven't sized the cache — you've just moved the cost somewhere it's harder to see.