Dictionaries

ClickHouse is deliberately weak at exactly the thing OLTP databases are strong at: joining a huge fact table against a small, frequently looked-up reference table (country codes, product catalogs, user tiers) row by row. Dictionaries exist to sidestep the join entirely for that specific, extremely common case.

The idea: load it once, look it up in memory

Instead of a JOIN that has to find matching rows in a second table for every row of the fact table, a dictionary is loaded into memory ahead of time — as a hash table or flat array — and looked up with a plain function call, roughly as fast as reading a local variable.

Defining and using one

CREATE DICTIONARY country_names
(
    id   UInt64,
    name String
)
PRIMARY KEY id
SOURCE(POSTGRESQL(host 'postgres' db 'ref' table 'countries'))
LAYOUT(HASHED())
LIFETIME(MIN 300 MAX 600);

SELECT
    user_id,
    dictGet('country_names', 'name', country_id) AS country
FROM events;

LIFETIME(MIN 300 MAX 600) tells ClickHouse to reload the dictionary from its source somewhere between 5 and 10 minutes after the last load — not on every query, and not on every change in the source. That staleness window is the trade you're making for speed.

Layouts trade memory for lookup shape

• FLAT — array-indexed by integer key; fastest, but only sensible for small, dense key ranges.
• HASHED — hash table; the general-purpose default for integer or string keys.
• RANGE_HASHED — for keys that change over time (e.g. exchange rates valid within a date range).
• DIRECT — no local storage; queries the source live, per lookup. Used when the reference table is too large to hold in memory.