Why ClickHouse rejects inserts with too many parts
Each insert into a MergeTree table writes a new part on disk, and background merges combine those parts into larger ones. The Too many parts error appears when merges cannot keep pace with the number of parts being created, so ClickHouse throttles and then rejects further inserts to protect the server. If your ingestion pipeline is writing event or log data to a self-managed cluster and inserts start failing with this message, the cause is almost always an imbalance between how fast parts arrive and how fast they are merged.
Teams usually need this fix in three situations. The first is many small, frequent inserts, where a streaming writer sends a few rows at a time and produces far more parts than merges can absorb. The second is a partition key that spreads data across too many partitions, so each partition holds only a few parts that never grow large enough to merge efficiently. The third is merges starved by heavy query or ingestion load, leaving the background merge pool with too little capacity. These causes call for different fixes: batching and async inserts address the first, partition key changes the second, and merge settings or capacity the third. Confirm which one applies before changing anything, by checking the parts count per table and partition in system.parts and the merge activity in system.merges during an incident. The sections that follow walk through each fix.
How to confirm the cause before changing settings
This sequence separates the three usual causes of the error, small inserts, too many partitions and stalled merges, so the team changes one thing at a time. Each step ends with a check whose output points toward or away from a cause.
Capture the exact error and affected table
Copy the full error message from the failed insert, including the table name and the part count it reports, and note the time it first appeared. Record which application or pipeline sent the insert and whether the error hits every insert or only some. The operator does this from the client logs and the server log. The step is done when you have one table name, one timestamp range and a sample of the error text to compare against later checks.
Count active parts per table and partition
Query system.parts for active parts, grouped by database, table and partition, and sort by count. A high number concentrated in a few partitions usually means the partition key creates many small slices, while a high number spread across the table points to a steady flow of small inserts. Confirm the column names and their meaning in the documentation for your ClickHouse version before you rely on the query. The step is done when you know the top table, its active part count and the partitions holding most of those parts.
Measure insert frequency and batch size
Use system.query_log, or your insert logs if query logging is sampled, to count INSERT statements per minute for the affected table and to compute the average rows per insert. Many inserts carrying only a few rows each generate a new part per insert, which is the usual source of small-insert pressure. Check whether producers send one row per request or buffer by time or count. The step is done when you can state the insert rate and the typical rows per insert for the table.
Check for queued or stalled merges
Inspect system.merges to see which merges are running and how long they have run, then check whether large merges hold resources while smaller parts wait. If the active part count keeps rising while merges show little progress, the merges are starved rather than the inserts being too frequent. Confirm the column meanings for your version in the documentation. The step is done when you can say whether merges are keeping pace with inserts or falling behind.
Check the replication queue on replicated tables
If the table uses ReplicatedMergeTree, query system.replication_queue for the table and count entries by type and by their last exception. Backlogged or repeatedly failing fetch and merge entries show that replication, not only local merging, is holding parts back. Also check that the replicas are healthy and that Keeper is responding. The step is done when you know whether the replication queue is empty, growing, or showing repeated errors for the table.
Decide whether one partition or the whole table is affected
Compare the part counts from the earlier partition query with the table total. If one or two partitions carry nearly all the parts, focus on the partition key and on inserts that write into those partitions, such as backfills or out-of-order timestamps. If every partition shows elevated counts, the problem is the overall insert rate or merge throughput. The step is done when you can name the scope as a single partition, a subset, or the whole table, and attach that finding to the insert and merge results.
Record the cause and the evidence before tuning
Write down the result of each check and classify the dominant cause as small inserts, too many partitions, merge starvation or replication backlog. Keep the query outputs with the timestamp so you can compare them after a change. Confirm the chosen fix against the version-specific documentation before applying settings. The step is done when one cause is supported by at least two checks and the team agrees on the first change to test in a pre-production environment with representative data.
Which fix fits each cause
This table pairs each common cause of too many parts with its symptom, the fix, what that fix changes and its main trade-off. Start by identifying which cause applies to your cluster, then choose the fix that addresses that cause rather than the one that is easiest to apply. Most incidents involve more than one cause, so check the rows in order.
| Cause | Symptom | Fix | What it changes | Main trade-off |
|---|---|---|---|---|
| Small frequent inserts | Many small parts appear in the same partition shortly after each insert batch, and merges fall behind. | Batch rows at the client or through a buffer so each insert carries a larger block. | Fewer, larger parts are created, which reduces merge work. | Rows wait in the client or buffer before they are visible, so freshness drops. |
| High insert concurrency | Many clients insert at once, each creating its own part, and the part count climbs during peaks. | Enable asynchronous inserts and choose the wait-for-flush setting that matches your durability needs. | The server collects small inserts and flushes them as larger batches. | Data is visible with more delay, and the wait setting determines how much durability you get before an insert acknowledgment. |
| Too many partitions from a fine-grained partition key | Parts spread across a large number of partitions, and each partition merges separately. | Move to a coarser partition key such as a month or a team-level grouping, then rebuild the table. | Each partition holds more data, so merges consolidate parts across a smaller set of partitions. | Rebuilding the table means copying data, which takes planning, disk space and a cutover window. |
| Merges that cannot keep up | Part counts stay high even when insert rates look normal, and the merge queue grows over time. | Review merge settings and background pool capacity, and check that the server has spare CPU and disk throughput. | Background merges get more capacity or work through parts more efficiently. | More merge threads consume CPU and I/O that queries and inserts also need. |
| Replicated cluster with one lagging replica | One replica shows many more parts than its peers, and its replication queue keeps growing. | Check the replication queue and the lagging node's health before changing any merge or insert settings. | Confirms whether the problem is local to one node, such as disk or network trouble, before tuning. | Diagnosis takes time, and the fix may be a node repair rather than a configuration change. |
Choosing batching, async inserts or a new partition key by workload
Services that write one row per request or per agent step create the most parts pressure, because each INSERT produces its own small part on disk. The first fix is almost always to change the writer so rows arrive in groups. If the application can hold rows for a short window, a client-side buffer is the simplest route. If it cannot hold rows, async_insert lets the server collect small inserts into larger batches before flushing them, and it is usually the first server-side setting to test.
- Buffer in the client first: Add a queue that flushes on a row count or a time limit, and agree with the application owner how much delay the business can accept.
- Choose async inserts when buffering is hard: Enable
async_insertfor the affected user or settings profile, and decide whetherwait_for_async_insertstays on so the client learns when data is flushed. - Make retries safe: Buffers and retries can duplicate rows, so agree on deduplication keys or insert tokens before rollout.
- Back off on insert errors: Retry with increasing delays instead of immediately, because immediate retries add more parts while the table is under pressure.
Test at production concurrency. Measure client-side insert latency, the time until rows appear in queries, and the parts count per partition during and after the run. Check that the parts count levels off under sustained load and falls once the test stops.
Collectors such as agents and gateways send many small batches from many hosts into the same table. Each batch becomes a part, so the fix starts at the collector: send larger batches and fewer concurrent writes. If the collectors cannot be tuned, async inserts can absorb the load on the server. A partition key that is too fine or includes a high-cardinality source field makes each batch touch many partitions, which multiplies the parts created by every send.
- Raise batch size in the collector: Adjust the batch processor limits in the exporter so each insert carries more rows, and confirm the limits fit the memory budget on the collector hosts.
- Cap writer concurrency: Many parallel exporters writing to one table multiply part creation, so limit concurrent inserts per gateway.
- Check partition fan-out: Query
system.partsgrouped by partition to see whether each batch lands in many partitions. A fine time grain or a high-cardinality key is the usual cause. - Scope any partition key change as a project: A new key usually means creating a new table, backfilling history and moving queries and writers over. Record the scope, the backfill window, the downtime plan and the rollback in writing before starting.
Test by replaying a representative day of traffic. Measure insert latency at the gateway, the freshness of data in dashboards and the parts count per partition after merges settle.
Scheduled loaders usually read large files and write them in few inserts, so they create fewer parts than per-row writers. Problems appear when a loader splits a file into many small inserts, runs several jobs against the same partitions at once, or backfills old data across many partitions. The first fix is to make each job write large, sorted chunks so that each insert touches as few partitions as possible.
- Insert in large, sorted chunks: Load each file as one insert or a small number of large inserts, with rows sorted by the partition key so each insert lands in few partitions.
- Serialize overlapping jobs: Schedule loads so two jobs do not write the same partitions at the same time. A scheduler lock or a queue usually enforces this.
- Run backfills in windows: Historical backfills into many old partitions create many parts at once. Run them in controlled windows and let merges catch up between windows.
- Treat a key change as a rebuild: If the loader cannot avoid fanning out, the table may need a new partition key. That means rebuilding the table, backfilling data and rerouting the loader, so scope the work, the downtime window and the verification in writing.
Test the largest historical file and one full backfill window in staging. Measure job duration, whether rows are visible to queries when the job finishes and the parts count per partition before and after merges. Confirm that the parts count returns to its baseline after each job so the next run starts from the same state.
Merge settings and what each one trades off
Part counts are controlled by a small set of families: the thresholds that slow or reject inserts, the background merge pool, the rules that choose which parts merge, and per-table MergeTree settings. Each one moves work between insert latency, merge CPU, disk I/O and memory, so changing one without reading the others often just relocates the problem.
Insert slowdown and rejection thresholds
These settings set the part count at which ClickHouse starts delaying inserts and the higher count at which it rejects them with the too many parts error. Raising them buys headroom during bursts but lets parts pile up, which makes later merges larger and slower and can push queries to read more files. Lowering them makes the error appear sooner but protects merges from falling behind. Before changing either, confirm the exact setting names and default values in the documentation for your production version, and check whether the thresholds are server-wide or per table.
Background merge pool size
The background merge pool sets how many merges run at the same time across the server. More concurrent merges drain parts faster and keep counts low, but they consume more CPU, disk throughput and memory, which can slow queries and inserts running on the same hardware. A small pool on a busy node lets parts accumulate during ingestion peaks. Check the merge-related settings for your version, and compare the merge rate with the insert rate on a representative load before raising the pool.
Rules that choose which parts merge
Merge selection decides which parts are combined next and how large a merge may grow. Permissive rules merge more often and keep part counts down, but they rewrite more data and create long merges that hold resources. Conservative rules limit write amplification and keep individual merges short, at the cost of more small parts between cycles. Confirm the selection and maximum-size settings in your version's documentation, and watch how long large merges take in the merge system table during a test load.
Per-table MergeTree settings
Per-table MergeTree settings let you tune part behavior for a single high-volume table, such as an event or log table, without changing the whole server. Adjusting the part thresholds or merge behavior on that table isolates the trade-off to the workload that needs it. The risk is that a table-level override can hide a schema or batching problem, and it may conflict with server-wide defaults that are set differently across replicas. Confirm which settings can be set per table in your version and record every override in your configuration management.
Verifying the fix on replicated clusters
Verify the fix in a pre-production environment with representative data and queries before it touches production. The ClickHouse production FAQ recommends this step, and it matters most for the change that carries the most risk here: a partition key change. Load the environment with a copy or a realistic sample of your event data, replay the insert pattern your producers actually use (batch size, concurrency and burst behavior), and run the dashboard and read queries that matter to the business at the same time. Then watch the part count per partition and the merge backlog over a full daily cycle, not a single short test. A fix that clears the error during a quiet window and returns during a peak insert period has not been verified.
Check every replica of each ReplicatedMergeTree table, not only the one your application writes to. Replicas are coordinated through ClickHouse Keeper, and a fix applied on one node can leave another node with a different merge pace or a stale queue. Compare system.parts and system.replication_queue across replicas and confirm that the queue drains and the part counts converge. If a replica falls behind, confirm the cause before acting: a recovered replica syncs missing data from a healthy replica without manual intervention, so a lagging replica usually points to a Keeper, network or resource problem rather than data that needs rebuilding by hand. Confirm your monitoring reads each replica separately so a single lagging node is not hidden by averages.
Before any partition key change or table rebuild in production, take a backup and test-restore it on a spare cluster. The ClickHouse backup documentation warns that a backup never test-restored will probably not restore properly when needed, and that replication alone does not protect against human error. Keep the backup until the new layout has run through a full insert and merge cycle, then keep the parts-per-partition alert and the replication queue alert active for a defined period after rollout. Confirm with your own team which thresholds and review cadence you will use, because those values depend on your insert rate and hardware.
What to confirm in your version and environment
Fixes for too many parts depend on exact setting names, the release line you run, and the health of your replicas and Keeper nodes. Confirm each item below in your own environment before you change merge settings, async insert behavior or table keys.
Exact setting names and defaults
Merge and insert thresholds such as the part count limits and merge tuning options can differ between ClickHouse versions. Query system.merge_tree_settings on your server to read the live values, and compare them with the documentation for that exact version before you copy a value from an older guide. Record the current defaults so you can roll back a change cleanly.
Supported release line
Check the security policy to see which versions still receive security updates, and the releases page for the current stable and LTS lines. A fix for too many parts applied to an unsupported release may leave you exposed to known issues. Plan any version change as a separate, tested step rather than bundling it with the merge fix.
Tolerance for asynchronous inserts
Async inserts buffer small writes on the server and flush them in larger batches, which reduces part creation. Confirm that your application can accept a delay between the insert call and data visibility, and that it handles errors from the buffered flush. If your pipeline needs each insert to be queryable immediately, batch on the client instead.
Feasibility of a partition key change
Changing the partition key usually means creating a new table and copying data, which takes disk space and time proportional to your data volume. Confirm how many partitions exist today, how much free space each node has, and whether the affected tables can be rebuilt in a maintenance window. Test the copy on a non-production copy of the largest table first.
Keeper and replica health baseline
Replicated tables depend on ClickHouse Keeper, and a lagging or read-only replica can stop merges from catching up. Before tuning, check replica queue length, read-only flags and Keeper status on every node, and note the baseline. Replication lag that exists before your change will look like a fix failure afterward.
Backward-incompatible merge changes
Read the changelog for your target version and search for entries that touch merges, parts, inserts or table settings. Minor releases can include backward-incompatible changes, so a setting that worked on your current version may behave differently after an upgrade. Test merge behavior on a staging cluster with representative data before applying the change in production.
How long a too many parts fix takes and what it costs
Can our own team fix the too many parts error without a partner?
Yes, many teams can, provided someone on the team can read ClickHouse system tables, change how the application batches inserts, and test changes before they reach production. The fix itself is usually a set of changes in three places: the writers that send data, the table definitions, and the merge settings. Problems tend to arise when the writers belong to several teams, when no one owns the insert path end to end, or when the fix requires a partition key change that means rebuilding large tables. A partner is most useful when the internal team lacks time, when the change crosses many services, or when the team wants an independent review of the plan before a rebuild. Whichever route you take, the cost is mostly the engineering time to find the writers, test the batching change and verify the result under production-like load.
What does the work need from our team before it starts?
The work needs four things from your side. First, read access to system tables such as system.parts, system.merges and system.asynchronous_inserts, so the cause can be measured rather than guessed. Second, a pre-production ClickHouse cluster with data volumes and insert patterns close to production, because ClickHouse's production guidance recommends testing with representative data and queries before changing production. Third, named owners for each client that writes to the affected tables, since batching and async insert settings live in application code, pipelines and connectors. Fourth, a change window and a rollback plan for any rebuild that changes the partition key. Ask the partner to list these inputs in the written scope so gaps are visible before the schedule is set.
What makes the effort grow for a too many parts fix?
Effort grows with the number of things that must change at once. The main drivers are the number of tables affected, the number of distinct writers and their languages or frameworks, the number of replicas and shards that must be checked and kept consistent, and whether the partition key must change. Changing batching or enabling async inserts in one writer is a small job. Changing a partition key is larger, because existing data may need to be copied into a new table, validated, and switched over, and that work scales with data volume. Each replica adds verification steps, and each writer adds a test and a deployment. Ask the partner which of these drivers apply to your environment and which of them they have estimated separately.
How long does a too many parts fix take?
The duration depends on scope, and no fixed duration applies across environments. A focused change, such as batching inserts in a single writer with merge settings reviewed on a test cluster, is much shorter than a multi-team change that includes a partition key rebuild and cutover. The main time sinks are finding every writer, getting code changes through review and release, and waiting for merges to show the effect in system tables. Ask for a written schedule that breaks the work into discovery, test, production change and verification phases, with each phase tied to the inputs it needs from your team. Be wary of any timeline given before the writers and table layout have been inventoried.
What should a partner quote for a too many parts fix include?
A quote should follow from a written scope and list the items separately so you can compare options. Ask for the following:
- Discovery: inventory of affected tables, writers, insert frequency and current part counts, with the method used to measure them.
- Changes: list of batching, async insert and merge setting changes, and which writers each one touches.
- Table work: any partition key or table rebuild, including data copy, validation and cutover steps.
- Testing: the pre-production environment used, the load pattern tested and the acceptance criteria.
- Operations: runbooks, monitoring of part counts after the change, and handover to your team.
- Exclusions: what is not covered, such as application code owned by other vendors.
Confirm which items are one-time and which recur, for example retainer-based monitoring or later tuning.
Does fixing too many parts require a ClickHouse version upgrade?
Usually not. Most causes of too many parts are insert pattern and table design issues that can be fixed on the version you already run. Before deciding, check the release notes and changelog for the version you run and the version you plan to move to, because minor backward-incompatible changes can occur, as ClickHouse's production guidance notes. Also check the security policy, which lists the versions that still receive security updates. If an upgrade is needed, it becomes a separate project with its own testing, rollback and sequencing across replicas. Ask the partner to confirm in writing whether any part of the fix depends on a version change, and to name the release notes they reviewed.
How DeployOpen scopes and delivers this fix
DeployOpen approaches a too many parts fix as a scoped engagement inside your own infrastructure. We first confirm the cause from your cluster's own tables and metrics, then agree in writing which ingestion, merge and schema changes are in scope before anything changes in production.
How we scope
- Review insert patterns, batch sizes, partition keys and merge settings on the affected tables before proposing changes.
- Agree in writing which tables, clusters and ingestion pipelines the fix covers, and which are excluded.
- Define the verification evidence you will receive, such as before and after part counts and insert error rates.
How we build
- Change batching, async insert and partition settings in a pre-production environment with representative data first.
- Test each merge or partition change against replicated tables, including the effect on Keeper and replica sync.
- Keep every change reversible, with the previous configuration and table definitions recorded before rollout.
How we hand over
- Document the confirmed root cause, the applied changes and the monitoring queries your team should keep.
- Walk your operators through the parts and merge checks so they can recognize the problem early next time.
- Deliver configuration and schema changes as versioned files your team can review and deploy again.
How we support
- Ongoing support, upgrades and continuous testing are available as a retainer after the fixed-scope work ends.
- Quotes follow from the written scope, so ask for line items covering analysis, changes, testing and handover.
- Confirm with your team which upgrade and backport policy applies to your ClickHouse version before the work starts.
