ClickHouse "Too many parts": what to check before you run OPTIMIZE FINAL

Disclosure: I build ProbeDeck, an iOS app for monitoring and operating ClickHouse clusters. The SQL
below works in any ClickHouse client. You do not need ProbeDeck to use it.

An insert fails with this message:

Too many parts (N). Merges are processing significantly slower than inserts

The tempting response is OPTIMIZE TABLE ... FINAL. Hold that command. ClickHouse raises this error
when a partition accumulates active parts faster than the server can merge them. A forced merge can
consume the same CPU, disk bandwidth, and free space that the background merge process needs.

Use this order during an incident:

1. Count active parts by partition and by table.
2. Read the live delay, throw, and total-part thresholds from the cluster.
3. Sample active merges twice to see whether the backlog shrinks.
4. Check disk headroom and, for replicated tables, replica health.
5. Reduce part creation at the writer or partitioning layer.
6. Consider a forced merge after you control incoming pressure.

Find the affected partition

parts_to_throw_insert applies to active parts in one partition. A table-wide count can hide the
shape of the problem. Run this first:

The system tables below report data from the node that serves your query. A load balancer can route
you to a healthy replica while another replica holds the backlog. Connect to each node, or list the
configured cluster names with SELECT DISTINCT cluster FROM system.clusters and replace a local
table such as system.parts with clusterAllReplicas('my_cluster', system.parts). Keep
hostName() in the result so you can see which node produced each row.

SELECT
    hostName() AS host,
    database,
    table,
    partition,
    count() AS active_parts,
    countIf(level = 0) AS level0_parts,
    max(level) AS highest_level,
    sum(rows) AS total_rows,
    formatReadableSize(sum(bytes_on_disk)) AS size_on_disk
FROM system.parts
WHERE active
  AND database NOT IN ('system', 'INFORMATION_SCHEMA', 'information_schema')
GROUP BY host, database, table, partition
ORDER BY active_parts DESC
LIMIT 20;

If one partition sits far above the rest, inspect the hot partition and its partitioning key. If you
see level0_parts grow between samples, inspect the writer for frequent small inserts. If the count
is high but spread across healthy partitions, compare the table total with max_parts_in_total
below.

Do not copy a threshold from a blog post. Read the settings on the cluster you are debugging:

SELECT
    hostName() AS host,
    name,
    value,
    changed
FROM system.merge_tree_settings
WHERE name IN (
    'parts_to_delay_insert',
    'parts_to_throw_insert',
    'max_parts_in_total',
    'max_avg_part_size_for_too_many_parts'
)
ORDER BY name;

system.merge_tree_settings gives you the server-level value. A table can override it in its own
SETTINGS clause. Check the table definition before you treat the result as the effective limit:

SHOW CREATE TABLE analytics.events;

The writer can also pass settings with the insert query. Run the checks through the same user and
connection path as the writer, then inspect its client configuration. Treat an app or dashboard
threshold as a warning heuristic unless it reads the effective setting.

ClickHouse's current guide to OPTIMIZE FINAL uses 150 active parts as an early-warning heuristic.
Your cluster limit comes from its effective settings. Investigate when two samples show growth or
level-0 parts dominate. A count near parts_to_delay_insert means ClickHouse may throttle inserts
soon; parts_to_throw_insert rejects them. max_avg_part_size_for_too_many_parts can disable the
delay and throw checks when the average part size in the affected partition exceeds its value. The
total table limit still applies.

max_parts_in_total protects the table across all partitions. Check the matching table total:

SELECT
    hostName() AS host,
    database,
    table,
    count() AS active_parts_total
FROM system.parts
WHERE active
  AND database NOT IN ('system', 'INFORMATION_SCHEMA', 'information_schema')
GROUP BY host, database, table
ORDER BY active_parts_total DESC
LIMIT 20;

Check whether merges are making progress

Next, inspect the active merge workload:

SELECT
    hostName() AS host,
    database,
    table,
    partition,
    elapsed,
    round(progress * 100, 1) AS progress_pct,
    num_parts,
    is_mutation,
    merge_type,
    formatReadableSize(total_size_bytes_compressed) AS source_size,
    formatReadableSize(memory_usage) AS memory
FROM system.merges
ORDER BY elapsed DESC;

system.merges shows work in progress, not a queue. An empty result does not prove that merges have
stopped. Read it together with two system.parts samples:

• Parts are high and merge progress changes between samples: the cluster is working through a backlog. Reduce incoming pressure and keep measuring.
• Parts keep rising while no merge appears for the affected table: inspect disk space, merge-pool pressure, disabled merges, replication state, and recent configuration changes.
• Merges run, but new level-0 parts arrive faster: fix the writer. More merge threads will not cure a producer that creates parts without bound.

If your server writes system.part_log, estimate how completed merges changed the part count over
the same window:

SELECT
    hostName() AS host,
    countIf(event_type = 'NewPart') AS new_parts,
    countIf(event_type = 'MergeParts') AS merge_outputs,
    sumIf(length(merged_from), event_type = 'MergeParts') AS merge_inputs,
    new_parts + merge_outputs - merge_inputs AS estimated_net_change
FROM system.part_log
WHERE event_time >= now() - INTERVAL 10 MINUTE
  AND database = 'analytics'
  AND table = 'events'
  AND event_type IN ('NewPart', 'MergeParts')
GROUP BY host;

One MergeParts event can consume several source parts, so raw event counts use different units.
The estimate above ignores downloads, removals, and events that cross the time-window boundary.
Use repeated system.parts snapshots as the primary signal. Replace the example identifiers and
save two or three samples a few minutes apart.

Stalled merges also need disk and replica checks. On self-managed clusters, inspect every server
that stores the affected table:

SELECT
    hostName() AS host,
    name,
    path,
    formatReadableSize(free_space) AS free,
    formatReadableSize(unreserved_space) AS unreserved,
    formatReadableSize(total_space) AS total,
    formatReadableSize(keep_free_space) AS reserved
FROM system.disks
ORDER BY name;

No fixed free-space percentage fits every merge because candidate parts and storage policies differ.
Treat disk as the bottleneck when unreserved_space keeps falling while merges stall, or when the
server log reports space-reservation failures. Check disk saturation in your host monitoring. For
ReplicatedMergeTree tables, inspect replica state as well:

SELECT
    hostName() AS host,
    database,
    table,
    is_readonly,
    is_session_expired,
    queue_size,
    inserts_in_queue,
    merges_in_queue,
    queue_oldest_time,
    absolute_delay
FROM system.replicas
WHERE is_readonly OR is_session_expired OR queue_size > 0
ORDER BY queue_size DESC;

A nonzero queue can be normal. Sample it twice. A read-only replica, an expired session, a growing
queue, or rising absolute_delay points to replication trouble and adds pressure to ClickHouse
Keeper.

Check the writer before tuning the server

Each synchronous insert can create at least one part for every partition touched by its block.
ClickHouse recommends batching at least 1,000 rows per insert, with 10,000 to 100,000 rows as the
better range for many workloads. The right batch size still depends on row width, latency, memory,
and the number of partitions touched. For synchronous ingestion, start near one insert per second
and adjust from measurements.

Check these failure modes:

• A producer sends one row or a tiny batch per request.
• One insert touches many partitions.
• Several writers use incompatible async-insert settings, so ClickHouse cannot combine their data into one buffer.
• Materialized views multiply one source insert into parts in several target tables.
• Mutations, TTL work, or disk pressure compete with regular merges.

Check partition granularity before you tune merge pools. Run SHOW CREATE TABLE and count the
active partitions:

SELECT
    hostName() AS host,
    database,
    table,
    uniqExact(partition) AS active_partitions,
    count() AS active_parts
FROM system.parts
WHERE active
  AND database NOT IN ('system', 'INFORMATION_SCHEMA', 'information_schema')
GROUP BY host, database, table
ORDER BY active_partitions DESC
LIMIT 20;

A large partition count alone does not prove a bad key. Look for a key that opens a partition per
ID, per tenant, or per timestamp truncated to an hour or less. ClickHouse cannot merge parts across
partition boundaries. Fixing that design requires a replacement table with a coarser partition key
and a controlled data migration.

ClickHouse 26.3 LTS enables async inserts by default. On older versions, enable them on the ingest
path when client-side batching is not practical. On 26.3 and later, verify that the client did not
override the setting. Check it using the same user and connection settings as the writer:

SELECT hostName() AS host, name, value, changed
FROM system.settings
WHERE name IN ('async_insert', 'wait_for_async_insert', 'compatibility')
ORDER BY name;

An older pinned compatibility value can preserve the previous async-insert default after a server
upgrade. Trust the async_insert value returned through the writer's connection instead of the
server version alone.

Keep wait_for_async_insert = 1 when the client needs acknowledgement after the buffer reaches
durable storage. With 0, the server can acknowledge data while it still lives in memory, and the
client will not receive flush errors in the same way.

OPTIMIZE FINAL does not fix ingestion

OPTIMIZE TABLE ... FINAL rewrites the parts that exist now. It does not change the rate at which
the writer creates new ones. When ingestion resumes, the exception can return. The forced merge can
also create a large part and consume resources on a cluster that already lacks merge capacity.

Reserve a forced merge for cases where you understand the affected table and partition, have enough
disk and I/O headroom, and have reduced the source of new parts. Raising parts_to_throw_insert has
the same problem: it moves the safety boundary without repairing ingestion.

The durable fixes are upstream:

1. Batch more rows per insert.
2. Reduce insert frequency.
3. Use async inserts where they fit the durability and latency requirements.
4. Revisit a high-cardinality partition key through a planned migration.
5. Tune merge resources after measurements show a server-side bottleneck.

During an incident, I run the same checks from the ProbeDeck guide to ClickHouse Too many parts.
ProbeDeck shows raw maximum active-part counts and current merges from an iPhone or iPad. Compare
those counts with the effective settings on your cluster. Monitoring is free. Credentials stay in
the iOS Keychain, and the app has no backend between the device and your cluster.

ClickHouse is a registered trademark of ClickHouse, Inc. ProbeDeck is not affiliated with,
endorsed by, or sponsored by ClickHouse, Inc.