Async INSERTs

Overview

Async INSERTs let ClickHouse® batch many small inserts in server memory before writing them to storage. Use them when clients cannot form sufficiently large batches themselves. Client-side batching remains preferable when you control the writers because it keeps buffering outside ClickHouse.

Starting with the 26.2.4 release line, ClickHouse enables async inserts by default . Earlier releases default to synchronous inserts. Set it explicitly in an ingestion user’s profile when you need stable behavior across versions:

ALTER USER ingest SETTINGS async_insert = 1, wait_for_async_insert = 1;

Keep wait_for_async_insert = 1 for production workloads. ClickHouse then acknowledges an insert only after the batch reaches storage and returns flush errors to the client. With wait_for_async_insert = 0 (fire-and-forget), the client receives an acknowledgment while data is still buffered and cannot observe later failures.

How Async Inserts Work

ClickHouse keeps separate buffers for different target tables, insert shapes, formats, and settings. Each server node has its own buffers. A buffer flushes when the first applicable condition is met:

• buffered data reaches async_insert_max_data_size
• the busy timeout expires async_insert_busy_timeout_ms
• buffered queries reach async_insert_max_query_number while insert deduplication is enabled.

Async inserts apply to INSERT ... VALUES and inserts with inline formats over HTTP or the native protocol. In 26.10+, eligible single-block INSERT ... SELECT queries into MergeTree tables without dependent views can also use the async queue; see the eligibility rules .

Adaptive timeout

Since ClickHouse 24.2, adaptive busy timeouts are enabled by default . Frequent inserts increase the timeout from async_insert_busy_timeout_min_ms toward async_insert_busy_timeout_max_ms, allowing larger batches. Sparse inserts decrease it toward the minimum to reduce latency.

Keep the adaptive mechanism enabled unless you need a fixed timeout for a tested workload. Tune the minimum for latency and the maximum for batching. Change async_insert_max_data_size when batch size, rather than time, should drive flushing. Leave the polling interval and timeout increase/decrease rates at their defaults unless measurements show a specific problem.

Fixed timeout mode

Set async_insert_use_adaptive_busy_timeout = 0 to use a fixed busy timeout. In this mode, ClickHouse uses async_insert_busy_timeout_max_ms.

async_insert_busy_timeout_ms is an alias for async_insert_busy_timeout_max_ms, not a separate setting. You can use either name, but use async_insert_busy_timeout_max_ms in new configurations.

SET async_insert_use_adaptive_busy_timeout = 0;
SET async_insert_busy_timeout_max_ms = 1000;

When adaptive timeouts are disabled, async_insert_busy_timeout_min_ms, async_insert_busy_timeout_increase_rate, and async_insert_busy_timeout_decrease_rate do not affect the flush timeout.

Critical Configuration Settings

Check effective values in system.settings, system.server_settings, and system.merge_tree_settings instead of assuming defaults match another deployment.

Scoped Async Inserts

Use user profiles or per-query SETTINGS for ordinary writers. Since 23.5, a MergeTree-level async insert setting can force-enable async inserts for one table:

ALTER TABLE db.events MODIFY SETTING async_insert = 1;

The table value is combined with the query/profile value using logical OR. Therefore, table-level async_insert = 0 does not override an inherited async_insert = 1.

Disable async inserts for one pipeline

A materialized view that writes through a Distributed table with distributed_foreground_insert = 1 enabled can create a secondary regular INSERT on a remote shard. If that query inherits async_insert = 1 (enabled by default) and batches little data, each pipeline execution can wait for the busy timeout. Prefer writing the view to the local table when that preserves the required sharding. Otherwise, use a dedicated definer profile to force disable asynchronous inserts for the pipeline.

CREATE SETTINGS PROFILE mv_sync_insert
SETTINGS async_insert = 0 CONST;

CREATE USER mv_sync_definer
IDENTIFIED WITH no_password
HOST NONE
SETTINGS PROFILE mv_sync_insert;

GRANT SELECT ON db.source TO mv_sync_definer;
GRANT INSERT ON db.destination TO mv_sync_definer;

ALTER TABLE db.events_mv
MODIFY SQL SECURITY DEFINER DEFINER = mv_sync_definer;

The CONST constraint prevents an inherited value from replacing the profile value. This method does not work if the destination MergeTree table has async_insert = 1, because the table setting force-enables async mode.

Observability

Use system.asynchronous_inserts to inspect buffers that have not flushed. Use system.asynchronous_insert_log to inspect completed flushes, failures, and time spent waiting.

To identify available async-insert metrics in system.metric_log, run:

clickhouse-client --connection localhost --query "
SELECT name
FROM system.columns
WHERE table = 'metric_log'
  AND (name ILIKE '%asyncinsert%' OR name ILIKE '%asynchronousinsert%')
ORDER BY name
"

Check the current pool, queue, and pending-buffer metrics with:

clickhouse-client --connection localhost --query "
SELECT metric, value, description
FROM system.metrics
WHERE metric ILIKE '%asyncinsert%'
   OR metric ILIKE '%asynchronousinsert%'
ORDER BY metric;
"

AsynchronousInsertThreads is the pool’s current size; AsynchronousInsertThreadsActive is the number running work; and AsynchronousInsertThreadsScheduled counts queued or active jobs. AsynchronousInsertQueueSize, AsynchronousInsertQueueBytes, and PendingAsyncInsert show pending work. Increase async_insert_threads only when the pool reaches its configured maximum, active threads stay near it, and scheduled work remains above it during normal load. Confirm CPU and storage headroom first: more insert threads can increase contention.

Version and Reliability Notes

ClickHouse 24.2 introduced adaptive busy timeouts and SQL security definers for materialized views .

Deduplication changes in 26.2

ClickHouse 26.2 unified synchronous and asynchronous insert deduplication under deduplicate_insert. It defaults to enable for Replicated* tables that retain a deduplication log, and supersedes insert_deduplicate and async_insert_deduplicate. To preserve the earlier per-insert behavior, set deduplicate_insert = 'backward_compatible_choice'. The legacy settings then select deduplication for their respective insert type.

Deduplication for dependent materialized views also defaults to enabled in 26.2. Async inserts cannot deduplicate a view that produces more than one output block per input block. Use synchronous inserts for that view, or set deduplicate_blocks_in_dependent_materialized_views = 0; the latter allows retries to duplicate rows in the view target.

Deduplication-hash migration

<code>insert_deduplication_version</code> is a server setting that migrates old, separate synchronous and asynchronous deduplication hashes to one unified hash. It affects async inserts whenever insert deduplication is enabled.

• old_separate_hashes uses the historical, different hashes.
• compatible_double_hashes writes both the old and unified hashes for every deduplicated block. This is the default in 26.2 through 26.5 and can increase Keeper work and deduplication-log entries on Replicated* tables.
• new_unified_hash writes only the unified hash. It becomes the default in 26.6 . From 26.7 , ClickHouse accepts only this value.

Check the effective value before and after an upgrade:

SELECT name, value, changed, description
FROM system.server_settings
WHERE name = 'insert_deduplication_version';

On 26.2–26.6, first run compatible_double_hashes on every replica. For Replicated* tables, keep it for the largest applicable sync or legacy async deduplication time window. Before switching an active async workload, ensure replicated_deduplication_window and replicated_deduplication_window_seconds cover the legacy *_for_async_inserts windows, then run with those values for a full legacy async time window (seven days by default). Alternatively, quiesce async inserts for that window. For non-replicated tables, run enough inserts to cover the count-based non_replicated_deduplication_window. Then change to new_unified_hash. See the 26.6 upgrade notes .

Run SYSTEM FLUSH ASYNC INSERT QUEUE before planned maintenance to flush pending buffers. Graceful shutdown also flushes them by default.

Related resources

• Asynchronous inserts
• Async insert server settings
• SQL security for views