Async Inserts & Batching

Parts & Background Merges already covers why small, frequent inserts are dangerous — each one creates a new part, and enough of them outrun the background merge process. Batching client-side is the ideal fix. This page is for when you don't control the client: many independent application servers, each with a handful of rows to insert, with no good place to accumulate a batch before sending it.

Option one: the Buffer engine

A Buffer table sits in front of a real MergeTree table. Inserts land in memory, and the buffer flushes into the underlying table once row-count, byte-size, or time thresholds are crossed — turning many tiny inserts into far fewer, larger ones before they ever become a part.

CREATE TABLE events_buffer AS events
ENGINE = Buffer(
    currentDatabase(), events,   -- target database, target table
    16,                          -- number of buffer layers
    10, 100,                     -- min/max seconds before flush
    10000, 1000000,              -- min/max rows before flush
    10000000, 100000000          -- min/max bytes before flush
);

-- application inserts here, not into 'events' directly
INSERT INTO events_buffer VALUES (...);

It's the older mechanism, still used, but with a real cost: the buffer lives in server memory and is lost on restart or crash — anything not yet flushed is gone.

Option two: async_insert (the modern default choice)

Rather than a separate table, async_insert is a setting that changes how INSERT itself behaves: the server accepts the (possibly tiny) insert immediately, holds it in an internal buffer alongside inserts from other connections, and flushes the accumulated buffer as one real part once a size or time threshold is hit.

INSERT INTO events
SETTINGS async_insert = 1, wait_for_async_insert = 1
VALUES (102, 'click', now());

wait_for_async_insert is the trade you're actually making explicit:

• = 1 (default) — the client's INSERT blocks until the buffered data is actually flushed to disk. Safer, but the client still waits roughly as long as it would have without async insert.
• = 0 — the server acknowledges the insert as soon as it's in the in-memory buffer, before it's durable. Much lower client-perceived latency, at the cost of a real window where an acknowledged insert can be lost if the server crashes before flushing.