Runbook: parts, merges and mutations

Every insert creates at least one part, background merges combine parts, and every ALTER ... UPDATE|DELETE rewrites parts. When inserts slow down, fail with TOO_MANY_PARTS, or a delete “does not finish”, the answer is almost always in one of the system tables below. Read this page top to bottom the first time; after that use the contents list.

What runs where. Queries on system.parts, system.merges, system.detached_parts and system.mutations were run on ClickHouse 26.4 in clickhouse local against a demo analytics.events and a monthly-partitioned analytics.metrics. Queries on system.query_log and system.part_log need a real server (those logs do not exist in clickhouse local); they were run against stand-in tables of the same column shape to check the logic, and their output is not shown. system.replication_queue also needs a server with replicated tables. All of these system tables are per node: wrap them in clusterAllReplicas('my_cluster', ...) to ask the whole cluster.

Related reading: parts and partitions, insert batching and “too many parts”, merges, OPTIMIZE and FINAL, mutations, lightweight DELETE and TTL, async inserts.

Contents

• How many parts does each table have?
• Which partitions have the most parts?
• Which INSERT queries create the most parts?
• How often is a table inserted into?
• How many rows were inserted in the last 15 minutes?
• What merges are running for a table right now?
• How do I OPTIMIZE one partition, and why did it do nothing?
• How do I list and drop detached parts?
• How do I watch mutation progress and find a stuck one?
• How do I run a lightweight DELETE without waiting?

How many parts does each table have?

A table with hundreds or thousands of active parts is slow to query and, past a threshold, ClickHouse rejects inserts with TOO_MANY_PARTS. Use this when inserts slow down or fail, when queries got slower for no obvious reason, or as a routine health check.

SELECT
    database,
    `table`,
    count() AS parts,
    sum(rows) AS total_rows,
    round(total_rows / parts) AS avg_rows_per_part,
    max(level) AS max_merge_level,
    formatReadableSize(sum(bytes_on_disk)) AS size
FROM system.parts
WHERE active
  AND database NOT IN ('system', 'INFORMATION_SCHEMA', 'information_schema')
GROUP BY database, `table`
ORDER BY parts DESC
LIMIT 20;

analytics  metrics     12   2400    200     0  29.41 KiB
analytics  events        2  105000  52500   0  924.60 KiB
default    jobs_queue    1  3       3       2  813.00 B

metrics was loaded in twelve 200-row inserts, so it has twelve parts for 2400 rows.

Reading it:

• avg_rows_per_part matters more than the raw count. Thousands of parts of 100 rows is an ingestion problem; a handful of huge parts is healthy.
• max_merge_level of 0 means no merge has happened yet: every part is a fresh insert.
• The limits that trigger errors apply per partition: parts_to_delay_insert (inserts get slower) and parts_to_throw_insert (inserts fail). See the MergeTree settings. The next section shows which partition is crowded.

The usual fix is to insert in larger batches (or use async inserts), not to merge harder.

Which partitions have the most parts?

The part limits are per partition, not per table, so the first question after a Too many parts error is which partition is crowded.

SELECT
    partition_id,
    count() AS parts,
    sum(rows) AS total_rows,
    formatReadableSize(sum(bytes_on_disk)) AS size
FROM system.parts
WHERE active
  AND database = 'analytics'
  AND `table` = 'metrics'
GROUP BY partition_id
ORDER BY parts DESC
LIMIT 20;

202602  2  400  4.90 KiB
202601  2  400  4.90 KiB
202603  2  400  4.90 KiB
202605  1  200  2.45 KiB
202608  1  200  2.45 KiB

To find the crowded partitions anywhere on the server, drop the table filter and group by database and table as well:

SELECT
    database,
    `table`,
    partition_id,
    count() AS active_parts,
    sum(rows) AS rows,
    formatReadableSize(sum(bytes_on_disk)) AS size
FROM system.parts
WHERE active
GROUP BY database, `table`, partition_id
ORDER BY active_parts DESC
LIMIT 20;

Reading it:

• partition_id is the internal identifier. For toYYYYMM(ts) it looks like the value; for strings or tuples it is a hash, and the real value is in the partition column of system.parts.
• A few partitions with far more parts than the rest are usually the hot partitions receiving current data. That is normal as long as merges keep up.
• Many partitions, each with a few fresh parts, mean every batch touches too many partitions: each batch is split per partition, multiplying parts. Use a coarser partition key.
• Partitioning is for data lifecycle (dropping or moving old data), not for query speed; the primary key does that.
• Partitions near the top of the server-wide list are where inserts outpace merges. To act on one, see OPTIMIZE one partition; running SQL in batch shows how to generate one OPTIMIZE ... PARTITION ID statement per partition.

Which INSERT queries create the most parts?

system.parts says a table has too many parts, not who made them. system.part_log records every part creation with the query_id of the insert that caused it, and system.query_log has the text.

“Creates many parts” means one of two things, and the query separates them:

1. One insert fans out into many partitions. An insert is split by partition key and each partition gets its own part, so one query can create hundreds of parts if its rows span many partitions. This shows up as a few query_ids with a high count.
2. Many tiny inserts. Each insert is a few rows, but they arrive constantly. This shows up as a huge number of query_ids with a count of 1 or 2 each.

SELECT
    query_id,
    count() AS parts_created,
    uniqExact(partition_id) AS partitions_touched,
    sum(rows) AS total_rows
FROM system.part_log
WHERE event_date = today()
  AND event_type = 'NewPart'
  AND database = 'analytics' AND `table` = 'events'
GROUP BY query_id
ORDER BY parts_created DESC
LIMIT 10;

Parts with an empty query_id are not attributed to a user query. A common source is the background flush of asynchronous inserts, which writes parts on behalf of many clients at once. Then look the text up:

SELECT
    normalized_query_hash,
    count() AS runs,
    any(substring(query, 1, 200)) AS example
FROM system.query_log
WHERE query_id IN (
        SELECT query_id
        FROM system.part_log
        WHERE event_date = today() AND event_type = 'NewPart'
          AND database = 'analytics' AND `table` = 'events'
        GROUP BY query_id
        ORDER BY count() DESC
        LIMIT 10)
  AND event_date >= today() - 7
GROUP BY normalized_query_hash;

If your clients set log_comment (a per-query setting), add it to the select list to see which job or service sent the insert.

What to do:

• Case 1: align inserts with the partition key (sort or batch by partition before inserting) or use a coarser key.
• Case 2: batch on the client to at least tens of thousands of rows per insert, or enable asynchronous inserts so the server batches for you.

How often is a table inserted into?

Insert frequency decides how many parts a table gets and how hard merges work. Knowing whether a table gets one big insert an hour or ten small ones a second tells you whether to batch, use async inserts, or leave it alone.

Inserts per day, then per hour of today:

SELECT
    event_date,
    count() AS inserts,
    sum(written_rows) AS rows_written
FROM system.query_log
WHERE has(tables, 'analytics.events')
  AND query_kind = 'Insert'
  AND type = 'QueryFinish'
  AND event_date >= today() - 10
GROUP BY event_date
ORDER BY event_date;

SELECT
    toStartOfHour(event_time) AS hour,
    count() AS inserts,
    sum(written_rows) AS rows_written
FROM system.query_log
WHERE has(tables, 'analytics.events')
  AND query_kind = 'Insert'
  AND type = 'QueryFinish'
  AND event_date = today()
GROUP BY hour
ORDER BY hour;

The average gap between inserts: lagInFrame gives each insert the time since the previous one. Skip the first row, which has no predecessor.

SELECT
    count() AS inserts,
    round(avg(gap_s), 2) AS avg_seconds_between_inserts,
    round(stddevSamp(gap_s), 2) AS stddev_seconds,
    round(1 / avg(gap_s), 3) AS inserts_per_second
FROM (
    SELECT
        row_number() OVER w AS n,
        event_time - lagInFrame(event_time) OVER w AS gap_s
    FROM system.query_log
    WHERE has(tables, 'analytics.events')
      AND query_kind = 'Insert'
      AND type = 'QueryFinish'
      AND event_date = today()
    WINDOW w AS (ORDER BY event_time ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
)
WHERE n > 1;

A standard deviation much larger than the average means bursty traffic; one near zero means a steady stream. event_time has one-second resolution; use event_time_microseconds for more. The old neighbor function is deprecated in favour of window functions. Which insert statements run most:

SELECT
    normalized_query_hash,
    count() AS runs,
    any(substring(query, 1, 150)) AS example
FROM system.query_log
WHERE has(tables, 'analytics.events')
  AND query_kind = 'Insert'
  AND type = 'QueryFinish'
  AND event_date = today()
GROUP BY normalized_query_hash
ORDER BY runs DESC
LIMIT 10;

Notes:

• With asynchronous inserts, parts are written per buffer flush rather than per client insert, so the part count is lower than the insert count (system.asynchronous_insert_log records the flushes). For part creation itself use the part log, or the next section.
• An INSERT ... SELECT through a materialized view lists the target table too.

How many rows were inserted in the last 15 minutes?

As long as a part has not been merged, it has level = 0 and its modification_time is when the data arrived. That gives a quick view of recent ingestion with no logging enabled.

SELECT
    toStartOfMinute(modification_time) AS minute,
    count() AS new_parts,
    sum(rows) AS rows
FROM system.parts
WHERE active
  AND database = 'analytics'
  AND `table` = 'events'
  AND level = 0
  AND modification_time > now() - INTERVAL 15 MINUTE
GROUP BY minute
ORDER BY minute;

2026-10-14 15:55:00  2  105000

(right after loading the demo data: two parts, one per insert.)

Reading it:

• It under-counts once merges kick in: a merged part has level > 0 and drops out of the filter. For a busy table, widen the window only as far as you trust merges not to have caught up, or use system.part_log (event type NewPart) for a complete history.
• new_parts per minute is as important as rows: many parts with few rows each is the small-insert pattern that makes merges struggle.
• It sees only the node you are on. On a replicated table, replicas fetch parts rather than creating them, so the count is parts that arrived there, wherever they were first written.

What merges are running for a table right now?

system.merges shows the merges executing at this moment on the node you are connected to, including mutations (ALTER ... UPDATE/DELETE), which are merges in disguise. Use it when a table has many parts and you want to know whether merging happens at all, when an OPTIMIZE or mutation seems stuck, or when CPU or memory is high.

SELECT
    hostName() AS host,
    database,
    `table`,
    partition_id,
    num_parts,
    round(progress * 100, 1) AS progress_pct,
    round(elapsed) AS elapsed_s,
    formatReadableSize(memory_usage) AS memory,
    is_mutation,
    merge_type,
    merge_algorithm
FROM system.merges
WHERE database = 'analytics' AND `table` = 'metrics';

On an idle server this returns no rows, which is itself the answer: nothing is merging.

Reading it:

• num_parts is how many source parts are being combined. result_part_name and source_part_names (not selected above) show exactly which.
• is_mutation = 1 marks a mutation rewriting a part rather than a regular merge.
• merge_algorithm is Horizontal (all columns at once) or Vertical (key columns first, then the rest one at a time, using less memory).
• memory_usage = 0 means the merge is scheduled but has not started reading yet.

The table is node-local. Ask every replica:

SELECT hostName() AS host, `table`, partition_id, num_parts, memory_usage
FROM clusterAllReplicas('my_cluster', system.merges)
WHERE `table` = 'metrics' AND memory_usage > 0;

Merges that are queued, not running. On Replicated tables every merge is first a log entry that each replica puts in its queue; system.replication_queue shows what waits and why.

SELECT
    `table`,
    type,
    new_part_name,
    num_tries,
    num_postponed,
    postpone_reason,
    last_exception
FROM system.replication_queue
WHERE database = 'analytics' AND `table` = 'metrics'
ORDER BY create_time;

type = 'MERGE_PARTS' entries are merges; GET_PART entries are downloads from another replica. A large num_postponed with a postpone_reason such as “not executing because source parts are being merged” is normal under load; a repeating last_exception is what to investigate. The queue exists only on a server with replicated tables: the statement parses but returns nothing in clickhouse local. For TTL-specific postponements see the replication runbook.

How do I OPTIMIZE one partition, and why did it do nothing?

Pick the partition with the most active parts:

SELECT database, `table`, partition_id, count() AS active_parts
FROM system.parts
WHERE `table` = 'metrics' AND active
GROUP BY database, `table`, partition_id
ORDER BY active_parts DESC
LIMIT 20;

Merge only that partition, not the whole table:

OPTIMIZE TABLE analytics.metrics PARTITION ID '202601';

In the demo, partition 202601 went from two parts to one. OPTIMIZE also returns “Ok” when it did nothing. To learn why, turn the no-op into an error:

SET optimize_throw_if_noop = 1;
OPTIMIZE TABLE analytics.metrics PARTITION ID '202604';

Code: 388. DB::Exception: Cannot OPTIMIZE table: There is only one part inside partition. (CANNOT_ASSIGN_OPTIMIZE)

A partition with a single part is already merged. Add FINAL to rewrite it anyway; that is only worthwhile for engines that deduplicate or aggregate (ReplacingMergeTree and friends), and it ignores merge size limits, so use it on one partition at a time. Other common reasons for a no-op: merges are stopped (SYSTEM STOP MERGES), or the parts are too large to merge without FINAL. More in MergeTree merges: horizontal, vertical, OPTIMIZE and FINAL.

How do I list and drop detached parts?

When ClickHouse refuses to use a part (after a crash, a failed fetch, a manual DETACH) it moves the part to the table’s detached/ directory instead of deleting it. Detached parts take disk space until you remove them, and each one is data somebody should look at once.

SELECT `table`, reason, count() AS parts, formatReadableSize(sum(bytes_on_disk)) AS size
FROM system.detached_parts
GROUP BY `table`, reason
ORDER BY `table`, reason;

metrics  (empty)           1  858.92 KiB
metrics  broken-on-start   1  858.92 KiB
metrics  ignored           1  858.92 KiB

reason comes from the prefix of the directory name:

Reason	Meaning
(empty)	Detached by hand with DETACH PART or DETACH PARTITION
broken-on-start	Failed validation when the server started
covered-by-broken	Healthy, but covered by a broken part, so it was set aside
ignored, unexpected, clone	Set aside automatically, for example superseded or unexpected parts left over from replica operations; inspect before deleting

The set of prefixes can grow between releases, so list the distinct values on your server first: SELECT DISTINCT reason FROM system.detached_parts;. Parts with an empty reason are yours to keep, attach or drop. Look at broken-on-start parts before deleting anything; on an unreplicated table the data may exist nowhere else.

Dropping detached parts is blocked by default; each statement needs allow_drop_detached = 1. Generate the statements:

SELECT format(
    'ALTER TABLE {0}.{1} DROP DETACHED PART \'{2}\' SETTINGS allow_drop_detached = 1;',
    database, `table`, name) AS stmt
FROM system.detached_parts
WHERE reason = 'ignored'
FORMAT TSVRaw;

ALTER TABLE analytics.metrics DROP DETACHED PART 'ignored_202603_5_5_0' SETTINGS allow_drop_detached = 1;

Review the list, then run it. Add a LIMIT for a first pass, or INTO OUTFILE 'drop-ignored.sql' to write a file. Detached parts are per replica, so on a cluster run it on each node or add ON CLUSTER. Replace 'ignored' with another reason only after you understand what is in it.

How do I watch mutation progress and find a stuck one?

Every ALTER TABLE ... UPDATE|DELETE, every lightweight DELETE FROM and every APPLY DELETED MASK is a mutation. List the unfinished ones:

SELECT
    database,
    `table`,
    mutation_id,
    command,
    create_time,
    parts_to_do,
    is_killed,
    latest_failed_part,
    latest_fail_time,
    latest_fail_reason
FROM system.mutations
WHERE NOT is_done
ORDER BY create_time;

Reading it:

• parts_to_do counts the parts still to rewrite; at 0 the mutation is done. A steadily dropping number is healthy; a long mutation on a big table simply takes time. parts_to_do_names lists which parts remain.
• command shows what was asked. A lightweight DELETE FROM t WHERE cond appears as (UPDATE _row_exists = 0 WHERE cond).
• A filled-in latest_fail_reason for the same part with a recent latest_fail_time means the mutation cannot be applied to that part (for example a failing cast) and blocks everything queued behind it.

Fix the cause, or cancel it:

KILL MUTATION WHERE database = 'analytics' AND `table` = 'events' AND mutation_id = 'mutation_42.txt';

On a replicated table the ids look like 0000000173, and each replica applies the mutation itself, so run the monitoring query on every replica (clusterAllReplicas) when is_done stays 0 on only one of them. To wait from the client instead of polling, use SETTINGS mutations_sync = 1 (this server) or 2 (all replicas); for lightweight deletes the setting is lightweight_deletes_sync, which defaults to 2 on 26.4. Background: mutations, lightweight DELETE and TTL.

How do I run a lightweight DELETE without waiting?

DELETE FROM events WHERE event_type = 'buy' SETTINGS lightweight_deletes_sync = 0;

By default (lightweight_deletes_sync = 2 on 26.4) the statement waits until all replicas have processed the delete. On a big table that can take long, and a dropped connection looks like a failure while the work goes on. With 0 it returns as soon as the mutation is queued. Check on it:

SELECT mutation_id, command, parts_to_do, is_done
FROM system.mutations
WHERE `table` = 'events' AND NOT is_done;

In the local test the mutation was visible with parts_to_do = 2 right after the statement returned, and count() still returned all rows at that moment; it dropped once the mutation finished. So with 0, do not expect the rows to vanish instantly: they are hidden part by part as the mutation reaches each one. When the query above returns no rows, the delete is applied.

Space is freed only when merges rewrite the parts. To rewrite the masked parts yourself:

ALTER TABLE events APPLY DELETED MASK IN PARTITION ID 'all' SETTINGS mutations_sync = 2;

('all' is the partition id of an unpartitioned table; use the real partition_id otherwise, or omit IN PARTITION for the whole table.) The mask rewrite shows up as its own (APPLY DELETED MASK) entry in system.mutations.

Use DELETE FROM for a modest share of rows. For large shares, drop partitions or rebuild the table: heavy masking makes every query filter on the hidden _row_exists column until the parts are merged. Full discussion: mutations, lightweight DELETE and TTL.