Use asynchronous inserts carefully

Asynchronous inserts move the batching from your code into ClickHouse. Small inserts land in an in-memory buffer on the server, and the server writes one healthy part when the buffer fills or a timeout passes.

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, and none of them lives long enough to hold a buffer. That’s when async inserts help.

You enable them 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. Two settings control when: async_insert_max_data_size for the buffer size and async_insert_busy_timeout_ms for the maximum wait.

Decide who waits for the flush

wait_for_async_insert is the setting that matters most here. It decides whether the client waits for the data to reach disk.

With wait_for_async_insert = 1, the insert returns only after the buffer is flushed to storage. A success response means your rows are durable. Throughput is still far 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. That changes what “success” means. If the server crashes before the flush, or the buffered rows turn out to be malformed, your client already got an OK for rows that never landed. The error shows up later, in server logs nobody is reading.

My advice is to keep wait_for_async_insert = 1. Switch it off only after you’ve measured that you can’t afford the latency and you can live with losing acknowledged rows.

Retries and duplicates

Retries need an explicit deduplication strategy, especially when materialized views transform the inserted data. A client that times out and resends may deliver the same rows twice. The block-level deduplication you might rely on for synchronous inserts is not something you can lean on here: buffered inserts are recombined into new blocks, so identical payloads no longer look identical to the server.

Check what’s 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 and FlushError rows are the writes your fire-and-forget clients believe succeeded.

Try this in the lab before production depends on the answer. Enable async inserts, send a stream of small writes, and stop the server at different moments. Then count what survived. With wait_for_async_insert = 0 you will lose the buffered tail. It’s much better to learn that from an experiment than from a dashboard that’s quietly missing an hour of data.