TopolearnRetention and cold storage are routine for event data, and partitions make them nearly free.
PARTITION BY splits a MergeTree table into independent groups of parts; parts from different partitions are never merged together. In ClickHouse partitioning is mainly a data management tool, not a speed trick; the sort key does the speed work.
What partitions give you:
ALTER TABLE t DROP PARTITION 202401deletes a month instantly, without rewriting anything.DETACH/ATTACH PARTITION,MOVE PARTITION ... TO TABLE, andREPLACE PARTITIONfor backfills and swaps.- Pruning: queries filtering on the partition expression skip whole partitions, as a bonus.
Keep partitions coarse. toYYYYMM(ts) (monthly) is the usual choice; daily can be fine for very large tables. Partitioning by a high-cardinality column such as user id creates huge numbers of parts and triggers "Too many parts" errors, because every insert touching N partitions creates N parts.
TTL expires data automatically during merges:
- Table TTL:
TTL ts + INTERVAL 90 DAYdeletes rows older than 90 days. - Column TTL resets a column to its default after a time (drop raw payloads, keep counters).
TTL ts + INTERVAL 30 DAY TO VOLUME 'cold'moves data to cheaper disks in a tiered storage policy.
When a TTL lines up with partitions, the setting ttl_only_drop_parts = 1 lets ClickHouse drop whole expired parts instead of rewriting them, which is much cheaper.
Example
CREATE TABLE metrics
(
ts DateTime,
host LowCardinality(String),
value Float64,
raw String TTL ts + INTERVAL 7 DAY -- column TTL: clear after a week
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(ts)
ORDER BY (host, ts)
TTL ts + INTERVAL 90 DAY -- delete rows after 90 days
SETTINGS ttl_only_drop_parts = 1;
INSERT INTO metrics VALUES ('2026-08-15 10:00:00', 'a', 1, 'x'), ('2026-09-15 10:00:00', 'a', 2, 'y');
SELECT partition, sum(rows) FROM system.parts
WHERE table = 'metrics' AND active GROUP BY partition ORDER BY partition;
-- 202608 1
-- 202609 1 (until the 90-day TTL removes them)
ALTER TABLE metrics DROP PARTITION 202608; -- instant month deleteRemember
- Partitions are for lifecycle: drop, move, replace whole chunks cheaply
- Keep partitions coarse, typically monthly
- TTL deletes or moves rows during merges; columns can have their own TTL
Watch out for
- Partitioning by a high-cardinality column, which explodes the number of parts.