Partitions — learning.lab

It's easy to confuse partitions with the primary key — both involve splitting data up. The primary key sorts rows within a part. A partition decides which parts a row can ever end up in, at a much coarser, physically visible level: partitions are real directories on disk.

Declaring a partition key

CREATE TABLE events
(
    event_date Date,
    user_id    UInt64,
    event      String
)
ENGINE = MergeTree
ORDER BY (user_id, event_date)
PARTITION BY toYYYYMM(event_date);

Every row is assigned a partition by evaluating the partition expression — here, its year and month. Merges never combine parts from different partitions, so each partition evolves as an independent set of parts.

What it buys you: partition pruning

When a query's WHERE clause can be matched against the partition expression, ClickHouse skips entire partitions before opening a single file inside them — before the sparse index is even consulted.

6 monthly partitions, 1 pruned query

SELECT count() FROM events
WHERE event_date >= '2026-04-01'

Click a partition to move the query's lower bound. The 3 directories before 2026-04 are never opened for this query — pruning happens before a single granule is read.

The other reason partitions exist: bulk operations

Because a partition is a physical, self-contained set of files, it can be manipulated as a unit — instantly:

-- delete a whole month in one metadata operation
ALTER TABLE events DROP PARTITION '2026-01';

-- move a month to a different disk (e.g. cold storage)
ALTER TABLE events MOVE PARTITION '2026-01' TO DISK 'cold';

DROP PARTITION unlinks files; it doesn't rewrite anything, so it's effectively instant even on a huge partition — compare that to a row-by-row DELETE, which MergeTree handles far more expensively.

Common mistake

Partitioning by something high-cardinality (per-user, per-hour on a high-volume table) creates thousands of tiny partitions, each merging independently — the opposite of what you want. A good partition key produces a modest number of large partitions (weekly or monthly is typical), not a huge number of small ones.

Production note

A table doesn't need a partition key at all — without one, every part lives in a single implicit partition. Add one only when you actually need pruning by date/tenant or bulk drop/move — not by default on every table.