Parts, merges & ingestion health

ClickHouse stores every insert as a part and merges parts in the background. When inserts arrive too small and too often, parts pile up faster than merges can keep up — and queries, merges and replicas all suffer. This playbook shows how to spot and fix merge debt at the source.

Symptoms

• Too many parts errors, or inserts being throttled / rejected.
• Query latency creeps up as part counts grow on a hot table.
• Background merges run constantly and never catch up.
• Replicas lag, or their replication queue keeps growing.

Step 1 — Find tables with too many parts

A healthy MergeTree table holds tens of active parts per partition, not thousands. Rank tables by active part count and watch the average part size — many tiny parts is the tell-tale sign of bad ingestion.

SELECT
    database,
    table,
    count()                                AS parts,
    formatReadableSize(avg(bytes_on_disk)) AS avg_part,
    formatReadableSize(sum(bytes_on_disk)) AS total
FROM system.parts
WHERE active
GROUP BY database, table
HAVING parts > 300
ORDER BY parts DESC;

Step 2 — Diagnose insert pressure

Tiny parts come from tiny, frequent inserts. `system.part_log` records each part as it is created — a high rate of small new parts means the writer is inserting row-by-row instead of in batches.

SELECT
    table,
    count()                                AS new_parts,
    formatReadableSize(avg(size_in_bytes)) AS avg_new_part
FROM system.part_log
WHERE event_type = 'NewPart'
  AND event_time > now() - INTERVAL 1 HOUR
GROUP BY table
ORDER BY new_parts DESC;

Step 3 — Check the merge backlog

`system.merges` shows merges in flight right now; a persistently long list means the cluster cannot keep up. Pair it with the part counts above to confirm the debt is growing, not transient.

SELECT
    database,
    table,
    elapsed,
    round(progress, 2)                       AS progress,
    formatReadableSize(total_size_bytes_compressed) AS size,
    num_parts
FROM system.merges
ORDER BY elapsed DESC;

Step 4 — Watch replica health

On a replicated cluster, merge debt and insert pressure show up as replication lag. `system.replicas` exposes the queue size and delay per replica — a growing queue_size or absolute_delay means a replica is falling behind.

SELECT
    database,
    table,
    is_readonly,
    absolute_delay,
    queue_size,
    inserts_in_queue,
    merges_in_queue
FROM system.replicas
WHERE is_readonly
   OR absolute_delay > 60
   OR queue_size > 100;

How OptiHouse automates this