ClickHouse Insert Strategies

Every INSERT creates at least one part per partition it touches. Ingestion design in ClickHouse is therefore about controlling how many parts you create.

Batch Inserts

INSERT INTO events (event_time, tenant_id, event_type, user_id, duration_ms)
VALUES
  ('2026-07-31 10:00:00', 42, 'page_view', 1001, 120),
  ('2026-07-31 10:00:01', 42, 'page_view', 1002, 95);
  -- … tens of thousands more rows in the same statement

For bulk loading, native or binary formats avoid parsing overhead:

clickhouse-client --query "INSERT INTO events FORMAT Native" < events.native
cat events.jsonl | clickhouse-client --query "INSERT INTO events FORMAT JSONEachRow"

Where the application cannot batch, put a buffer in front: a queue consumer that accumulates rows, or Kafka with a Kafka engine table and a materialized view.

Asynchronous inserts

When many clients each send small inserts and you cannot restructure them, let the server batch:

SET async_insert = 1;
SET wait_for_async_insert = 1;      -- return only once the batch is flushed
SET async_insert_max_data_size = 10000000;
SET async_insert_busy_timeout_ms = 1000;

The server buffers rows in memory and flushes when the size or the timeout is reached.

wait_for_async_insert = 0 returns immediately, which is faster and means the client is told the insert succeeded before it is durable — a data loss window on a server crash. Choose it only for data where that is acceptable.

Part limits

-- Parts per partition; the number that triggers throttling and errors.
SELECT table, partition, count() AS parts
FROM system.parts WHERE active
GROUP BY table, partition
HAVING parts > 100
ORDER BY parts DESC;

Two thresholds matter, both per partition:

• parts_to_delay_insert (150 by default) — inserts start being deliberately slowed.
• parts_to_throw_insert (300 by default) — inserts are rejected with "Too many parts".

Deduplication of retried inserts

ClickHouse hashes each inserted block and, for replicated tables, ignores a block identical to one recently inserted:

SET insert_deduplicate = 1;                 -- default for Replicated tables
SET insert_deduplication_token = 'batch-4711';

This makes a retried insert safe after an ambiguous network failure, provided the retry sends the identical block. Setting an explicit token is more reliable than depending on byte-identical content.

Durability on insert

SET insert_quorum = 2;             -- wait for 2 replicas to confirm
SET insert_quorum_timeout = 60000;
SET select_sequential_consistency = 1;   -- read only quorum-confirmed data

Without a quorum setting, an insert acknowledged by one replica can be lost if that replica fails before replicating. Set it for data whose loss matters, and accept the added latency.