Wrap a string column in LowCardinality and ClickHouse stops storing the string. It builds a dictionary of distinct values and stores small integer positions into it, so a GROUP BY compares integers instead of strings and the column compresses like integers too. On a country code or an HTTP method the win is large and free.
The advice attached to it is always some version of "use it under ten thousand distinct values". That number is real, and it's a rounded-off consequence of a setting most people have never looked at.
low_cardinality_max_dictionary_size = 8192What the setting does
8192 is the maximum size, in rows, of the dictionary ClickHouse will write for a column. The documentation's blunt about what happens when you exceed it:
All the data that can't be encoded due to maximum dictionary size limitation ClickHouse writes in an ordinary method.
No error. No warning. No log line at the default level. Values that fit go in the dictionary as integer keys; everything past the limit is written as plain strings, in the same column, and the column keeps working exactly as before.
There's a companion setting that shapes what "past the limit" means:
low_cardinality_use_single_dictionary_for_part = falseWith the default, when a dictionary fills up the server starts a new one rather than giving up. So a part with many distinct values ends up carrying several dictionaries plus whatever spilled over. Set it to 1 and you get exactly one dictionary per part and everything beyond 8192 stored the ordinary way.
Either way the outcome is the same in the direction that matters: the type stops being an optimisation and starts being an overhead, and nothing tells you.
Why the failure is quiet in both directions
The reason this catches people is that the type's behaviour degrades along two axes that don't move together.
Reads degrade gently. A partly-encoded column still answers queries; you just get less of the compression and less of the integer-comparison speedup than you expected, proportional to how much spilled.
Writes degrade immediately. Every insert has to hash the incoming value and look it up in the current dictionary, and that cost is paid whether or not the value ends up encoded. On a column with millions of distinct values you're paying full price for hashing on every row and getting nothing back.
Memory sits in the middle. Dictionaries live in RAM while parts are being read and merged, so a high-cardinality LowCardinality column adds resident memory in exchange for the storage saving it isn't delivering.
The documentation puts the far edge at a hundred thousand distinct values, past which the type "can perform worse in comparison with using ordinary data types". Between 8192 and 100k you're in a zone where it's neither clearly good nor clearly bad, and that zone is where nobody investigates.
Finding out which columns are lying to you
Cardinality per column is one query, and it's worth running against a real table rather than a sample:
SELECT
uniqExact(status) AS status_uniq,
uniqExact(user_agent) AS ua_uniq,
uniqExact(request_id) AS rid_uniq
FROM logs
WHERE event_date >= today() - 7;Anything above 8192 that's currently declared LowCardinality is spilling. Anything comfortably under it that's a plain String is leaving a win on the table.
The subtlety is that cardinality is measured per part, not per table, and parts are built from inserts and merges. A column with 50,000 distinct values spread evenly across time might sit under 8192 within any single part and encode perfectly well, while the same 50,000 values arriving in one bulk load will not. Sorting order matters here too, since data ordered by the column groups its values into fewer parts.
Which means the honest test is measurement rather than arithmetic:
SELECT
name,
formatReadableSize(sum(data_compressed_bytes)) AS compressed,
formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed,
round(sum(data_uncompressed_bytes) / sum(data_compressed_bytes), 1) AS ratio
FROM system.columns
WHERE table = 'logs' AND database = currentDatabase()
GROUP BY name
ORDER BY sum(data_compressed_bytes) DESC;A LowCardinality column whose compression ratio looks like its neighbouring plain strings isn't being encoded.
Enum is not the alternative people think it is
Enum8 and Enum16 do the same job with the value list fixed in the DDL, which makes them faster on insert - the mapping is static, so there's no hashing and no dictionary to grow - and completely rigid. A value that isn't in the enum is an error, and adding one means an ALTER TABLE.
That rigidity is a feature for a closed set: days of the week, a payment state machine you control, HTTP methods. It's a production incident for anything a third party can extend, because the day a new value appears your inserts start failing.
Enum16 also tops out at 65,535 values, which is comfortably above the point where LowCardinality has stopped helping. If you're choosing between them on capacity you've already picked the wrong tool for that column.
The limits worth knowing
LowCardinality isn't free at query time in every direction. Most string functions work through it transparently, but anything that has to materialise the actual strings - tokenisation, some regex paths, certain joins - decodes the whole column first, and for those the type costs you rather than saving you.
The setting is per-insert rather than per-column, which surprises people who go looking for a way to raise the limit on one wide column. Raising low_cardinality_max_dictionary_size globally trades RAM across every LowCardinality column in the server for the benefit of one, so the usual answer is to change the column type rather than the setting.
And the guidance shifts with version: this is ClickHouse 26.7 behaviour, and defaults in this area have moved before. Read the value out of your own server rather than trusting a number in a post:
SELECT name, value FROM system.settings WHERE name LIKE 'low_cardinality%';