Analytics PipelineClickHouse & Materialized Views
Guide Real-Time Data Ingestion Pipeline Implementation

Tuning Buffer Table Flush Thresholds

A Buffer table flushes when its per-layer counters cross the min/max thresholds baked into the engine signature, and those numbers — not your insert rate — decide whether the destination MergeTree receives merge-friendly blocks or a hail of undersized parts. This guide derives the flush-size math that lands each drain in the merge-optimal window, then wires it to live counters in system.asynchronous_metrics so the thresholds are calibrated against real accumulation rather than guessed.

The nine numbers after the database and table name in Buffer(...) are the entire tuning surface, and every one of them is evaluated per layer, not across the whole table. Getting them right is a size problem before it is a latency problem: pick the row and byte floors so a single layer’s flush produces a block ClickHouse will keep as one part, and the small-part churn that triggers TOO_MANY_PARTS disappears without touching the writers at all.

Prerequisites

The flush-size target

Every threshold decision reduces to one question: how many rows should a single layer hold before it drains? Aim for a flushed block that background merges will accept as a finished part rather than immediately re-merging. In practice that is 100k–1M rows or 10–50 MiB compressed per flush — the same window that batch insert optimization targets for direct inserts. Below that, parts arrive faster than merges can coalesce them; far above it, a single layer pins too much RAM and the max ceilings start blocking inserts.

The merge-optimal flush window between small-part churn and memory pressure A per-layer flush-size number line: below ~100k rows / 10 MiB is small-part churn ending in TOO_MANY_PARTS; the 100k–1M row / 10–50 MiB middle band is the merge-optimal window where each flush is one clean part; above ~1M rows / 50 MiB layers pin RAM and max_bytes blocks inserts. min_rows/min_bytes set the left edge, max_rows/max_bytes the right. Small-part churn parts > merges TOO_MANY_PARTS Merge-optimal window one flush → one clean part 100k–1M rows · 10–50 MiB Memory pressure layer pins RAM max_bytes blocks inserts min_rows · min_bytes max_rows · max_bytes per-layer flush size → larger

Step 1 — Baseline the destination’s current part sizes

Before changing anything, measure what the buffer is actually producing today. A healthy destination shows a handful of active parts per partition with row counts in the hundreds of thousands; a churning one shows dozens of tiny parts.

sql
SELECT
    partition,
    count()                              AS active_parts,
    formatReadableSize(avg(bytes_on_disk)) AS avg_part,
    round(avg(rows))                     AS avg_rows,
    max(rows)                            AS max_rows
FROM system.parts
WHERE database = 'analytics' AND table = 'events_raw' AND active
GROUP BY partition
ORDER BY partition DESC
LIMIT 10;

Expected on a well-tuned buffer: avg_rows in the 100k–1M range and single-digit active_parts per recent partition. If avg_rows sits in the low thousands, the buffer is flushing undersized blocks and the row/byte floors are too low.

Step 2 — Measure the live accumulation rate

The thresholds only make sense relative to how fast one layer fills. Derive per-layer rates from the destination’s write history and the current num_layers.

sql
-- Rows/s and bytes/s written to the destination over the last hour.
SELECT
    sum(written_rows)                       AS rows_1h,
    formatReadableSize(sum(written_bytes))  AS bytes_1h,
    round(sum(written_rows) / 3600)         AS rows_per_sec,
    round(sum(written_bytes) / 3600)        AS bytes_per_sec
FROM system.query_log
WHERE type = 'QueryFinish' AND query_kind = 'Insert'
  AND has(tables, 'analytics.events_buffer')
  AND event_time > now() - INTERVAL 1 HOUR;

Divide rows_per_sec by num_layers to get the per-layer fill rate. If the whole table takes in 160k rows/s across 16 layers, each layer accumulates ~10k rows/s, so a min_rows of 100k means a layer reaches its floor in about ten seconds — a sane flush cadence.

Step 3 — Set the row and byte floors from the fill rate

The min thresholds are the primary size lever. Set min_rows and min_bytes so a layer that has been filling for min_time seconds lands inside the merge-optimal window, and set the max thresholds as a safety ceiling at roughly 5–10× the floor.

sql
-- Recreate the buffer with size-driven thresholds. AS copies the destination schema.
DROP TABLE IF EXISTS analytics.events_buffer;

CREATE TABLE analytics.events_buffer AS analytics.events_raw
ENGINE = Buffer(
    'analytics', 'events_raw',
    16,            -- num_layers
    10,            -- min_time  (s)
    45,            -- max_time  (s): staleness backstop
    100000,        -- min_rows : ~1 merge-optimal block per layer
    1000000,       -- max_rows : force flush at 10x the floor
    10485760,      -- min_bytes: 10 MiB floor keeps blocks merge-friendly
    268435456      -- max_bytes: 256 MiB hard ceiling per this table
);

Because a Buffer engine has no ALTER, changing thresholds always means DROP + CREATE. Recreate during a lull, or OPTIMIZE first (Step 5) so no resident rows are discarded. The min_bytes floor of 10 MiB is the single most important number here: keep it at or above 1 MiB and a premature min_time flush still emits a reasonable block.

Step 4 — Reason about thresholds being per-layer

A frequent miscalculation is treating max_rows as a table-wide cap. It is not. Each of the num_layers layers carries its own counters, so peak resident rows across the whole table approach num_layers × max_rows, and peak memory approaches num_layers × max_bytes. With num_layers = 16 and max_bytes = 256 MiB, the buffer can pin ~4 GiB before every layer blocks.

sql
-- Sanity-check the worst-case memory the current signature permits.
SELECT
    extract(engine_full, 'Buffer.*') AS buffer_signature
FROM system.tables
WHERE database = 'analytics' AND name = 'events_buffer';

Size max_bytes as (RAM budget for buffering) / num_layers, not as the whole budget. If you want a 4 GiB ceiling across 16 layers, each layer’s max_bytes is 256 MiB — the value used in Step 3.

Step 5 — Force a drain and confirm block size

OPTIMIZE TABLE on a buffer flushes every layer synchronously. Use it to drain before a recreate, and to prove a fresh signature produces correctly sized parts.

sql
OPTIMIZE TABLE analytics.events_buffer;

-- The parts written by that drain, newest first.
SELECT name, rows, formatReadableSize(bytes_on_disk) AS size, modification_time
FROM system.parts
WHERE database = 'analytics' AND table = 'events_raw' AND active
ORDER BY modification_time DESC
LIMIT 20;

Each freshly written part should carry rows in the 100k–1M band. If a drain produces many small parts instead, the layers were flushing on max_time before reaching their row floor — lengthen max_time or raise the fill rate per layer by reducing num_layers.

Verification

Watch the two counters that tell you whether the thresholds are holding under live load. system.asynchronous_metrics exposes resident buffer rows and bytes, sampled roughly once a second.

sql
SELECT metric, value
FROM system.asynchronous_metrics
WHERE metric LIKE '%Buffer%'
ORDER BY metric;

Then confirm the flush cadence against part arrivals — count how many parts the destination gained per minute and their average size.

sql
SELECT
    toStartOfMinute(modification_time)     AS minute,
    count()                                AS parts_added,
    formatReadableSize(avg(bytes_on_disk)) AS avg_part
FROM system.parts
WHERE database = 'analytics' AND table = 'events_raw'
  AND modification_time > now() - INTERVAL 15 MINUTE
GROUP BY minute
ORDER BY minute DESC;

A well-tuned buffer adds a small, steady number of parts per minute — roughly num_layers divided by the average seconds-to-fill — each in the merge-optimal band. A spike in parts_added with a low avg_part means a threshold is firing too early.

Gotchas & edge cases

  • max_time masquerading as the size lever. During low-throughput windows a layer hits max_time long before min_rows, so it flushes a tiny block. This is correct behavior — the buffer is bounding staleness — but if freshness does not matter, raise max_time so size-based flushes dominate. Treat max_time as a staleness backstop, never as the primary trigger.
  • Byte thresholds count uncompressed in-memory size. min_bytes/max_bytes measure the rows resident in RAM, not the compressed part they become on disk. A 10 MiB in-memory flush can land as a 1–3 MiB part after compression, so calibrate the byte floors against the raw row width, and confirm the on-disk size in system.parts rather than assuming the two match.
  • Recreating the buffer discards resident rows. DROP TABLE on a buffer throws away anything not yet flushed. Always OPTIMIZE TABLE analytics.events_buffer immediately before the drop, and pause writers, or you silently lose the in-memory window. This is the same RAM-only exposure covered in preventing data loss on buffer flush failure.
  • Small floors defeat background merges downstream. A min_bytes under 1 MiB emits parts that the merge scheduler must immediately re-merge, inflating merge CPU and write amplification. Alert on the destination’s part count with the thresholds described in alerting on part-count thresholds so churn from an over-eager floor is caught before it becomes TOO_MANY_PARTS.

Up: Async Processing & Buffer Tables