ClickHouse's Query Result Cache: A Hash of the AST, Not the Text

TL;DR: ClickHouse caches query results by hashing the parsed query, not the text you typed, plus the settings and database that could change the answer. Everything else - TTLs instead of invalidation, per-user quotas, an optional on-disk tier - follows from that one decision.

Every database has this feature and most of them get it half-right. The idea is old and simple: run the same query twice, serve the second one from memory.

The hard part was never the storage. It was the key.

Textual equality is hopeless. The same query can be spelled with different whitespace, different letter case, a different current database, or a different value of a setting that changes the answer. Meanwhile two queries that look different on the surface may produce identical rows, and you would love them to share an entry.

ClickHouse’s answer: hash the parsed query. That decision ripples through the whole design - the key construction, the TTL cache, the writer’s long list of reasons to refuse an entry, the reader’s access checks, and the on-disk backend that parks entries in a filesystem cache.

Let’s walk it end to end.

Why caching results is hard to get right

The header of the cache class states the contract plainly:

cpp
// src/Interpreters/Cache/QueryResultCache.h:48
/// Maps queries to query results. Useful to avoid repeated query calculation.
///
/// The cache does not aim to be transactionally consistent (which is difficult to get right). For example, the cache is not invalidated
/// when data is inserted/deleted into/from tables referenced by queries in the cache. In such situations, incorrect results may be
/// returned. In order to still obtain sufficiently up-to-date query results, a expiry time (TTL) must be specified for each cache entry
/// after which it becomes stale and is ignored. Stale entries are removed opportunistically from the cache, they are only evicted when a
/// new entry is inserted and the cache has insufficient capacity.

Read that again, because it is the whole correctness story: TTL, not invalidation. A result is valid for as long as its TTL says, and the entry is not invalidated when the underlying table changes.

This is a deliberate trade. Tracking table versions per entry would make every cache entry depend on the write path - and the write path is exactly the hot, latency-sensitive thing you do not want to slow down for the read path’s convenience.

The key: a hash of the AST, not of the text

The hashed part of the key is an IASTHash - a 128-bit hash of the parsed query:

cpp
// src/Interpreters/Cache/QueryResultCache.h:59
    struct Key
    {
        /// ----------------------------------------------------
        /// The actual key (data which gets hashed):

        /// The hash of the query AST.
        /// Unlike the query string, the AST is agnostic to lower/upper case (SELECT vs. select).
        IASTHash ast_hash;

Notice the comment. SELECT and select produce the same AST, so they produce the same hash, so they share one cache entry. That is the whole trick, and it falls out for free.

The AST is not hashed as-is, though. Two normalisations happen first.

Query-cache settings are stripped. A query that says SETTINGS use_query_cache = true should hit the entry written by the same query without that clause:

cpp
// src/Interpreters/Cache/QueryResultCache.cpp:500
ASTPtr removeQueryResultCacheSettings(ASTPtr ast)
{
    RemoveQueryResultCacheSettingsMatcher::Data visitor_data;
    RemoveQueryResultCacheSettingsVisitor(visitor_data).visit(ast);

    return ast;
}

And the predicate that decides which settings get pruned is deliberately generous about output-format settings, since those change how the answer is rendered, not what it is:

cpp
// src/Interpreters/Cache/QueryResultCache.cpp:413
bool settingDoesNotAffectQueryResultCache(const String & setting_name)
{
    return setting_name == "log_comment"
        /// As of today, the output format settings only affect the final output.
        /// However, it should be taken with caution - we should not use these settings in deterministic SQL functions.
        || setting_name.starts_with("output_format_")
        /// This setting is used to tune the server response, but does not affect the query behavior.
        /// An example why it should not affect query caching:
        /// - if you run a query as usual, and then run the same query with asking the server
        /// for Content-Disposition: attachment to download the result.
        || setting_name == "http_response_headers";
}

That last comment is a great one. Ask the server for the same rows as a download and you still want the same cached rows. Output format is not part of the result.

Planner-generated table aliases are removed for subqueries, so two identical subqueries that ended up under different plans share one entry. The pattern is matched exactly, because a user identifier that happens to look like __table1 must not be rewritten:

cpp
// src/Interpreters/Cache/QueryResultCache.cpp:513
bool isPlannerGeneratedTableAlias(std::string_view name)
{
    constexpr std::string_view prefix = "__table";
    if (!name.starts_with(prefix))
        return false;
    auto suffix = name.substr(prefix.size());
    if (suffix.empty())
        return false;
    for (char c : suffix)
    {
        if (c < '0' || c > '9')
            return false;
    }
    return true;
}

Strictly __table plus digits. No regex looseness, no user input sneaking through. This is the kind of detail that bites you years later if you get it wrong, and it took about four lines to get right.

What else goes into the hash

After the AST, the hash takes the current database and the changed settings:

cpp
// src/Interpreters/Cache/QueryResultCache.cpp:578
IASTHash calculateASTHash(ASTPtr ast, const String & current_database, const Settings & settings, const bool is_subquery, const bool pre_cleaned = false)
{
    if (!pre_cleaned)
    {
        ast = ast->clone();
        if (is_subquery)
            ast = removeTableAliases(ast);
    }
    ast = removeQueryResultCacheSettings(ast);

    /// Hash the AST, we must consider aliases (issue #56258)
    SipHash hash;
    ast->updateTreeHash(hash, /*ignore_aliases=*/ false);

    /// Also hash the database specified via SQL `USE db`, otherwise identifiers in same query (AST) may mean different columns in different
    /// tables (issue #64136)
    hash.update(current_database);

The current_database line is worth pausing on. An unqualified column name means different things in different databases, so the database is part of the identity of the answer. Hashing the AST alone would have been wrong here.

Then the settings, sorted, because Settings::changes() returns them in random order and a hash must be order-independent:

cpp
// src/Interpreters/Cache/QueryResultCache.cpp:596
    /// Finally, hash the (changed) settings as they might affect the query result (e.g. think of settings `additional_table_filters` and `limit`).
    /// Note: allChanged() returns the settings in random order. Also, update()-s of the composite hash must be done in deterministic order.
    /// Therefore, collect and sort the settings first, then hash them.
    auto changed_settings = settings.changes();
    std::vector<std::pair<String, String>> changed_settings_sorted; /// (name, value)
    for (const auto & change : changed_settings)
    {
        const String & name = change.name;
        if (!isSettingIgnoredInQueryResultCache(name)) /// see removeQueryResultCacheSettings() and isSubquerySpecificSetting() for why this is a good idea
            changed_settings_sorted.push_back({name, Settings::valueToStringUtil(change.name, change.value)});
    }

Only changed settings are hashed - a setting nobody touched cannot change the result relative to the default.

But there is a subtlety for subqueries, because the planner forcibly sets extremes, max_result_bytes and max_result_rows for them. Left alone, a subquery’s key would differ from the same query run at top level, and the cache would never hit:

cpp
// src/Interpreters/Cache/QueryResultCache.cpp:608
    /// The Planner forcibly sets `extremes`, `max_result_bytes`, `max_result_rows` for subqueries, which makes them
    /// appear in `settings.changes()`. Non-subquery executions of the same AST typically don't have these in their
    /// changed settings. To ensure cache hits between subquery writes and subquery reads, normalize by always
    /// including the default values of these settings for subqueries.
    if (is_subquery)
    {
        if (!changed_settings.tryGet("extremes"))
            changed_settings_sorted.push_back({"extremes", settings[Setting::extremes].toString()});

The equality of two keys

Now note what is not in the equality check. Two keys are equal if the AST hashes match and they are both subqueries or both not:

cpp
// src/Interpreters/Cache/QueryResultCache.cpp:715
bool QueryResultCache::Key::operator==(const Key & other) const
{
    return ast_hash == other.ast_hash && is_subquery == other.is_subquery;
}

The user id and the roles are not part of the key. They get checked at read time instead, which is why one entry per query is enough to still enforce access control. More on that below.

The hasher is trivial, because the AST hash is already a good hash:

cpp
// src/Interpreters/Cache/QueryResultCache.cpp:720
size_t QueryResultCache::KeyHasher::operator()(const Key & key) const
{
    SipHash hash;
    hash.update(key.ast_hash.low64);
    hash.update(key.is_subquery);
    return hash.get64();
}

What may be cached at all

Not every query may be cached. A query with a non-deterministic function (now(), rand()) would return a different answer the second time. The check walks the AST and asks the function factory:

cpp
// src/Interpreters/Cache/QueryResultCache.cpp:105
            if (const auto func = FunctionFactory::instance().tryGet(function->name, data.context))
            {
                if (!func->isDeterministic())
                    data.has_non_deterministic_functions = true;
                return;
            }

Two escape hatches get special care.

The eval table function hides its real query inside an opaque string, so ClickHouse cannot inspect it. What it cannot see, it must not trust:

cpp
// src/Interpreters/Cache/QueryResultCache.cpp:96
            /// The `eval` table function hides its real query inside an opaque string argument, so the
            /// generated query cannot be inspected here. Treat it as non-deterministic (and, in
            /// HasSystemTablesMatcher, as touching a system table) to keep such queries out of the cache,
            /// e.g. `eval('SELECT now()')` must not be cached as if it were deterministic.
            if (function->name == "eval")
            {
                data.has_non_deterministic_functions = true;
                return;
            }

And SQL UDFs, whose determinism ClickHouse simply does not know:

cpp
// src/Interpreters/Cache/QueryResultCache.cpp:111
            if (const auto udf_sql = UserDefinedSQLFunctionFactory::instance().tryGet(function->name))
            {
                /// Driver-created executable functions are also persisted in the SQL-object storage,
                /// but their determinism is described by the generated executable UDF configuration checked below.
                if (!udf_sql->as<ASTCreateFunctionWithDriverQuery>())
                {
                    /// ClickHouse currently doesn't know if SQL-based UDFs are deterministic or not. We must assume they are non-deterministic.
                    data.has_non_deterministic_functions = true;
                    return;
                }
            }

The obfuscate table function is subtler still: it is deterministic if a seed is given, and the seed can be overridden by a SETTINGS clause on any enclosing query. So the check has to descend into the AST while tracking the effective settings:

cpp
// src/Interpreters/Cache/QueryResultCache.cpp:309
    if (const auto * function = node->as<ASTFunction>())
        if (function->name == "obfuscate" && context->getSettingsRef()[Setting::obfuscate_seed].value.empty())
            return true;

What the user wants to happen on a violation is a setting, with three values (throw, save, ignore):

cpp
// src/Interpreters/Cache/QueryResultCache.cpp:371
        const QueryResultCacheNondeterministicFunctionHandling nondeterministic_function_handling
            = settings[Setting::query_cache_nondeterministic_function_handling];
        const QueryResultCacheSystemTableHandling system_table_handling = settings[Setting::query_cache_system_table_handling];

        if (ast_contains_nondeterministic_functions && nondeterministic_function_handling == QueryResultCacheNondeterministicFunctionHandling::Throw)
            throw Exception(ErrorCodes::QUERY_CACHE_USED_WITH_NONDETERMINISTIC_FUNCTIONS,
                "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 omit caching");

So:

sql
-- A non-deterministic query is refused by default:
SELECT * FROM events WHERE ts > now() - INTERVAL 1 HOUR;
-- ERROR: The query result was not cached because the query contains a non-deterministic function.

-- ... unless you insist:
SELECT * FROM events WHERE ts > now() - INTERVAL 1 HOUR
SETTINGS query_cache_nondeterministic_function_handling = 'save';

Gotcha: the default is throw, and the error message arrives after the query has already run. You paid for the computation, then threw the result away.

The cache itself

The cache is a TTL cache with per-user quotas and its own metrics:

cpp
// src/Interpreters/Cache/QueryResultCache.cpp:1180
QueryResultCache::QueryResultCache(size_t max_size_in_bytes, size_t max_entries, size_t max_entry_size_in_bytes_, size_t max_entry_size_in_rows_)
    : cache(std::make_unique<TTLCachePolicy<Key, Entry, KeyHasher, EntryWeight, IsStale>>(
            CurrentMetrics::QueryCacheBytes, CurrentMetrics::QueryCacheEntries, std::make_unique<PerUserTTLCachePolicyUserQuota>()))
{
    updateConfiguration(max_size_in_bytes, max_entries, max_entry_size_in_bytes_, max_entry_size_in_rows_);
}

Staleness is a pure function of the key:

cpp
// src/Interpreters/Cache/QueryResultCache.cpp:738
bool QueryResultCache::IsStale::operator()(const Key & key) const
{
    return (key.expires_at < std::chrono::system_clock::now());
};

The eviction policy is worth spelling out, because it is not an LRU. When an insert does not fit, the cache first purges the stale entries - the ones whose TTL has passed - and if that is still not enough, the insert is simply refused and the new result is dropped. The cache never evicts a fresh entry to make room for another fresh entry:

cpp
// src/Common/TTLCachePolicy.h:237
        if (!sufficient_space_in_cache() || !sufficient_space_in_cache_for_user())
        {
            /// Remove stale entries
            for (auto it = cache.begin(); it != cache.end();)
            {
                if (is_stale_function(it->first))
                {
                    size_t sz = weight_function(*it->second);
                    if (it->first.user_id.has_value())
                        Base::user_quotas->decreaseActual(*it->first.user_id, sz);
                    it = cache.erase(it);
                    size_in_bytes -= sz;
                }
                else
                    ++it;
            }
        }

That’s a deliberate refusal to thrash. A cache that evicts live entries to admit fresh ones can end up with a 0% hit rate under pressure. This one keeps its hot set and simply refuses new work when full.

The weight of an entry is the number of bytes its chunks actually allocate:

cpp
// src/Interpreters/Cache/QueryResultCache.cpp:728
size_t QueryResultCache::EntryWeight::operator()(const Entry & entry) const
{
    size_t res = 0;
    for (const auto & chunk : entry.chunks)
        res += chunk.allocatedBytes();
    res += entry.totals.has_value() ? entry.totals->allocatedBytes() : 0;
    res += entry.extremes.has_value() ? entry.extremes->allocatedBytes() : 0;
    return res;
}

Per-user quotas are pushed into the cache on every write, because there is no better hook for “the settings profile of this user changed”:

cpp
// src/Interpreters/Cache/QueryResultCache.cpp:1220
    /// Update the per-user cache quotas with the values stored in the query context. This happens per query which writes into the query
    /// cache. Obviously, this is overkill but I could find the good place to hook into which is called when the settings profiles in
    /// users.xml change.
    /// user_id == std::nullopt is the internal user for which no quota can be configured
    if (key.user_id.has_value())
        cache.setQuotaForUser(*key.user_id, max_query_result_cache_size_in_bytes_quota, max_query_result_cache_entries_quota);

The comment admits it: this is overkill. It works, but it is the kind of thing you notice on a Friday afternoon and want to fix. It never gets fixed.

The two backends

text
   query AST
      |  clone + strip cache settings + strip planner aliases
      |  hash(AST) + hash(current_database) + hash(changed settings)
      v
   QueryResultCache::Key  { ast_hash, user_id, roles, is_shared, ttl, header }
      |
      +--> in-memory TTLCachePolicy  <-- Chunks (optionally compressed)
      |        |  weight = allocated bytes, per-user quota
      |        v
      |     SourceFromChunks  --> the query pipeline
      |
      +--> on-disk: FileCacheKey(salted hash)  --> filesystem cache
               |  FixedHeader (magic, version, sizes, ttl, build fingerprint, checksum)
               |  AccessMetadata (is_shared, user, roles, tag)
               |  CompressedWriteBuffer + NativeWriter (header + chunks + totals + extremes)
               v
            filesystem cache (evicted by the same rules as any other data)

Writing: many reasons to refuse

The writer buffers the result chunks as they stream by and decides at the very end whether to keep them. That is deliberate - a query that dies half-way must not leave a partial entry:

cpp
// src/Interpreters/Cache/QueryResultCache.h:222
/// Buffers multiple partial query result chunks (buffer()) and eventually stores them as cache entry (finalizeWrite()).
///
/// Implementation note: Queries may throw exceptions during runtime, e.g. out-of-memory errors. In this case, no query result must be
/// written into the query result cache. Unfortunately, neither the Writer nor the special transform added on top of the query pipeline
/// which holds the Writer know whether they are destroyed because the query ended successfully or because of an exception (otherwise, we
/// could simply implement a check in their destructors). To handle exceptions correctly nevertheless, we do the actual insert in
/// finalizeWrite() as opposed to the Writer destructor. This function is then called only for successful queries in finish_callback() which
/// runs before the transform and the Writer are destroyed, whereas for unsuccessful queries we do nothing (the Writer is destroyed w/o
/// inserting anything).

Here’s the thing: the insert happens in finalizeWrite(), not in a destructor. Destructors cannot tell “the query finished” from “the query threw”, so the code routes the decision through a callback that only fires on success. That is a small piece of engineering taste that saves a lot of corrupted cache entries.

The list of refusal reasons in finalizeWrite is the interesting part:

cpp
// src/Interpreters/Cache/QueryResultCache.cpp:862
    if (result_is_incomplete)
    {
        LOG_TRACE(logger, "Skipped insert because the query result is incomplete (a consumer stopped reading it before the end), query: {}", doubleQuoteString(key.query_string));
        return;
    }

    if (auto query_runtime = std::chrono::duration_cast<std::chrono::milliseconds>(std::chrono::system_clock::now() - query_start_time); query_runtime < min_query_runtime)
    {
        LOG_TRACE(logger, "Skipped insert because the query is not expensive enough, query runtime: {} msec (minimum query runtime: {} msec), query: {}",
                query_runtime.count(), min_query_runtime.count(), doubleQuoteString(key.query_string));
        return;
    }

The second one is the classic “don’t cache the cheap queries” rule. Caching a 1 ms query costs more than recomputing it. The setting is query_cache_min_query_duration.

Partial chunks are squashed into blocks of max_block_size rows before being stored, so the cached result looks like a freshly produced one:

cpp
// src/Interpreters/Cache/QueryResultCache.cpp:889
    if (squash_partial_results)
    {
        /// Squash partial result chunks to chunks of size 'max_block_size' each. This costs some performance but provides a more natural
        /// compression of neither too small nor big blocks. Also, it will look like 'max_block_size' is respected when the query result is
        /// served later on from the query result cache.

And the entries are compressed, at the cost of a decompression on read:

cpp
// src/Interpreters/Cache/QueryResultCache.cpp:969
    if (key.is_compressed)
    {
        /// Compress result chunks. Reduces the space consumption of the cache but means reading from it will be slower due to decompression.

        Chunks compressed_chunks;

        for (const auto & chunk : query_result->chunks)
        {
            const Columns & columns = chunk.getColumns();
            Columns compressed_columns;
            for (const auto & column : columns)
            {
                auto compressed_column = column->compress(/*force_compression=*/false);
                compressed_columns.push_back(compressed_column);
            }
            Chunk compressed_chunk(compressed_columns, chunk.getNumRows());
            compressed_chunks.push_back(std::move(compressed_chunk));
        }
        query_result->chunks = std::move(compressed_chunks);
    }

column->compress(force_compression=false) is the interesting bit: a column is compressed only if it is worth it. A tiny column of random UInt64s costs more CPU than it saves in RAM, so it is stored as-is.

Finally the size limits are checked against the compressed weight:

cpp
// src/Interpreters/Cache/QueryResultCache.cpp:992
    size_t new_entry_size_in_bytes = QueryResultCache::EntryWeight()(*query_result);
    size_t new_entry_size_in_rows = count_rows_in_chunks(*query_result);

    if ((new_entry_size_in_bytes > max_entry_size_in_bytes) || (new_entry_size_in_rows > max_entry_size_in_rows))
    {
        LOG_TRACE(logger, "Skipped insert because the query result is too big, query result size: {} (maximum size: {}), query result size in rows: {} (maximum size: {}), query: {}",
                formatReadableSizeWithBinarySuffix(new_entry_size_in_bytes, 0), formatReadableSizeWithBinarySuffix(max_entry_size_in_bytes, 0), new_entry_size_in_rows, max_entry_size_in_rows, doubleQuoteString(key.query_string));
        return;
    }

    cache.set(key, query_result);

Reading: access control

The reader looks the key up and then checks whether the caller may have it. The user id and the current roles live in the key but are not part of the equality check, so the same entry can be found by a different user - and then rejected:

cpp
// src/Interpreters/Cache/QueryResultCache.cpp:1067
    const auto & entry_key = entry->key;
    const auto & entry_mapped = entry->mapped;

    const bool is_same_user_id = ((!entry_key.user_id.has_value() && !key.user_id.has_value()) || (entry_key.user_id.has_value() && key.user_id.has_value() && *entry_key.user_id == *key.user_id));
    const bool is_same_current_user_roles = (entry_key.current_user_roles == key.current_user_roles);
    if (!entry_key.is_shared && (!is_same_user_id || !is_same_current_user_roles))
    {
        LOG_TRACE(logger, "Inaccessible query result found for query {}", doubleQuoteString(key.query_string));
        return;
    }

The rationale for checking the roles and not just the user is in the key’s comment:

cpp
// src/Interpreters/Cache/QueryResultCache.h:78
        /// The id and current roles of the user who executed the query.
        /// These members are necessary to ensure that a (non-shared, see below) entry can only be written and read by the same user with
        /// the same roles. Example attack scenarios:
        /// - after DROP USER, it must not be possible to create a new user with with the dropped user name and access the dropped user's
        ///   query result cache entries
        /// - different roles of the same user may be tied to different row-level policies. It must not be possible to switch role and
        ///   access another role's cache entries
        std::optional<UUID> user_id;
        std::vector<UUID> current_user_roles;

Read that as two attack scenarios. First: drop a user, recreate someone with the same name, and try to read the old user’s cached rows. Second: one user, two roles, different row-level policies - switch role, keep the rows.

Storing roles in the key but not in the equality check is the clever part. The entry stays shareable by identity, and access control happens on the way out.

A stale entry is also refused:

cpp
// src/Interpreters/Cache/QueryResultCache.cpp:1078
    if (QueryResultCache::IsStale()(entry_key))
    {
        LOG_TRACE(logger, "Stale query result found for query {}", doubleQuoteString(key.query_string));
        return;
    }

And the chunks are cloned (uncompressed case) or decompressed (compressed case) before being handed to a SourceFromChunks processor, which becomes the new head of the query pipeline.

The on-disk backend

The in-memory cache is bounded by RAM. The on-disk backend stores entries in a filesystem cache - the same infrastructure used for remote table data - which means the entries can be much larger, survive restarts, and (with a distributed filesystem cache) be shared between servers.

cpp
// src/Interpreters/Cache/QueryResultCacheOnDisk.h:14
/// Stores query result cache entries in a preconfigured filesystem cache (an entry of the `filesystem_caches` section of the server
/// configuration), so that cached query results get more space than in memory, survive server restarts, and (with a distributed
/// filesystem cache) can be shared between servers. The filesystem cache is selected per query with setting
/// `query_cache_on_disk_cache_name`.
///
/// The entries are ordinary filesystem cache entries, indistinguishable from other data in the same filesystem cache: they are not
/// held from deletion, they are evicted by the same rules, and there are no specific limits on their total size or number other than
/// the limits of the filesystem cache itself. The keys are hashed with a salt so that they cannot intersect with other keys in the
/// filesystem cache. All metadata needed to validate an entry (TTL, access control) is serialized into the entry itself; no in-memory
/// state is kept.

Reusing the filesystem cache is a lovely bit of leverage. You inherit its eviction, its checksumming, its remote-storage support, and its cluster plumbing, instead of inventing a parallel universe.

The key

The on-disk key is a salted hash of the AST hash, plus the access context for non-shared entries:

cpp
// src/Interpreters/Cache/QueryResultCacheOnDisk.cpp:136
FileCacheKey makeFileCacheKey(const QueryResultCache::Key & key, bool is_shared)
{
    /// Salt the hash so that keys of the on-disk query result cache cannot intersect with other keys in the same filesystem cache
    /// (those are typically derived from storage paths).
    SipHash hash;
    hash.update(std::string_view("QueryResultCacheOnDisk"));
    hash.update(key.ast_hash.low64);
    hash.update(key.ast_hash.high64);
    hash.update(key.is_subquery);
    hash.update(is_shared);
    if (!is_shared)
    {
        hash.update(key.user_id.has_value());
        if (key.user_id)
            hash.update(key.user_id->toUnderType());
        /// The roles are compared as an ordered sequence, same as in the in-memory query result cache.
        hash.update(key.current_user_roles.size());
        for (const auto & role : key.current_user_roles)
            hash.update(role.toUnderType());
    }

Note that unlike the in-memory cache, the on-disk key does include the user. The filesystem cache is shared storage, so one user’s entry must never shadow another’s - there is no “check on read” step in shared storage, so identity has to be baked into the key itself.

A marker in the middle of the key lets the cache find its own entries without reading anything from disk:

cpp
// src/Interpreters/Cache/QueryResultCacheOnDisk.cpp:103
/// Bits 64..95 of every key of the on-disk query result cache hold this marker ("QRCC"), so that the entries can be told apart from
/// the other keys of the filesystem cache by the key alone, without reading anything from disk (see `clear`). The marker sits in the
/// middle of the key on purpose: the filesystem cache derives the directory of a key from its topmost bits and the metadata bucket
/// from its lowest bits, both of which must keep varying. The remaining 96 bits of hash are plenty to keep the entries apart. A key
/// of other data carries the marker by chance with probability 2^-32; such a key is still recognized as foreign by its content.
constexpr UInt32 KEY_MARKER = 0x51524343;

That comment is a small masterpiece of constraint juggling. The marker cannot sit at the top (the directory is derived from the top bits) and cannot sit at the bottom (the metadata bucket is derived from the lowest bits), so it goes in the middle - and it admits the 2^-32 collision chance right there.

The entry format

The on-disk entry has a fixed-size header, then uncompressed access metadata, then the compressed payload:

cpp
// src/Interpreters/Cache/QueryResultCacheOnDisk.cpp:63
/// On-disk entry layout, version 6:
///
///     Fixed header (FIXED_HEADER_SIZE bytes):
///         char[8]  magic "QRCache1"
///         UInt32   format version
///         UInt32   TCP protocol revision the Native blocks are serialized with
///         UInt64   total entry size in bytes, including the fixed header
///         UInt64   created_at, seconds since epoch
///         UInt64   expires_at, seconds since epoch
///         UInt64   fingerprint of the server build which wrote the entry (see `getServerBuildFingerprint`)
///         UInt128  SipHash-128 of the preceding fixed header bytes and of everything after the fixed header. The freshness
///                  fields are authenticated as well: a corruption of `expires_at` or `total_size` alone would otherwise
///                  silently change how long the entry is served, without ever failing the integrity check.
///
///     Access metadata (uncompressed):
///         UInt8    is_shared
///         UInt8    has_user_id, [UInt128 user_id]
///         varUInt  number of user roles, [UInt128 role]...
///         String   tag (setting `query_cache_tag`), so that `SYSTEM DROP QUERY CACHE TAG` can find the entry
///
///     Result payload (compressed with the codec from setting `query_cache_on_disk_codec`; the compression frames are
///     self-describing, so reading does not depend on the setting):
///         varUInt  number of result chunks
///         Native   header block (zero rows)
///         Chunk    one per result chunk
///         UInt8    has_totals, [Chunk]
///         UInt8    has_extremes, [Chunk]

Two details are worth dwelling on.

The build fingerprint. An entry is only valid for the server build that wrote it, because a new build may compute a different answer for the same query:

cpp
// src/Interpreters/Cache/QueryResultCacheOnDisk.cpp:110
/// The entries survive restarts and can be shared between servers, but a result is only valid for the server build which computed
/// it: a newer or older build may compute a different result for the same query (fixes of functions or of the planner, changed
/// defaults), even when neither the TCP protocol revision nor the entry format changes. So every entry records the build which wrote
/// it, and an entry of another build is treated like an entry of an incompatible format: it is never served, and it is overwritten
/// on the next write.

The checksum covers the freshness fields. Otherwise a corrupted expires_at would silently change how long the entry lives - and a bit flip in the wrong byte would be invisible.

The header is patched in place after the payload is serialised, and the checksum is computed over the header and the body:

cpp
// src/Interpreters/Cache/QueryResultCacheOnDisk.cpp:514
        unalignedStoreLittleEndian<UInt64>(data.data() + TOTAL_SIZE_OFFSET_IN_FIXED_HEADER, data.size());

        SipHash hash;
        hash.update(data.data(), CHECKSUM_OFFSET_IN_FIXED_HEADER);
        hash.update(data.data() + FIXED_HEADER_SIZE, data.size() - FIXED_HEADER_SIZE);
        UInt128 checksum = hash.get128();
        transformEndianness<std::endian::little>(checksum);
        memcpy(data.data() + CHECKSUM_OFFSET_IN_FIXED_HEADER, &checksum, sizeof(checksum));

Probing and writing

Writing is best-effort and never throws. Before writing, the existing entry is probed to see whether it is fresh, being written concurrently, or garbage:

cpp
// src/Interpreters/Cache/QueryResultCacheOnDisk.cpp:365
QueryResultCacheOnDisk::ProbeResult QueryResultCacheOnDisk::probeExistingEntry(const FileCacheKey & cache_key) const
{
    const auto & user_id = FileCache::getCommonOrigin().user_id;

    auto holder = file_cache->getDownloadedContiguousOrEmpty(cache_key, 0, FIXED_HEADER_SIZE, user_id);
    for (size_t attempt = 0; holder->empty(); ++attempt)
    {
        /// No readable header: either there is no entry, or another query is writing one (its segments are held and not
        /// downloaded yet), or only the tail of a partially evicted entry is left. The tail must be removed, otherwise no
        /// entry could be created for the key until it is evicted too.
        const auto segments = file_cache->tryGetFileSegmentInfos(cache_key, user_id);
        if (segments.empty())
            return ProbeResult::None;
        for (const auto & segment : segments)
        {
            if (segment.state != FileSegment::State::DOWNLOADED || segment.references > 1)
                return ProbeResult::InProgress;
        }

The write itself reserves the space for all the segments of the entry before writing any of them, so an entry is either complete or absent:

cpp
// src/Interpreters/Cache/QueryResultCacheOnDisk.cpp:529
    /// Create all file segments of the entry at once. This fails gracefully if another query created an entry for the same key in
    /// the meantime (then we simply skip the insert), and guarantees that we exclusively own all segments of the key while writing.
    /// A reader sees the incomplete entry as a miss because not all of its bytes are downloaded yet.
    auto holder = file_cache->trySet(cache_key, 0, data.size(), CreateFileSegmentSettings(FileSegmentKind::Regular), origin);
    if (!holder)
    {
        LOG_TRACE(logger, "Skipped insert into the on-disk query result cache because of a concurrent insert for query {}",
            doubleQuoteString(key.query_string));
        return;
    }

Two concurrent writers, one winner, no corruption. The loser just skips the insert. That is a much nicer failure mode than the usual write-write race.

The reader side is symmetric: it downloads the whole entry, verifies the checksum, and only then decompresses it.

Using it

The cache is off by default; you turn it on per query or per session:

xml
<!-- Server configuration: a 1 GiB in-memory query result cache. -->
<query_cache>
    <max_size_in_bytes>1073741824</max_size_in_bytes>
    <max_entries>1024</max_entries>
    <max_entry_size_in_bytes>1048576</max_entry_size_in_bytes>
    <max_entry_size_in_rows>30000000</max_entry_size_in_rows>
</query_cache>

sql
SELECT count() FROM hits WHERE UserID = 42 SETTINGS use_query_cache = true;
-- the second execution is served from the cache

The per-user quota is a setting, not a config element, so it can differ per profile: query_cache_max_size_in_bytes and query_cache_max_entries.

The observable state lives in system tables:

sql
SELECT query_id, length, hits, QueryCacheHits, QueryCacheMisses FROM system.query_log LIMIT 10;
SELECT query, result_size, tag, stale, shared, compressed, expires_at FROM system.query_result_cache LIMIT 10;

SYSTEM CLEAR QUERY CACHE clears everything, SYSTEM CLEAR QUERY CACHE TAG '<tag>' clears one tag’s worth.

Trade-offs

What it buys: a dashboard that runs the same heavy aggregate every 10 seconds stops paying for the aggregate, and the server’s CPU goes to the queries that are actually different. If you have ever watched a Grafana dashboard redraw in lockstep with 40 open tabs asking for the same number, this is the fix.

What it costs:

Staleness is unbounded up to the TTL. The cache is not invalidated on INSERT. A TTL of 1 hour means an hour-old answer. This is the documented contract, not a bug, but it is the reason the cache is opt-in.
The key is a hash, so it inherits hash-collision risk. Two different queries with the same 128-bit AST hash would share an entry. In practice this does not happen; there is no secondary check of the query text.
Memory is the scarce resource. The in-memory cache holds result data, not plans, and the results are held compressed. A server with a 1 GiB cache can easily be asked for 10 GiB of results; the size limits (max_entry_size_in_bytes, max_entry_size_in_rows) exist to keep one huge result from evicting everything else.
Compression is a trade, not a win. The default query_cache_compress_entries makes reads slower and saves RAM; on a server that is memory-bound it is right, on one that is CPU-bound it is wrong.
The on-disk backend has its own failure modes: partially evicted entries, entries written by a different build, a filesystem cache that is not yet initialised. All of them are handled by treating the entry as a miss, and the probe-then-write dance means two concurrent writers cannot corrupt each other - one of them simply loses.

Wrapping up

The one thing to remember: the cache’s hardest problem - the key - is solved by ClickHouse’s parser rather than by the cache. Hashing the AST instead of the text means select and SELECT share an entry, and the planner’s internal alias noise never leaks into the key.

Worth internalizing: this is a cache that is allowed to be wrong, on purpose. TTLs instead of invalidation is a choice, and the price is an answer that can be up to a TTL old. Pick the TTL like you mean it.

What to go do about it: if you have a dashboard, an ORM that generates the same SQL every 30 seconds, or a report job that hammers the same aggregate - turn on use_query_cache for exactly those queries, set a short TTL, and check system.query_result_cache to see whether the hit rate justifies the memory.