How to Choose a ClickHouse Primary Key and ORDER BY

October 27, 2026

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_date alone when ORDER BY starts with tenant_id gives 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 KEY that differs from ORDER BY is 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:

  1. Filtered-with-equality columns first. Tenant ID, region, environment — anything that is always filtered and has few distinct values relative to data volume.
  2. Time column next, if you almost always filter by date range. It compresses well because sorted timestamps delta-compress tightly.
  3. 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 JSON and check statistics.rows_read.
  • Compare against SELECT count() FROM table as the full-scan baseline.
  • Or use EXPLAIN indexes = 1 to 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 minmax can 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 BY clause 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.