When small inserts causes part accumulation, the most common ClickHouse performance issue surfaces. Learn how to detect it, and how batch inserts and async inserts can prevent it.

If you have operated ClickHouse in production for any length of time, you have likely encountered - or will encounter - the "too many parts" error. It is the single most common performance issue we see across ClickHouse deployments, from small startups to large-scale production clusters. The root cause is almost always a workload problem, not a system or configuration issue - and once you understand the mechanics, it is straightforward to diagnose and fix.

This post covers what causes excessive part accumulation, how to detect it before it becomes critical, and the practical fixes that resolve the problem.

How Parts Accumulate

In the MergeTree engine, every INSERT operation creates a new data part on disk. Background merge threads continuously combine small parts into larger ones, keeping the total part count manageable. Under normal operation, this cycle stays in balance - parts are created by inserts and consumed by merges at a roughly similar rate.

The problem arises when inserts create parts faster than merges can consolidate them. There are two primary causes:

Too many small inserts. When an application inserts rows one at a time - or in very small batches of two to three rows - each insert creates its own part. Thousands of tiny inserts per hour can overwhelm the merge process. This is by far the most common cause we encounter.

Overly granular partitioning. ClickHouse does not merge parts across different partitions. If your partition key produces too many unique values - for example, daily partitions on a high-volume table that has been running for years - you end up with many partitions, each with its own set of parts that must be merged independently. Monthly partitions instead of daily ones can make a meaningful difference here.

The important thing to understand is that this is almost always a workload problem. The fix is rarely a configuration change - it is about how data gets written into the system.

Diagnosing Part Count Issues

Detecting part accumulation before it causes errors is straightforward with a query against system.parts:

SELECT
      database,
      table,
      partition,
      count() AS part_count,
      sum(rows) AS total_rows,
      formatReadableSize(sum(bytes_on_disk)) AS total_size
  FROM system.parts
  WHERE active
  GROUP BY database, table, partition
  ORDER BY part_count DESC

This gives you the current active part count, total rows, and disk size for every table in the cluster. You should have a threshold in mind for what constitutes a healthy part count:

  • Under 100 parts per partition is generally healthy.
  • 100-300 parts per partition is a telltale sign that you are either doing too many small inserts or merges are not keeping up. High-throughput tables with sustained heavy ingest may run in this range, but it warrants investigation.
  • Above 300 parts per partition is a clear signal that action is needed. Left unchecked, this will eventually hit ClickHouse's hard limit and trigger the "too many parts" error, rejecting new inserts entirely.

These thresholds vary by system - a table ingesting millions of rows per second will naturally carry more parts than one receiving periodic batch loads. The key is to track the trend: a steadily increasing part count that never stabilizes means merges are losing the race.

Tracking Insert Patterns

To understand what is driving part creation, query the insert workload from system.query_log:

SELECT
      database,
      tables[1] AS table,
      count() AS insert_count,
      min(event_time) AS first_insert,
      max(event_time) AS last_insert,
      sum(written_rows) AS total_rows,
      round(avg(written_rows)) AS avg_rows_per_insert
  FROM system.query_log
  WHERE event_date >= today()
      AND type = 'QueryFinish'
      AND query_kind = 'Insert'
  GROUP BY database, table
  ORDER BY insert_count DESC

If you see tables with thousands of inserts per hour averaging single-digit rows per insert, you have found your culprit.

Fixing the Problem

Batch Your Inserts

Batching inserts is the number one fix. Instead of inserting rows one at a time, accumulate them in your application and write them in larger batches. A single insert of 10,000 rows creates one part; 10,000 individual inserts create 10,000 parts. The difference in merge pressure is enormous.

If your application architecture makes client-side batching difficult - for example, if you have many independent services each writing small amounts of data - ClickHouse provides a built-in solution.

Use Async Inserts

Async inserts are a ClickHouse feature that performs batching internally on the server side. When enabled, ClickHouse buffers incoming insert requests and flushes them together as a single part based on configurable time and size thresholds. This is transparent to the client - your application continues sending individual inserts, but ClickHouse consolidates them before writing to disk.

You can enable async inserts at the user, session, or query level:

SET async_insert = 1;
  SET wait_for_async_insert = 1;

This is particularly useful when you cannot modify the application's insert behavior and need a server-side fix.

Review Your Partitioning Strategy

If your partition key produces too many partitions, each with a small number of parts, consider coarsening it. Monthly partitions (toYYYYMM(timestamp)) instead of daily ones (toYYYYMMDD(timestamp)) reduce the number of independent partition groups that need separate merge attention. This is a complementary fix to batching - getting the partitioning right reduces the overall surface area that the merge process needs to cover.

Key Takeaways

  • The "too many parts" problem is almost always a workload issue, not a configuration issue. Small, frequent inserts are the primary cause.
  • Monitor part counts per table using system.parts and establish thresholds - under 100 per partition is healthy, above 300 needs action.
  • Batch inserts on the client side as the first and most effective fix. Use async inserts as a server-side alternative when client changes are not feasible.
  • Review your partitioning strategy - monthly instead of daily partitions can meaningfully reduce merge pressure.