When ClickHouse async inserts fit many agents sending small writes
Async inserts fit when many collectors or agents send small batches that would otherwise create far more data parts than the server can merge comfortably. Each synchronous INSERT normally becomes its own part on disk, so a fleet of agents posting a few rows every second can overwhelm background merges, slow queries and trigger the too-many-parts errors operators dread. With the async_insert setting enabled, the server collects incoming rows in an in-memory buffer and flushes them as a larger batch once a size or time threshold is reached. The client gets control back earlier, and the server does the batching work that the clients would otherwise have to do. Your job is to confirm that this pattern describes your traffic before you change any setting, because the buffer only helps when the writes really are small and frequent.
Client-side batching is still the better answer in several common cases. If a producer already accumulates rows into large blocks, such as a log shipper flushing every few seconds or a batch job loading files, adding server-side buffering gives little benefit and adds a second place where data waits. It is also the better answer when the producer can tolerate a short delay and can retry safely, because the client then decides batch size and failure handling directly. Ask each team which of these describes its pipeline: continuous trickle from many sources, or already batched output from a small number of jobs. The answer determines whether async inserts are a fix or an unnecessary layer.
Before enabling the feature across a fleet, confirm three things with your own environment: the insert rate per table, the number of distinct clients, and whether any client needs an acknowledgment that the rows are durably stored when the call returns. The durability and deduplication behavior depends on how the client waits for the flush, and the reference documentation for the settings involved should be read against the ClickHouse version you run. Pilot the change on a pre-production cluster with representative insert patterns and watch the part count and insert latency before and after. This check will show whether async inserts solve your problem or only move it.
Async insert settings and when each one fits
This table compares the five async insert settings that control buffering, acknowledgement and deduplication, with the trade-off each one carries. Read it to choose values for your agents, then confirm exact names and defaults against your ClickHouse version's documentation before you apply them.
| Setting | Scope | Trade-off | When it fits |
|---|---|---|---|
| async_insert | Per query, session or settings profile | Turns on server-side buffering, so many small inserts become fewer larger parts; the client gets no immediate per-insert write result unless it waits | High-volume small writes from many agents where merge pressure from tiny parts is the problem |
| wait_for_async_insert | Per query, session or settings profile | Waiting until the buffer is flushed gives a durable acknowledgement but adds latency; not waiting returns faster but a crash can lose buffered rows | Use waiting for data you cannot replay; skip it for telemetry that your agents can resend |
| async_insert_max_data_size | Per query, session or settings profile | A larger buffer means fewer, bigger parts and more memory held per server; a smaller one flushes sooner with more parts | Raise it when agents send steady volume and memory is available; lower it when freshness matters more |
| async_insert_busy_timeout_ms | Per query, session or settings profile | A longer timeout batches more rows but delays visibility of new data; a shorter one improves freshness and creates more parts | Tune it against your freshness target for dashboards and alerts, then confirm the default for your version |
| async_insert_deduplicate | Per query, session or settings profile | Deduplicates identical retried async inserts within the buffer and recent blocks; it does not replace a deduplication design for all retries | Enable when agents retry after timeouts and you need to suppress repeated identical blocks; test it on representative retries first |
Choosing wait_for_async_insert for your durability needs
When a write feeds a decision that cannot be repeated later, such as a billing event or an audit record that another process reads, the client needs confirmation that the data reached durable storage before it moves on. With wait_for_async_insert set to 1, the server holds its response until the buffered data is flushed to the table, so a success reply means the insert was written. The cost is added latency per request, and the agent still needs a retry path for timeouts, because a lost reply does not tell it whether the batch landed.
- Acknowledgement means flushed: a success response follows the flush to the table, not the arrival of the row.
- Timeouts are ambiguous: a dropped connection after sending leaves the client unsure whether the batch was stored, so retries must be safe to repeat.
- Deduplication on retry: confirm how
async_insert_deduplicatebehaves on your version before relying on it to prevent duplicate rows. - Latency budget: waiting for the flush adds time per request, so measure it under representative load before choosing this mode.
- Replica scope: check which replica and replication behavior apply to acknowledged inserts in the ClickHouse replication guide.
Confirm that every writer has a documented retry path and that forced timeouts in a test produce no duplicate rows.
Some high-volume writes, such as sampled device counters or non-critical click events, can tolerate losing the last few seconds of data after a server crash. For these, setting wait_for_async_insert to 0 lets the server acknowledge the client as soon as the data is buffered in memory. Client latency and throughput improve, but a crash before the flush discards whatever sits in the buffer, and the client has already received success for it.
- Early acknowledgement: the client gets success once data is buffered, so it cannot recover those rows from its own side.
- Crash exposure: rows still in memory when the server stops are lost. The size of the acceptable window is a business decision, so involve the data owner.
- Flush thresholds set the window: settings such as
async_insert_busy_timeout_msandasync_insert_max_data_sizecontrol how long data waits. Confirm their names and behavior for your version before tuning them. - Recorded ownership of loss: write down which sources accept loss, the maximum window, and who signed off.
- Replay option: if the source can resend data, document the replay path. If it cannot, state that the loss is accepted.
Run a crash test on a copy of production-like traffic and obtain written acceptance from the data owner before changing the setting.
When agents publish to a durable queue such as Apache Kafka before ClickHouse reads it, the queue already holds the durable copy. The ingestion path then needs to be replayable, not necessarily acknowledged at the database layer. The key question moves to the pipeline: does the consumer commit its offsets only after ClickHouse confirms the insert? A wait_for_async_insert value of 0 weakens that confirmation, so the offset commit and the persistence guarantee can drift apart.
- Queue as the durable copy: queue retention lets you replay batches after a ClickHouse failure, provided retention covers the outage and recovery time.
- Offset commits: commit offsets only after the insert is confirmed. If you use
0for throughput, accept that offsets may advance before the data is durable. - Batch shape: consumer batch sizes and the async flush thresholds interact, so test them together rather than separately.
- Replay and duplicates: replays create duplicate rows unless deduplication or a table design that collapses duplicates is in place.
- Retention versus outage length: confirm that queue retention exceeds your longest expected ClickHouse outage plus recovery time.
Run a forced ClickHouse restart during load and verify that offsets, row counts and replayed batches match the design.
Setting up async inserts on a self-managed cluster
This sequence moves async inserts from a planned workload to a verified configuration, so the ingest user's small writes are buffered, flushed and confirmed in the cluster's own system tables before they reach production traffic.
Confirm the insert pattern and row sizes
The team that owns the agents and the platform team measure how many clients send writes, how often, how many rows each insert carries and the approximate size of each row. They record the target tables and the latency the senders can tolerate. The step is done when the team has a written profile of the insert rate, batch size and acceptable delay, which sets the flush thresholds in a later step. If the agents cannot report these figures, the team should sample them from the sending side before any setting is changed.
Create a test table with the production ordering key
An engineer creates a table on a non-production cluster with the same engine, ORDER BY key, partitioning and column types as the production table. Using the production ordering key matters because part layout and merge behavior depend on it, so a test table with a different key will not show realistic results. The step is done when the table exists, the schema matches production exactly and the team has confirmed the ordering key with the data owners. Use the production ordering key guidance in the related topic before finalizing it.
Enable async_insert in a settings profile
A cluster administrator creates a settings profile that sets async_insert for the ingest user only, and assigns that profile to the user. Keeping the setting out of the default profile limits its effect to agent writes and keeps analyst sessions unchanged. The team also decides, from the sender requirements in the first step, whether senders wait for the flush to complete and what that means for lost-data risk on failure. The step is done when a session logged in as the ingest user shows the setting value in system.settings, and an analyst session shows the default.
Load representative agent traffic
The team replays or generates traffic that matches the profile from the first step, using the ingest user and the test table. It should cover normal load, bursts and a period where many agents send at once, since flush behavior changes with concurrency. The step is done when the replay runs to completion without unexpected client errors, and the team records the number of inserts sent and rows delivered so they can compare against the table counts in the next step.
Verify flushes in system tables
An operator checks the asynchronous insert queue and the query log on the test cluster to see how the buffered inserts were flushed. The asynchronous insert queue and the query log record flushed batches, and their counts and row totals should match what the replay sent. The operator also compares row counts in the test table against the replay totals and checks for duplicates if the senders retry. The step is done when the delivered row count matches the sent count, every batch flushed and no error entries remain in the queue.
Adjust thresholds and promote to production
If the flush counts show many small batches or long waits, the team adjusts the flush thresholds in the settings profile and repeats the replay. Once results are stable, the change is applied to the production ingest user through the same change process used for other configuration. Before promotion, the team confirms the exact setting names and behavior against the ClickHouse documentation for the installed version, since defaults and related settings can change between releases. The step is done when the production profile is applied and the first production flush checks match the test results.
Monitoring buffer flushes and insert health
Async inserts move batching from the agents to the server, so the server's buffer and the parts it creates need watching. The five signals below cover the buffer, the part count, insert outcomes, replica state and exported metrics, and each one points to the next thing to check.
Async insert buffer flushes
Check the pending buffer in system.asynchronous_inserts to see which tables hold rows waiting for a flush and how many are queued. Then read system.asynchronous_insert_log to see when flushes ran, how many rows each carried and whether any failed. Compare flush timing with your agents' send interval. If the buffer stays populated for long stretches, the flush thresholds are too loose for your write rate, or flushes are blocked by errors or merge pressure.
Part counts per partition
Query system.parts for active parts grouped by table and partition. Each flush writes a block that becomes a new part, so a steady stream of small flushes raises the count until background merges catch up. Watch the trend across several readings instead of relying on one. A count that climbs through the day points to flushes that are too small or merges that cannot keep pace. Check system.merges alongside it to see whether merges are running and how long they take.
Insert latency and errors
Track insert duration and failures in system.query_log by filtering on INSERT statements, and check system.errors for recurring exception codes. With asynchronous inserts, the acknowledgment time a client sees may differ from the time rows become visible, so measure both. Alert on rising error counts from the agents, and confirm how your agents retry a rejected batch, because retry behavior decides whether a failed flush loses data or duplicates it.
Replica lag on replicated tables
On ReplicatedMergeTree tables, system.replicas shows each replica's absolute delay, queue size and read-only status. An insert accepted by one replica still has to reach the others, so a lagging replica serves stale rows to dashboards that query it. Check Keeper session health at the same time, because lost coordination stalls replication. A recovered replica syncs missing data from a healthy one on its own, so your job is to confirm the lag clears.
Prometheus metrics for ingestion
ClickHouse exposes metrics that a Prometheus server can scrape, including counters tied to inserts, asynchronous insert activity and merges. Confirm the metric names and endpoint in the version you run, since names can change between releases. Build alerts on rates and on the gap between rows received and rows flushed, because raw totals only grow and rarely signal trouble. Link each alert to the system table query that explains it.
Deduplicating retried async inserts safely
Retries from agents are the usual source of duplicate rows in an async insert pipeline. When an agent does not receive an acknowledgment, because of a network timeout, a restarted process or a load balancer reset, it resends the same batch. The server may already have accepted the first copy into its buffer, so the second copy lands as additional rows. The async_insert_deduplicate setting asks ClickHouse to drop identical inserted blocks, but it only works within the conditions ClickHouse documents for that setting, and its behavior on replicated tables differs from single-node tables. Confirm those conditions in the current ClickHouse documentation for your version before you rely on it, and do not assume a retried batch is ignored just because the setting is enabled.
Treat deduplication as a layered decision rather than a single switch. For a pipeline where a few duplicate rows are tolerable, a ReplacingMergeTree table with a version column and a deterministic sort key may be enough, provided queries use FINAL or the team accepts eventual merges. For pipelines that must count each event once, give every event a stable identifier generated at the source, such as an agent-side UUID, and make that identifier part of the ordering or uniqueness check your queries rely on. An idempotent key is only useful if the agent generates it once per event and reuses it on every retry, so review the agent's retry code as part of the design.
Test retry behavior on a copy of the production table before enabling it. Point a test agent at a staging cluster, force timeouts and resends, then compare row counts per event identifier against the source. Also test a replica restart during a buffer flush, because that is where replicated deduplication limits show up. The result should decide which combination of the setting, engine and key you ship, and it should be written down for the operations team, who will need to recheck it after each ClickHouse upgrade.
Security and operational risks of async writes
Asynchronous inserts trade per-request durability and predictable memory use for throughput. The cards below cover the risks that follow from that trade: what can be lost, who can write, how buffers consume resources, and how the ingest path is locked down.
Data loss window on crash
With wait_for_async_insert set to 0, the server acknowledges a write before the buffered data is flushed to a part. If the server process crashes or the host fails before the flush, rows the client believed were stored can be gone. Agree with the data owners which event streams can tolerate that window, and test a forced restart under representative load before go-live. Set the acknowledgement mode per ingest path instead of globally.
Broad permissions on ingest users
Agents often connect with a shared account that also has read, DDL or administrative rights. Any leaked credential then becomes a path to altering or dropping tables. Create a dedicated ingest user that can only insert into the target tables and is restricted to the settings it needs. Confirm that the built-in default user, which starts with all permissions, is restricted or disabled on every node.
Ingest profile and settings control
Async insert behavior is set through query settings, and clients can override them unless a settings profile constrains them. A client that sets a very large buffer, an unbounded wait or a disabled deduplication setting changes the server's behavior for everyone. Bind the async insert settings to a dedicated settings profile for the ingest role and review which values clients are allowed to change.
Memory pressure from large buffers
Buffered rows sit in server memory until a flush threshold is reached. Many agents with large batches or high flush thresholds can push memory use up on the same node that serves dashboards. Set limits on the ingest path and watch memory use during peak agent counts. Ask your team which thresholds were used in testing and whether the ingest path shares memory with query workloads.
Quota exhaustion from insert bursts
Quotas limit queries, rows and resource use per user or role over time intervals. Insert bursts from many agents can exhaust a quota shared with analysts, causing unrelated failures. Give the ingest role its own quota sized from measured peak rates, and alert when rejections start. Confirm the quota behavior on your server version because limits and counters are configured in the access control model.
Identity for ingest clients
Agents authenticate over the network, so the method matters as much as the permissions. Password-based accounts in configuration files are hard to rotate across thousands of hosts. ClickHouse supports external authenticators such as LDAP, Kerberos, X.509 certificates and JWT. Decide which method suits each agent fleet and confirm with your identity team how credentials are issued, rotated and revoked.
Questions teams ask before enabling async inserts
What drives the cost of implementing async inserts?
The cost depends mostly on how many writers you have, how many tables they write to, and how much of your existing ingestion path has to change. Async inserts are a server-side setting and a change to how clients send data, so the main effort is in the client path, load testing and monitoring rather than in the setting itself. Cost components are the planning and design work, configuration and testing on a non-production cluster, and the monitoring you build afterwards. Ask any quote to separate one-time work (design, testing, runbooks) from recurring work (version upgrades, tuning as volume grows, on-call handling of flush errors). Ask for the scope in writing before work starts, since DeployOpen engagements are fixed-scope and agreed in writing.
How long does it take to enable async inserts?
Duration depends on how many ingestion clients exist, whether they already batch their writes, and whether you have a pre-production cluster with representative data. The configuration change itself is small, but validating durability behavior, flush thresholds and deduplication under realistic load usually takes the most time. ClickHouse's production guidance recommends a pre-production environment with representative data and queries before changing production behavior, so plan for that stage. Ask for a timeline tied to specific deliverables in the written scope, and confirm which milestones depend on your team's access to the test environment.
What does our internal team need to provide?
Your team needs to supply the write patterns, the agents or services that send data, and the tables they target. Someone must own the client libraries or agent configuration, because the insert format and settings are set there. You also need access to a non-production cluster, metrics storage for monitoring flushes, and a person who can approve changes to production ingestion. Confirm these inputs early, since missing access to client code is one of the most common delays in ingestion work.
Can we configure async inserts in-house instead?
Yes, a team with ClickHouse operations experience can configure async_insert alone, and the setting is documented in the ClickHouse docs. The harder part is judging whether your traffic suits buffering, choosing thresholds, and deciding how much durability you accept when wait_for_async_insert is disabled. In-house teams often need to build the monitoring for flush behavior and rejected inserts themselves. A partner is most useful when the team lacks capacity for the test plan or when several ingestion paths must be redesigned together. Compare both options against the same written scope.
How do async inserts change our retention and backup plan?
Async inserts do not change retention rules, but they change what must be protected. Rows buffered in memory are not yet in a part, so a crash before flush can lose data unless wait_for_async_insert is set to wait for the flush. Backups should be planned with the ClickHouse BACKUP and RESTORE workflow, which supports full and incremental backups to local disk, S3-compatible storage or Azure Blob Storage. The backup documentation notes that replication alone does not protect against human error or corruption, and that a backup never test-restored will probably not restore properly. Schedule restore tests on a spare cluster as part of the rollout.
Does async insert change how our replicas behave during failures?
Replication still applies to the parts that are flushed, because ReplicatedMergeTree tables coordinate through ClickHouse Keeper. Per the replication guide, a recovered replica automatically syncs missing data from a healthy replica without manual intervention. Buffered data that has not flushed is held on the node that received it, so the failure behavior of that node matters more with async inserts. Confirm your Keeper layout, which the replication guide recommends as at least three nodes on dedicated hosts in production, before you rely on buffering at scale.
Who supports us after the async insert rollout?
Support after go-live can be handled by your own team, by a DeployOpen retainer for ongoing support, upgrades and continuous testing, or by a mix of both. The ongoing work includes watching flush metrics, reviewing ingestion errors, applying security updates and planning upgrades. The ClickHouse security policy lists which versions currently receive security updates, so confirm your version is on that list before committing to a support model. Ask for the support scope, response expectations and handover documentation in the written agreement.
Official ClickHouse documentation to read next
Before you set any async_insert value in production, read the ClickHouse documentation for your exact release. The settings that control buffering, when a client receives an acknowledgement, and how the server handles retries are defined per version, and the defaults and names can change between releases. Start with the ClickHouse production version guidance, which recommends stable releases for most production use, a pre-production environment with representative data and queries, and a changelog review before every upgrade because minor backward-incompatible changes can occur. Then confirm which release line your cluster runs against the supported versions in the ClickHouse security policy, since an unsupported build will not receive fixes to the insert path.
Once the insert settings are chosen, the replication documentation is the next read. Buffered rows that have not yet been flushed exist only on the node that received them, so the replication behavior of your ReplicatedMergeTree tables determines what a node loss costs you. The ClickHouse replication guide describes how a recovered replica syncs missing data from a healthy peer and what ClickHouse Keeper must provide for that to work. Test a node failure during a simulated agent load and record which acknowledged writes survive.
Replication does not protect against bad writes, duplicate batches or a faulty deduplication setting, so pair this reading with the ClickHouse backup and restore documentation. It notes that replication alone does not guard against human error or corruption, and it warns that a backup never test-restored will probably not restore when needed. Schedule a restore test on a spare cluster that includes the tables your async insert pipeline writes to, and confirm the exact BACKUP and RESTORE syntax for your release before you rely on it.
How DeployOpen scopes and delivers ClickHouse async insert work
DeployOpen plans and delivers async insert configuration for self-managed ClickHouse inside your own infrastructure. We start from your agents, batch sizes and durability requirements, then build, test and document the ingestion path so your team can operate it.
How we scope
- We map every agent, producer and batch pattern that will write to ClickHouse, including peak send rates.
- The written scope states which tables use async_insert, the flush thresholds, and the wait_for_async_insert setting for each write path.
- We agree in writing how deduplication, retry behavior and buffer monitoring will be handled before any build starts.
How we build
- We configure async insert settings on a pre-production cluster loaded with representative event volumes and sample traffic.
- We test flush behavior under bursty and steady load, and record how many rows wait in the buffer at each threshold.
- We document durability trade-offs for each acknowledgement mode so data owners can confirm the loss tolerance they accept.
How we hand over
- You receive the final profile settings, client configuration, and a runbook covering flush monitoring and failure response.
- Your engineers walk through the monitoring queries and alert thresholds we set up for buffer flushes and insert errors.
- The handover includes the test results, the changelog check for your chosen ClickHouse release, and open items to confirm.
How we support
- Engagements are fixed-scope, with deliverables and timelines agreed in writing before work starts.
- Ongoing support, upgrades and continuous testing for your ClickHouse ingestion path are available as a retainer.
- Before any version upgrade, we review the release notes against your async insert settings and retest on a copy.
