Asynchronous inserts explained

Part 1 explained that every INSERT creates a part and that many tiny inserts are the classic way to hurt a ClickHouse cluster. The best fix is to batch in the client. When that is not possible (many independent producers, serverless functions, IoT devices, a legacy application), ClickHouse can batch for you: asynchronous inserts.

The examples in this article need a running server, because asynchronous inserts rely on the server’s insert queue. Statements were parsed with clickhouse format on 26.4.

What it does

With async_insert = 1, the server does not write a part per statement. Instead it appends each insert’s data to an in-memory buffer keyed by table, format and settings. When the buffer is flushed, all the data in it is written as a single part. Inserts from many clients with the same query shape end up in the same buffer.

INSERT INTO events SETTINGS async_insert = 1
VALUES (now(), 42, 'click', '/pricing', 120, 'DE');

Note that the settings go before VALUES (or before SELECT). Settings placed after the data are not parsed as settings.

In recent releases (26.x, where this was checked) async_insert is enabled by default for every session, while older versions defaulted to 0. Check SELECT name, value FROM system.settings WHERE name LIKE 'async_insert%' on your version, and set it explicitly in client code so you do not depend on the default.

A little history

Asynchronous inserts arrived in ClickHouse 21.11, in late 2021, as an opt-in per query (async_insert = 1), with wait_for_async_insert choosing whether the client waits for the flush. Early adopters tried them from client drivers almost immediately; a 2022 proof of concept in Go (ch-async-insert-poc) sent 100,000 single-row inserts through clickhouse-go’s AsyncInsert with the wait flag off, against the same loop using plain synchronous inserts. Today async_insert is on by default, which turns that once-experimental pattern into the normal path for small writes.

When the buffer is flushed

A buffer is flushed when any of these happens first:

Trigger	Setting
The collected data reaches a size limit	async_insert_max_data_size (10 MiB by default in 26.4)
A timer expires after the first data arrived	async_insert_busy_timeout_max_ms (200 ms by default; adaptive in recent versions, with a lower bound async_insert_busy_timeout_min_ms)
A number of insert queries is collected	async_insert_max_query_number (450; only with async_insert_deduplicate)

The defaults differ in ClickHouse Cloud, which uses larger values. The practical consequence: a lone insert waits up to the timeout before it reaches disk. A flush every 200 ms means up to five parts per second per buffer instead of hundreds.

wait_for_async_insert: the important choice

wait_for_async_insert decides what the client is told and when.

Value	Behaviour	Trade-off
1 (default)	The server answers only after the buffer holding your data has been flushed and written.	Latency is the flush interval. If the data is bad or the write fails, you get the error.
0	The server answers as soon as it has put the data in the buffer (“fire and forget”).	Very fast acknowledgement, but if parsing or the write fails later, the client never learns about it. A server crash loses the buffer.

So the question is not “async or not” but “do you need to know”. Use wait_for_async_insert = 1 for anything you cannot lose or re-send. Use 0 only for data you can afford to drop, such as best-effort telemetry.

With wait_for_async_insert = 1, a client that sends one insert at a time and waits for each answer is limited to roughly one insert per flush interval. Five sequential inserts at a 200 ms flush take about a second. The way to get throughput is concurrency, many connections or in-flight requests sharing the buffer, not a faster loop. Parallelism turns the wait into a shared one: all of them are acknowledged by the same flush.

What a client should look like

# many concurrent workers, each doing small inserts, all sharing server-side batches
client.command(
    "INSERT INTO events SETTINGS async_insert = 1, wait_for_async_insert = 1 "
    "VALUES (now(), 1, 'view', '/', 10, 'US')"
)

Prefer sending a moderately sized batch per call (hundreds or thousands of rows) over single rows even when async inserts are on; the per-statement overhead of parsing and the network round trip is what async inserts cannot remove.

Gotchas

• Errors arrive late. The data is parsed and checked against the table when it is processed, not when you send it. With wait_for_async_insert = 0 you will never see that error, which is another reason to keep waiting enabled while you develop.
• Test with the real client path. Asynchronous insert is a server feature, but how it behaves depends on how your client sends data (statement with VALUES, or a data stream in the request body). Measure with your actual driver.
• Deduplication. Retries after a timeout can double-insert. async_insert_deduplicate = 1 (default 0) deduplicates identical retried inserts on replicated tables, at some cost.
• Visibility. Data is not visible to SELECT until the flush. Do not read-after-write against the same table with wait_for_async_insert = 0.
• Monitoring. system.asynchronous_inserts shows what is currently buffered, and the asynchronous_insert_log table (when enabled) records flushes with their status and errors.

Decision guide

Situation	Choice
You control the writer	Batch in the client; async inserts are unnecessary
Many small writers, must not lose data	async_insert = 1, wait_for_async_insert = 1, high concurrency
Many small writers, loss acceptable	async_insert = 1, wait_for_async_insert = 0
One huge bulk load	Synchronous large inserts, or ATTACH PARTITION FROM

Next: inserting into Distributed tables.

Reference: asynchronous inserts in the docs.