> ## Documentation Index > Fetch the complete documentation index at: https://private-7c7dfe99-parallel-read-in-order-multi-part.mintlify.site/llms.txt > Use this file to discover all available pages before exploring further. > Guide to using and configuring the query cache feature in ClickHouse # Query cache The query cache allows to compute `SELECT` queries just once and to serve further executions of the same query directly from the cache. Depending on the type of the queries, this can dramatically reduce latency and resource consumption of the ClickHouse server.

Background, design and limitations

Query caches can generally be viewed as transactionally consistent or inconsistent. * In transactionally consistent caches, the database invalidates (discards) cached query results if the result of the `SELECT` query changes or potentially changes. In ClickHouse, operations which change the data include inserts/updates/deletes in/of/from tables or collapsing merges. Transactionally consistent caching is especially suitable for OLTP databases, for example [MySQL](https://dev.mysql.com/doc/refman/5.6/en/query-cache.html) (which removed query cache after v8.0) and [Oracle](https://docs.oracle.com/database/121/TGDBA/tune_result_cache.htm). * In transactionally inconsistent caches, slight inaccuracies in query results are accepted under the assumption that all cache entries are assigned a validity period after which they expire (e.g. 1 minute) and that the underlying data changes only little during this period. This approach is overall more suitable for OLAP databases. As an example where transactionally inconsistent caching is sufficient, consider an hourly sales report in a reporting tool which is simultaneously accessed by multiple users. Sales data changes typically slowly enough that the database only needs to compute the report once (represented by the first `SELECT` query). Further queries can be served directly from the query cache. In this example, a reasonable validity period could be 30 min. Transactionally inconsistent caching is traditionally provided by client tools or proxy packages (e.g. [chproxy](https://www.chproxy.org/configuration/caching/)) interacting with the database. As a result, the same caching logic and configuration is often duplicated. With ClickHouse's query cache, the caching logic moves to the server side. This reduces maintenance effort and avoids redundancy.

Configuration settings and usage

In ClickHouse Cloud, you must use [query level settings](/concepts/features/configuration/settings/settings-query-level) to edit query cache settings. Editing [config level settings](/concepts/features/configuration/server-config/configuration-files) is currently not supported. [clickhouse-local](/concepts/features/tools-and-utilities/clickhouse-local) runs a single query at a time. Since caching query results in memory does not make sense for a single process, the in-memory query cache is disabled in clickhouse-local. The [query cache on disk](#query-cache-on-disk) is available: its entries live in a filesystem cache on disk, so results cached by one clickhouse-local process can be served to later ones which use the same filesystem cache directory. Setting [use\_query\_cache](/reference/settings/session-settings/use-query#use_query_cache) can be used to control whether a specific query or all queries of the current session should utilize the query cache. For example, the first execution of query ```sql theme={null} SELECT some_expensive_calculation(column_1, column_2) FROM table SETTINGS use_query_cache = true; ``` will store the query result in the query cache. Subsequent executions of the same query (also with parameter `use_query_cache = true`) will read the computed result from the cache and return it immediately. Setting `use_query_cache` and all other query-cache-related settings only take an effect on stand-alone `SELECT` statements. In particular, the results of `SELECT`s to views created by `CREATE VIEW AS SELECT [...] SETTINGS use_query_cache = true` are not cached unless the `SELECT` statement runs with `SETTINGS use_query_cache = true`. The way the cache is utilized can be configured in more detail using settings [enable\_writes\_to\_query\_cache](/reference/settings/session-settings/enable#enable_writes_to_query_cache) and [enable\_reads\_from\_query\_cache](/reference/settings/session-settings/enable#enable_reads_from_query_cache) (both `true` by default). The former setting controls whether query results are stored in the cache, whereas the latter setting determines if the database should try to retrieve query results from the cache. For example, the following query will use the cache only passively, i.e. attempt to read from it but not store its result in it: ```sql theme={null} SELECT some_expensive_calculation(column_1, column_2) FROM table SETTINGS use_query_cache = true, enable_writes_to_query_cache = false; ``` For maximum control, it is generally recommended to provide settings `use_query_cache`, `enable_writes_to_query_cache` and `enable_reads_from_query_cache` only with specific queries. It is also possible to enable caching at user or profile level (e.g. via `SET use_query_cache = true`) but one should keep in mind that all `SELECT` queries may return cached results then. The query cache can be cleared using statement `SYSTEM CLEAR QUERY CACHE`. The content of the query cache is displayed in system table [system.query\_cache](/reference/system-tables/query_cache). The number of query cache hits and misses since database start are shown as events "QueryCacheHits" and "QueryCacheMisses" in system table [system.events](/reference/system-tables/events). Both counters are only updated for `SELECT` queries which run with setting `use_query_cache = true`, other queries do not affect "QueryCacheMisses". They describe the query cache as a whole: with the query cache on disk enabled (see below), a query that misses in memory and is then served from disk counts as one "QueryCacheHits", and a query that misses in both backends counts as one "QueryCacheMisses". Field `query_cache_usage` in system table [system.query\_log](/reference/system-tables/query_log) shows for each executed query whether the query result was written into or read from the query cache. Metrics `QueryCacheEntries` and `QueryCacheBytes` in system table [system.metrics](/reference/system-tables/metrics) show how many entries / bytes the in-memory query cache currently contains. They do not account for the entries of the [query cache on disk](#query-cache-on-disk), which currently has no occupancy metrics. The query cache exists once per ClickHouse server process. However, cache results are by default not shared between users. This can be changed (see below) but doing so is not recommended for security reasons. Query results are referenced in the query cache by the [Abstract Syntax Tree (AST)](https://en.wikipedia.org/wiki/Abstract_syntax_tree) of their query. This means that caching is agnostic to upper/lowercase, for example `SELECT 1` and `select 1` are treated as the same query. To make the matching more natural, all query-level settings related to the query cache and [output formatting](/reference/settings/formats)) are removed from the AST. If the query was aborted due to an exception or user cancellation, no entry is written into the query cache. The size of the query cache in bytes, the maximum number of cache entries and the maximum size of individual cache entries (in bytes and in records) can be configured using different [server configuration options](/reference/settings/server-settings/settings/query#query_cache). ```xml theme={null} 1073741824 1024 1048576 30000000 ``` It is also possible to limit the cache usage of individual users using [settings profiles](/concepts/features/configuration/settings/settings-profiles) and [settings constraints](/concepts/features/configuration/settings/constraints-on-settings). More specifically, you can restrict the maximum amount of memory (in bytes) a user may allocate in the in-memory query cache and the maximum number of stored query results (these limits do not apply to the [query cache on disk](#query-cache-on-disk)). For that, first provide configurations [query\_cache\_max\_size\_in\_bytes](/reference/settings/session-settings/query-cache#query_cache_max_size_in_bytes) and [query\_cache\_max\_entries](/reference/settings/session-settings/query-cache#query_cache_max_entries) in a user profile in `users.xml`, then make both settings readonly: ```xml theme={null} 10000 100 ``` To define how long a query must run at least such that its result can be cached, you can use setting [query\_cache\_min\_query\_duration](/reference/settings/session-settings/query-cache#query_cache_min_query_duration). For example, the result of query ```sql theme={null} SELECT some_expensive_calculation(column_1, column_2) FROM table SETTINGS use_query_cache = true, query_cache_min_query_duration = 5000; ``` is only cached if the query runs longer than 5 seconds. It is also possible to specify how often a query needs to run until its result is cached - for that use setting [query\_cache\_min\_query\_runs](/reference/settings/session-settings/query-cache#query_cache_min_query_runs). Entries in the query cache become stale after a certain time period (time-to-live). By default, this period is 60 seconds but a different value can be specified at session, profile or query level using setting [query\_cache\_ttl](/reference/settings/session-settings/query-cache#query_cache_ttl). The query cache evicts entries "lazily", i.e. when an entry becomes stale, it is not immediately removed from the cache. Instead, when a new entry is to be inserted into the query cache, the database checks whether the cache has enough free space for the new entry. If this is not the case, the database tries to remove all stale entries. If the cache still has not enough free space, the new entry is not inserted. If the query is run via HTTP, then ClickHouse sets the `Age` and `Expires` headers with the age (in seconds) and expiration timestamp of the cached entry. Entries in the query cache are compressed by default. This reduces the overall memory consumption at the cost of slower writes into / reads from the query cache. To disable compression, use setting [query\_cache\_compress\_entries](/reference/settings/session-settings/query-cache#query_cache_compress_entries). Sometimes it is useful to keep multiple results for the same query cached. This can be achieved using setting [query\_cache\_tag](/reference/settings/session-settings/query-cache#query_cache_tag) that acts as a label (or namespace) for a query cache entries. The query cache considers results of the same query with different tags different. Example for creating three different query cache entries for the same query: ```sql theme={null} SELECT 1 SETTINGS use_query_cache = true; -- query_cache_tag is implicitly '' (empty string) SELECT 1 SETTINGS use_query_cache = true, query_cache_tag = 'tag 1'; SELECT 1 SETTINGS use_query_cache = true, query_cache_tag = 'tag 2'; ``` To remove only entries with tag `tag` from the query cache, you can use statement `SYSTEM CLEAR QUERY CACHE TAG 'tag'`.

Subquery Caching

By default, `use_query_cache` on the outer query does not propagate to subqueries. This means each subquery must explicitly opt in to caching: ```sql theme={null} SELECT * FROM (SELECT number FROM system.numbers LIMIT 1000 SETTINGS use_query_cache = true) WHERE number > 500; ``` In this example, only the inner subquery result is cached. The outer query is not cached. To enable caching for all subqueries at once, use the setting `query_cache_for_subqueries`: ```sql theme={null} SELECT * FROM (SELECT number FROM system.numbers LIMIT 1000) WHERE number > 500 SETTINGS use_query_cache = true, query_cache_for_subqueries = true; ``` To explicitly disable caching for a specific subquery while bulk propagation is enabled, set `use_query_cache = false` on that subquery: ```sql theme={null} SELECT * FROM (SELECT number FROM system.numbers LIMIT 1000 SETTINGS use_query_cache = false) WHERE number > 500 SETTINGS use_query_cache = true, query_cache_for_subqueries = true; ``` Subquery cache entries of the in-memory query cache are visible in [system.query\_cache](/reference/system-tables/query_cache) with `is_subquery = 1`. Subquery results are also cached on disk if the [query cache on disk](#query-cache-on-disk) is enabled, but these entries are not shown in `system.query_cache`. The `query_cache_ttl` setting also applies to subquery cache entries and can be set per subquery. ClickHouse reads table data in blocks of [max\_block\_size](/reference/settings/session-settings/max#max_block_size) rows. Due to filtering, aggregation, etc., result blocks are typically much smaller than 'max\_block\_size' but there are also cases where they are much bigger. Setting [query\_cache\_squash\_partial\_results](/reference/settings/session-settings/query-cache#query_cache_squash_partial_results) (enabled by default) controls if result blocks are squashed (if they are tiny) or split (if they are large) into blocks of 'max\_block\_size' size before insertion into the query result cache. This reduces performance of writes into the query cache but improves compression rate of cache entries and provides more natural block granularity when query results are later served from the query cache. As a result, the query cache stores for each query multiple (partial) result blocks. While this behavior is a good default, it can be suppressed using setting [query\_cache\_squash\_partial\_results](/reference/settings/session-settings/query-cache#query_cache_squash_partial_results). Also, results of queries with non-deterministic functions are not cached by default. Such functions include * functions for accessing dictionaries: [`dictGet()`](/reference/functions/regular-functions/ext-dict-functions) etc. * [user-defined functions](/reference/statements/create/function) without tag `true` in their XML definition, * functions which return the current date or time: [`now()`](/reference/functions/regular-functions/date-time-functions#now), [`today()`](/reference/functions/regular-functions/date-time-functions#today), [`yesterday()`](/reference/functions/regular-functions/date-time-functions#yesterday) etc., * functions which return random values: [`randomString()`](/reference/functions/regular-functions/random-functions#randomString), [`fuzzBits()`](/reference/functions/regular-functions/random-functions#fuzzBits) etc., * functions whose result depends on the size and order or the internal chunks used for query processing: [`nowInBlock()`](/reference/functions/regular-functions/date-time-functions#nowInBlock) etc., [`rowNumberInBlock()`](/reference/functions/regular-functions/other-functions#rowNumberInBlock), [`runningDifference()`](/reference/functions/regular-functions/other-functions#runningDifference), [`blockSize()`](/reference/functions/regular-functions/other-functions#blockSize) etc., * functions which depend on the environment: [`currentUser()`](/reference/functions/regular-functions/other-functions#currentUser), [`queryID()`](/reference/functions/regular-functions/other-functions#queryID), [`getMacro()`](/reference/functions/regular-functions/other-functions#getMacro) etc. To force caching of results of queries with non-deterministic functions regardless, use setting [query\_cache\_nondeterministic\_function\_handling](/reference/settings/session-settings/query-cache#query_cache_nondeterministic_function_handling). Results of queries that involve system tables (e.g. [system.processes](/reference/system-tables/processes)\` or [information\_schema.tables](/reference/system-tables/information_schema)) are not cached by default. To force caching of results of queries with system tables regardless, use setting [query\_cache\_system\_table\_handling](/reference/settings/session-settings/query-cache#query_cache_system_table_handling). Finally, entries in the query cache are not shared between users due to security reasons. For example, user A must not be able to bypass a row policy on a table by running the same query as another user B for whom no such policy exists. However, if necessary, cache entries can be marked accessible by other users (i.e. shared) by supplying setting [query\_cache\_share\_between\_users](/reference/settings/session-settings/query-cache#query_cache_share_between_users).

Query cache on disk

By default, the query cache keeps its entries in memory. Additionally, query results can be cached on disk, backed by a [filesystem cache](/concepts/features/configuration/server-config/storing-data#using-local-cache). Compared to the in-memory query cache, the query cache on disk provides more space and survives server restarts. An entry is only served by the same server build which wrote it: after an upgrade or a downgrade, the entries written by the previous build are cache misses, and they are overwritten or evicted eventually. To use the query cache on disk, first configure a filesystem cache in the `filesystem_caches` section of the server configuration: ```xml theme={null} query_results_cache/ 10Gi ``` then specify its name in setting `query_cache_on_disk_cache_name`: ```sql theme={null} SELECT some_expensive_calculation(column_1, column_2) FROM table SETTINGS use_query_cache = true, query_cache_on_disk_cache_name = 'query_results'; ``` The query cache on disk works independently of the in-memory query cache and can separately be enabled for reads and/or writes using settings `enable_reads_from_query_cache_on_disk` and `enable_writes_to_query_cache_on_disk` (both `true` by default). If reads are enabled for both caches, the lookup is attempted first from memory and only on a miss from disk. If writes are enabled for both caches, the result is stored in both. If no cache can store the result (e.g. in `clickhouse-local`, which has no in-memory query cache, with `enable_writes_to_query_cache_on_disk = false`), the query is not rejected because of non-deterministic functions, system tables, or a non-`throw` overflow mode: these checks only protect against storing wrong results. Entries on disk are compressed with the codec specified by setting `query_cache_on_disk_codec` (`ZSTD(3)` by default). The server settings `max_entry_size_in_bytes` and `max_entry_size_in_rows` of the query cache limit the entries of both backends; each backend measures an entry the way it stores it, so on disk the byte limit applies to the serialized, compressed entry. Setting either of them to 0 disables writes to both backends. All other query cache settings (e.g. `query_cache_ttl`, `query_cache_min_query_duration`) apply to the query cache on disk in the same way as to the in-memory query cache, with the two exceptions below. Settings `query_cache_max_size_in_bytes` and `query_cache_max_entries` are per-user limits of the in-memory query cache only. They are not enforced on the query cache on disk: entries on disk are bounded by the size of the configured filesystem cache and evicted by its rules (see below), so a user with a restrictive settings profile can still store more results on disk than these settings allow in memory. This is intentional - the filesystem cache is a shared, size-bounded resource which the query cache on disk does not reserve space in. Setting `query_cache_share_between_users` is another exception: it has the same meaning for both backends - an entry written with it is served to every user - but non-shared entries are isolated differently. The in-memory query cache keeps a single entry per query and rejects it on read if it belongs to another user, so a non-shared entry of user A shadows the writes of user B until the entry expires or is evicted. The query cache on disk instead makes the user and the set of the user's current roles part of the key of a non-shared entry, so user B misses on the entry of user A and stores its own copy of the result. The same holds for one user under different sets of current roles, e.g. after `SET ROLE`: the roles carry privileges and row policies, so results computed under different roles are cached separately. The entries of the query cache on disk are ordinary entries of the filesystem cache, indistinguishable from other data cached in the same filesystem cache: they are not held from deletion and they are evicted by the same rules (e.g. least-recently-used) when the filesystem cache runs out of space. There are no separate limits on their total size or number - only the limits of the filesystem cache itself apply. Statement `SYSTEM CLEAR QUERY CACHE` (and `SYSTEM CLEAR QUERY CACHE TAG 'tag'`) also removes the entries (with the given tag; corrupt entries and entries of an incompatible format are removed regardless of their tag) of the query cache on disk from the filesystem cache selected by setting `query_cache_on_disk_cache_name` of the session (e.g. after `SET query_cache_on_disk_cache_name = 'query_results'`), and leaves the other data of the filesystem cache in place. To find the entries, it walks the metadata of all file segments of the filesystem cache (in memory, without reading from disk) and reads the header of every key which is marked as a query cache entry. The walk takes time proportional to the number of file segments in the filesystem cache, so a filesystem cache dedicated to query results keeps `SYSTEM CLEAR QUERY CACHE` cheap. If the filesystem cache has already evicted the beginning of an entry, the remainder of the entry is left in place until the filesystem cache evicts it or the same query result is written again, but it is never served. An entry which is still being written is left in place as well. Statement `SYSTEM DROP FILESYSTEM CACHE ''` removes them together with everything else in the filesystem cache. System table [system.query\_cache](/reference/system-tables/query_cache) and metrics `QueryCacheEntries` and `QueryCacheBytes` show only the entries of the in-memory query cache. The number of query cache hits and misses on disk are shown as events "QueryCacheOnDiskHits" and "QueryCacheOnDiskMisses" in system table [system.events](/reference/system-tables/events). These are the breakdown of the on-disk backend alone, whereas "QueryCacheHits" and "QueryCacheMisses" stay the counters of the query cache as a whole, and "QueryCacheAgeSeconds" accounts for hits of both backends. The in-memory cache is probed first, so the number of in-memory hits is "QueryCacheHits" minus "QueryCacheOnDiskHits".

Related content

* Blog: [Introducing the ClickHouse Query Cache](https://clickhouse.com/blog/introduction-to-the-clickhouse-query-cache-and-design)