$ guides / clickhouse / clickhouse-async-inserts-tuning ▌

Operations Guides

How To Tune ClickHouse Async Inserts: Key Settings

Async inserts move batching responsibility from the client to the ClickHouse server. The server buffers rows and flushes them as larger blocks instead of persisting every INSERT as a separate part. When the root cause is an unbatchable client, this stops the small-inserts anti-pattern from flooding the merge pool. When the root cause is high-cardinality partitioning or sustained over-ingestion, async inserts relocate the crisis into an in-memory buffer that loses data on crash and hides backpressure from the application.

This guide covers flush mechanics, durability trade-offs, and how to distinguish genuine fixes from masked failures.

What it is and why it matters

ClickHouse creates one or more immutable parts per INSERT. The small-inserts anti-pattern, where clients send single-row or tiny batches, generates a part per insert. Parts accumulate faster than background merges consolidate them, producing the TOO_MANY_PARTS failure. Each part carries metadata overhead and merge scheduling cost; too many parts slow queries and can halt inserts. Async inserts collect many small INSERT statements in a server-side buffer and flush them as fewer, larger parts.

This changes the durability and visibility contract. You need to know whether the buffer is absorbing a transient burst or masking a structural mismatch between ingest pattern and partition design.

How it works

With async_insert = 1, ClickHouse holds incoming insert data in a server-side buffer. The buffer flushes to disk when the first of three thresholds is reached:

async_insert_max_data_size (10 MiB by default; 100 MiB on ClickHouse Cloud)
async_insert_busy_timeout_ms (default 200 ms; 1000 ms on ClickHouse Cloud)
async_insert_max_query_number (default 450; it applies only when async-insert deduplication is enabled)

Starting in 24.2, async_insert_use_adaptive_busy_timeout defaults to 1, dynamically adjusting the flush interval based on data arrival rate. Disable this explicitly if you need deterministic flush intervals.

Each flush produces at least one part per distinct partition key in the buffer. If buffered rows exceed max_insert_block_size (roughly 1 million rows), a single flush produces multiple parts even within the same partition. Async inserts do not apply to INSERT INTO ... SELECT; those remain synchronous regardless of the setting.

wait_for_async_insert controls the client-visible durability guarantee. With wait_for_async_insert = 1 (default), the client blocks until the flush completes and receives flush errors directly. With wait_for_async_insert = 0, the server acknowledges as soon as data enters the buffer. The client sees low latency, but data is not persisted. If the server crashes before flush, that data is lost. Errors such as disk full, read-only table, or TOO_MANY_PARTS surface only in server logs, not to the client.

Before 26.2, async-insert deduplication was off by default (async_insert_deduplicate = 0). In 26.2 and later, deduplicate_insert defaults to enable and applies to both synchronous and asynchronous inserts. ClickHouse 24.3 introduced throw_if_deduplication_in_dependent_materialized_views_enabled_with_async_insert with default 1; the default became 0 and the setting became obsolete in 26.1. On versions that still enforce it, do not combine async inserts with deduplicate_blocks_in_dependent_materialized_views = 1.

If any row in a buffered insert has a parse or type error, ClickHouse drops the entire query’s data from that buffer on flush. In wait_for_async_insert = 0 mode, this error appears only in server logs.

Introspection tables:

system.asynchronous_inserts: live buffer state
system.asynchronous_insert_log: historical flush results
system.query_log where query_kind = 'AsyncInsertFlush' (since 23.7): per-flush timing and errors

Before maintenance or shutdown, you can force flush all pending buffers with SYSTEM FLUSH ASYNC INSERT QUEUE. This triggers immediate disk I/O; use with caution during peak load.

flowchart LR
    Client([Client]) -->|"INSERT async_insert=1"| Buffer[Server Buffer]
    Buffer -->|"Threshold met"| Flush{Flush to Disk}
    Flush -->|"Per partition key"| Part[(Data Parts)]
    Flush -->|"wait_for_async_insert=1"| ACK[ACK after fsync]
    Buffer -->|"wait_for_async_insert=0"| Fast[Immediate ACK]
    Fast -.->|"Crash before flush"| Loss[(Data Loss)]
    Part --> Merge[Background Merge]

Where it shows up in production

Async inserts solve TOO_MANY_PARTS when the root cause is a client that cannot batch and would otherwise send thousands of single-row inserts. The server buffer coalesces those rows into fewer parts, giving the merge pool a chance to keep up.

Async inserts do not protect against high-cardinality partition keys. Even a single flush produces one part per distinct partition value in the buffer. If a flush spans hundreds of partitions, the part count still explodes, just in larger batches. The merge pool pressure returns as soon as the flush completes.

wait_for_async_insert = 0 relocates risk rather than eliminating it. The application sees healthy insert latency and no errors while the server rejects flushes due to TOO_MANY_PARTS or disk full. Because the client already received an acknowledgment, these errors are invisible to the application and surface only in system.asynchronous_insert_log or server logs. The TOO_MANY_PARTS error still exists; it is deferred to flush time and disconnected from client retry logic.

Back pressure is limited. Async insert buffers are flushed sequentially, not in parallel. Under high concurrent insert rates, buffers queue and latency climbs dramatically. Issue #47623 documents latencies of approximately 20 seconds at 800 inserts per second with Cloud default settings. Raising timeout settings may help, but sequential flush processing is the fundamental bottleneck.

24.9 contained a performance regression (Issue #70366) where async insert throughput degraded, memory grew, and PendingAsyncInsert climbed until inserts timed out. Upgrade if you are on 24.9.

Tradeoffs and when to use it

Use async inserts when:

You control the database but not the client library, making client-side batching impractical.
The workload produces many small inserts that would otherwise trigger a part explosion.
You set wait_for_async_insert = 1 so flush failures return errors to the client.

Do not rely on async inserts as the primary fix when:

The table uses a high-cardinality partition key. Fix the partitioning scheme.
You need strong durability and cannot tolerate buffer loss on crash. Use wait_for_async_insert = 1 and accept the latency cost.
You depend on deduplication across materialized views. Either disable deduplicate_blocks_in_dependent_materialized_views or avoid async inserts.
AsynchronousInsertQueueSize or PendingAsyncInsert is growing steadily. This signals flushes are not keeping up and the buffer is accumulating debt.

The fire-and-forget pattern (wait_for_async_insert = 0) is appropriate only for loss-tolerant telemetry where occasional dropped buffers are acceptable. It is not a substitute for client-side error handling.

Signals to watch in production

Signal	Why it matters	Warning sign
`PendingAsyncInsert` / `AsynchronousInsertQueueSize`	Buffered insert queries waiting to flush.	Sustained growth means flushes are falling behind ingest.
`AsynchronousInsertQueueBytes`	Memory occupied by the async insert buffer.	Approaching multiples of `async_insert_max_data_size` suggests queue buildup.
`AsynchronousInsertThreadsActive`	Flush threads currently busy.	Stuck at pool limit while queue grows indicates sequential processing is bottlenecked.
`FailedAsyncInsertQuery`	Flush failures.	Any sustained increase means parts are being rejected after the buffer.
Active part count per partition	Verifies whether buffering actually reduced part pressure.	Count still approaching `parts_to_delay_insert` despite async inserts being enabled.
`AsyncInsertFlush` latency in `system.query_log`	Actual time from buffer to disk.	Latency climbing while client latency stays flat signals hidden backpressure.
`system.asynchronous_inserts`	Live view of buffer contents.	Large numbers of old entries indicate stuck or slow flushes.
`system.asynchronous_insert_log`	Historical record of flush outcomes.	Repeated errors or large numbers of rejected rows.

system.query_log exposes async insert flush events with query_kind = 'AsyncInsertFlush' starting in 23.7. Use this to measure server-side durability latency separately from client-reported insert latency.

How Netdata helps

Correlate rising PendingAsyncInsert with MaxPartCountForPartition and RejectedInserts to determine whether buffering is masking a part explosion or if flushes are simply slow.
Track client insert latency alongside AsyncInsertFlush latency from system.query_log to spot backpressure invisible to the application.
Alert on active part count per partition regardless of async insert status. Server-side buffering does not remove per-partition part limits.
Compare MemoryTracking or MemoryResident against AsynchronousInsertQueueBytes to catch unbounded buffer growth before it triggers OOM.

The Netdata solution

ClickHouse monitoring with Netdata

Netdata monitors ClickHouse with per-second metrics and ML anomaly detection. Track merge debt, memory usage, replication lag, Keeper/ZooKeeper saturation, and disk headroom against the host signals that drive them.

See ClickHouse monitoring → Start monitoring free