ClickHouse's native query cache can slash database load for dashboard-heavy workloads - but it's vulnerable to cache stampedes. What the thundering herd problem looks like in ClickHouse, and where the single-node and Redis-backed fixes stand.

Taming the ClickHouse Cache Stampede

Imagine you're building an observability platform that currently supports 5 concurrent users. Now, as part of a scaling initiative, you're asked to support 100 concurrent users instead.

Sounds like a straightforward capacity-planning exercise - until you realize the database itself is the bottleneck.

The resource nightmare

If your database needed x resources to serve 5 concurrent users, basic math says resources need to grow roughly 20x to serve 100. That's a nightmare in any environment, but it's especially brutal in resource-constrained, on-prem setups - and doubly so when the database powering your dashboards is a beast like ClickHouse.

So you do what any reasonable engineer would do: you go spelunking through your query patterns. What is your platform actually asking the database to compute every time it renders one of those beautiful graphs for your customers?

That's when you notice something encouraging: across users, the queries are largely the same. The result of one user's query can, in many cases, be reused for another user's identical request. It's a classic software engineering problem - and a classic software engineering solution: a shared cache. With one in place, database load stops scaling with the number of users and starts scaling with the number of unique queries - a much smaller, much saner number.

Enter ClickHouse's native query cache

The good news is you don't have to build this cache yourself. ClickHouse has a query result cache built in natively since version 23.1. When enabled, it stores the full result set of a SELECT, and identical queries are served straight from memory without touching the data.

A few things to know before you flip it on, because the defaults are conservative:

  • It's opt-in, per query. Nothing is cached until you set use_query_cache = 1 on the query, session, or user profile.
  • Entries expire after 60 seconds by default. Tune with query_cache_ttl - remember this number, it's the villain of the next section.
  • Results are not shared between users by default, for security reasons. If your dashboards query ClickHouse through a single service account, you get sharing for free; otherwise look at query_cache_share_between_users.
  • Queries with non-deterministic functions aren't cached at all by default. A dashboard query filtering on now() - INTERVAL 15 MINUTE is exactly that. Round your time boundaries (e.g. toStartOfMinute()) so identical queries are byte-identical, or reach for query_cache_nondeterministic_function_handling = 'save' if you understand the staleness trade-off.

You run it through its paces, most cases check out, and it looks like the fix you needed.

But there's one dangerous edge case waiting to turn your happy customers into grumpy ones.

What is a cache stampede?

A cache stampede (also known as a thundering herd) is a system failure that happens when multiple concurrent requests simultaneously experience a cache miss on the same popular item - and all of them turn around and hit the underlying database at the exact same moment.

Here's the sequence:

  1. A popular query result is cached.
  2. Its TTL expires.
  3. A wave of concurrent, identical queries all miss the cache at once.
  4. Every single one of them independently recomputes the result and pounds the database simultaneously.

A single expired cache key can be enough to bring down your entire system.

Where ClickHouse's query cache falls short today

Right now, ClickHouse's query cache is susceptible to exactly this problem. Here's what happens under the hood:

  1. The same query runs repeatedly, and the first execution's result gets cached.
  2. When the TTL expires, the entry goes stale.
  3. Every new (still identical) query that comes in now sees a cache miss.
  4. All of them recompute the result independently and write it back to the cache.

In an ideal world, only the first query after expiry would actually hit the database - recompute the result, refresh the cache - while every other identical query in flight would simply wait for that result and reuse it. Instead, ClickHouse currently lets all of them stampede the database at once.

This isn't us reading tea leaves: the gap is documented in issue #99226, opened by the ClickHouse engineer who built the query cache. And there's already an open PR working to solve it within a single ClickHouse node, by making concurrent identical queries block on a token and share the one recomputation - currently being reworked by the ClickHouse team after review.

But wait - there's a cluster-sized catch

Remember why we started this whole exercise? We're not scaling a single node - we're scaling an entire observability platform. That means the fix can't stop at single-node caching. The query cache exists once per ClickHouse server process, so in a multi-node cluster each node maintains its own isolated cache - and its own stampede risk. The same expensive query can miss on every node behind your load balancer at once.

There's movement here too. A proposal for a remote query cache (issue #80252) - storing results in an external system like Redis so nodes can share them - got an explicit thumbs-up from ClickHouse's creator, Alexey Milovidov ("it looks practical, not complex, and a good thing to do"), and a community PR implementing a Redis-backed query result cache with stampede protection is under active review, with Milovidov himself pushing fixes to it.

Until either lands, the workaround Milovidov recommends is routing: hash each query and pin it to a specific replica (see replica-aware routing), so at least each unique query has exactly one cache to warm - and one node to stampede, instead of all of them.

Where things stand

If you've run into this problem in ClickHouse, you're not alone - the ClickHouse team is well aware of it. Work is actively in progress on both the single-node and cluster-wide fronts, and both fixes are being tracked in the open. In the meantime: enable the query cache deliberately (it's opt-in), normalize your dashboard queries so they actually hit it, stagger your TTLs where you can, and pin queries to replicas so a stampede is contained to a single node rather than your whole cluster.