Operate parts, retention, and replication

A healthy MergeTree table keeps a manageable stream of parts and background merges. Too many tiny inserts or too many partitions overwhelm that work, and the part count is the vital sign that warns 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 the merge work 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. Mutations are asynchronous: the statement returns immediately while ClickHouse rewrites every affected part in the background. Check what has 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 there 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 every mutation queued behind it. Mutations are for rare corrections. TTL and partition drops are for routine retention.

Replication and sharding solve different problems

Replication gives you copies and failover. Distributed tables and shards spread data and query work across machines. Each one adds failure modes and operational cost, and people mix them up all the time.

A ReplicatedMergeTree table keeps the same data on several servers, coordinated through Keeper. Lose one replica and the others keep serving. That’s availability.

Sharding splits the dataset across servers behind a Distributed table, so no single machine has to hold or scan everything. That’s capacity.

The asymmetry matters. Losing one replica costs you redundancy. Losing a shard with no replicas costs you that slice of the data, for good. That’s why production clusters replicate every shard, and why a single well-sized server with backups is the right starting point until data volume forces the complexity on you. I’d stay on one server as long as I could.

Try this in the lab. Set a short TTL on a disposable events table, look at system.parts before and after merges run, and watch the expired rows disappear. Then explain, in two sentences, how one replica failure differs from one shard failure. If it takes more than two, reread this lesson before you operate a cluster.