Operate parts, retention, and replication

Healthy MergeTree tables maintain a manageable stream of parts and background merges. Too many tiny inserts or partitions can overwhelm that work, and part count is the vital sign that tells you early:

SELECT count() AS active_parts, sum(rows) AS total_rows
FROM system.parts
WHERE table = 'events' AND active;

A steady table holds tens to low hundreds of active parts. A count climbing into the thousands means merges are losing the race against inserts — fix the insert pattern before the Too many parts errors start. system.merges shows what merge work is running right now.

Retention that matches the storage model

Use TTL rules or deliberate partition drops for retention. A TTL clause makes expiry automatic:

ALTER TABLE events MODIFY TTL ts + INTERVAL 90 DAY;

ClickHouse removes expired rows during background merges — eventually, not at midnight on day 90. Partition drops (ALTER TABLE events DROP PARTITION '202505') are the manual alternative: instant, predictable, and cheap because ClickHouse deletes the partition’s parts outright instead of rewriting anything.

What retention should not be is ALTER TABLE events DELETE WHERE ts < ... on a schedule. That’s a mutation, and mutations are asynchronous: the statement returns immediately while ClickHouse rewrites every affected part in the background. Check what’s actually finished:

SELECT command, is_done, latest_fail_reason
FROM system.mutations
WHERE table = 'events';

The classic surprise is running a delete, seeing it “succeed”, and finding the rows still present in the next query — is_done = 0 means the rewrite is still grinding, possibly for hours on a big table. A stuck mutation with a latest_fail_reason blocks the ones queued behind it. Mutations are for rare corrections; TTL and partition drops are for routine retention.

Replication and sharding solve different problems

Replication provides copies and failover; distributed tables and shards spread data and query work. Each adds failure modes and operational cost.

A ReplicatedMergeTree table keeps the same data on several servers, coordinated through Keeper. Lose one replica and the others keep serving — availability. Sharding splits the dataset across servers behind a Distributed table so no single machine holds or scans everything — capacity. Losing one replica of a replicated table costs you redundancy; losing a shard with no replicas costs you that slice of the data. That asymmetry is why production clusters replicate each shard, and why a single well-sized server with backups is the right starting point until data volume forces the complexity.

Set a short retention policy on disposable events. Observe parts before and after merges, then explain how one replica failure differs from one shard failure — if the explanation takes more than two sentences, revisit this lesson before operating a cluster.