ORDER BY & Primary Keys

ORDER BY is the single most consequential decision in a MergeTree schema. It defines the physical sort order of every row on disk, and — unless you say otherwise — doubles as the table's primary key.

Not a B-Tree — a sparse index

Postgres's B-Tree index has one entry per row. ClickHouse tables can hold tens of billions of rows, so indexing every single one would make the index itself enormous. Instead, rows are grouped into granules — 8192 rows by default — and the primary index stores exactly one entry per granule: the primary key value of its first row. That's the "sparse" part.

How a lookup actually works

For WHERE user_id = 204, ClickHouse doesn't scan rows — it binary-searches the small in-memory array of marks to find which granule could contain 204, then reads and decompresses only that granule's columns (and its immediate neighbors, since the target may straddle a boundary). On a real table that's the difference between reading three rows and reading nine — at production scale, between reading megabytes and reading gigabytes.

CREATE TABLE events
(
  user_id    UInt64,
  event_time DateTime,
  event      LowCardinality(String)
)
ENGINE = MergeTree
ORDER BY (user_id, event_time);

Column order in ORDER BY matters

Rows are sorted by the first column, then the second breaks ties within it, and so on — exactly like a compound index. A filter on the first column can binary-search straight to the right granules. A filter on only the second column gets no such benefit, because rows with a matching event_time are scattered across every user_id group.

• Put the column your queries filter on most often first.
• Prefer lower-cardinality columns before higher-cardinality ones when both are filtered together — it keeps runs of identical values longer, which compresses and skips better.
• The primary key doesn't need to be unique — unlike Postgres, duplicate keys are completely normal.