ClickHouse Ingestion Rate Metrics from system.part

Q: How do I find inserts that produced exactly one part?

Filter the inner query by number_of_parts_per_insert = 1 . Those are the ideal inserts. Compare their rate to the total to see how often you achieve them.

system.part_log records every part operation in MergeTree tables: new parts from inserts, merges, mutations, downloads from replicas, and removals. The three queries below extract operational ingestion metrics from it. They answer the questions every ClickHouse operator eventually asks: are inserts batched well, which partition is creating part churn, and which tables receive inserts more often than once per second per table (the threshold above which Too Many Parts errors start). Run these queries on each shard. They scope to the current day by default.

Insert rate, parts per insert, rows per part

This query rolls up NewPart events from part_log and exposes the metrics that matter when tuning insert batching. The inner subquery aggregates per query_id (one insert) so the outer query can compute distributions across inserts.

SELECT database, table, time_bucket,
       max(number_of_parts_per_insert)    AS max_parts_pi,
       median(number_of_parts_per_insert) AS median_parts_pi,
       min(min_rows_per_part)             AS min_rows_pp,
       max(max_rows_per_part)             AS max_rows_pp,
       median(median_rows_per_part)       AS median_rows_pp,
       min(rows_per_insert)               AS min_rows_pi,
       median(rows_per_insert)            AS median_rows_pi,
       max(rows_per_insert)               AS max_rows_pi,
       sum(rows_per_insert)               AS rows_inserted,
       sum(seconds_per_insert)            AS parts_creation_seconds,
       count()                            AS inserts,
       sum(number_of_parts_per_insert)    AS new_parts,
       max(last_part_pi) - min(first_part_pi) AS insert_period,
       inserts * 60 / insert_period       AS inserts_per_minute
FROM
(
    SELECT
        database,
        table,
        toStartOfDay(event_time) AS time_bucket,
        count() AS number_of_parts_per_insert,
        min(rows)         AS min_rows_per_part,
        max(rows)         AS max_rows_per_part,
        median(rows)      AS median_rows_per_part,
        sum(rows)         AS rows_per_insert,
        min(size_in_bytes) AS min_bytes_per_part,
        max(size_in_bytes) AS max_bytes_per_part,
        median(size_in_bytes) AS median_bytes_per_part,
        sum(size_in_bytes) AS bytes_per_insert,
        median_bytes_per_part / median_rows_per_part AS avg_row_size,
        sum(duration_ms) / 1000 AS seconds_per_insert,
        max(event_time) AS last_part_pi,
        min(event_time) AS first_part_pi
    FROM system.part_log
    WHERE
        -- Enum8('NewPart' = 1, 'MergeParts' = 2, 'DownloadPart' = 3, 'RemovePart' = 4, 'MutatePart' = 5, 'MovePart' = 6, 'MergePartsStart' = 7, 'MutatePartStart' = 8)
        event_type = 1
        AND
        -- change if another time period is desired
        event_date >= today()
    GROUP BY query_id, database, table, time_bucket
)
GROUP BY database, table, time_bucket
ORDER BY time_bucket, database, table ASC;

How to read the output

Column	Meaning
`median_parts_pi`	Typical parts produced per insert. Closer to 1 is better. Anything above 5 means inserts hit too many partitions.
`median_rows_pi`	Typical batch size. Aim for at least tens of thousands.
`inserts_per_minute`	Insert frequency. Above 60 per table per shard is the danger zone.
`rows_inserted`	Total rows ingested in the window.
`parts_creation_seconds`	Total wall time spent creating parts. Sanity check for CPU cost of writes.

A healthy MergeTree ingest pattern has median_parts_pi between 1 and 2, inserts_per_minute under 60 per table per shard, and median_rows_pi in the tens to hundreds of thousands.

New parts per partition

When a single insert produces many parts, the cause is usually that the batch spans many partitions. This query attributes new parts to the partition that received them.

SELECT
    database,
    table,
    event_type,
    partition_id,
    count() AS c,
    round(avg(rows)) AS avg_rows
FROM system.part_log
WHERE event_date >= today() AND event_type = 'NewPart'
GROUP BY database, table, event_type, partition_id
ORDER BY c DESC;

If one table dominates the result and its parts spread across many partition_id values per insert, revisit the partition key. Daily partitioning on a column that has high cardinality per insert produces this exact symptom.

Too-fast inserts detector

The MergeTree engine generally cannot keep up with more than one new part per table per second. Above that threshold, Too Many Parts errors become likely as background merges fall behind. This query bins parts into one-minute buckets so you can spot tables that exceed 60 new parts per minute.

SELECT
    toStartOfMinute(event_time) AS t,
    database,
    table,
    count() AS c,
    round(avg(rows)) AS avg_rows
FROM system.part_log
WHERE event_date >= today()
  AND event_type = 'NewPart'
  --AND event_time > now() - 3600
GROUP BY database, table, t
ORDER BY t;

Uncomment the event_time > now() - 3600 predicate to limit the window to the last hour. Any row with c > 60 is a table that exceeded the safe insert rate during that minute. Two common fixes: batch on the client, or use a Buffer table / async inserts to absorb high-frequency writes.

When to use which query

Symptom	Query
Tuning insert batching	Insert rate / parts per insert
`Too Many Parts` errors	Too-fast inserts detector
Inserts producing many parts each	New parts per partition
Capacity planning, daily volume	Insert rate (rows_inserted column)

Common Pitfalls

event_type accepts both the integer (1) and the enum name ('NewPart'). Pick one style per query and stick with it.
part_log is not enabled by default on every distribution. Confirm with SELECT * FROM system.part_log LIMIT 1 before relying on the metrics.
These queries scope to a single node. For a cluster-wide view, replace system.part_log with clusterAllReplicas('{cluster}', system.part_log) and add hostName() to the grouping.
Merges also produce new parts (event_type = 'MergeParts'). Confusing them with NewPart inflates ingestion counts. Filter explicitly.
The duration_ms field measures the part creation time, not the full insert duration. Network and replication time live elsewhere.
query_id is empty for some internal operations. The first query filters those out implicitly by grouping by query_id, but inserts triggered by ON CLUSTER distributed DDL may show empty IDs.

Frequently Asked Questions

Q: What is a safe insert rate per table? A: Aim for no more than one insert per table per second per shard, which is roughly 60 inserts per minute. Above that, background merges struggle to keep up and you risk Too Many Parts.

Q: My median_parts_pi is 10. Is that bad? A: Yes. Each insert is producing 10 parts, which means the batch is spanning 10 partitions. Either reduce the partition cardinality (for example, partition by month instead of day for low-volume tables) or sort the batch by partition key before sending.

Q: How do I find inserts that produced exactly one part? A: Filter the inner query by number_of_parts_per_insert = 1. Those are the ideal inserts. Compare their rate to the total to see how often you achieve them.

Q: Why is event_type = 1 used instead of 'NewPart'? A: They are equivalent. The integer form skips a string comparison and is slightly faster on large part_log tables, but the enum name is more readable. Pick whichever you prefer.

Q: What about MergeParts and MutatePart events? A: They reflect background activity, not ingestion. To track merge load, build a similar query against event_type IN ('MergeParts', 'MutatePart') and look at duration_ms to see how long merges take.

ClickHouse Ingestion Rate Metrics from system.part_log