Note

LowCardinality in ClickHouse: I measured the rule of thumb, and it was wrong both ways

"LowCardinality helps between a thousand and a hundred thousand distinct values, and hurts otherwise." A code review quoted this rule, so I measured it on ClickHouse 23.8. LowCardinality wins at any small number of values, even two, and starts losing badly at about twenty thousand per part.

2 min read · Markdown

The measurement

ClickHouse 23.8.5.16 in Docker, 16 CPUs.

Data: one string column, 10 million rows, merged into one part, with values random relative to the sort key.
Comparison: the same data as String and as LowCardinality(String).
Method: five runs, best time.

Distinct values	Disk, String → LC	GROUP BY, ms	`WHERE val = 'v1'`, ms	INSERT, ms
2	9.7 → 4.8 MiB	18 → 9	15 → 5	617 → 604
18	15.5 → 9.6 MiB	18 → 8	16 → 4	617 → 912
1,000	28.3 → 19.2 MiB	27 → 10	16 → 6	719 → 705
8,000	38.1 → 19.2 MiB	35 → 25	17 → 6	765 → 904
20,000	42.5 → 49.0 MiB	66 → 181	20 → 95	628 → 1,482
100,000	49.0 → 64.7 MiB	171 → 414	20 → 256	674 → 2,204
1,000,000	59.1 → 76.9 MiB	339 → 714	23 → 262	777 → 4,210
10,000,000 (all unique)	39.9 → 44.7 MiB	516 → 1,321	24 → 239	966 → 2,623

Memory tells the same story. A GROUP BY over 8,000 values used 129 MiB as String and 18 MiB as LowCardinality. Over 10 million unique values it used 745 MiB and 2.3 GiB.

Why the threshold sits there

A LowCardinality column stores, per part, a dictionary of values and a small integer index per row. The dictionary is shared across the part only up to low_cardinality_max_dictionary_size, 8,192 by default. Beyond that, ClickHouse writes additional dictionaries per block. Below the limit you get a compact, shared dictionary and integer comparisons. Above it, by the shape of the curve, you pay for many dictionaries on top of the data.

So the number that matters is distinct values per part, not per table. A column with a million values overall, sorted so that each part sees a few thousand, can still be a good LowCardinality column.

Joins and dictionaries

A second set of measurements, closer to how the column was actually used:

A 198-row translation dictionary is the same size either way (1.48 vs 1.44 KiB).
Joining a 30-million-row fact to it with a full scan was slower with a LowCardinality key: 524 vs 826 ms median. It held far less memory (14 vs 133 MiB): less memory, more CPU.
Three joins as in the real view, for one supplier: no meaningful difference (19 vs 20 ms).
A low-cardinality attribute stored in the fact table itself (18 values) is where LowCardinality shines: 80.7 → 28.4 MiB on disk, GROUP BY 111 → 30 ms, filter 91 → 20 ms.
Mixed key types (Nullable(String) on one side, LowCardinality(String) on the other) joined without any penalty.

The rule I use now

Use LowCardinality for any column with up to a few thousand distinct values per part, including flags and statuses with two or three values.
Avoid it above roughly 10–20 thousand distinct values per part. IDs, names of millions of products and free text are worse as LowCardinality on disk, in queries and on insert.
For small dimension tables it hardly matters. For the columns of large facts it matters a lot.
Measure on your own data. The curve is cheap to reproduce: one table, one column, a loop over cardinalities.

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