ClickHouse Async Inserts in Practice

Q: What is an async insert in ClickHouse?

An async insert is an INSERT that ClickHouse buffers in server memory instead of writing to a MergeTree part immediately. The server groups inserts by query shape (table, format, settings, user), accumulates rows until a buffer-size, query-count, or time threshold is hit, then writes a single part for the whole batch. The feature is enabled per query, per user, or per session via async_insert = 1 and is documented in the optimization guide.

Q: What does wait_for_async_insert do?

With wait_for_async_insert = 1 (default), the INSERT returns to the client only after the server has flushed the buffer to disk and the resulting part is durable. With wait_for_async_insert = 0, the INSERT returns as soon as the server has accepted the rows into the buffer, before the flush. The fire-and-forget mode is faster but loses any buffered-but-unflushed rows if the server crashes. Use 1 for almost everything; use 0 only for true fire-and-forget log shipping where individual rows are dispensable.

Q: How does the adaptive busy timeout work?

With async_insert_use_adaptive_busy_timeout = 1, ClickHouse measures the ingestion rate per buffer and chooses a flush timeout dynamically between async_insert_busy_timeout_min_ms and async_insert_busy_timeout_max_ms. Heavy traffic flushes near the lower bound (small timer, big batches), sparse traffic flushes near the upper bound (longer timer, since the batch will be small anyway). Shipped in 24.2 and enabled by default in recent releases — see the ClickHouse engineering blog for the design.

Q: Does insert deduplication work with async inserts?

Yes, but you have to enable it. Set async_insert_deduplicate = 1 on the writing user or query — the server then computes a per-client dedup token before batching, so a client retry inserts the same logical block once. Without it, the default ReplicatedMergeTree dedup window only sees the post-batch part hash, which is too coarse to catch client-level retries. For pipelines fed by at-least-once delivery (Kafka, SQS, application-level retries), turn this on.

Q: How do I monitor async insert health in production?

Two system tables. system.asynchronous_inserts is the live view of active buffers — query it to find buffers whose age (in seconds) is many times the async_insert_busy_timeout_max_ms window (which is in milliseconds, so anything past a few seconds is suspect) — a stuck buffer. system.asynchronous_insert_log is the historical record, one row per buffered insert — alert on status != 'Ok', and track flush sizes by aggregating bytes per flush_query_id first (a plain avg(bytes) averages per-insert rows, not per-flush sizes). system.part_log correlates each flush with the resulting MergeTree part for end-to-end traceability.

When ClickHouse async inserts beat client-side batching, the settings that matter, and how to monitor them in production without the Too many parts error.

This post was written by an engineer at QueryPlane. QueryPlane is an app builder for your database: bring your own postgres db and you can create interactive applications to share with other developers, coworkers or even your customers. If you’re interested in trying it out, get started here.

ClickHouse loves big batches and hates small ones. Drop a few million rows in a single INSERT and the MergeTree engine is happy: one part, one flush, the background merger handles compaction at its leisure. Send the same rows one or two at a time from a fleet of application servers and the engine starts producing thousands of tiny parts per minute, the merger falls behind, parts_to_throw_insert trips, and the cluster starts rejecting writes with Too many parts.

For a long time the only fix was client-side batching: write a buffer in your application, accumulate rows, flush every N seconds or M megabytes. That works when you control every writer. It does not work when you do not — when the writers are short-lived Lambdas, when they are third-party clients, when they are an MCP server you can’t modify, or when the team running ClickHouse is not the team running the apps. Async inserts move the buffer to the server: the application keeps sending small INSERT statements, the ClickHouse server batches them in memory, and a single MergeTree part is written when the buffer fills or a timer fires.

This guide is the practical version. The mechanics, the settings that actually matter, what to set them to, how the adaptive busy timeout changes the calculation, when async inserts are the wrong answer (Buffer tables, client-side batching), and what to monitor when things go sideways. Examples were verified locally against ClickHouse 25.5.

In this post, we’ll cover:

Why frequent inserts hurt MergeTree — parts, merges, and Too many parts
What async inserts do — the server-side buffer model
Enabling and tuning — the four settings that matter
wait_for_async_insert — synchronous vs fire-and-forget acknowledgement
Adaptive busy timeout — what changed in 24.2 and why it matters
Deduplication — async_insert_deduplicate and idempotency
Monitoring — system.asynchronous_inserts and system.asynchronous_insert_log
Async inserts vs Buffer tables vs client batching — the decision rules
Production pitfalls — fairness, large rows, schema mismatches, query fingerprinting

Why frequent inserts hurt MergeTree

Every INSERT to a MergeTree table becomes a new on-disk part: a directory with column files, an index, and a checksum. The background merger reads parts, combines them into larger parts, and deletes the originals, which keeps the part count bounded and the read path efficient. The cost model that matters: each part adds metadata the merger has to process, each merge reads and writes the entire data in the parts it combines, and the engine can only merge so many parts in parallel per partition.

ClickHouse enforces two limits to keep the part count from running away. parts_to_delay_insert (default 1000 per partition) starts inserting an artificial delay on every new INSERT once the part count exceeds it, scaling the delay up as more parts accumulate. parts_to_throw_insert (default 3000 per partition) hard-fails inserts with Too many parts (N). Merges are processing significantly slower than inserts. once the count is too high. The full list is in the MergeTree settings docs. Either limit is a sign that the write pattern is wrong, not a number to raise.

The pattern that produces this fastest: a fleet of writers, each sending small INSERT INTO events VALUES (...) for one row at a time, hundreds of times per second. Even with cheap inserts, a sustained 1000 inserts/second to a single partition produces 1000 parts per second. The merger cannot keep up. The cluster falls behind in minutes.

The right answer is always: insert fewer, bigger batches. The question is where the batching happens.

What async inserts do

Async inserts move the batching from the client to the server. When async_insert=1 is set on an INSERT, ClickHouse does not write a new part immediately. Instead it appends the rows to an in-memory queue keyed by the query shape (the INSERT INTO ... VALUES statement, the settings, the user, the database). When one of three conditions triggers a flush — the queue reaches async_insert_max_data_size (default 10 MiB), the queue reaches async_insert_max_query_number (default 450 queries), or the timer set by async_insert_busy_timeout_ms (default 200ms self-hosted, 1000ms on ClickHouse Cloud) elapses — the server combines every buffered insert with the same shape into one part and writes it to disk.

The shape matters. Two clients sending INSERT INTO events FORMAT JSONEachRow get batched into the same buffer. A third client sending INSERT INTO events VALUES is a different shape and gets its own buffer. A fourth client sending the same INSERT statement but with a different value for session_user gets yet another buffer. The grouping is conservative because a flush has to produce a single coherent part — same table, same format, same column set, same settings.

-- Async insert (server-side buffering)
INSERT INTO events SETTINGS async_insert=1, wait_for_async_insert=1
FORMAT JSONEachRow
{"event_id":1,"event_type":"page_view","ts":"2026-06-25 10:00:00"}

The client sends a tiny payload. The server stores it in the buffer, returns a success once the row is durable enough, and writes the actual MergeTree part later when the buffer is full or the timer fires.

This shifts the failure surface. If the buffer fills faster than the merger can handle the resulting parts, the merger still falls behind — async inserts do not magically solve part count problems, they just move the point at which batching happens. The fix in that case is bigger buffers (raise async_insert_max_data_size), not more frequent flushes.

Enabling async inserts

There are four ways to turn on async inserts, and the choice matters for who owns the configuration.

Per-query: set async_insert=1 in the query itself.

INSERT INTO events SETTINGS async_insert=1
VALUES (1, 'page_view', now());

This works if the application can control the SQL. It’s the most explicit and the easiest to reason about, because the setting travels with the query and survives upgrades.

Per-user: attach the setting to the user profile in users.xml or via CREATE SETTINGS PROFILE.

CREATE SETTINGS PROFILE async_writers SETTINGS async_insert = 1, wait_for_async_insert = 1;
ALTER USER ingester SETTINGS PROFILE 'async_writers';

This is the right pattern when a specific role does the writing and you don’t want every caller to remember the setting. The profile also lets you set the buffer limits and the busy timeout in one place.

Per-session: set it in the connection string or as the first statement on a session.

SET async_insert = 1;
SET wait_for_async_insert = 1;
INSERT INTO events VALUES (1, 'page_view', now());

Less common in production because it relies on every connection running the SET once.

Server-wide: set async_insert = 1 in the default profile in users.xml. Discouraged. Async inserts make sense for high-frequency small writes; turning them on globally also affects the migration scripts and analytical writes where async behavior is the wrong default.

For the rest of this post the examples assume per-user or per-query enabling — that’s what we see in production.

The four settings that matter

Most of the 25+ async-insert settings can stay at their defaults. The four worth tuning:

Setting	Default	What it controls
`async_insert_max_data_size`	`10485760` (10 MiB)	Buffer size in bytes before a flush is forced
`async_insert_busy_timeout_ms`	`200` (1000 on ClickHouse Cloud)	Max ms between flushes when the buffer isn’t full
`async_insert_max_query_number`	`450`	Max queries in a buffer before a flush is forced
`wait_for_async_insert`	`1`	Whether the client waits for the part to be written

async_insert_max_data_size is the throughput knob. Raise it when the merger is healthy and you want larger parts (fewer of them); lower it when you need faster visibility of newly-inserted data. The hard upper bound is roughly max_memory_usage_for_queries / number_of_concurrent_distinct_buffers — every distinct buffer holds up to this much in RAM, and a busy ingestion path with many distinct query shapes can accumulate gigabytes of buffered data if you set it too high.

async_insert_busy_timeout_ms is the latency knob. Until 24.2 this was a fixed timeout — every buffer flushed every N ms regardless of fill rate. From 24.2 it interacts with the adaptive busy timeout (covered below). For most workloads 1000 ms is a reasonable floor; 100 ms is the lowest we’ve seen anyone need, and below that the per-flush overhead dominates the saved buffering time.

async_insert_max_query_number exists because a buffer with many distinct queries is expensive to merge into a single part — every contributing query has to be parsed and validated. The default 450 prevents a pathological case where thousands of tiny inserts pile up in one buffer.

wait_for_async_insert is the durability knob and the most important one to get right.

`wait_for_async_insert` — the durability tradeoff

When wait_for_async_insert=1 (the default), the INSERT statement returns to the client only after the server has flushed the buffer to a MergeTree part. The client knows the data is durable, the part exists on disk, and (in a replicated setup) the insert has been distributed.

When wait_for_async_insert=0, the INSERT returns as soon as the server has accepted the rows into the buffer. The client gets a success response immediately. If the server crashes before the buffer flushes — or if the flush itself fails — those rows are lost, silently.

The performance difference is large. With wait_for_async_insert=1, the client’s latency is bounded by async_insert_busy_timeout_ms — every insert waits up to ~1 second for the next flush. With wait_for_async_insert=0, the client’s latency is bounded by network round-trip — microseconds.

The right answer for almost every production workload is wait_for_async_insert=1. The latency is acceptable for ingestion-shaped writes, and the durability guarantee matches what teams actually need. The exception is true fire-and-forget log shipping where individual rows are dispensable and the writer cannot tolerate any latency increase — wait_for_async_insert=0 is correct there, with explicit acknowledgement in the application that some rows will be lost on server restart.

The middle option people miss: wait_for_async_insert=1 plus a higher async_insert_busy_timeout_ms. The client still gets a synchronous acknowledgement, but each acknowledgement covers a larger batch. If the application is throughput-bound and not latency-bound, that’s the better lever.

Adaptive busy timeout

In ClickHouse 24.2 the adaptive busy timeout shipped under async_insert_use_adaptive_busy_timeout (defaults to enabled in recent versions). The mechanic: instead of using async_insert_busy_timeout_ms as a fixed flush interval, the server tracks the ingestion rate per buffer and adjusts the timeout dynamically between async_insert_busy_timeout_min_ms and async_insert_busy_timeout_max_ms.

The effect is significant. A buffer receiving heavy traffic flushes near the lower bound (fast, big batches). A buffer receiving sparse traffic flushes near the upper bound (slower, but the batches are still meaningful because there’s so little incoming data anyway). The same async_insert_busy_timeout_min_ms = 50 and async_insert_busy_timeout_max_ms = 5000 config handles both the steady-state high-write case and the bursty low-volume case without manual tuning.

The settings on a deployment that uses the adaptive timeout:

<async_insert>1</async_insert>
<async_insert_use_adaptive_busy_timeout>1</async_insert_use_adaptive_busy_timeout>
<async_insert_busy_timeout_min_ms>50</async_insert_busy_timeout_min_ms>
<async_insert_busy_timeout_max_ms>2000</async_insert_busy_timeout_max_ms>
<async_insert_max_data_size>10485760</async_insert_max_data_size>
<wait_for_async_insert>1</wait_for_async_insert>

The min/max set the boundaries; the server picks the actual timeout per buffer based on observed ingestion rate. The other settings still apply — the buffer flushes whichever trigger fires first.

When to disable the adaptive timeout: only when you have a measured reason to. The fixed-timeout mode is mostly useful in synthetic tests where reproducible flush cadence matters. For production workloads the adaptive mode is the right default.

See what QueryPlane can build for you

Connect to your database, write SQL with AI, and build shareable apps — all from your browser.

Get Started Book a Demo

Deduplication and idempotency

ClickHouse supports insert deduplication on ReplicatedMergeTree tables: the server keeps a window of recent insert block hashes, and if the same block is inserted twice within the window (default 100 blocks, 7 days), the duplicate is discarded silently. This is what makes INSERT retries safe — a client that times out and retries doesn’t double-insert.

Async inserts originally bypassed this — the server batched inserts before computing the hash, so the dedup window only saw the post-batch part hash, not the individual client inserts. From 23.4 the async_insert_deduplicate setting (default off) computes a token per client insert, lets the server detect identical client retries inside the buffer, and dedupes correctly.

Enable it on any pipeline where the client might retry:

CREATE SETTINGS PROFILE async_writers
  SETTINGS async_insert = 1, wait_for_async_insert = 1, async_insert_deduplicate = 1;

The cost is a small amount of extra hashing per insert. The benefit is that a client retry after a timeout no longer inserts the same row twice. For pipelines feeding ReplicatedMergeTree from at-least-once delivery sources (Kafka, SQS, application-level retry loops), turn this on.

An explicit insert_deduplication_token can also be supplied per-client when the natural insert hash isn’t stable enough — useful when the same logical event is constructed slightly differently on retry. The token is a free-form string and the server treats it as the dedup key.

Monitoring async insert health

Two system tables answer “are my async inserts healthy” questions.

system.asynchronous_inserts is the live view of the queue — one row per active buffer, with top-level query, database, table, format, first_update, and total_bytes, plus the nested arrays entries.query_id and entries.bytes (one element per buffered insert). A query against this table tells you which buffers are currently active and how full they are.

SELECT
  database,
  table,
  format,
  total_bytes,
  length(entries.query_id) AS pending_queries,
  arraySum(entries.bytes) AS pending_bytes,
  first_update,
  age('second', first_update, now()) AS age_sec
FROM system.asynchronous_inserts
ORDER BY total_bytes DESC
LIMIT 20;

A buffer whose age_sec is in the tens of seconds is stuck — that is far beyond async_insert_busy_timeout_max_ms, which is in milliseconds (sub-second by default), so the flush should have fired long ago. (Mind the units: age_sec is seconds, the timeout is milliseconds — convert before comparing.) Common causes: a permission revoked from the writer that prevents the flush, a downstream replication problem, or a setting misconfiguration where the timeout is set to a value that effectively never fires.

system.asynchronous_insert_log is the historical record — one row per buffered insert query, with query_id for the original insert, database, table, format, bytes, rows, exception, status, flush_time, flush_query_id (the id of the flush that wrote this entry). Multiple rows share the same flush_query_id when a single flush combines many buffered inserts. This is the table to alert on, but flush-level metrics need to be aggregated by flush_query_id first — averaging the per-insert bytes reports per-row sizes, not per-flush sizes.

-- Failed flushes in the last hour
SELECT
  flush_time,
  database,
  table,
  flush_query_id,
  any(status)                    AS status,
  any(exception)                 AS exception,
  count()                        AS entries_in_flush
FROM system.asynchronous_insert_log
WHERE flush_time > now() - INTERVAL 1 HOUR
  AND status != 'Ok'
GROUP BY flush_time, database, table, flush_query_id
ORDER BY flush_time DESC;

-- Flush size distribution by table (aggregated to one row per flush first)
WITH per_flush AS (
  SELECT
    database,
    table,
    flush_query_id,
    sum(bytes)               AS flush_bytes,
    sum(rows)                AS flush_rows,
    count()                  AS entries_in_flush
  FROM system.asynchronous_insert_log
  WHERE event_time > now() - INTERVAL 1 DAY
    AND status = 'Ok'
  GROUP BY database, table, flush_query_id
)
SELECT
  database,
  table,
  count()                                 AS flushes,
  formatReadableSize(avg(flush_bytes))    AS avg_flush_size,
  formatReadableSize(max(flush_bytes))    AS max_flush_size,
  avg(flush_rows)                         AS avg_rows_per_flush,
  avg(entries_in_flush)                   AS avg_entries_per_flush
FROM per_flush
GROUP BY database, table
ORDER BY flushes DESC;

A healthy async-insert deployment has flush sizes well above async_insert_max_data_size divided by 100 — averaging a few hundred KB or more per flush. Flushes consistently in the kilobyte range mean the buffer is timing out before filling, which is fine for low-volume tables but a sign that the busy timeout is too aggressive for the actual ingestion rate. The avg_entries_per_flush column is the diagnostic for whether shape fragmentation is the problem — a healthy buffer has many entries combined into one flush; one-entry flushes mean each buffered insert has a distinct shape and the server can’t coalesce them.

The system table to also know about: system.part_log lets you correlate the flush events with the actual MergeTree parts that resulted. Each successful flush produces one row in part_log with event_type = 'NewPart' — the size of that part is the size of the flushed buffer, and the merge_reason tells you whether the part was subsequently merged.

Async inserts vs Buffer tables vs client-side batching

Three patterns solve the small-insert problem. They are not interchangeable.

Buffer tables are a separate table engine that buffers rows in memory and periodically flushes them to a target table. Predates async inserts by years. The configuration knobs (min_time, max_time, min_rows, max_rows, min_bytes, max_bytes) and the basic model (memory buffer flushed to a backing table) are conceptually similar to async inserts, but the implementation differs: Buffer tables are themselves tables, with their own DDL, their own row counts queryable via SELECT, and their own failure mode (data lost on server restart if not flushed).

Buffer tables are still the right choice in two cases. First, when you need to query the buffered data — SELECT against a Buffer table returns rows that haven’t yet been flushed, which is useful for low-latency dashboards. Async inserts don’t offer this; the rows are not visible to SELECT until the buffer flushes. Second, when the target table is a Distributed table and you want the buffering to happen at the shard level — Buffer tables compose with Distributed engines in a way async inserts don’t.

Async inserts replace Buffer tables for most other use cases. They have better visibility (the system tables), better durability semantics (when wait_for_async_insert=1), don’t require schema changes (no separate buffer table to create and maintain), and don’t carry the memory-lost-on-restart risk.

Client-side batching — accumulating rows in the application and sending a single INSERT every N rows or M seconds — is still the highest-performing option when the writer can do it. The server doesn’t have to maintain the buffer state, the buffer lives in the writer’s memory (so it doesn’t compete with other ClickHouse work), and the merger sees one large part per batch directly. The downside is operational: every writer has to implement and tune the batching independently, and a writer that crashes loses the buffered rows.

The decision rule:

If you control the writers and they have enough memory to buffer locally — client-side batching. Highest throughput.
If you don’t control the writers, or the writers are too short-lived to buffer locally — async inserts. Same effect, server-side.
If you need to query the buffered data before it flushes — Buffer tables. The only option that exposes the buffer to SELECT.

Async inserts and client-side batching can also be combined. A client that batches up to 10K rows or 1 MB before sending, with the server then applying async inserts on top, gets both the client-side savings (one network round-trip per 10K rows) and the server-side coalescing across multiple clients (one MergeTree part per N clients).

Production pitfalls

Patterns that bit teams in the first six months of running async inserts at production scale.

Distinct query shapes fragmenting the buffer. The server keys buffers by the query string. INSERT INTO events (col_a, col_b) and INSERT INTO events (col_b, col_a) are different buffers. Inserts from clients that interpolate values into the query string (rather than using parameter binding) produce a new buffer per unique value combination. The fix: use FORMAT Values or FORMAT JSONEachRow for the actual row data, never interpolate values into the INSERT statement itself. ClickHouse drivers like clickhouse-connect do this correctly by default.

Large rows blowing through async_insert_max_data_size. A single 5 MB row with async_insert_max_data_size = 10 MiB means the buffer flushes after one or two rows — back to the small-batch problem the setting was supposed to fix. For wide rows or JSON columns with large payloads, raise async_insert_max_data_size to 50-100 MiB so the buffer actually accumulates a useful batch. Watch RAM usage when doing this; the total async-insert memory budget is set by async_insert_threads × async_insert_max_data_size × the number of distinct buffers.

Unbalanced fairness across users. Async inserts share a server-wide thread pool (async_insert_threads, default 16). One heavy writer flushing many large buffers can starve a lighter writer’s small buffers, increasing their latency. The fix is per-user resource limits via settings profiles, or — for cleaner isolation — a separate ClickHouse service per noisy-neighbor tier.

Schema drift between flushed buffers. If the table schema changes (a column added, a default changed) while a buffer is mid-flight, the buffered rows can fail validation when the flush fires. ClickHouse logs the failure to system.asynchronous_insert_log with status = 'ParsingError'. On a busy ingestion path, schedule schema migrations during a low-ingestion window or pause writes through the buffer before migrating.

Misreading wait_for_async_insert=0 as faster, period. Fire-and-forget is faster for the client, but it does not increase the server’s flush throughput. If the buffer is the bottleneck — buffer fills faster than the merger can write parts — wait_for_async_insert=0 just means the rejection happens later. The right fix in that case is bigger buffers, more shards, or fewer distinct query shapes, not bypassing the acknowledgement.

Insertion through a Distributed table. Async inserts on a Distributed table buffer on the initiator, then split to shards on flush. The setting at the shard level is independent — async inserts on the underlying MergeTree are still controlled by that shard’s settings. For multi-shard async inserts, configure both: the initiator (for the cross-shard batching) and the shards (for the per-shard batching).

Near-empty flushes from too many distinct shapes — async_insert_max_query_number is the wrong knob. A writer that sends thousands of distinct query shapes per second produces thousands of separate buffers (one per shape), each containing a handful of entries that flush at the busy timeout — not one buffer hitting async_insert_max_query_number = 450. Lowering that setting will not help; with one entry per shape, fewer-shape buffers will still flush near-empty on the timer. The actionable fix is upstream: normalize the insert shape and format on the writer side. Use FORMAT Values or FORMAT JSONEachRow with the values supplied as data (not interpolated into the INSERT statement), and pin the column list so two writers don’t produce different shapes for the same logical write. The monitoring query above’s avg_entries_per_flush near 1 is the symptom; a fixed writer brings it up to hundreds of entries per flush.

Frequently asked questions

What is an async insert in ClickHouse? An async insert is an INSERT that ClickHouse buffers in server memory instead of writing to a MergeTree part immediately. The server groups inserts by query shape (table, format, settings, user), accumulates rows until a buffer-size, query-count, or time threshold is hit, then writes a single part for the whole batch. The feature is enabled per query, per user, or per session via async_insert = 1 and is documented in the optimization guide.

When should I use async inserts instead of client-side batching? Use async inserts when you don’t control the writers (third-party clients, short-lived workers, an MCP server you can’t modify) or when each writer can’t buffer enough rows locally to produce large batches. Use client-side batching when you control the writers and they can hold thousands of rows in memory before flushing — that path has lower server overhead and produces the largest possible MergeTree parts. The two are compatible: client-side batching that produces moderately sized batches plus async inserts that coalesce across writers gives the best of both.

What does wait_for_async_insert do? With wait_for_async_insert = 1 (default), the INSERT returns to the client only after the server has flushed the buffer to disk and the resulting part is durable. With wait_for_async_insert = 0, the INSERT returns as soon as the server has accepted the rows into the buffer, before the flush. The fire-and-forget mode is faster but loses any buffered-but-unflushed rows if the server crashes. Use 1 for almost everything; use 0 only for true fire-and-forget log shipping where individual rows are dispensable.

Do async inserts solve the Too many parts error? Not directly — they coalesce writes into fewer, larger parts, which slows the rate of new-part creation. If the merger was barely keeping up with manual batching, async inserts will help by reducing the part count produced per ingestion. If the merger is fundamentally too slow for the workload (too few merge threads, too aggressive partitioning, undersized hardware), async inserts move the failure later but don’t fix the root cause. The Too many parts rate after enabling async inserts is the diagnostic.

What is the difference between async inserts and Buffer tables? Both buffer rows in memory before writing to a backing MergeTree table. Async inserts buffer at the server level keyed by query shape, are configured via settings, and produce parts that go straight to the target table. Buffer tables are a distinct table engine with their own DDL, expose buffered rows to SELECT (which async inserts do not), and compose with Distributed engines. Buffer tables are the right choice when you need to query buffered data; async inserts are the right choice for everything else.

How does the adaptive busy timeout work? With async_insert_use_adaptive_busy_timeout = 1, ClickHouse measures the ingestion rate per buffer and chooses a flush timeout dynamically between async_insert_busy_timeout_min_ms and async_insert_busy_timeout_max_ms. Heavy traffic flushes near the lower bound (small timer, big batches), sparse traffic flushes near the upper bound (longer timer, since the batch will be small anyway). Shipped in 24.2 and enabled by default in recent releases — see the ClickHouse engineering blog for the design.

Does insert deduplication work with async inserts? Yes, but you have to enable it. Set async_insert_deduplicate = 1 on the writing user or query — the server then computes a per-client dedup token before batching, so a client retry inserts the same logical block once. Without it, the default ReplicatedMergeTree dedup window only sees the post-batch part hash, which is too coarse to catch client-level retries. For pipelines fed by at-least-once delivery (Kafka, SQS, application-level retries), turn this on.

How do I monitor async insert health in production? Two system tables. system.asynchronous_inserts is the live view of active buffers — query it to find buffers whose age (in seconds) is many times the async_insert_busy_timeout_max_ms window (which is in milliseconds, so anything past a few seconds is suspect) — a stuck buffer. system.asynchronous_insert_log is the historical record, one row per buffered insert — alert on status != 'Ok', and track flush sizes by aggregating bytes per flush_query_id first (a plain avg(bytes) averages per-insert rows, not per-flush sizes). system.part_log correlates each flush with the resulting MergeTree part for end-to-end traceability.

Can I use async inserts with Distributed tables? Yes. The async-insert buffer runs on the initiator node — the node receiving the INSERT against the Distributed table — and the buffered rows split to shards on flush. The per-shard MergeTree tables can also have async inserts enabled independently. For multi-shard ingestion you typically want both, with the initiator’s settings tuned for the cross-shard batch size and each shard’s settings tuned for its local merger.

What’s the recommended async_insert_max_data_size? The default 10 MiB is a reasonable starting point. Raise it to 50-100 MiB if you have wide rows or large JSON columns and the average flush size is barely a few hundred KB — bigger buffers produce fewer, larger parts. Watch the total RAM used by async inserts (async_insert_threads × max_data_size × distinct_buffers); the total should be a small fraction of the server’s memory budget. Lower it only when freshness matters more than throughput — for example, when downstream tools need to read newly-inserted rows within a fraction of a second.

Wrapping up

Async inserts are the right answer when small, frequent writes need to become large, infrequent MergeTree parts and the writer can’t (or shouldn’t) do the batching itself. The pattern is well-trodden — async inserts have been in ClickHouse since 21.11 and most of the rough edges were sanded down by the 24.x line — and the tuning surface is small enough that most deployments need to touch only wait_for_async_insert, the busy-timeout min/max, and async_insert_max_data_size.

The shape of the work to do before flipping the setting on:

Measure the current part-creation rate per partition and the Too many parts rate — async inserts help the most when both are high.
Decide whether wait_for_async_insert should be 1 (the right answer for almost everything) or 0 (true fire-and-forget log shipping only).
Set the busy-timeout min/max conservatively at first (say 50 ms / 2000 ms) and let the adaptive mode pick the actual cadence.
Add the monitoring queries above to whichever observability stack the team uses and alert on status != 'Ok' in system.asynchronous_insert_log.

If you want a fast way to run those monitoring queries and explore system.asynchronous_inserts and system.asynchronous_insert_log interactively, our guide to the best ClickHouse GUI tools covers the editors that handle large system.* tables well. Pair this with our LowCardinality guide — async inserts and LowCardinality are the two highest-leverage knobs on a high-ingestion ClickHouse cluster — and our partition by, order by, primary key guide to make sure the table layout the async inserts are landing into is well-tuned. For the merge side of the same problem (what happens to the parts after the flush), the ReplacingMergeTree guide and the TTL and data skipping indexes guide cover the merge-time mechanics. And if you’re building dashboards on top of these ingestion paths and need to expose them to non-technical teammates, that’s exactly what QueryPlane handles — see the ClickHouse integration for the end-to-end shape.