title_wip

It is also possible to limit the cache usage of individual users using [settings profiles](/docs/operations/settings/settings-profiles) and [settings
constraints](/docs/operations/settings/constraints-on-settings). More specifically, you can restrict the maximum amount of memory (in bytes) a user may
allocate in the query cache and the maximum number of stored query results. For that, first provide configurations
[query\_cache\_max\_size\_in\_bytes](/docs/operations/settings/settings#query_cache_max_size_in_bytes) and
[query\_cache\_max\_entries](/docs/operations/settings/settings#query_cache_max_entries) in a user profile in `users.xml`, then make both settings
readonly:

```
<profiles>
    <default>
        <!-- The maximum cache size in bytes for user/profile 'default' -->
        <query_cache_max_size_in_bytes>10000</query_cache_max_size_in_bytes>
        <!-- The maximum number of SELECT query results stored in the cache for user/profile 'default' -->
        <query_cache_max_entries>100</query_cache_max_entries>
        <!-- Make both settings read-only so the user cannot change them -->
        <constraints>
            <query_cache_max_size_in_bytes>
                <readonly/>
            </query_cache_max_size_in_bytes>
            <query_cache_max_entries>
                <readonly/>
            <query_cache_max_entries>
        </constraints>
    </default>
</profiles>
```

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](/docs/operations/settings/settings#query_cache_min_query_duration). For example, the result of query

```
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](/docs/operations/settings/settings#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](/docs/operations/settings/settings#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](/docs/operations/settings/settings#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](/docs/operations/settings/settings#query_cache_tag) that acts as 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:

```
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'`.

ClickHouse reads table data in blocks of [max\_block\_size](/docs/operations/settings/settings#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](/docs/operations/settings/settings#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](/docs/operations/settings/settings#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()`](/docs/sql-reference/functions/ext-dict-functions) etc.
- [user-defined functions](/docs/sql-reference/statements/create/function) without tag `<deterministic>true</deterministic>` in their XML
  definition,
- functions which return the current date or time: [`now()`](/docs/sql-reference/functions/date-time-functions#now),
  [`today()`](/docs/sql-reference/functions/date-time-functions#today),
  [`yesterday()`](/docs/sql-reference/functions/date-time-functions#yesterday) etc.,
- functions which return random values: [`randomString()`](/docs/sql-reference/functions/random-functions#randomString),
  [`fuzzBits()`](/docs/sql-reference/functions/random-functions#fuzzBits) etc.,
- functions whose result depends on the size and order or the internal chunks used for query processing:
  [`nowInBlock()`](/docs/sql-reference/functions/date-time-functions#nowInBlock) etc.,
  [`rowNumberInBlock()`](/docs/sql-reference/functions/other-functions#rowNumberInBlock),
  [`runningDifference()`](/docs/sql-reference/functions/other-functions#runningDifference),
  [`blockSize()`](/docs/sql-reference/functions/other-functions#blockSize) etc.,
- functions which depend on the environment: [`currentUser()`](/docs/sql-reference/functions/other-functions#currentUser),
  [`queryID()`](/docs/sql-reference/functions/other-functions#queryID),
  [`getMacro()`](/docs/sql-reference/functions/other-functions#getMacro) etc.

To force caching of results of queries with non-deterministic functions regardless, use setting
[query\_cache\_nondeterministic\_function\_handling](/docs/operations/settings/settings#query_cache_nondeterministic_function_handling).

Results of queries that involve system tables (e.g. [system.processes](/docs/operations/system-tables/processes)` or
[information\_schema.tables](/docs/operations/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](/docs/operations/settings/settings#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](/docs/operations/settings/settings#query_cache_share_between_users).

## Related content[​](#related-content "Direct link to Related content")

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

- [Background, design and limitations](#background-design-and-limitations)- [Configuration settings and usage](#configuration-settings-and-usage)- [Related content](#related-content)

Was this page helpful?