Choose a primary key

Ordering keys must be defined at table creation and cannot be added later. Changing your mind means creating a new table and copying the data, so this decision deserves the time.

Two rules for picking columns#

Prioritize columns used in WHERE filters, especially those that exclude large numbers of rows. A key column that no query filters on contributes nothing but index size.

Prefer columns correlated with other data. Contiguous storage of related values improves compression ratios and memory efficiency during GROUP BY and ORDER BY.

Four to five columns is typically sufficient. Every extra column enlarges the index and slows inserts without helping queries that never filter on it.

Column order is the decision inside the decision#

The key prunes left to right. A key of (town, street) serves a filter on town, and serves town AND street even better — but a filter on street alone prunes almost nothing, because one street's rows are scattered across every town's range.

Put the column your queries filter on most often first. Where two columns are filtered equally often, the lower-cardinality one usually belongs first, because it groups rows into larger contiguous runs.

Measured effect#

The same query against 60 million Stack Overflow posts, changing only the key.

Without a useful key:

-- ORDER BY tuple()
SELECT count()
FROM stackoverflow.posts_unordered
WHERE (CreationDate >= '2024-01-01') AND (PostTypeId = 'Question')
 
┌─count()─┐
│  192611 │
└─────────┘
1 row in set. Elapsed: 0.055 sec. Processed 59.82 million rows, 361.34 MB

With a key matching the filter:

-- ORDER BY (PostTypeId, toDate(CreationDate))
SELECT count()
FROM stackoverflow.posts_ordered
WHERE (CreationDate >= '2024-01-01') AND (PostTypeId = 'Question')
 
┌─count()─┐
│  192611 │
└─────────┘
1 row in set. Elapsed: 0.013 sec. Processed 196.53 thousand rows, 1.77 MB

Same result, 300 times less data read, about 4 times faster.

Two choices produced that. PostTypeId has a cardinality of 8, making it the logical first entry. toDate(CreationDate) is used rather than the full datetime because a date fits in 16 bits, producing a smaller and faster index.

A worked schema#

CREATE TABLE uk_price_paid
(
    price      UInt32,
    date       Date,
    postcode   LowCardinality(String),
    type       Enum8('terraced' = 1, 'semi-detached' = 2, 'detached' = 3, 'flat' = 4, 'other' = 0),
    is_new     UInt8,
    duration   Enum8('freehold' = 1, 'leasehold' = 2, 'unknown' = 0),
    addr1      String,
    addr2      String,
    street     LowCardinality(String),
    town       LowCardinality(String),
    county     LowCardinality(String)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(date)
ORDER BY (postcode, addr1, addr2);

This serves address lookups, which is what the workload does. It does not serve GROUP BY county — that reads all 30,033,199 rows, and no key can serve every query at once.

Verify your key before you commit to it#

Load a representative sample and check what the index prunes:

EXPLAIN indexes = 1
SELECT count() FROM uk_price_paid WHERE postcode LIKE 'SW1A%';

Read the Granules: N/M line. A small N relative to M means the key works for that query. If N equals M, the index pruned nothing and the key does not serve this filter.

When one key cannot serve everything#

Production workloads query the same table several ways. Three options, in order of cost:

Data-skipping indexes — cheap metadata on non-key columns. See query optimization.
Projections — an alternate ordering of the same table, chosen automatically by the optimizer. Costs disk.
A materialized view into a second table with a different key. Costs disk and insert throughput. See materialized views.

Start with the first. Reach for the third when a second access pattern is as important as the first.

Common mistakes#

Putting a high-cardinality column first. A key starting with user_id or a UUID prunes well only for exact-id lookups and poorly for everything else.
Using the full datetime when a date suffices. Wider index, no extra pruning.
Adding every column you might filter on. Four to five is typically enough; beyond that you pay on every insert.
Assuming the key deduplicates. Primary keys in ClickHouse are not unique. Use ReplacingMergeTree if you need one row per key.

Next steps#

The sparse primary index — the mechanism behind the pruning
Optimize query performance — tools for queries the key does not serve
Insert data efficiently — the other half of MergeTree performance

Updated Aug 15, 2026