ClickHouse® Small Inserts: Fix Batching Before Buying More IOPS

Your dashboard slows down while ingestion continues. The storage graphs are busy, so a larger disk or more provisioned IOPS looks like the obvious next step. Before changing the infrastructure, inspect how the application writes: a stream of tiny inserts can make the database spend too much time creating and merging small data parts.

The goal is not to make every insert wait longer. It is to deliver fresh, correct data with less avoidable work, then size infrastructure for the work that remains.

TL;DR

• Inspect insert size, request rate and active parts per partition before blaming storage.
• Compare client-side batching with server-side asynchronous inserts using the same input stream.
• Keep wait_for_async_insert=1 when evaluating acknowledged writes; accepting data into a buffer is a different result.
• Measure query latency, data freshness, errors and merge behavior together, not ingestion throughput alone.

Technical sources reviewed 15 October 2026. SQL below is a diagnostic template and proposed acceptance procedure, not an executed ClickHouse benchmark.

Why do small inserts create work beyond writing the rows?

MergeTree tables store immutable data parts. Creating a part involves sorting, indexes, compressed column data and metadata. Background merges later combine smaller parts. An application can therefore make the database repeatedly organize and rewrite data even when its useful incoming payload is modest. ClickHouse table-parts documentation

Consider an illustrative workload of 12,000 events each second. Sending 100 rows per request produces 120 requests per second; sending 12,000 rows in one request produces one. Those are arithmetic examples, not measured performance results, and request count does not map one-to-one to parts in every configuration.

Partitioning also matters. One batch spanning many partition-key values can still create many parts. A larger batch is not a cure for an unnecessarily fragmented partition design. Keep the production partition spread in the test rather than making the test easier with a single convenient partition. Insert strategy and partition behavior

Should batching happen in the client or the server?

Client batching gives the application direct control of the size and dispatch time of each batch. ClickHouse recommends batches of at least 1,000 rows, ideally 10,000–100,000 where the workload permits. Treat that as a starting range, not a freshness guarantee: wide rows, sparse traffic and strict latency requirements can change the right choice. Insert-strategy guidance

Asynchronous inserts move batching into ClickHouse. Buffers are separate for different insert shapes and settings, and they are maintained per node. A size, time or query-count threshold triggers a flush; the buffered data is not queryable until it is flushed. Asynchronous inserts

For your evaluation, use this decision table:

Constraint	Candidate to test	What to keep visible
One application controls the stream	Client batches bounded by size and time	Oldest event's wait before dispatch
Many independent producers send small payloads	Server-side asynchronous inserts	Buffer fragmentation and flush delay
Traffic becomes sparse overnight	A bounded flush interval	Freshness of the last few events
Many partitions receive each batch	Review partitioning as well as batching	Active parts per partition

Do not start by changing settings for every user. Test a dedicated workload profile or connection first, with a clear rollback to the previous configuration.

Which acknowledgement are you measuring?

With wait_for_async_insert=1, ClickHouse acknowledges an asynchronous insert after its buffer is flushed to disk and returns flush errors to the caller. With 0, acknowledgement can arrive when the data is merely buffered, so later failures or loss may not appear as a failed request. ClickHouse strongly recommends the waiting mode for reliable error handling and backpressure. Acknowledgement modes

A test that compares synchronous completion with buffer acceptance is comparing different promises. Keep the requested acknowledgement semantics explicit. “Flushed to disk” also should not be expanded into an untested promise about every replica, a power failure or a remote disaster-recovery copy.

Inspect the version and effective settings in the same user/session used by the producer:

SELECT version();

SELECT name, value, changed
FROM system.settings
WHERE name IN (
    'async_insert', 'wait_for_async_insert', 'compatibility',
    'deduplicate_insert', 'insert_deduplicate',
    'async_insert_deduplicate'
)
ORDER BY name;

system.settings describes session settings; request-level overrides and other users can differ. Missing names may reflect version differences. Save the query settings and table definition with the result rather than copying a default from an older tutorial. Settings reference

What should you inspect before changing storage?

Start with the target table and its active parts. Replace the example database and table names:

SHOW CREATE TABLE analytics.events;

SELECT
    partition,
    count() AS active_parts,
    sum(rows) AS stored_rows,
    sum(bytes_on_disk) AS stored_bytes,
    round(sum(rows) / count(), 1) AS rows_per_part
FROM system.parts
WHERE active
  AND database = 'analytics'
  AND table = 'events'
GROUP BY partition
ORDER BY active_parts DESC;

These columns describe the current parts on the server you query. A single sample is not a merge-rate measurement, and a single node is not the whole cluster. Capture repeated samples across the relevant nodes, alongside producer request sizes, errors, query latency, CPU and storage telemetry. System parts reference

A rising number of small active parts is a reason to investigate, not standalone proof that storage is inadequate. Check whether batching reduces part accumulation at the same useful event rate and whether the backlog drains after the ingest burst ends.

Avoid routinely forcing OPTIMIZE TABLE ... FINAL to make the graph look better. ClickHouse warns that this can bypass normal merge limits and trigger expensive work. It does not fix a producer that immediately creates the same small-part pressure again. Why to avoid routine OPTIMIZE FINAL

How do you prove the change improved the workload?

Keep schema, sorting key, partition spread, event rate, query mix and infrastructure fixed. Compare the current producer, bounded client batching and asynchronous inserts with waiting enabled. Change one approach at a time.

Choose a freshness target first, then record:

Result	Acceptance question
Acknowledged rows and errors	Did the same intended input succeed without hidden failures?
Event-to-queryable delay	Did batching keep the freshness target during both busy and quiet periods?
Dashboard p95/p99	Did readers improve while ingestion continued?
Active parts and drain time	Did the system keep up and recover after the burst?
Retry correctness	Did interrupted requests add duplicate logical events?

Retries deserve a separate failure test. Current ClickHouse documentation makes deduplication conditional on both query settings and a table deduplication log. Plain MergeTree needs a positive non-replicated window, and the finite window can be exceeded. Preserve batch identity and verify materialized-view destinations too; do not assume an event ID column automatically enforces uniqueness. Deduplicating insert retries

Only after removing avoidable work should the infrastructure comparison begin. Nirvana's ABS documentation lists 20,000 baseline IOPS included with a volume. That specification is a reason to evaluate the remaining sustained I/O, not a prediction of this application's insert rate or query latency. ABS documentation

FAQ

Will asynchronous inserts always be faster?

No. The outcome depends on the producer, buffering, data shape and workload. A reader may value lower event-to-queryable delay more than peak insert throughput. Compare both against the same target.

Can I fix “too many parts” by raising the limit?

A higher threshold does not remove the work that produced the parts. Diagnose batching, partitioning and whether background work keeps up before treating a limit change as a capacity solution.

Is this the same as storage sizing?

No. This guide changes how much work ingestion creates. Once that is understood, use the Kubernetes storage-sizing guide to evaluate capacity and the workload's remaining storage requirements.

Evaluating a managed service for ClickHouse? Start an Altinity.Cloud trial and bring the same batching and freshness acceptance plan to the evaluation.

Give the database less unnecessary work. Then give the remaining work the infrastructure it needs. Review Nirvana ABS and compare your actual ingestion, freshness and dashboard targets.

About Nirvana Labs

Nirvana Labs is a high-performance storage cloud purpose built for blockchain, AI and databases i.e. the most demanding, real-time, stateful workloads. Accelerated Block Storage (ABS) offers 20K baseline IOPS included, no over provisioning. Nirvana Kubernetes Service (NKS) with Karpenter auto-scaling, high clock-speed compute and private networking. Backed by Jump Trading, Crucible, etc with 50+ customers live in production today.

Learn more at Nirvana Labs

Nirvana Cloud | Pricing | Blog | Docs | Changelog | LinkedIn | Twitter | Telegram | YouTube