How to Choose a ClickHouse Primary Key and ORDER BY
In ClickHouse, ORDER BY defines the physical sort of the table and the sparse primary index built on top of it. You set it at table creation in the MergeTree engine clause: ORDER BY (tenant_id, event_date, user_id). Choose badly and every query scans the whole table because the index cannot skip granules.
The mental model that fixes most confusion: unlike Postgres, ClickHouse has no separate conventional index. The sort order is the index. Everything follows from that.
How does the ClickHouse primary index actually work?
ClickHouse stores data in parts, each sorted by the ORDER BY tuple. Within a part it writes a sparse index — one mark entry per 8192 rows (the index_granularity default). A query with a matching WHERE clause binary-searches the marks and reads only the granules it needs.
Consequences:
- The index can skip data only on a prefix of the ORDER BY columns. Filtering on
event_datealone when ORDER BY starts withtenant_idgives you almost nothing. - A mark covers 8192 rows. If your filter matches scattered values with high cardinality, you may read nearly every granule anyway.
- Adding a column to
PRIMARY KEYthat differs fromORDER BYis allowed but rarely helpful — it can only be a prefix subset of the sort key.
What is the rule for column order?
Low cardinality first, high cardinality later, ordered by how you filter.
The practical heuristic:
- Filtered-with-equality columns first. Tenant ID, region, environment — anything that is always filtered and has few distinct values relative to data volume.
- Time column next, if you almost always filter by date range. It compresses well because sorted timestamps delta-compress tightly.
- High-cardinality columns last. User ID, session ID, event ID — they help only when the earlier columns have already narrowed the scan.
Inverting this — ORDER BY (user_id, event_date) on a multi-tenant events table — is the classic mistake. Every query with a date filter reads everything.
How do you verify a key actually helps?
Measure granules read, not wall clock.
- Run the query with
FORMAT JSONand checkstatistics.rows_read. - Compare against
SELECT count() FROM tableas the full-scan baseline. - Or use
EXPLAIN indexes = 1to see which parts and granules were skipped.
If the query reads 90% of rows for a filter that should be tight, the key is wrong or the filter is not on the prefix.
What about cardinality extremes?
| Column type | Example | Verdict |
|---|---|---|
| Very low (2–10 values) | env, is_mobile |
Good early if always filtered, otherwise wastes prefix |
| Low-medium (100s–1000s) | tenant_id, region |
Best early columns |
| Medium (100k–1M) | user_id |
Last position only |
| Near-unique | event_id, uuid |
Never in ORDER BY; use as a data column |
A near-unique first column makes the sparse index useless — the mark ranges are effectively random relative to that column’s neighbours, and compression degrades badly.
How do you handle multiple access patterns?
You cannot serve every query pattern with one sort order. Three honest options:
- Projections. A projection is an alternative sort order for the same data, maintained automatically. Available in current ClickHouse versions and the right answer for a secondary access pattern that is used often.
- Materialized views. Pre-aggregate for dashboard queries. Costs storage, saves enormous query time for rollups.
- A second table. Simplest to reason about, doubles ingestion cost.
Pick projections for point-lookup patterns and materialized views for aggregations. Do not try to make one table serve both.
What breaks in practice
- Changing ORDER BY later. You cannot alter it in place. You create a new table and insert-select, which for a large table is a real migration window.
ORDER BY tuple(). This means no sort, no index. It is valid for tiny reference tables and a disaster for anything else.- Skipping indexes as a patch.
ADD INDEX ... TYPE minmaxcan help, but bolting them on after the fact usually means the ORDER BY was wrong. - Forgetting partitions.
PARTITION BY toYYYYMM(event_date)with a tight range in queries keeps part counts manageable; too many parts slows merges.
Who this matters for
Anyone putting more than a few hundred million rows into ClickHouse — the point where a bad key becomes minutes instead of milliseconds. Below that, almost any reasonable ORDER BY performs acceptably and you should ship rather than deliberate.
Teams migrating from Postgres should spend an hour on this before creating tables. The migration is much harder after ingestion starts.
Key Takeaways
- In ClickHouse the
ORDER BYclause is the index; there is no separate conventional index on MergeTree tables. - Only a prefix of the ORDER BY columns can be used for skipping granules.
- Put low-cardinality, always-filtered columns first and near-unique IDs last or nowhere.
- Verify with rows_read and EXPLAIN indexes rather than query timing.
- Use projections or materialized views for secondary access patterns, not a compromised sort key.
