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.

It wasn't always there. Before 23.1 (January 2023) ClickHouse had no built-in result cache at all, and the standard stand-in was to front ClickHouse with chproxy, a caching HTTP proxy that sat between your dashboards and the database. The native cache shipped as experimental at first, and even today the docs describe it as transactionally inconsistent by design: serving slightly stale results within the TTL is a deliberate trade, not a bug.

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. There is a query_cache_share_between_users setting, but the docs explicitly say enabling it is not recommended for security reasons - one user's cached result could leak rows another user isn't allowed to see.
  • A result is only cached after the query has run a few times. query_cache_min_query_runs controls how many executions a query needs before its result is stored. The default is 0, but if you raise it to keep one-off queries out of the cache, your dashboards will pay full price for the first N runs.
  • Big results silently aren't cached. Per-entry caps default to 1 MiB (max_entry_size_in_bytes) and 30 million rows (max_entry_size_in_rows), and the whole cache is capped at 1 GiB (max_size_in_bytes). A wide dashboard result that blows past the entry limit just doesn't get cached - no error, no warning, and every run hits the data.
  • Queries touching system tables aren't cached by default. query_cache_system_table_handling decides whether they throw, get skipped, or get saved; the default is to throw.
  • 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. Once your queries are normalized and your results fit under the size caps, it does what it says on the tin - it's genuinely useful, if a little rough around the edges.

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.

The pragmatic alternative: chproxy

Here's the thing: the old stand-in never went away, and it already ships what those unmerged PRs promise. chproxy is a small Go proxy that sits in front of ClickHouse's HTTP interface and caches responses. Two of its knobs matter for this post:

  • A cache shared across replicas. Set the cache to mode: "redis" and every chproxy instance in front of every replica reads and writes the same Redis-backed cache. Redis is the only distributed backend; file_system mode is fine for a single node.
  • Stampede protection. grace_time (default 5s) is exactly the "wait for the first execution" behavior missing from the native cache. When a popular query misses, the first request registers a transaction (in Redis or in RAM) and runs it; concurrent identical queries wait on that transaction for up to grace_time and then reuse the result instead of re-running it.

Results are keyed per user unless shared_with_all_users: true (default false), max_payload_size keeps oversized results out of the cache, and every response carries an X-Cache: HIT or X-Cache: MISS header so you can see the hit rate from your dashboards' side.

A minimal config looks like this:

caches:
    - name: "dashboards"
      mode: "redis"
      redis:
        addresses: ["redis:6379"]
      expire: 60s
      grace_time: 5s
      shared_with_all_users: true
      max_payload_size: 50MiB
  
  users:
    - name: "grafana"
      to_cluster: "observability"
      to_user: "readonly"
      cache: "dashboards"

The trade-offs are real but familiar: an extra network hop on every query, it only fronts the HTTP interface (native-protocol clients bypass it), it's one more component to run and monitor, and it's still TTL-based - you're trading staleness for load just like with the native cache, only now the staleness is shared cluster-wide. Full details are in the chproxy caching docs.

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, pin queries to replicas so a stampede is contained to a single node rather than your whole cluster - and if you need cluster-wide caching with stampede protection today, not when the PRs merge, put chproxy in front of ClickHouse and let Redis and grace_time do the work.