Partitions

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.

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.