ORDER BY Design

ORDER BY defines the physical sort order of every part. Because ClickHouse skips granules using that order, it decides which queries are fast and which read the whole table.

Primary Keys

In ClickHouse the primary key is not a uniqueness constraint. It is the prefix of the sorting key that is stored in the sparse index.

ENGINE = MergeTree
ORDER BY (tenant_id, event_type, event_time)
-- PRIMARY KEY defaults to the ORDER BY expression.

PRIMARY KEY may be a prefix of ORDER BY when you want the data sorted more finely than it is indexed — the index stays smaller while the physical order still helps compression and range reads:

ORDER BY (tenant_id, event_type, event_time, user_id)
PRIMARY KEY (tenant_id, event_type)

Duplicate rows are allowed. Nothing enforces uniqueness; deduplication is a merge-time behaviour of specific engines, not a constraint.

Choosing the column order

The rule is: low cardinality first, and the columns you always filter on before the ones you sometimes filter on.

Granule skipping works on prefixes. With ORDER BY (a, b, c):

• A filter on a skips effectively.
• A filter on a and b skips very effectively.
• A filter on b alone skips almost nothing — the values of b are scattered across the whole table.

-- Queries always filter by tenant, usually by event type, and by a time range.
ORDER BY (tenant_id, event_type, event_time)

Putting a high-cardinality column such as user_id first would make every other filter useless and would compress poorly, because neighbouring rows would no longer resemble each other.

Verifying that skipping happens

EXPLAIN indexes = 1
SELECT count() FROM events
WHERE tenant_id = 42 AND event_time >= now() - INTERVAL 1 DAY;

The output shows granules selected per index step. Compare read_rows in system.query_log against the table's total rows — if they are close, the sorting key is not serving this query.

When queries disagree

If one query filters by tenant_id and another by user_id, one sorting key cannot serve both. Options, in order of preference:

1. A projection — a second physical ordering of the same table, maintained automatically. See Projections.
2. A materialized view into a second table with a different ORDER BY.
3. A data skipping index for the secondary column, which helps only when values are clustered.
4. A second table populated in parallel, accepting the storage and consistency cost.

Changing it later