OLAP on Tap: High time for low cardinality

As we discussed yesterday, the strictness of a type is very much tied to the read and aggregate performance of OLAP databases. The amount of variance in a column is called "cardinality". Fields with higher cardinality have more variance, like UUIDs. Fields with lower cardinality have lower variance: like boolean.

The corollary of this is compression and encoding efficiency. Low cardinality, great compression. High cardinality, bad compression.

Cardinality comparison table

Low Cardinality Wrapper

You notice there in country, that the column type is string, but that it uses a wrapper, LowCardinality().

What that does is encode each of those 200 countries as a dictionary, drastically reducing the possible "amount of information" that could be possible in that column, allowing for improved compression.

This is really powerful, and you should use it if you have less than 10-50k unique values in a column (assuming OLAP scale tables, in the millions of rows). As you get past this limit, the overhead of dictionary lookup can obviate the performance gains from using this wrapper.

Enums

When the possible space of results is small, manually defining them as enumerates (e.g. CA = 1, NY = 2, TX = 3) makes for an even faster and smaller column than using LowCardinality(). Don't use this when you expect new values (will require a migration to update).

Agent docs

Use these in with your Claude, Cursor or other agents:

🐙 https://github.com/oatsandsugar/olap-agent-ref/blob/main/1-type-rules.md#2-lowcardinality-rules-dictionary-encoding

References

• 🔗 CH Low Cardinality: https://clickhouse.com/docs/sql-reference/data-types/lowcardinality
• 🔗 CH Enum: https://clickhouse.com/docs/sql-reference/data-types/enum

Appendix: Practical Heuristics for Cardinality

Table showing heuristics for Types / Wrappers and expected cardinality