The query cache in ClickHouse

ClickHouse can store the result of a SELECT and serve the next identical query from memory without touching the table. It is opt-in per query, it is a server-side cache (not a client one), and it trades freshness for speed. That trade is the whole design: a cached answer is correct for the moment it was computed, not for the moment you asked.

Examples here need a running server with the cache enabled. The settings names are checked against 26.4 but the timings are not repeated, because they depend entirely on your data.

Using it

Add use_query_cache = 1 to the query:

SELECT event_type, count(), avg(duration_ms)
FROM events
GROUP BY event_type
SETTINGS use_query_cache = 1;

The first run computes the result and stores it. The second identical run returns the stored result almost instantly and reads no rows. Running the same text without the setting always recomputes, and neither reads from nor writes to the cache.

Two switches let you control the direction separately:

• enable_writes_to_query_cache (default 1): may this query store its result?
• enable_reads_from_query_cache (default 1): may this query be answered from the cache?

A dashboard that should only consume what a background job already computed sets writes to 0. A job that refreshes the cache without consuming it sets reads to 0.

How long does an entry live?

query_cache_ttl is the entry lifetime in seconds. The default is 60.

SELECT count() FROM events
SETTINGS use_query_cache = 1, query_cache_ttl = 300;   -- cached for 5 minutes

Pick the TTL from the business tolerance for staleness, not from performance. A metric that is recomputed every five minutes by a pipeline can tolerate a five-minute TTL; a “number of orders right now” panel cannot. When the underlying table changes, the cache does not notice: entries simply expire.

What does it refuse to cache?

Non-deterministic queries. now(), rand() and friends change from call to call, so caching them is almost always a bug. By default ClickHouse throws:

The query result was not cached because the query contains a non-deterministic function.
Use setting `query_cache_nondeterministic_function_handling = 'save'` or `= 'ignore'` to cache the
query result regardless or to omit caching. (QUERY_CACHE_USED_WITH_NONDETERMINISTIC_FUNCTIONS)

Choose 'ignore' when the query should simply run uncached, or 'save' when you really mean it (for example a random sample you want to freeze). The same logic applies to queries against system tables (query_cache_system_table_handling).

Cheap or rare queries. To avoid filling the cache with things that are fast anyway:

• query_cache_min_query_duration: only cache queries that ran at least this long.
• query_cache_min_query_runs: only cache after the query has been seen this many times.

Who shares an entry?

An entry belongs to the user that created it, and a different user does not see it unless query_cache_share_between_users is on. That is the secure default: the cache would otherwise let one user read another’s result without going through row policies or grants. The cache key is the query itself (its syntax tree, so whitespace and comments do not matter) together with the user.

query_cache_tag is a free-text label stored with the entry. Use it to drop a group of entries:

SELECT ... SETTINGS use_query_cache = 1, query_cache_tag = 'dashboard-home';

SYSTEM DROP QUERY CACHE TAG 'dashboard-home';   -- just those
SYSTEM DROP QUERY CACHE;                        -- everything

Looking inside

SELECT query, result_size, stale, shared, compressed, expires_at
FROM system.query_cache;

system.events carries counters (QueryCacheHits, QueryCacheMisses) so you can see whether the cache is earning its keep. The cache has a size and entry limit in the server configuration (<query_cache> with max_size_in_bytes, max_entries, max_entry_size_in_bytes, max_entry_size_in_rows); if it is configured to size zero, nothing is ever stored.

When it helps

• Dashboards that fire the same expensive aggregate from many browsers.
• Reports whose underlying data is loaded in batches, so an answer is valid until the next load.

When it does not

• Queries that must be exactly current.
• Queries whose text changes every call (a timestamp literal in the WHERE), since the key will never repeat.
• Workloads already served well by a materialized view: pre-aggregation is stronger because it is correct, not just recent.