> ## 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.

# cache dictionary layout

> Store a dictionary in a fixed-size in-memory cache.

The `cached` dictionary layout type is stores the dictionary in a cache that has a fixed number of cells.
These cells contain frequently used elements.

The dictionary key has the [UInt64](/reference/data-types/int-uint) type.

When searching for a dictionary, the cache is searched first. For each block of data, all keys that are not found in the cache or are outdated are requested from the source using `SELECT attrs... FROM db.table WHERE id IN (k1, k2, ...)`. The received data is then written to the cache.

Cache contents are local to each server and are lost when a server restarts or is replaced. Do not use `cache` or `complex_key_cache` for reliable insert-time enrichment: an empty cache makes inserts depend on lookups to the dictionary source. In ClickHouse Cloud, autoscaling and managed node replacement make this condition more likely. Use a layout that loads the complete dictionary on each server instead; see [Dictionary best practices](/concepts/features/dictionaries/best-practices#cache-dictionaries-in-ingestion-path).

That applies to looking a key **up** - `dictGet` and the other dictionary functions. Reading the dictionary **as a table** with `SELECT ... FROM <dictionary>` is different: because a cache keeps no record of which keys exist, the read enumerates only the cells that happen to be resident in the cache at that moment and that hold a value, and a `WHERE` on the key is an ordinary filter over those cells, not a list of keys to fetch. A key that is not in the cache cannot be discovered this way, no matter what the `WHERE` says. A key that *was* looked up but was not found at the source is not visible either: the cache remembers the miss as a default cell, and a table read skips default cells. Resident cells are not free of the source either: an expired cell is read through the same path as `dictGet`, so it is re-requested from the source - synchronously, or asynchronously if `allow_read_expired_keys` is enabled.

```sql theme={null}
CREATE DICTIONARY cache_dict (id UInt64, data String) PRIMARY KEY id
SOURCE(CLICKHOUSE(TABLE 'cache_src')) LIFETIME(MIN 0 MAX 900) LAYOUT(CACHE(SIZE_IN_CELLS 1000));

-- nothing is cached yet, so nothing comes back and the source is not queried
SELECT count() FROM cache_dict WHERE id IN (1, 2, 3);
0

-- looking the keys up populates the cache
SELECT dictGet('cache_dict', 'data', toUInt64(number + 1)) FROM numbers(3);

-- and now the same read sees them
SELECT count() FROM cache_dict WHERE id IN (1, 2, 3);
3
```

So a cache dictionary is meant to be used through the dictionary functions. If you need a lookup of arbitrary keys to always reach the source, use `dictGet` with the [direct](/reference/statements/create/dictionary/layouts/direct) layout, which queries the source on every lookup and caches nothing. Note that a table read of a `direct` dictionary is not a keyed fetch either: `SELECT ... FROM <dictionary> WHERE key IN (...)` loads the whole source and filters afterwards, because ClickHouse does not push the key filter into the dictionary. To read a dictionary as a table, use a layout that holds all of it, such as [flat](/reference/statements/create/dictionary/layouts/flat) or [hashed](/reference/statements/create/dictionary/layouts/hashed).

If keys are not found in dictionary, then update cache task is created and added into update queue. Update queue properties can be controlled with settings `max_update_queue_size`, `update_queue_push_timeout_milliseconds`, `query_wait_timeout_milliseconds`, `max_threads_for_updates`.

For cache dictionaries, the expiration [lifetime](/reference/statements/create/dictionary/lifetime) of data in the cache can be set. If more time than `lifetime` has passed since loading the data in a cell, the cell's value is not used and key becomes expired. The key is re-requested the next time it needs to be used. This behaviour can be configured with setting `allow_read_expired_keys`.

This is the least effective of all the ways to store dictionaries. The speed of the cache depends strongly on correct settings and the usage scenario. A cache type dictionary performs well only when the hit rates are high enough (recommended 99% and higher). You can view the average hit rate in the [system.dictionaries](/reference/system-tables/dictionaries) table.

If setting `allow_read_expired_keys` is set to 1, by default 0. Then dictionary can support asynchronous updates. If a client requests keys and all of them are in cache, but some of them are expired, then dictionary will return expired keys for a client and request them asynchronously from the source.

To improve cache performance, use a subquery with `LIMIT`, and call the function with the dictionary externally.

All types of sources are supported.

Example of settings:

<Tabs>
  <Tab title="DDL">
    ```sql theme={null}
    LAYOUT(CACHE(SIZE_IN_CELLS 1000000000))
    ```
  </Tab>

  <Tab title="Configuration file">
    ```xml theme={null}
    <layout>
        <cache>
            <!-- The size of the cache, in number of cells. Rounded up to a power of two. -->
            <size_in_cells>1000000000</size_in_cells>
            <!-- Allows to read expired keys. -->
            <allow_read_expired_keys>0</allow_read_expired_keys>
            <!-- Max size of update queue. -->
            <max_update_queue_size>100000</max_update_queue_size>
            <!-- Max timeout in milliseconds for push update task into queue. -->
            <update_queue_push_timeout_milliseconds>10</update_queue_push_timeout_milliseconds>
            <!-- Max wait timeout in milliseconds for update task to complete. -->
            <query_wait_timeout_milliseconds>60000</query_wait_timeout_milliseconds>
            <!-- Max threads for cache dictionary update. -->
            <max_threads_for_updates>4</max_threads_for_updates>
        </cache>
    </layout>
    ```
  </Tab>
</Tabs>

<br />

Set a large enough cache size. You need to experiment to select the number of cells:

1. Set some value.
2. Run queries until the cache is completely full.
3. Assess memory consumption using the `system.dictionaries` table.
4. Increase or decrease the number of cells until the required memory consumption is reached.

<Note>
  ClickHouse is not recommended as a source for this layout. Dictionary lookups require random point reads, which are not the access pattern ClickHouse is optimized for.
</Note>