What does ClickHouse's async_insert setting change about the INSERT write path?

## The problem it solves ClickHouse wants large, infrequent inserts because every insert block becomes a data part and parts must be merged in the background. But plenty of real architectures cannot batch: hundreds of stateless application pods, each handling one request, each with a couple of rows to write. Coordinating a shared batch across them means building a queue, and a queue is a service to operate. Asynchronous inserts move that batching into the server. `async_insert = 1` tells ClickHouse: do not write a part for my rows now, add them to a buffer and write one part for everyone. ## How the buffer works The server maintains in-memory queues keyed by the *shape* of the insert — the target table, the column list, the format, and the relevant settings. Two inserts of the same shape share a queue; two inserts with different column lists do not. A queue is flushed into a single new part when either of two limits trips: - **Size** — the accumulated data exceeds `async_insert_max_data_size` (there is also a bound on the number of queries collected, `async_insert_max_query_number`). - **Time** — the busy timeout expires. Recent versions use an adaptive timeout that shortens under heavy load and lengthens when the stream is sparse, tuned through the busy-timeout settings rather than one fixed interval. On older versions the single `async_insert_busy_timeout_ms` governs it. The net effect on a table receiving 2,000 tiny inserts per second is dramatic: instead of 2,000 parts per second, you get a handful, and the merge pool stops losing the race that produces `Too many parts`. ## The durability knob you must understand `wait_for_async_insert` is the setting an interviewer will probe: - **`1` (default)** — the client's INSERT does not return until the buffer containing its rows has been flushed and the part written. Latency for any one insert rises to roughly the flush interval, but a successful acknowledgement genuinely means the data is on disk. This is the safe default and it is still a huge win, because the *throughput* limit was never per-insert latency, it was parts per second. - **`0`** — the INSERT returns as soon as the rows are copied into the in-memory buffer. Lowest latency, highest throughput, and an explicit data-loss window: if the server crashes or is killed before the flush, acknowledged rows are gone. Choose it only for telemetry-shaped data where losing a second of events is acceptable, and never for anything you would have to reconcile. ```sql INSERT INTO events SETTINGS async_insert = 1, wait_for_async_insert = 1 VALUES (now(), 42, 'click'); ``` The settings can also be set per user, per profile, or on the session, which is usually how it is rolled out — the application code does not change at all. ## Observability `system.asynchronous_insert_log` records each buffered insert and the flush it landed in, including status and any error. It is the table to reach for when someone asks why an insert seemed to succeed but the rows are not visible yet, or when you want to see how many queries each flush is collapsing. `system.parts` still tells you whether the part-per-second rate actually dropped after enabling the feature. ## Trade-offs and failure modes - **Visibility lag.** Rows are not queryable until the buffer flushes. A read-your-own-write test right after an insert can legitimately return nothing. This surprises people more than any other aspect. - **Errors move.** With `wait_for_async_insert = 0`, a parse error or a schema mismatch surfaces in the log and not to the caller. The client saw success. - **Memory.** Buffers live in server memory, and many distinct insert shapes mean many buffers. Uniform insert statements across your fleet keep the buffer count small. - **Deduplication.** Because rows from different clients are combined, per-block deduplication does not map cleanly onto a single client's retry. If retries must not duplicate, supply an `insert_deduplication_token` so identical retried content is recognised, or design the target table (for example a ReplacingMergeTree keyed appropriately) to tolerate duplicates. - **It is not a substitute for real batching.** If the producer *can* accumulate 100k rows and send one statement, that is still cheaper — no server memory, no visibility lag, no shared-buffer semantics. Async inserts are for when it genuinely cannot. ## The comparison an interviewer wants Asynchronous inserts and the older `Buffer` table engine solve the same problem differently. A `Buffer` table is a separate object you insert into that periodically flushes to a target table, and its data is invisible to some operations and lost on an unclean shutdown. Async inserts are a per-insert setting on the real table with an explicit durability switch and their own log. For new systems the setting is the modern answer; the Buffer engine survives mostly in older deployments.