ClickHouse Partitioning Strategy

PARTITION BY splits a table into independent groups of parts. Partitions are a data management feature first and a query optimisation second — that ordering is the key to using them well.

CREATE TABLE events
(
    event_time DateTime,
    event_date Date MATERIALIZED toDate(event_time),
    tenant_id  UInt64,
    event_type LowCardinality(String)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_date)
ORDER BY (tenant_id, event_type, event_time);

What partitions give you

Instant deletion of whole ranges. Dropping a partition removes its parts as a metadata operation:

ALTER TABLE events DROP PARTITION '202501';

That is the difference between expiring a month of data in milliseconds and rewriting parts with a DELETE mutation for hours.

Partition-level operations. Detach, attach, freeze for backup, and move between storage tiers all work per partition.

Coarse pruning. A query filtering on the partition expression skips whole partitions before the primary index is consulted. This is a bonus — granule skipping from the sorting key does the real work.

Over-partitioning is the common mistake

Reasonable starting points:

If tenants must be deletable independently, use TTL or targeted DELETE mutations rather than partitioning by tenant.

Checking the layout

SELECT partition,
       count() AS parts,
       sum(rows) AS rows,
       formatReadableSize(sum(bytes_on_disk)) AS size
FROM system.parts
WHERE active AND table = 'events'
GROUP BY partition
ORDER BY partition DESC;

A healthy table has few parts per partition (single digits to low tens after merges settle) and a manageable number of partitions.

Partition operations

-- Detach: parts remain on disk in the detached directory, queries no longer see them.
ALTER TABLE events DETACH PARTITION '202501';
 
-- Reattach if you detached the wrong one.
ALTER TABLE events ATTACH PARTITION '202501';
 
-- Move a partition to another table with an identical structure.
ALTER TABLE events MOVE PARTITION '202501' TO TABLE events_archive;
 
-- Snapshot for backup: hard links under shadow/.
ALTER TABLE events FREEZE PARTITION '202501';