ClickHouse Dictionaries: Replacing JOINs with In-Memory Lookups

A ClickHouse dashboard query joins a fact table of billions of rows to a dim_product table of a few million. On every run, the hash JOIN reads the dimension and builds a hash table from it on each server, and under load some queries fail with MEMORY_LIMIT_EXCEEDED. A dictionary keeps that dimension in a key-value structure that loads once, refreshes in the background, and is read with dictGet or a direct JOIN. This guide covers CREATE DICTIONARY, sources, layouts, LIFETIME, the lookup functions, memory in system.dictionaries, and the failure modes.

Scope and review date: November 2026. This post covers self-managed ClickHouse 26.8 LTS and 26.10 (the newest release in the changelog on 9 November 2026), with notes on ClickHouse Cloud and on JOIN changes since 24.12. Syntax, defaults, and version notes were checked against the ClickHouse documentation and changelog on 9 November 2026. The commands were not run in a lab, so test them on staging first.

When does a ClickHouse dictionary beat a JOIN?

A dictionary is an in-memory key-to-attributes map that ClickHouse loads from a source and keeps on each server. The dictionary best-practice page says to use one in place of a JOIN against a small dimension table when the fact row already has the key. A JOIN builds a hash table from the right-hand side first; dictGet runs inline, only on rows that survived filtering.

A dictionary loses when WHERE filters on a looked-up value across most rows, such as dictGet('dict', 'elevation', id) > 1800. A regular column can use PREWHERE and indexes; dictGet runs row by row.

-- The shape that suits a dictionary: one key, many fact rows, few attributes
SELECT
    dictGetOrDefault('analytics.product_dict', 'category', product_id, 'unknown') AS category,
    count() AS orders
FROM analytics.orders
WHERE order_date >= today() - 7
GROUP BY category
ORDER BY orders DESC;

Interpretation: the date filter runs first, then each surviving row does one lookup. No hash table is built.

How do you create a dictionary with CREATE DICTIONARY?

Use the DDL form. ClickHouse recommends it over XML files, and ClickHouse Cloud only supports DDL. The statement names the attributes, the PRIMARY KEY, a SOURCE, a LAYOUT, and a LIFETIME.

CREATE DICTIONARY analytics.product_dict ON CLUSTER main
(
    product_id  UInt64,
    category    String DEFAULT 'unknown',
    brand       String DEFAULT '',
    supplier_id UInt64 DEFAULT 0
)
PRIMARY KEY product_id
SOURCE(CLICKHOUSE(
    USER 'dict_reader' PASSWORD '********'
    DB 'analytics' TABLE 'dim_product'
))
LAYOUT(HASHED_ARRAY())
LIFETIME(MIN 300 MAX 360)
COMMENT 'Product dimension for order dashboards';

Interpretation: without HOST, the source is the local server. DEFAULT is the value returned for a missing key. Self-managed clusters need ON CLUSTER because each server holds its own copy; ClickHouse Cloud replicates it for you.

Give the source a dedicated user with SELECT on the table. In ClickHouse Cloud, a non-default creator must set both USER and PASSWORD, or CREATE DICTIONARY fails with BAD_ARGUMENTS.

Sources: MySQL, PostgreSQL, HTTP, and files

The sources page lists files, HTTP(S), ODBC, MongoDB, Redis, Cassandra, and more. The two most common database sources outside ClickHouse:

-- PostgreSQL, reloaded only when the invalidate query's result changes
SOURCE(POSTGRESQL(
    host 'pg-replica.internal' port 5432
    user 'ch_dict' password '********'
    db 'shop' table 'products'
    invalidate_query 'SELECT max(updated_at) FROM products'
))

-- MySQL with two replicas tried in priority order
SOURCE(MYSQL(
    port 3306 user 'ch_dict' password '********'
    replica(host 'mysql-a' priority 1)
    replica(host 'mysql-b' priority 2)
    db 'shop' table 'products'
))

Interpretation: point database sources at a replica, because every server in the cluster runs this query on every reload. SOURCE(HTTP(url '...' format 'TabSeparated')) is checked against remote_url_allow_hosts. A SOURCE(FILE(...)) created by DDL must sit under user_files.

Which dictionary layout should you choose?

The layouts reference names flat, hashed, and complex_key_hashed as the fastest and advises against cache layouts. Layouts without complex_key take a UInt64 key.

-- range_hashed: the DDL adds LAYOUT(RANGE_HASHED(range_lookup_strategy 'max'))
-- and RANGE(MIN start_date MAX end_date); the lookup takes a fourth argument
SELECT dictGet('analytics.discount_dict', 'amount', toUInt64(123), toDate('2026-10-15'));

Interpretation: the fourth argument picks the range. With overlapping ranges, max returns the match with the latest range_min. The default strategy, min, returns the earliest. No match returns the attribute's default.

For ip_trie, the key is one String column of CIDR prefixes, and dictGet takes an IPv4 or IPv6 value. When prefixes overlap, the most specific one wins.

How do LIFETIME and refresh work?

LIFETIME is the update interval in seconds, or the cell expiry for cache layouts. Per the LIFETIME page, updates do not block queries except on first load, a successful update replaces the old version atomically, and a failed one is logged while queries keep the old version.

• LIFETIME(MIN 300 MAX 360) picks a random time in the window on each server, spreading load on the source.
• LIFETIME(0) turns off timed reloads.
• File sources reload when the file's modification time changes; other sources reload in full each time unless invalidate_query says nothing changed.
• update_field fetches only changed rows for flat, hashed, hashed_array, and complex_key_hashed, and update_lag widens the window.

-- Incremental refresh: ClickHouse adds WHERE updated_at >= (last update - 15 s)
SOURCE(CLICKHOUSE(
    USER 'dict_reader' PASSWORD '********'
    DB 'analytics' TABLE 'dim_product'
    update_field 'updated_at' update_lag 15
))

-- Force a reload now, on every server
SYSTEM RELOAD DICTIONARY ON CLUSTER main analytics.product_dict;

Interpretation: an incremental refresh selects only rows whose update_field moved, so it cannot see deleted rows. If deletes matter, schedule SYSTEM RELOAD DICTIONARY, which the SYSTEM reference says reloads it completely.

How do you look up values with dictGet and dictGetOrDefault?

The dictionary functions take the dictionary name, one attribute or a tuple of attributes, and the key. A simple-key dictionary expects a UInt64 key. A composite key is a tuple, and a single-column composite key can be passed bare. For DDL dictionaries, qualify the name with the database, or the current database is used.

SELECT
    order_id,
    dictGet('analytics.product_dict', 'category', toUInt64(product_id)) AS category,
    dictGetOrDefault('analytics.product_dict', 'brand', toUInt64(product_id), 'n/a') AS brand,
    dictGetOrNull('analytics.product_dict', 'supplier_id', toUInt64(product_id)) AS supplier,
    dictHas('analytics.product_dict', toUInt64(product_id)) AS known_product
FROM analytics.orders
WHERE order_date = today()
LIMIT 10;

Interpretation: for a missing key, dictGet returns the attribute's DEFAULT, dictGetOrDefault returns your fourth argument, and dictGetOrNull returns NULL. dictHas returns 1 or 0. Use it to count orphan keys rather than reading defaults as real values.

Passing a tuple of attribute names, such as ('category', 'brand'), returns both in one lookup. Two tips from the layouts page: call the function after GROUP BY where you can, and mark an attribute INJECTIVE when different keys always map to different values. ClickHouse can then move the lookup out of GROUP BY automatically.

Can a JOIN use a dictionary directly?

Yes. A DDL dictionary can be named in a JOIN like a table, and ClickHouse can run it with the direct algorithm: no build phase, one lookup per left-hand row, memory charged to the dictionary, no spill. The JOIN algorithms page lists the conditions. The JOIN is INNER ALL or LEFT with ALL, ANY, SEMI, or ANTI. ON is exactly one equality on the dictionary's single-column key, with a matching type. The dictionary has no range key.

Since 26.8 the default join_algorithm is direct,parallel_hash,hash,ie_join, so a qualifying JOIN uses direct with no setting change. Anything else falls through to the next algorithm.

SELECT o.order_id, p.category
FROM analytics.orders AS o
LEFT ANY JOIN analytics.product_dict AS p ON o.product_id = p.product_id
WHERE o.order_date = today()
SETTINGS join_algorithm = 'direct';

Interpretation: pinning direct while testing makes a non-qualifying JOIN fail instead of falling back to hash. In ClickHouse's direct join walkthrough, EXPLAIN PLAN shows a FilledJoin step with no read of the dimension underneath. From 26.10, system.query_log also records used_join_algorithms.

How much memory does a dictionary use?

Dictionaries store data uncompressed, so bytes_allocated can far exceed the table's size on disk, on every server. Measure the uncompressed columns first, then read the real figure from system.dictionaries.

SELECT
    formatReadableSize(sum(data_compressed_bytes))   AS on_disk,
    formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed
FROM system.columns
WHERE database = 'analytics' AND table = 'dim_product'
  AND name IN ('product_id', 'category', 'brand', 'supplier_id');

Interpretation: the uncompressed figure is a floor before hash table overhead. Multiply by the server count for the cluster cost.

Three memory traps come from the docs and changelog:

• Sparse flat keys. The array is sized to the largest key, and a key above max_array_size fails the load.
• Under-reported hashed_array. The 26.9 changelog fixed bytes_allocated for HASHED_ARRAY dictionaries, which came out roughly 2 to 5 times lower than the memory held. On older releases, check the server's resident memory before trusting the column.
• Reload headroom. The old version serves until the new one replaces it, so budget for two copies during a reload. That is our reading of the atomic swap, not a documented figure; measure it on staging.

What breaks when the source fails or the data grows?

The source is down

If the first load fails, the query that triggered it gets the exception, and so does every later query until a load succeeds. If a reload fails after a successful load, queries keep reading the old copy and only the log, error_count, and last_exception show it. For MySQL, fail_on_connection_loss defaults to false, so ClickHouse retries at least three times before reporting an error, and the docs note that retrying increases response times.

-- Alert query: dictionaries failing, or not refreshed for 30 minutes
SELECT database, name, status, error_count, last_exception,
       last_successful_update_time
FROM clusterAllReplicas('main', system.dictionaries)
WHERE status IN ('FAILED', 'FAILED_AND_RELOADING')
   OR error_count > 0
   OR (lifetime_max > 0 AND last_successful_update_time < now() - INTERVAL 30 MINUTE);

Interpretation: any row is a dictionary serving stale data or none. The table is local to each server, so the query fans out. Set the threshold to a few multiples of LIFETIME MAX.

Stale data

Upgrades can cause it too. The 26.10 changelog fixed a local CLICKHOUSE source that kept serving stale data with ACCESS_DENIED after its user's roles changed. The same release stops DDL MySQL sources from enabling enable_local_infile, and makes sources that set ssl_ca verify the certificate and host name. Either can turn a working dictionary into a failed load.

Memory blowups

A growing dimension grows the dictionary on every server, outside any per-query limit. cache layouts are no escape for insert paths: a restarted server starts with an empty cache, so inserts that call dictGet depend on the source again. For MATERIALIZED columns and materialized views, use a fully loaded layout, or enrich before insert.

How do you convert a dimension JOIN to a dictionary?

This runbook replaces a LEFT JOIN dim_product in a dashboard query. Production queries are untouched until step 6.

What are the alternatives to a dictionary?

A dictionary is one of several ways to avoid a costly JOIN. ClickHouse's JOIN best-practice page still recommends denormalizing for latency-sensitive queries when the joined attributes rarely change.

• The Join table engine. A prepared in-RAM hash table, persisted to disk, read with joinGet or as a JOIN's right side. Its strictness and type must match the JOIN, or, per the docs, ClickHouse may return incorrect data without an exception. It cannot be used in GLOBAL JOIN.
• Insert-time enrichment. A MATERIALIZED column with dictGet stores the value once, so use it only for attributes that never change.
• A better regular JOIN. Since 24.12 the planner can build the smaller side and push filters below the JOIN. Since 25.12, multi-table reordering and statistics are on by default, and direct works against MergeTree when it is the only join_algorithm. Statistics went GA in 26.3 and are built on insert from 26.8, and ClickHouse's JOIN guide now says to use 26.8 or later.

CREATE TABLE analytics.product_join (product_id UInt64, category String)
ENGINE = Join(ANY, LEFT, product_id);
INSERT INTO analytics.product_join SELECT product_id, category FROM analytics.dim_product;
SELECT joinGet('analytics.product_join', 'category', toUInt64(42));

Interpretation: the Join table is as fresh as your last INSERT. It has no LIFETIME, so you schedule the refresh yourself.

Frequently asked questions

Key takeaways

Tune ClickHouse JOINs with JusDB

JusDB's ClickHouse performance tuning team can review the JOINs behind your slowest and most memory-hungry queries. We can pick the ones that suit a dictionary, size the layouts against your servers, and rewrite and verify the queries with you.

Bring your top queries from system.query_log, table sizes, version, and cluster layout. Your team approves every production change.

Related reading

• ClickHouse explained: MergeTree, Distributed engine, and real-time OLAP
• ClickHouse MergeTree engine: storage architecture and query optimization
• Real-time analytics pipeline: PostgreSQL to ClickHouse via CDC
• ClickHouse documentation: Dictionary best practices