ClickHouse: Count Active Parts Per Partition

ClickHouse stores every insert as a new data part, and a background scheduler decides when to merge them into larger ones. The merge decisions account for overall load, current part count, and part sizes, not insertion time. As a result, a partition can carry many small parts for a while before the scheduler combines them. Knowing how many active parts each partition holds helps you spot partitions that are lagging behind, predict query performance, and decide whether manual OPTIMIZE is warranted.

The query below is the fastest way to get that picture across every table on a server.

The Query

SELECT
    database,
    table,
    partition,
    sum(rows) AS rows,
    count() AS part_count
FROM system.parts
WHERE (active = 1) AND (table LIKE '%') AND (database LIKE '%')
GROUP BY
    database,
    table,
    partition
ORDER BY part_count DESC
LIMIT 20;

The query returns the twenty partitions with the most active parts. active = 1 filters out parts that are about to be removed after a merge, so the result reflects the storage layout queries will actually scan.

Reading the Results

A typical analytic table holds 1 to 10 active parts per partition. Hundreds of parts is a sign of either very high insert frequency, suspended merges, or part sizes already at the upper merge limit.

Why ClickHouse Does Not Merge Immediately

ClickHouse deliberately avoids merging every new part. The merge scheduler balances:

• Total active part count across the table
• Sizes of candidate parts (preferring similar sizes for efficiency)
• Current CPU and disk I/O load on the node
• The max_bytes_to_merge_at_max_space_in_pool limit (default 150 GB)

When candidate parts approach 150 GB combined, the scheduler stops merging them with others to keep merge cost bounded. That is intentional. Repeatedly merging hundred-gigabyte parts would consume the entire I/O budget and starve queries.

Drill Down on a Single Partition

When the top result looks suspicious, narrow the lens to that partition:

SELECT
    name,
    rows,
    bytes_on_disk,
    formatReadableSize(bytes_on_disk) AS size,
    min_block_number,
    max_block_number,
    level,
    modification_time
FROM system.parts
WHERE active
  AND database = 'db_name'
  AND table = 'table_name'
  AND partition = 'partition_id'
ORDER BY min_block_number;

The level column shows how many merge rounds a part has been through. A partition full of level = 0 parts means inserts have not been merged at all yet. A mix of high-level and small low-level parts typically means a recent batch of inserts has not caught up with older data.

When to Run OPTIMIZE

Manual OPTIMIZE is rarely necessary, but it can be useful in specific cases:

• The partition is closed (no more inserts expected) and you want a clean storage layout for archival
• You need deterministic deduplication for ReplacingMergeTree or CollapsingMergeTree
• A backlog has accumulated and the workload pattern allows a manual cleanup window

OPTIMIZE TABLE db_name.table_name PARTITION 'partition_id' FINAL;

FINAL forces a merge even if the scheduler would not pick it up. Use it sparingly, especially on partitions near the max_bytes_to_merge_at_max_space_in_pool limit, because the resulting merge can be very expensive.

Alerting Thresholds

ClickHouse will reject inserts with the Too many parts error if a single partition holds more parts than parts_to_throw_insert (default 300). Set up monitoring on part_count and alert before you hit the threshold:

Common Pitfalls

• Counting parts without active = 1 inflates the result. Inactive parts are kept briefly for safety but are not scanned by queries.
• A single table with many partitions can hide the real problem. Always group by partition, not just table.
• Partition strategies based on high-cardinality keys (such as raw timestamps) create thousands of partitions and break the merge scheduler. Use coarser granularity like month or week.
• OPTIMIZE ... FINAL can lock merges for a long time on very large partitions. Run it during low-traffic windows.
• ReplacingMergeTree counts the same row multiple times until merges deduplicate it. High rows may not equal logical row count for those engines.

Frequently Asked Questions

Q: How many parts per partition is too many? A: As a rule of thumb, fewer than 30 is healthy, 30 to 100 is worth investigating, and 100 or more usually indicates a merge backlog or an over-eager insert pattern.

Q: Why does ClickHouse not merge old parts that are still small? A: The merge scheduler prefers to merge parts of similar size. Once a partition has a few large parts and many small ones, the small ones may stay for a while waiting for compatible merge candidates.

Q: Will increasing background_pool_size solve a high part count? A: It helps when the bottleneck is CPU or merge slots, not when inserts are simply faster than merges can keep up. The durable fix is larger insert batches and fewer inserts per second.

Q: What is max_bytes_to_merge_at_max_space_in_pool? A: The largest combined size that a single merge job can produce, default 150 GB. It caps the cost of any one merge to keep the system responsive.

Q: Does this query show parts on every replica or just one? A: It runs locally on the node where you execute it. Wrap with clusterAllReplicas to see all replicas in a cluster.