TopolearnJoins are where ClickHouse queries most often run out of memory, and dictionaries are the idiomatic fix.
ClickHouse supports all standard joins plus a few extras: ANY JOIN (take one match per row), ASOF JOIN (match the nearest earlier timestamp, perfect for prices or sessions), and SEMI/ANTI joins.
By default a join is a hash join: one side, the build side, is loaded into an in-memory hash table and the other side streams past it. Historically the right table was always the build side, so the rule was "put the small table on the right". Recent versions can swap the sides automatically when statistics show the right one is bigger, but writing big-left, small-right is still a good habit.
When the build side is too large for memory, choose another algorithm with the join_algorithm setting: grace_hash and partial_merge spill to disk, full_sorting_merge sorts both sides and is efficient when they're already ordered by the join key. Filtering and pre-aggregating each side in subqueries before joining helps more than any setting.
Dictionaries are the idiomatic alternative for enrichment lookups: a key-value structure loaded into memory from a table, file, HTTP endpoint or external database and refreshed every LIFETIME seconds. dictGet('dict', 'attr', key) is a function call, not a join, so it's cheap even on billions of rows. Layouts include HASHED, FLAT for small dense integer keys, and RANGE_HASHED for keys valid over date ranges. Join a dimension table only when you need many columns or non-key conditions.
Example
CREATE TABLE countries (code String, name String) ENGINE = MergeTree ORDER BY code;
INSERT INTO countries VALUES ('DE', 'Germany'), ('FR', 'France');
CREATE TABLE sales (country String, amount UInt32) ENGINE = MergeTree ORDER BY country;
INSERT INTO sales VALUES ('DE', 10), ('DE', 5), ('FR', 7);
-- Big table left, small table right
SELECT c.name, sum(s.amount) AS total
FROM sales AS s INNER JOIN countries AS c ON s.country = c.code
GROUP BY c.name ORDER BY total DESC; -- Germany 15 / France 7
-- Same enrichment with a dictionary lookup
CREATE DICTIONARY country_dict (code String, name String)
PRIMARY KEY code
SOURCE(CLICKHOUSE(TABLE 'countries'))
LAYOUT(COMPLEX_KEY_HASHED()) LIFETIME(MIN 300 MAX 600);
SELECT dictGet('country_dict', 'name', tuple(country)) AS name, sum(amount)
FROM sales GROUP BY name ORDER BY name; -- France 7 / Germany 15Remember
- Hash joins hold one side in memory: keep that side small
- Switch join_algorithm (grace_hash, partial_merge, full_sorting_merge) for huge joins
- Dictionaries + dictGet are the cheap way to enrich with dimension attributes
Watch out for
- Joining two billion-row tables without filtering or pre-aggregating either side first.