In a ClickHouse MergeTree table, how do PRIMARY KEY and ORDER BY differ?
answer
- two keys, two different jobs
- one sorts rows, one builds the index
- the index key must be a prefix
- collapsing engines need a wider sort key
- omit PRIMARY KEY and it copies ORDER BY
basics
~20 sIn ClickHouse MergeTree, ORDER BY sets the physical sort order of rows inside every part; PRIMARY KEY only defines the sparse index and must be a prefix of ORDER BY. If PRIMARY KEY is omitted it equals ORDER BY.
solid answer
~50 s`ORDER BY` is the sorting key: rows in every data part are written sorted by that expression list. `PRIMARY KEY` is the indexing key: ClickHouse stores one index entry per granule holding the primary-key values of that granule's first row. The primary key must be a **prefix** of the sorting key, and when you leave it out it defaults to the whole `ORDER BY`. You separate them when the sort order needs more columns than the index does — typically in `ReplacingMergeTree` or `AggregatingMergeTree`, where `ORDER BY` must contain every identifying column so rows collapse correctly, but you don't want all of them in an index that is held in memory. A shorter `PRIMARY KEY` also lets you later append columns to `ORDER BY` with `ALTER TABLE ... MODIFY ORDER BY`, which is not possible if the two are identical.
code
sql · 11 linesCREATE TABLE events
(
tenant_id UInt32,
event_date Date,
user_id UInt64,
payload String
)
ENGINE = ReplacingMergeTree
PARTITION BY toYYYYMM(event_date)
PRIMARY KEY (tenant_id, event_date)
ORDER BY (tenant_id, event_date, user_id);go deeper
Be able to say that a MergeTree table stores its rows sorted by ORDER BY, and that PRIMARY KEY, when written separately, must be the first few columns of that same list.
Explain the sparse index: one entry per granule holding the first row's key values, which is why the index key has to be a prefix of the sort key. Know that PRIMARY KEY is not a uniqueness constraint.
Show judgment about when to split them — collapsing engines needing a wide identity, index memory across many parts, and leaving room for ALTER TABLE MODIFY ORDER BY later.
Own the fact that the key is effectively permanent: changing leading columns means a rebuild and swap. Frame key choice as a schema-lifecycle decision reviewed against the query mix, not a one-off DDL detail.
## Two clauses, two jobs A `MergeTree` table declares both a sorting key and an indexing key, and interview candidates routinely conflate them because most DDL only writes one of them. - `ORDER BY (a, b, c)` — the **sorting key**. Every data part written to disk contains its rows physically sorted by `(a, b, c)`. This is what makes column data compress well (neighbouring values are similar) and what makes range reads sequential. It is also what deduplication/aggregation engines use as their row identity. - `PRIMARY KEY (a, b)` — the **indexing key**. ClickHouse builds a *sparse* index: one entry per granule (by default one entry per 8192 rows), storing the primary-key values of the first row of that granule. That index is small enough to be held in memory and is binary-searched at query time to pick a range of granules to read. If you write only `ORDER BY`, the primary key silently becomes the same expression list. If you write both, ClickHouse enforces that `PRIMARY KEY` is a **prefix** of `ORDER BY` — `PRIMARY KEY (a, b)` with `ORDER BY (a, b, c)` is legal; `PRIMARY KEY (b)` with `ORDER BY (a, b)` is not. ## Why the prefix rule exists The index entry for a granule records the key values of the granule's *first* row. That is only usable for skipping if the rows are sorted by exactly that expression, in exactly that order. If the index key were not a prefix of the sort key, the first row's values would tell you nothing about the range covered by the granule, and pruning would be unsound. The same rule is why the index prunes only on a leading run of key columns: a filter on `c` alone cannot narrow granules, because `c` is only sorted *within* equal `(a, b)`. ## When to declare them separately Three real reasons: 1. **Collapsing engines.** `ReplacingMergeTree` and `AggregatingMergeTree` treat the whole `ORDER BY` as the row's identity. If dedup identity is `(tenant_id, entity_id, version_source)` but queries only ever filter on `tenant_id, entity_id`, put the first two in `PRIMARY KEY` and all three in `ORDER BY`. You keep correct collapsing without paying for a wider index. 2. **Index memory.** The primary index is resident per part; a wide key over many parts costs RAM and slows the binary search for no pruning benefit. Trimming trailing columns that no query filters on is free. 3. **Evolvability.** `ALTER TABLE ... MODIFY ORDER BY` can *append* columns to the sorting key only if the primary key stays unchanged as a prefix. Tables where `PRIMARY KEY` was never separated out have no room to grow the sort key later without rebuilding the table. ```sql CREATE TABLE events ( tenant_id UInt32, event_date Date, user_id UInt64, payload String ) ENGINE = ReplacingMergeTree PARTITION BY toYYYYMM(event_date) PRIMARY KEY (tenant_id, event_date) ORDER BY (tenant_id, event_date, user_id); ``` Here the index is two columns wide; dedup identity is three columns wide; the on-disk sort is by all three. ## What neither clause is Neither is a uniqueness constraint. ClickHouse does not enforce uniqueness on the primary key — two rows with identical key values coexist happily in a plain `MergeTree`, and even the collapsing engines only merge duplicates eventually and per part. Coming from PostgreSQL or MySQL, this is the single biggest surprise: `PRIMARY KEY` here means "the thing the sparse index is built on", nothing more. Neither is a B-tree either. There is no per-row index entry to look a row up by; a point lookup on the key still reads at least one whole granule. ## Changing them later You cannot change `ORDER BY` arbitrarily — only append columns, and only when the primary key is a strict prefix that stays put. Changing the leading columns means creating a new table with the desired key and copying the data (`INSERT INTO new SELECT * FROM old`), then swapping names with `EXCHANGE TABLES`. Because of that, key choice is close to irreversible in practice, which is why interviewers press on it. ## Interview traps Saying "the primary key is the unique identifier" or "ORDER BY is just for ORDER BY queries" both signal that the candidate has only used `MergeTree` from a template. The strong answer names the prefix rule, the sparse-index-per-granule model, and one concrete case where separating the two clauses pays.
- Does ClickHouse enforce uniqueness on a MergeTree PRIMARY KEY?No. `PRIMARY KEY` names the columns the sparse index is built on; duplicate key values are stored without complaint. Collapsing engines such as `ReplacingMergeTree` remove duplicates only during background merges, and only within a single partition, so a `SELECT` can still see duplicates until a merge happens — which is why `FINAL` or an aggregate-based rewrite exists.
- Which parts of ORDER BY can you change on an existing table?Only appended columns, via `ALTER TABLE ... MODIFY ORDER BY`, and only when the declared `PRIMARY KEY` remains an unchanged prefix. Changing a leading column means building a new table with the new key, copying data with `INSERT INTO ... SELECT`, and swapping with `EXCHANGE TABLES`. Plan the key as if it were permanent.
- Why does a narrower PRIMARY KEY than ORDER BY save resources?The sparse index holds one entry per granule per part and is kept in memory. Trailing key columns no query filters on inflate every entry, costing RAM across thousands of parts and slowing the binary search, while adding no pruning power — the index can only narrow on a leading run of columns anyway.
saying these in an interview costs you the question
- Calling PRIMARY KEY a uniqueness constraint like in PostgreSQL
- Claiming ClickHouse builds a B-tree over the primary key
- Thinking ORDER BY only affects queries that sort
- Believing PRIMARY KEY can be any subset of ORDER BY
- Assuming ORDER BY can be freely changed later with ALTER