ClickHouse async inserts: when async_insert fixes too-many-parts and when it hides it

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 (default 100 MiB per current documentation)
  • async_insert_busy_timeout_ms (default 200 ms; 1000 ms on ClickHouse Cloud)
  • async_insert_max_query_number (default 450)

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.

Deduplication is off by default for async inserts (async_insert_deduplicate = 0). Sync inserts respect the normal insert_deduplicate behavior; async inserts require an explicit opt-in. Since 24.2, ClickHouse rejects queries that set both async_insert = 1 and deduplicate_blocks_in_dependent_materialized_views = 1. This is controlled by throw_if_deduplication_in_dependent_materialized_views_enabled_with_async_insert (default 1). Prior to 26.1, enabling dedup for async inserts with dependent materialized views could create inconsistency: the source table could deduplicate retried async inserts while dependent views could not.

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

SignalWhy it mattersWarning sign
PendingAsyncInsert / AsynchronousInsertQueueSizeBuffered insert queries waiting to flush.Sustained growth means flushes are falling behind ingest.
AsynchronousInsertQueueBytesMemory occupied by the async insert buffer.Approaching multiples of async_insert_max_data_size suggests queue buildup.
AsynchronousInsertThreadsActiveFlush threads currently busy.Stuck at pool limit while queue grows indicates sequential processing is bottlenecked.
FailedAsyncInsertQueryFlush failures.Any sustained increase means parts are being rejected after the buffer.
Active part count per partitionVerifies whether buffering actually reduced part pressure.Count still approaching parts_to_delay_insert despite async inserts being enabled.
AsyncInsertFlush latency in system.query_logActual time from buffer to disk.Latency climbing while client latency stays flat signals hidden backpressure.
system.asynchronous_insertsLive view of buffer contents.Large numbers of old entries indicate stuck or slow flushes.
system.asynchronous_insert_logHistorical 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.