Accelerate query performance with ClickHouse query cache

ClickHouse is designed for high-performance analytical workloads, routinely scanning large volumes of data and performing massive aggregations with exceptionally low latency. However, in real-world production environments, especially dashboards, reporting tools, and Business Intelligence (BI) workloads, the exact same queries are often executed repeatedly.

For instance, a dashboard might auto-refresh every 30 seconds, multiple concurrent users might open the identical report, or an application might repeatedly request the same aggregated metrics. In these scenarios, repeatedly re-computing the same result set can lead to unnecessary strain on CPU, memory, and I/O resources.

This is exactly where the ClickHouse query cache comes into play. It allows ClickHouse to compute a SELECT query once and serve subsequent executions directly from memory. By bypassing the most expensive execution stages for repeated identical requests, it drastically reduces query latency and frees up server resources for other queries and workloads.

What is query cache?

The query cache was introduced in ClickHouse version 23.1 as an experimental feature. Subsequent releases improved its stability and functionality, making it suitable for production analytical workloads from version 23.5 onwards. It stores the result of SELECT queries in memory. When the same query is executed again with Query Cache enabled, ClickHouse returns the cached result directly from memory, bypassing storage reads and expensive query execution stages.

Query workflow without and with query cache:

1. Query execution without query cache

Without Query Cache, ClickHouse executes the full query workflow for every request, including query planning, data access, and result processing. Repeated execution of identical queries can therefore consume additional CPU, memory, and I/O resources.

2.Query execution with query cache

When Query Cache is enabled, ClickHouse first parses the incoming SELECT query and builds an Abstract Syntax Tree (AST), which is an internal representation of the query structure. ClickHouse then performs a Query Cache lookup to determine whether a valid cached result exists for that query.

If a valid cache entry is found, this is a cache hit. In this case, ClickHouse returns the cached result to the client and avoids expensive execution steps such as query planning, partition pruning, storage reads, filtering, aggregation, sorting, and final result generation.

If no matching cache entry exists, this is a cache miss. ClickHouse then continues with the normal query execution process, including query planning, partition and index pruning, data access, filtering, aggregation, and sorting. Once the result set is generated, it is stored in the Query Cache and returned to the client. Subsequent executions of the same query can then reuse the cached result until the cache entry expires.

How to enable Query Cache:

The query cache is not enabled by default. To use it, the use_query_cache setting must be enabled. This can be done at the query level, session level, or through a user/profile-level setting.

The recommended approach is to enable Query Cache selectively at the query level for workloads where repeated executions of identical queries are expected. Session-level or profile-level enablement can be useful for reporting and dashboard workloads but should be evaluated carefully to avoid unnecessary cache utilization.

1. Enable at query level: To enable query cache for a specific SELECT query, add the SETTINGS clause at the end of the query:
   
   SELECT count(), sum(salary) FROM employees WHERE start_date >= '2026-01-01' AND department = 'HR' SETTINGS use_query_cache = 1;

   1 2 3 4

   SELECT count(), sum(salary) FROM employees WHERE start_date >= '2026-01-01' AND department = 'HR' SETTINGS use_query_cache = 1;

   
   This is the safest and most controlled approach because only this query is allowed to use the query cache.
2. Enable at session level: To enable query cache for all eligible SELECT queries in the current session, set use_query_cache before running the queries:
   
   SET use_query_cache = 1; -- All queries in this session will now use the cache SELECT * FROM employees WHERE status = 'active';

   1 2 3 4

   SET use_query_cache = 1;   -- All queries in this session will now use the cache SELECT * FROM employees WHERE status = 'active';

   
   This setting applies only to the current session. It is useful for testing or for specific workloads, but it should be used carefully because all eligible queries in that session may start using cached results.
3. Configure globally via User/Profile: Query Cache can also be enabled through a user profile in users.xml. This makes it active by default for users assigned to that profile. Any eligible SELECT query executed by users assigned to this profile can utilise Query Cache without requiring SETTINGS use_query_cache=1 in individual queries
   
   <clickhouse> <profiles> <default> <use_query_cache>1</use_query_cache> <query_cache_ttl>300</query_cache_ttl> </default> </profiles> </clickhouse>

   1 2 3 4 5 6 7 8

   <clickhouse>   <profiles>     <default>       <use_query_cache>1</use_query_cache>       <query_cache_ttl>300</query_cache_ttl>     </default>   </profiles> </clickhouse>

   This approach should be used carefully because it may cause many queries from that user/profile to use query cache.

Configure Query Cache Capacity

You should also size the cache at the server level in config.xml. Unlike the previous options, these settings do not enable query cache usage. They define the amount of memory available to the query cache and the limits for cached entries.

Important: Query Cache is local to each ClickHouse server process. In a distributed cluster, cache entries are not synchronized or shared between nodes. As a result, the same query executed on different servers may result in different cache hit rates. Ensure cache sizing and profile settings are applied consistently across all nodes where Query Cache is expected to be used.

Key Query Cache Settings and Server-Level Capacity Parameters

Important: Not all query cache settings are configured at the same level. Query Cache behaviour settings (such as use_query_cache and query_cache_ttl) can be configured at the query, session, or profile level. In contrast, query cache capacity parameters (such as max_size_in_bytes and max_entries) are server-level settings configured in config.xml.

Monitoring Query Cache

Effective monitoring helps ensure that query cache delivers the expected performance benefits while making efficient use of available resources.

1. Check current query cache entries:
   
   SELECT query, result_size, stale, shared, compressed, expires_at, key_hash FROM system.query_cache ORDER BY expires_at DESC;

   1 2	SELECT query, result_size, stale, shared, compressed, expires_at, key_hash FROM system.query_cache ORDER BY expires_at DESC;

   This helps identify which queries are stored in the cache and how much space they consumed.

   Outcome review: Review the result_size column to understand how much cache space each cached query consumes. A large number of entries with very small result sizes may indicate that cache space is being used by queries that provide limited caching benefit. Conversely, a small number of very large entries may consume a disproportionate amount of cache memory, reducing cache effectiveness for other queries.

   The expires_at and stale columns should also be reviewed to understand cache freshness and utilisation. If entries expire before they are reused, the TTL may be too short, or the workload may not be a good fit for query cache. On the other hand, if entries remain valid for an extended period, organisations should confirm that the associated data freshness trade-off is acceptable.

2. Check Query Cache Size:
   
   
   SELECT metric, value FROM system.metrics WHERE metric IN ('QueryCacheEntries', 'QueryCacheBytes');

   1 2 3 4 5

   SELECT     metric,     value FROM system.metrics WHERE metric IN ('QueryCacheEntries', 'QueryCacheBytes');

   • QueryCacheEntries: Displays the number of current cache entries.
   • QueryCacheBytes: Displays the current amount of memory used by the query cache.

   Note: ClickHouse exposes these metrics in system.metrics.

   Outcome review: These metrics help determine whether the allocated cache size is being utilised effectively. Consistently low values may indicate that Query Cache is underutilized, while consistently high memory usage combined with low cache hit rates may suggest that cache entries are being evicted before they can provide value. Monitoring these metrics over time helps identify appropriate cache sizing for your workload.

3. Check Cache Hits and Misses:
   
   
   SELECT event, value FROM system.events WHERE event IN ('QueryCacheHits', 'QueryCacheMisses');

   1 2 3 4 5

   SELECT     event,     value FROM system.events WHERE event IN ('QueryCacheHits', 'QueryCacheMisses');

   This gives a high-level view of whether queries are being served from cache or missing the cache.

   Outcome review: A growing number of query cache hits generally indicates that workloads are benefiting from cached results. If cache misses significantly outnumber hits over time, consider reviewing query patterns, cache TTL settings, and whether the workload contains enough repeated queries to benefit from query cache.

4. Check per-query cache behavior using system.query_log:
   
   
   SELECT event_time, query, result_rows, query_cache_usage, ProfileEvents['QueryCacheHits'] AS cache_hits, ProfileEvents['QueryCacheMisses'] AS cache_misses FROM system.query_log WHERE type = 'QueryFinish' AND event_time >= now() - INTERVAL 1 HOUR AND query_cache_usage IN ('Read', 'Write') ORDER BY event_time DESC LIMIT 10;

   1 2 3 4 5 6 7 8 9 10 11 12 13

   SELECT     event_time,     query,     result_rows,     query_cache_usage,     ProfileEvents['QueryCacheHits'] AS cache_hits,     ProfileEvents['QueryCacheMisses'] AS cache_misses FROM system.query_log WHERE type = 'QueryFinish'   AND event_time >= now() - INTERVAL 1 HOUR   AND query_cache_usage IN ('Read', 'Write') ORDER BY event_time DESC LIMIT 10;

   This query shows recent queries that interacted with the Query Cache.

   • query_cache_usage: Indicates whether the result was read from the cache (Read) or written into the cache (Write).
   • ProfileEvents: Provides additional cache hit/miss counters for each completed query.

   Outcome review: Using query_cache_usage IN ('Read', 'Write') limits the output to queries that interacted with the query cache, making it easier to identify which queries are benefiting from cached results and which queries are generating new cache entries. This information can help validate cache effectiveness and identify workloads that benefit most from query cache. The cache_hits and cache_misses values provide additional insight into cache effectiveness for each completed query. Users can use this information to identify which dashboards, reports, or analytical workloads benefit most from query cache and where cache settings, TTL, or query patterns may need adjustment.

Conclusion

ClickHouse query cache is a valuable feature for improving the performance of repeated SELECT queries by reusing previously computed results. When configured appropriately, it can significantly reduce query execution overhead, lower resource consumption, and improve response times for repetitive queries.

However, query cache should be used intentionally. It is best suited for dashboards, BI reports, recurring analytical queries, and workloads where the same queries are executed repeatedly. It is not a substitute for good schema design, efficient queries, appropriate partitioning, or effective primary-key filtering.

Key Takeaways

• Query Cache usage must be enabled through use_query_cache at the query, session, or profile level.
• Cache capacity and limits are configured separately using the <query_cache> section in the server configuration (config.xml).
• Settings such as query_cache_min_query_duration and query_cache_min_query_runs can help prevent short-running or one-off queries from occupying cache space.
• Monitoring cache hits, misses, memory consumption, and per-query cache behaviour is essential to confirm that Query Cache is delivering measurable benefits for your workload.

Combined with the operational expertise and managed platform capabilities of NetApp Instaclustr for ClickHouse, organisations can confidently optimise performance, improve scalability, and focus on deriving greater value from their data.

For more information, contact the NetApp Instaclustr for ClickHouse experts or start a free Instaclustr trial.