Balancing Partitioning and Sorting in ClickHouse MergeTree

The Performance Wall When Time-Series Queries Scan Everything

With ClickHouse MergeTree, a query that should touch recent hours can end up opening every data part on disk. That happens when PARTITION BY and ORDER BY do not match the query and retention pattern. The useful takeaway is to use PARTITION BY for pruning whole directories and ORDER BY for in-part skipping, so reads only open relevant parts and blocks.

Partitioning for Pruning and Lifecycle

MergeTree stores data in immutable parts physically sorted by ORDER BY and grouped into directories by PARTITION BY. Partition pruning happens at planning time when a filter matches the PARTITION BY expression, so a query limited to recent months only opens the relevant parts.

Partitioning is the primary engineering lever for retention. A TTL that drops old partitions removes whole directories instantly without row scans. Over-partitioning creates many small parts, increasing metadata overhead, merge queue pressure and memory use for the parts list.

Ordering for Granular Retrieval

ORDER BY defines the primary index and in-part sort order. Leading columns are used for min-max skipping and granular index lookups, making prefix filters efficient for point and range queries.

Choosing a high cardinality leading column improves selectivity but can increase merge time and memory during background merges. PARTITION BY and ORDER BY are schema-defining; changing them requires a table rebuild or CREATE TABLE AS SELECT.

Worked Example: Monthly Events with Tenant Scoping

A common decision is monthly partitions for daily event data with a 13 month TTL.

CREATE TABLE system_events (
    tenant_id UInt32,
    event_date Date,
    event_id UInt64,
    payload String
)
ENGINE = MergeTree
PARTITION BY toStartOfMonth(event_date)
ORDER BY (tenant_id, event_date, event_id)
TTL event_date + INTERVAL 13 MONTH;

Engineering decisions:

• PARTITION BY toStartOfMonth(event_date) groups data by month for pruning and fast TTL drops.
• ORDER BY tenant_id, event_date, event_id makes tenant scoped time range queries efficient via prefix skipping.
• TTL moves or drops old partitions at partition granularity.

Trade-offs and Verification

Read selectivity improves with a specific ORDER BY, but write amplification and merge cost rise. TTL drops are partition granularity dependent; fine grained TTL needs projections to avoid scanning unwanted rows within a partition.

Verify layout with system.parts:

SELECT partition, name, rows, bytes
FROM system.parts
WHERE table = 'system_events' AND active = 1;

Check pruning with EXPLAIN:

EXPLAIN SELECT * FROM system_events WHERE tenant_id = 123 AND event_date >= '2026-01-01' AND event_date < '2026-02-01';

Confirm selected parts are limited to the matching month partitions. Compare scanned bytes and time from system.query_log for filtered vs unfiltered queries to quantify benefit.