MergeTree Insert Buffer Tuning for Bursty Kafka Workloads

MergeTree writes a new immutable part for every INSERT, and Kafka's poll loop hands it bursts. That mismatch, not any single misconfigured setting, is why bursty Kafka pipelines into MergeTree tend to degrade quietly and then fail loudly.

MergeTree's part-creation model versus Kafka's poll loop

Trace the path a message actually takes. A Kafka table engine polls on a fixed schedule, pulls whatever landed in that window, and writes it as a new on-disk part. The schedule doesn't know or care how many messages arrived. During a quiet period, a poll might catch a handful of rows and write a small part. The background merge pool exists to consolidate these small parts into larger ones over time, but it works on a budget: a fixed number of merge threads, a fixed amount of I/O capacity. Feed it a flood of tiny parts faster than it can combine them, and merge debt compounds.

This isn't the same problem as high steady-state volume. Burst traffic breaks that assumption even at a moderate average throughput, because the data arrives in uneven clumps. Ten thousand rows spread evenly across a minute might produce one well-sized part. The same ten thousand rows arriving in three seconds, caught across a dozen polls, can produce a dozen small parts instead.

What makes this dangerous is that none of it throws an error. Inserts return OK. Nothing in the standard response path tells an operator that any of this is happening. The first visible symptom is often a replica going read-only once the storage engine's part-count safety limits trip, by which point the backlog has been building for a while.

How the async-insert buffer layer interrupts the poll-to-part pipeline

Async inserts break the direct line from poll to part. Instead of every write becoming its own part immediately, the server accumulates incoming rows in a buffer and flushes that buffer into a single part once a threshold is crossed. A burst of twenty small polls, instead of producing twenty small parts, can collapse into one or two properly sized ones.

Without async inserts enabled, the Kafka engine has its own batching logic: kafka_max_block_size and kafka_flush_interval_ms control how much a single consumer thread accumulates before writing. The async-insert buffer works differently: it sits across all arriving writes to the table, not per consumer, and it respects both a size threshold and a time threshold before committing anything to disk.

Turning on async inserts doesn't make part creation disappear. It changes the cadence and the minimum size of what gets created, which is the lever that keeps merge pressure tied to actual data volume. Four settings govern how that buffer behaves once it's live, and getting the values and the order right is most of what separates a stable pipeline from one that's merely delaying the same failure.

The four async-insert settings that govern buffer behavior under burst conditions

async_insert_max_data_size

This is the throughput knob. Its default is 10 MiB, and once the buffer reaches that size, it flushes. The setting hides a trap for wide-row workloads: a single row weighing 5 MiB, paired with the 10 MiB default, means the buffer flushes after one or two rows arrive. That reproduces the exact small-batch problem async inserts were meant to fix. For wide rows or large JSON payloads, raising this to somewhere in the 50-100 MiB range gives the buffer room to accumulate a batch that's actually worth writing as one part.

async_insert_busy_timeout_ms

This is the latency knob. On self-hosted deployments it defaults to 200 ms, and it triggers a flush once that time elapses, regardless of whether the size threshold has been met. That range lets the system handle both quiet, low-volume stretches and sudden spikes without needing a human to re-tune it mid-incident. Adaptive mode fits bursty Kafka workloads specifically because the shape of the burst itself changes: a fixed timeout either wastes merge capacity sitting idle during quiet periods, or fails to accumulate a proper batch when a spike hits.

async_insert_max_query_number

This setting caps how many individual async insert queries can accumulate into the buffer before it's forced to flush, working alongside the size and time thresholds as a third ceiling on batch formation.

wait_for_async_insert

This is the durability knob, and it defaults to 1. At that setting, the INSERT call blocks until the buffer has actually flushed and the part is written to disk. The client gets a guarantee that its data is durable before it moves on. Set to 0, the INSERT returns as soon as the server accepts the rows into the buffer, without waiting for the flush. With wait_for_async_insert=0, any rows sitting in the buffer are silently lost if the server crashes before that buffer flushes. For nearly every production ingestion workload, the added latency of keeping this at 1 is a reasonable price, and the durability guarantee it buys matches what most teams actually need from their pipeline.

Aligning Kafka engine geometry with the async buffer's expectations

Turning on async inserts and stopping there is a half-measure. The buffer itself will accumulate correctly, but if the Kafka consumer feeding it is misconfigured, it feeds the buffer unevenly, and the same small-part pattern reappears, just at a slower pace than before.

Consumer count and block size both shape how evenly data arrives at the buffer. If too many consumer threads are reading from too few partitions, or if kafka_max_block_size is set too small relative to burst volume, the buffer receives data in uneven dribbles instead of steady streams, undercutting the batching work the async layer is supposed to do.

Poll timeout plays a role too. kafka_poll_timeout_ms, set to a short-to-moderate millisecond value, reduces end-to-end latency, at the cost of more empty polls during quiet stretches. During an actual burst, this setting governs how quickly the consumer hands messages off to the buffer in the first place, so it needs to be short enough to keep up without being so short that it wastes cycles polling nothing.

kafka_thread_per_consumer = 1 gives each consumer its own dedicated flush thread, so a single slow block doesn't stall the rest of the consumer pool behind it. That matters most during burst conditions, when one partition can race ahead of the others and a shared thread model would let its backlog drag everyone down.

Consumer group rebalances can produce duplicate rows. When a rebalance happens before offsets for a partition have been committed, the partition gets revoked and reassigned, and the new owner replays the same block of messages, landing them in the table a second time. A table can show count() exceeding uniqExact(event_id) on the affected partition as a direct result. This is distinct from the normal at-least-once delivery duplicates that Kafka, SQS, and application-level retry loops all produce, and it's specific to rebalance timing. For any pipeline feeding a ReplicatedMergeTree table from an at-least-once source, enabling deduplication at the part level is the way to handle both cases without the table silently double-counting events.

The Kafka engine settings and the async buffer settings are one pipeline, and misalignment on the Kafka side will undercut correct tuning on the buffer side every time.

Sequencing the tuning changes so the system stabilizes rather than shifts the bottleneck

Turning on all four async-insert settings at once, without first checking whether the background merge pool has any headroom, can move the bottleneck elsewhere. Larger, less frequent flushes do produce fewer parts, but those parts are bigger, and if the merge pool is already saturated, bigger parts just sit in the queue longer before getting merged.

A more reliable order starts by establishing a baseline on the merge pool: how much headroom does it actually have right now, before anything changes. Each step should get validated against observability signals before the next setting changes, so a regression gets caught at the step that caused it.

Schema partition granularity belongs inside this sequence, not off to the side as a separate concern. Fewer active partitions means the merge pool is working across a smaller surface at any given time, which directly expands its effective capacity to absorb whatever the async buffer flushes its way. That change belongs before raising async_insert_max_data_size, because wider partition windows reduce the number of concurrent merge targets the pool has to juggle, and tuning buffer size against a narrower partition scheme means re-tuning it again later.

One might argue partition granularity shouldn't be a first move at all, since changing it on a live table requires an ALTER that touches existing data and can be impractical to run against production. That objection holds for a live table under load. But on a new table, or during a planned migration window, wider partitioning is still the right first move to make. On a table that can't be touched right now, merge pool headroom can be partially recovered another way: temporarily reducing kafka_num_consumers to slow the rate of incoming parts while the rest of the sequence gets worked through.

The five observability signals that tell you whether the system has stabilized or is silently degrading

Because part explosion hides behind normal-looking inserts and normal-looking query responses, confirming that a tuning pass actually worked requires watching five specific signals, each covering a different way the system can degrade without raising an alarm.

Merge backlog comes first. MaxPartCountForPartition trending upward while the background merge pool is already running at full capacity is the clearest sign that inserts are outpacing merge throughput, whether that's from async-insert settings that haven't caught up yet or from some other source of pressure.

Materialized view chain drift is the second. A row-count gap between a source table and the tables fed by its materialized views means the view chain has dropped rows somewhere along the way.

TTL falling silent is the third, and it's easy to miss because nothing about it looks like an error. TTL deletes only run inside merges, and TTL-driven merges are rate-limited, with merge_with_ttl_timeout defaulting to four hours per table, and deprioritized whenever the merge pool is busy with other work. Table size growing while insert bytes stay flat is the tell that retention has quietly stopped doing its job.

Replica divergence is the fourth. absolute_delay in system.replicas measures how far behind a replica has fallen in applying replicated data. If absolute_delay is climbing after an async-insert tuning pass, that's a signal to reduce flush frequency, not increase it.

Shard skew rounds out the five. Neither async-insert tuning nor merge pool tuning can fully compensate for that kind of imbalance. Fixing consumer assignment is the only thing that actually addresses it.

Agentic query bursts as a second contention source at the read layer

Write-side tuning solves half the problem. AI agents querying a real-time analytics table introduce a second source of burst contention, this time on the read side, and it can land at the worst possible moment. A single agent task can issue several parallel SQL probes in the course of answering one question, and those probes hit the merge pool's shared resources right as a Kafka spike is loading the same pool from the write side.

A human analyst tends to run one query, look at the result, then decide what to run next. Structurally, that read pattern looks a lot like the Kafka poll burst already discussed on the write side, hitting a different part of the system.

This interaction compounds. Neither write-side tuning nor read-side tuning on its own predicts that combined failure.

The fix is architectural isolation. Handled this way, agentic access adds no new contention at the read layer, and it does so without placing any artificial limit on what the agent is able to ask.

Recovery when the system has already degraded: restoring merge health without data loss

Arriving after the damage is done calls for a different sequence than the one used to prevent it. If MaxPartCountForPartition has already climbed and the merge pool is saturated, reducing ingest pressure has to come first, before any buffer re-tuning. Adding async-insert headroom to a pool that's already saturated only grows the backlog faster, it doesn't shrink it.

Replica divergence has its own recovery path. SYSTEM RESTART REPLICA is the first thing to try, and it attempts to restart replication cleanly. If that doesn't resolve it, SYSTEM RESTORE REPLICA rebuilds lost ZooKeeper metadata using the local data already present on the affected replica. It does not pull a fresh copy from another replica, and it does not permanently delete local data on its own, but it is still a destructive operation against that replica's metadata, so confirming that at least one healthy replica holds a complete copy of the data should come before running it.

TTL recovery runs into the same constraint that caused the problem: deletes only happen inside merges, and those merges get deprioritized whenever the pool is busy. Neither option clears the backlog instantly, and disk pressure keeps building for as long as the backlog takes to work through.

MergeTree doesn't offer point-in-time recovery the way an OLTP database does. Recovery happens at the level of a replica or a part, not at the level of a transaction. Any team running a burst ingestion pipeline needs backup and restore procedures built around that constraint well before a degradation event happens, not improvised once one is already underway. The tuning and the observability work covered earlier in this piece are the hedge against a recovery path that offers no clean rollback once the backlog has already formed.