📖 ClickHouse Dictionaries

In-Memory Key-Value Lookups — Replace JOINs with O(1) Reads

ClickHouse dictionaries are in-memory key-value data structures that load data from external sources (MySQL, PostgreSQL, HTTP, files, ClickHouse tables) and serve it with O(1) hash lookups. Instead of expensive JOINs against dimension tables, you call dictGet('dict_name', 'attr', key) — turning a multi-second JOIN into a nanosecond lookup. Dictionaries support automatic background reloads, hierarchical structures, and multiple layout types optimized for different access patterns.

⚡ Dictionary Lookup vs JOIN

See the performance difference: a JOIN scans the dimension table, while dictGet does a hash lookup.

Traditional JOIN

SELECT e.event, d.country_name
FROM events e
JOIN countries d ON e.country_id = d.id

Rows scanned0

Time—

dictGet() Lookup

SELECT event,
 dictGet('countries', 'name', country_id)
FROM events

Hash lookups0

Time—

🗂️ Dictionary Layouts

Choose the right layout based on your key type and memory constraints.