# 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.*

## 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.

---

Source: https://ivanblinov.com/writing/clickhouse-lowcardinality-measured/