Choosing a Primary Key in ClickHouse MergeTree for Faster Range Queries

The problem: slow range scans despite using ClickHouse

You have a ClickHouse table that stores event logs. Queries that filter on a date range and a user identifier still read large fractions of the data, causing high latency and unnecessary I/O. The table uses the default MergeTree engine, but the primary key you selected does not align with the query patterns, so ClickHouse cannot prune partitions effectively.

Thesis: a well‑chosen primary key enables partition pruning and skip indexes, turning expensive scans into fast range lookups

In ClickHouse’s MergeTree family, the primary key defines the sort order of data parts on disk. Because the engine is LSM‑based, each part is stored sorted by that key, and the query processor can skip whole parts when the key’s ranges do not overlap the query predicates. Additionally, skip indexes (min/max, set, bloom) are built automatically on the primary key columns, further reducing I/O.

Understanding how MergeTree uses the primary key

• Sorting: When data is inserted, it is first written to a temporary part, then merged in the background into larger parts that are sorted by the primary key.
• Partition pruning: If the table is also partitioned (e.g., by month), ClickHouse can skip entire partitions when the partition key does not match the query.
• Skip indexes: Each part stores minimal metadata (min/max values) for the primary key columns; the query engine uses this to skip parts that cannot contain matching rows.

Practical steps to select a good primary key

1. Identify the most selective equality or range predicates in your frequent queries. In the event‑log example, these are event_date (range) and user_id (equality).
2. Order the key columns by selectivity and query frequency. Put the column used for range filters first, followed by equality columns.
3. Keep the key short to reduce merge overhead; avoid high‑cardinality columns that are never used in filters.
4. Test with EXPLAIN to verify that the predicted number of parts read drops after the change.

Worked example: redesigning an event table

Original table:

CREATE TABLE events_orig (
    event_date Date,
    user_id UInt64,
    page String,
    value Float64
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY (page, event_date);

Typical query:

SELECT count() FROM events_orig
WHERE event_date BETWEEN '2026-09-01' AND '2026-09-30'
  AND user_id = 12345;

Because the primary key is (page, event_date), the range on event_date cannot prune parts effectively; the filter on user_id is not part of the key, so skip indexes do not help.

Re‑created table with a better key:

CREATE TABLE events_new (
    event_date Date,
    user_id UInt64,
    page String,
    value Float64
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_date, user_id, page);

Now the primary key starts with event_date (range) followed by user_id (equality). Running the same query:

EXPLAIN SELECT count() FROM events_new
WHERE event_date BETWEEN '2026-09-01' AND '2026-09-30'
  AND user_id = 12345;

You should see a marked reduction in the rows_read and bytes_read estimates, indicating that ClickHouse is skipping irrelevant parts.

Trade‑offs and limitations

• Write amplification: The LSM merge process may increase storage usage 2‑3×, especially if the primary key leads to many small parts that need frequent merging.
• Merge latency spikes: Background merges can cause occasional latency bursts; monitor system.merges for duration and merged bytes.
• Key immutability: Changing the primary key requires recreating the table (or using ALTER TABLE ... MODIFY ORDER BY on newer versions) and re‑inserting data.

To check the impact in production, periodically query:

SELECT partition, count() AS parts, sum(bytes) AS bytes
FROM system.parts
WHERE table = 'events_new' AND active
GROUP BY partition
ORDER BY parts DESC;

Watch for a steady or decreasing number of parts per partition over time; a rising trend may indicate that merges are not keeping up.

Actionable closing

Start by logging your top‑10 queries and extracting the columns used in range and equality filters. Prototype a new table with the suggested key order, run EXPLAIN to confirm reduced scans, and then migrate a small subset of data to validate performance before a full cut‑over. Keep an eye on merge metrics and disk usage to ensure the write amplification stays within acceptable bounds.