ClickHouse Query Cache: Results

Tip: Enable Query Cache

SELECT * FROM events SETTINGS use_query_cache = 1;

Gotcha: Cache Key

The cache key is the exact query text. Whitespace changes invalidate the cache.

Tip: Cache TTL

SET query_cache_ttl = 60;

Cache results for 60 seconds.

Gotcha: Non-Deterministic Queries

Queries with now(), rand(), etc. are not cached by default.

Tip: Force Cache

SELECT * FROM events SETTINGS use_query_cache = 1, query_cache_ttl = 300;

Gotcha: Cache Size

<query_cache_max_size>1073741824</query_cache_max_size>

1GB default. Increase for more cached queries.

Tip: Order of Columns in ORDER BY Matters Massively

ClickHouse's primary key is defined by ORDER BY. Put high-cardinality columns first for better data skipping. ORDER BY (timestamp, user_id) is very different from ORDER BY (user_id, timestamp) in query performance.

Tip: Use LowCardinality for Enum-Like Strings

Strings like status, country, browser benefit from LowCardinality(String) — it's stored as a dictionary internally, reducing storage 10x and speeding up scans.

Gotcha: Mutations Are Heavy

ALTER TABLE ... UPDATE and DELETE in ClickHouse create new parts instead of modifying in place. A single mutation on a large table can take hours and block merges. Design for append-only from day one.

Senior Insight

ClickHouse's query cache (since 22.8) caches query results in memory based on the query text. I use it for dashboard queries that run repeatedly with the same parameters. The important setting: use_query_cache = true enables caching for specific queries, and query_cache_ttl controls cache duration. The gotcha: the cache is invalidated when the underlying data changes, so it's most useful for slowly-changing dimension data, not real-time event data.

Source: ClickHouse Blog (https://clickhouse.com/blog), Altinity Blog (https://altinity.com/blog), Altinity Knowledge Base (https://kb.altinity.com/)