Too many parts in ClickHouse: causes and fixes

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 — you get Too many parts errors, throttled inserts, and queries that slow down as part counts grow.

Find the fragmented tables

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.

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

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.

When to force a merge

OPTIMIZE TABLE ... FINAL merges parts on demand, but it is heavy — run it off-peak and only as a one-time cleanup, not on a schedule. Fixing the insert pattern is the real solution.

Catch it automatically

OptiHouse flags fragmented tables, insert pressure and merge debt on every read-only scan, each with a runbook. Start free or try the optimizer.