We’re getting killed by tiny frequent inserts and table fragmentation—how do teams design ingestion to avoid that?

Tiny, frequent inserts are one of the easiest ways to make a ClickHouse cluster feel “slow and brittle” long before you hit real scale. The good news is that the teams running billions-of-rows-per-day workloads all converge on the same ingestion patterns: batch aggressively, keep parts under control, and validate behavior via system tables instead of guesswork.

Quick Answer: To avoid table fragmentation and “too many parts” errors, design ingestion around batching thresholds (time and size), server‑side batching (async_insert, buffer tables, Kafka), and low-cardinality partitioning. Monitor system.parts and system.merges so you can spot small-part explosions early and adjust your ingest strategy before latency and storage costs spike.

Why This Matters

ClickHouse gets its millisecond query performance on petabyte-scale data by relying on a healthy MergeTree: a manageable number of parts per partition, merges that can keep up, and inserts that arrive in decent-sized batches. When you hammer it with tiny inserts—10+ per second of a few rows each—you force ClickHouse to create and merge thousands of tiny parts. Latency climbs, background merges fall behind, and you eventually hit “Too many parts” errors that take dashboards down.

If you’re building real-time analytics, logs, metrics, or AI/ML feature stores, ingest volume and concurrency only go up. Designing ingestion properly on day one is the difference between a cluster that quietly handles billions of rows per day and one that spends its life on fire.

Key Benefits:

• Predictable performance: Batching inserts and controlling parts keeps merge load stable, so queries stay in the millisecond range even as data volume grows.
• Lower cost at scale: Fewer, larger parts mean better compression and more efficient scans, which translates directly into lower CPU and storage usage.
• Operational sanity: Clear batching rules and ingestion settings reduce “mystery” incidents—no more debugging fragmentation and timeouts at 2 a.m. because a new service started spamming 1-row inserts.

Core Concepts & Key Points

Concept	Definition	Why it's important
Parts & “Too many parts”	Each insert into a MergeTree table creates a part; background merges compact parts into larger ones. “Too many parts” occurs when tiny frequent inserts create more parts than merges can handle.	Too many parts per partition degrade performance and can trigger errors; healthy ingestion keeps parts per partition low (preferably < 100).
Batching strategy	A combination of application-side and server-side logic that groups rows into larger inserts based on row count, time, or size (e.g., 10k rows or 200MB).	Proper batching is the primary lever to avoid table fragmentation and keep merges healthy, especially at high ingest rates.
Ingest architecture choices	Use of async_insert, buffer tables, Kafka, or dedicated ingest services to normalize how data flows into ClickHouse.	Robust ingestion pipelines absorb jitter, isolate noisy producers, and give you knobs to tune without touching the core application.

How It Works (Step-by-Step)

At a high level, teams that aren’t “getting killed” by tiny inserts do three things consistently: they batch, they validate, and they iterate. Here’s the practical flow.

1. Quantify the problem with system tables

Before redesigning ingestion, validate how bad things are.

Check parts and partitions:

SELECT
    database,
    table,
    partition,
    count() AS parts,
    formatReadableSize(sum(bytes_on_disk)) AS total_size,
    min(min_time) AS min_time,
    max(max_time) AS max_time
FROM system.parts
WHERE active AND database = 'your_db' AND table = 'your_table'
GROUP BY database, table, partition
ORDER BY parts DESC
LIMIT 20;

Watch for:

• Part counts per partition: community guidance is preferably < 100 parts per partition.
• Very small parts (KB–few MB) dominating the table.

Inspect merges:

SELECT
    database,
    table,
    partition_id,
    source_part_names,
    result_part_name,
    bytes_read,
    bytes_written,
    elapsed AS seconds
FROM system.merges
WHERE database = 'your_db' AND table = 'your_table'
ORDER BY seconds DESC
LIMIT 20;

If merges are constantly running but part counts aren’t dropping, ingestion is outpacing merge capacity.

2. Set explicit batching targets

The official recommendation is clear:

• Minimum 1,000 rows per insert
• Ideally 10,000–100,000 rows per insert

Teams typically pick thresholds like:

• row-based: 10,000–50,000 rows per insert, or
• time-based: 5–30 seconds of data per insert, and/or
• size-based: target ~50–200MB per insert batch.

In practice:

• For logs/metrics: buffer 5–15 seconds of data or 10k–50k events, whichever comes first.
• For events/telemetry: group by tenant or topic to keep inserts homogeneous and compressible.

Crucially, inserts above ~10 per second of tiny batches are known to trigger “too many parts” because ClickHouse can’t merge parts fast enough. Aim for fewer, larger inserts instead of many micro-inserts.

3. Use server-side batching: async_insert and buffer patterns

If you can’t fully control client behavior, let ClickHouse help.

async_insert for automatic batching

async_insert groups small inserts into larger server-side batches:

SET async_insert = 1;
SET async_insert_busy_timeout_ms = 200;   -- wait up to 200ms to collect more rows
SET wait_for_async_insert = 0;            -- return immediately (faster, less durable)

How it helps:

• Multiple tiny inserts arriving within the timeout window are combined into a larger part.
• This dramatically reduces parts-per-second and eases merge load.
• Modern ClickHouse versions support deduplication within these batches, so you can safely retry inserts without ballooning data.

Tradeoff:

• If wait_for_async_insert = 0, the client may see success before data hits disk; if you need stricter durability guarantees, set it to 1 but expect slightly higher latency.

Buffer tables for backend-side batching

For some workloads, you can isolate noisy producers behind a buffer:

CREATE TABLE events_buffer
ENGINE = Buffer(
    your_db,
    events,        -- destination MergeTree table
    16,            -- num_layers
    10,            -- min_time
    60,            -- max_time
    100000,        -- min_rows
    1000000,       -- max_rows
    100000000      -- max_bytes
) AS
SELECT * FROM your_db.events
LIMIT 0;

The Buffer engine:

• Accepts tiny inserts cheaply.
• Flushes them into the target MergeTree table when time/rows/bytes thresholds are met.
• Centralizes batching behavior, so clients just insert into events_buffer.

Combine this with async_insert and you get two layers of protection against tiny inserts.

4. Introduce a proper ingest layer (Kafka, queues, or ETL)

Most high-volume teams don’t let every microservice speak SQL directly to the MergeTree tables. They:

• Collect events in a durable queue (Kafka, Redpanda, Pulsar, Kinesis).
• Use ClickHouse’s Kafka engine or external ETL (e.g., Flink, Debezium, custom consumer) to:
  • Control batch size,
  • Normalize schema, and
  • Commit in large chunks.

Example: Kafka engine + materialized view:

CREATE TABLE raw_events_kafka (
    ts DateTime,
    user_id UInt64,
    event_type String,
    payload String
) ENGINE = Kafka
SETTINGS
    kafka_broker_list = 'broker:9092',
    kafka_topic_list = 'events',
    kafka_group_name = 'clickhouse_consumer',
    kafka_format = 'JSONEachRow',
    kafka_num_consumers = 4;

CREATE TABLE events
(
    ts DateTime,
    user_id UInt64,
    event_type LowCardinality(String),
    payload String
)
ENGINE = MergeTree
PARTITION BY toDate(ts)
ORDER BY (ts, user_id);

CREATE MATERIALIZED VIEW mv_events TO events AS
SELECT * FROM raw_events_kafka;

Tune batch size via Kafka and engine settings so that each MV flush produces healthy batches (10k+ rows per insert).

5. Design partitioning for lifecycle, not “speed hacks”

Partitioning is mostly for data management: retention, maintenance, and manageability—not a magic query accelerator.

When you’re already fighting fragmentation, over-partitioning makes it worse:

• Too many partitions × too many parts per partition = explosion in metadata and merges.
• High-cardinality partitions (e.g., per-user, per-session) are especially dangerous.

General guardrails:

• Keep partition key low-cardinality: typically < 100–1,000 distinct values (e.g., by day, tenant, or region).
• Common patterns:
  • toDate(ts) for time-series (daily partitions),
  • toYYYYMM(ts) for lighter ingest loads and longer retention windows,
  • (toDate(ts), tenant) when you need lifecycle per tenant but can keep tenant count reasonable.

Goal:

• Combine reasonable partitioning with healthy batching so each partition has tens to low hundreds of parts instead of thousands.

6. Make ingestion observable and enforce guardrails

You’ll eventually add new services or change traffic patterns. Don’t let them quietly regress your ingest design.

Monitor with system tables:

• Parts per partition:

  SELECT
      database,
      table,
      partition,
      count() AS parts,
      avg(rows) AS avg_rows_per_part,
      formatReadableSize(avg(bytes_on_disk)) AS avg_size_per_part
  FROM system.parts
  WHERE active AND database = 'your_db' AND table = 'your_table'
  GROUP BY database, table, partition
  HAVING parts > 100
  ORDER BY parts DESC;
  

• Insert patterns from system.query_log:

  SELECT
      regexpExtract(query, 'INSERT INTO ([^ ]+)', 1) AS table_name,
      sum(rows_read) AS total_rows,
      count() AS insert_count,
      round(avg(rows_read), 2) AS avg_rows_per_insert
  FROM system.query_log
  WHERE
      type = 'QueryFinish'
      AND event_time >= now() - INTERVAL 15 MINUTE
      AND query LIKE 'INSERT %'
  GROUP BY table_name
  ORDER BY insert_count DESC;
  

Trigger alerts when:

• avg_rows_per_insert drops below your target (e.g., < 1,000 rows).
• Parts-per-partition exceeds your threshold (e.g., > 100).
• Insert rate rises above ~10–20 per second for tiny inserts.

In ClickHouse Cloud, you can also look at cluster behavior (e.g., multi-node merges) via ON CLUSTER queries to correlate ingest changes with merge load.

Common Mistakes to Avoid

• Letting every service do ad‑hoc inserts:
  When each microservice constructs its own INSERT pattern, you end up with mixed row sizes, unbounded insert frequencies, and no clear place to tune batching.
  How to avoid it: Route writes through a shared ingest client, gateway, or queue that enforces row/time/size thresholds and uses async_insert appropriately.

• Over-partitioning to “speed up” queries:
  Partitioning by high-cardinality keys (user, session, random hash) leads straight to fragmentation and expensive cross-partition scans.
  How to avoid it: Use partitioning for lifecycle control (time, tenant, region) with low cardinality, and rely on ORDER BY plus columnar scans for performance. Keep an eye on system.parts to validate.

Real-World Example

I’ve seen this play out in a logs & metrics migration from Elasticsearch to ClickHouse. The team started with each service sending one log line per INSERT, peaking around 2–3k inserts per second into a few core tables. Within days, we saw:

• system.parts showing thousands of parts per daily partition,
• system.merges permanently backlogged,
• and regular “Too many parts” errors when traffic spiked.

We introduced a thin ingest proxy that:

1. Buffered logs per service and per table.
2. Flushed every 10 seconds or 50,000 rows, whichever came first.
3. Used INSERT into a Buffer table with async_insert enabled.

We also normalized partitioning to toDate(ts) and dropped a per-service partition key that had exploded partition count.

Within a day:

• Parts per partition fell from thousands to low double digits.
• Merge backlog cleared.
• Query latency dropped from seconds back to the low hundreds of milliseconds, even under peak ingest.

Pro Tip: Treat system.parts and system.query_log as part of your ingestion contract. Any new producer or pipeline change must keep avg_rows_per_insert above your minimum (1,000+ rows) and parts-per-partition below your threshold (ideally < 100); if it doesn’t, the change isn’t ready for production.

Summary

If you’re getting killed by tiny frequent inserts and table fragmentation, the fix isn’t a tuning “magic flag”—it’s an ingestion design that respects how MergeTree works. Aim for:

• Batching at 1,000–100,000 rows per insert, using time and size thresholds.
• Server-side help via async_insert, Buffer tables, or Kafka-based pipelines.
• Low-cardinality partitioning that serves lifecycle, not premature “optimizations.”
• Continuous observability of parts, merges, and inserts via system tables.

Once you bake those patterns into your architecture, ClickHouse can go back to doing what it’s good at: millisecond queries on billions of rows, instead of drowning in tiny parts.

Next Step

Get Started