/clickhouse-best-practices

Use Async Inserts for High-Frequency Small Batches

Impact: HIGH

When client-side batching isn't practical, async inserts buffer server-side and create larger parts automatically.

Incorrect (small batches without async):

# Small batches without async_insert - creates too many parts
for batch in chunks(events, 100):
    client.execute("INSERT INTO events VALUES", batch)

Correct (enable async inserts):

# Enable async_insert with safe defaults
client.execute("SET async_insert = 1")
client.execute("SET wait_for_async_insert = 1")  # Confirms durability

for batch in chunks(events, 100):
    client.execute("INSERT INTO events VALUES", batch)
# Server buffers and creates larger parts automatically

-- Configure server-side for specific users
ALTER USER my_app_user SETTINGS
    async_insert = 1,
    wait_for_async_insert = 1,
    async_insert_max_data_size = 10000000,  -- Flush at 10MB
    async_insert_busy_timeout_ms = 1000;    -- Flush after 1s

Flush conditions (whichever occurs first):

• Buffer reaches async_insert_max_data_size
• Time threshold async_insert_busy_timeout_ms elapses
• Maximum insert queries accumulate

Return modes:

Reference: Selecting an Insert Strategy