Case study

665 GiB of dictionaries: when ClickHouse dictionaries stop scaling

Nine hashed dictionaries quietly grew to two thirds of a terabyte per node, and every reload tried to build a second copy. How we found that only five views used them, and replaced them with joins that read 17 granules out of 282,102.

3 min read · Markdown

TL;DR

• On a two-node ClickHouse cluster with 1 TB of RAM per node, nine hashed dictionaries keyed by product and size IDs took 663.5 GiB on each node.
• They rebuilt by full reload every two to four hours. A reload builds the new hash table next to the old one, so one dictionary needed 183 GiB more on top of 857 GiB already in use. Reloads hit the memory limit, retried forever, and dragged queries down with them.
• Only five views used the dictionaries. Rewritten as a CTE plus ANY LEFT JOIN, with primary-key pruning through an IN subquery, they read 17 of 282,102 granules, and the dictionaries could go.

The symptom

Reports on the cluster started failing with MEMORY_LIMIT_EXCEEDED, in waves. Nothing obvious had changed: no new heavy queries, no data spike. system.dictionaries showed the cause. Two dictionaries had been stuck in LOADED_AND_RELOADING for three days, and the total bytes_allocated across nine dictionaries was 663.5 GiB on each node, against a server memory limit of 906 GiB (0.9 × RAM).

The mechanism

The dictionaries mapped product and size IDs to names, categories and attributes.

• Their source was a view with FINAL over a ReplicatedReplacingMergeTree of 2.31 billion rows (139 GiB compressed), growing by about 51 million rows a day.
• Every two to four hours each dictionary reloaded in full: ClickHouse reads the source and builds a new hash table while the old one keeps serving, then swaps them.
• The double-memory moment. For the largest dictionary, the reload needed 183 GiB on top of the 857 GiB already allocated. The reload failed on the memory limit and retried, again and again. Every attempt pushed ordinary queries over the limit too.

The growth explains why it worked before. Over two years the number of products grew about eight times, and one product dictionary went from 17 GiB to 134 GiB. Hashed dictionaries scale with the key space, not with what anyone actually looks up.

Who actually used them

Before touching anything, we needed the real consumers, not the ones the repository implied:

• DDL: no materialized views or column defaults referenced the dictionaries; only views did.
• query_log on both replicas for three days (logs are per node, so one replica gives half the picture). Exactly five views used the big dictionaries:
  • three language variants of a sales report, about 23,000 calls a day with a median result of 12 rows;
  • a geography report, about 39,000 calls a day;
  • a weekly sales report, about 7,000 calls a day.

So 663 GiB of RAM per node was spent to look up names for a few dozen rows per call.

The fix

A precedent existed on another cluster. Instead of a dictionary lookup per row, each view first collects the IDs it needs, then joins only those rows of the source:

WITH chrt AS (
    SELECT chrt_id, subject_name, brand_name /* … */
    FROM chrt_source FINAL
    WHERE is_deleted = 0
      AND chrt_id IN (SELECT DISTINCT chrt_id FROM report_rows)
)
SELECT r.*, coalesce(c.subject_name, '') AS subject_name /* … */
FROM report_rows AS r
ANY LEFT JOIN chrt AS c USING (chrt_id)

The source table is ordered by the ID, so the IN subquery prunes by primary key even on ClickHouse 23.8: EXPLAIN indexes = 1 showed 17 granules read out of 282,102. The join costs milliseconds and no standing memory.

The five views were rewritten and benchmarked against the old ones on production data, and the dictionaries were dropped. About 540 GiB of RAM per node came back at once, and the memory-limit storm ended. The last dictionary, 126 GiB used by a single report, was left for a second phase.

A trick that didn't work. A DIRECT layout over a view on numbers() avoids holding the dictionary in memory, but it relies on predicate pushdown into numbers() that arrived only in 23.11. On 23.8 it was a dead end.

Takeaways

• A hashed dictionary costs twice its size at reload time. Budget memory for the peak, not the steady state.
• Dictionaries scale with the key space, joins with the query. For sparse lookups (a few dozen rows per call), a join with primary-key pruning beats a giant cache.
• Find consumers in the logs of every replica, and in the DDL, before deciding anything.
• Watch growth. An eight-fold growth over two years turns a convenience into an outage; a rule of "no new dictionaries on large ID spaces" is cheaper than the next incident.

All writing · A similar problem? contact@ivanblinov.com