Use asynchronous inserts carefully

Asynchronous inserts can buffer small writes on the server and flush them as larger blocks. They help when changing every producer is difficult.

The previous lesson said to batch on the client. Sometimes you can’t: hundreds of short-lived processes, edge functions, or third-party agents each deliver a handful of rows. Async inserts move the batching into ClickHouse itself — small inserts land in an in-memory buffer, and the server writes one healthy part when the buffer fills or a timeout passes.

You enable it per query or per user with settings:

INSERT INTO analytics.events
SETTINGS async_insert = 1, wait_for_async_insert = 1
VALUES ('2026-08-03 10:00:01', 'api', 101, 'request', 42);

The server collects these small inserts and flushes them together, controlled by thresholds like async_insert_max_data_size and async_insert_busy_timeout_ms.

The acknowledgement decision

Choose whether the client waits for the flush. That’s what wait_for_async_insert controls, and it’s the most consequential setting here.

With wait_for_async_insert = 1, the insert returns only after the buffer is flushed to storage. A success response means your data is durable. Throughput is still better than raw small inserts, because many clients share each flush.

With wait_for_async_insert = 0, the insert returns as soon as the data enters the buffer. Acknowledging before durable insertion changes failure semantics: if the server crashes before the flush, or the buffered data turns out to be malformed, your client already got a success response for rows that never landed. The error surfaces later, in server logs nobody is watching.

My advice is to keep wait_for_async_insert = 1 unless you’ve measured that you can’t afford it and you can tolerate losing acknowledged rows.

Retries and duplicates

Retries need an explicit deduplication strategy, especially when materialized views transform inserted data. A client that times out and resends may deliver the same rows twice, and with async inserts the automatic block-level deduplication you might rely on for synchronous inserts behaves differently — buffered blocks are recombined, so identical-payload detection is no longer a guarantee you can lean on.

Verify what’s actually flowing through the buffer:

SELECT status, count()
FROM system.asynchronous_insert_log
WHERE event_time > now() - INTERVAL 1 HOUR
GROUP BY status;

Ok rows flushed cleanly; ParsingError or FlushError rows are the writes your fire-and-forget clients think succeeded.

Configure async inserts in a lab, stop the server at different moments, and document which acknowledged writes survive. Do this before production traffic depends on the answer — with wait_for_async_insert = 0 you will lose the buffered tail, and it’s much better to learn that from an experiment.