ClickHouse Atomic Inserts: Single-Partition Guarantees

ClickHouse provides an atomicity guarantee for INSERTs, but it is narrower than the table-wide transactions you may know from OLTP databases. An INSERT is atomic — all rows commit or none do — when it writes a single data part into a single partition of a single MergeTree-family table. Once you cross a partition boundary or exceed a block-size threshold, the insert becomes multiple parts, and each part commits independently.

This page explains exactly when the single-partition atomic guarantee holds, the settings that control it, and how to structure inserts so a failure never leaves partially visible data.

What "Atomic" Means in ClickHouse

ClickHouse's transactional support is documented around a specific case: an INSERT into one partition of one table of the MergeTree family, where the rows are packed into a single block. In that case the insert is:

• Atomic — the INSERT succeeds or is rejected as a whole; either all rows are inserted or none are.
• Isolated — a new part becomes visible to SELECTs only after it is fully written and committed, so no half-written part is ever visible. Note: clients operating outside an explicit transaction have read-uncommitted isolation per the official docs; snapshot isolation applies only to clients inside another transaction.
• Durable — a successful INSERT is written to the filesystem before the server replies. Durability across replicas is governed by `insert_quorum`, and fsync_after_insert controls fsync to disk.

The key implementation fact behind this: atomicity is per data part. Each INSERT that produces exactly one part is atomic. An INSERT that produces several parts is a set of independent atomic operations, not one big atomic operation.

The Single-Part Rule

An insert is atomic if it creates only one part. An insert creates one part when both of these are true:

1. Data is inserted directly into a MergeTree table (not through a Distributed table, and without a materialized view fan-out — see below).
2. Data is inserted into a single partition.

If the incoming rows belong to two different partitions, ClickHouse writes (at least) two parts — one per partition. Those parts are committed separately, so a reader could briefly see rows from one partition and not the other, and a mid-insert failure could leave one partition's rows present and the other's absent.

CREATE TABLE events
(
    event_date Date,
    user_id    UInt64,
    payload    String
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_date, user_id);

-- Atomic: every row falls in the 2026-06 partition -> one part
INSERT INTO events VALUES
    ('2026-06-01', 1, 'a'),
    ('2026-06-02', 2, 'b');

-- NOT atomic as a whole: rows span 2026-05 and 2026-06 -> two parts
INSERT INTO events VALUES
    ('2026-05-31', 3, 'c'),
    ('2026-06-01', 4, 'd');

How you define PARTITION BY therefore directly shapes atomicity. See Creating tables in ClickHouse for partition-key design, and note that a table with no PARTITION BY has a single partition (tuple()), so partition boundaries never split an insert there.

Block-Size Thresholds Within a Partition

Even when all rows target one partition, an insert can still be split into multiple parts if the data is large enough to be broken into multiple blocks. The relevant settings:

For a row-based input format (CSV, TSV, Values, JSONEachRow, etc.), the client's rows are packed into a single block — and thus a single part — when the data contains fewer than max_insert_block_size rows (~1,000,000 by default), or is below the min_chunk_bytes_for_parallel_parsing threshold (default 1 MiB). Exceed that and the server may produce several parts, breaking whole-statement atomicity.

Making Large Single-Partition Inserts Atomic

To keep a large insert (still targeting one partition) atomic, prevent ClickHouse from splitting it into multiple blocks. The documented approach is to disable parallel parsing and raise the squash thresholds high enough to force a single block:

clickhouse-client \
  --input_format_parallel_parsing=0 \
  --min_insert_block_size_bytes=0 \
  --min_insert_block_size_rows=1000000000 \
  --query="INSERT INTO events FORMAT CSV" < data.csv

The same settings can be applied per-query with a SETTINGS clause on an INSERT ... SELECT:

INSERT INTO events
SELECT event_date, user_id, payload
FROM staging
WHERE toYYYYMM(event_date) = 202606   -- restrict to one partition
ORDER BY event_date, user_id
SETTINGS
    max_insert_block_size = 1000000000,
    min_insert_block_size_rows = 1000000000,
    min_insert_block_size_bytes = 0;

Be deliberate here: forcing a single giant block raises memory usage and can produce one very large part. For routine high-volume loading, smaller batches with too-many-parts avoidance in mind are usually healthier than chasing whole-statement atomicity. Reach for the single-block approach only when the all-or-nothing guarantee genuinely matters.

INSERT SELECT Determinism

For INSERT ... SELECT, atomicity also requires the data to be produced deterministically, because ClickHouse may retry. Avoid non-deterministic functions like rand() or now() in the SELECT, and constrain block formation so the result lands in one block. In practice that means one of:

• Set max_threads = 1, or
• Add a real ORDER BY to the SELECT (not ORDER BY tuple()), or
• Rely on a meaningful ORDER BY in the target table's DDL,

combined with the squash settings above so the deterministic result is written as a single part.

What Is NOT Atomic

The single-partition guarantee has hard edges. The following are explicitly not covered by a single atomic operation:

1. Multi-partition inserts. Rows spanning N partitions create N parts that commit independently.
2. Inserts exceeding block-size thresholds. Even in one partition, oversized data becomes multiple parts.
3. The base table together with its materialized views. There is no atomicity across the Table ↔ MV boundary. The write to the source table and the write triggered into each MV target are separate operations; one can succeed while another fails. Use deterministic, idempotent MV logic and an insert deduplication token to make retries safe rather than relying on atomicity.
4. Distributed table inserts that fan out to multiple shards — each shard is a separate write.
5. Multiple tables in one statement. ClickHouse has no cross-table transaction for inserts.

For multi-replica durability (a different axis from single-partition atomicity), see insert_quorum, which controls how many replicas must acknowledge a write before it is considered successful.

Best Practices

1. Align batches with partitions. Group rows by partition key on the client so each INSERT targets exactly one partition. This is the simplest path to whole-statement atomicity.

2. Keep batches under max_insert_block_size when you need atomicity, so the rows form a single block and a single part.

3. Don't rely on MV atomicity. If a base-table insert and its materialized-view writes must stay consistent, make the MV transformation deterministic and use a deduplication token so retried inserts don't double-count.

4. Make INSERT SELECT deterministic. Remove rand()/now() and add an explicit ORDER BY so retries reproduce identical parts.

5. Verify with system.part_log. Inspect how many parts an insert actually produced; if one statement created several NewPart rows, it was not atomic as a whole. See part_log.

6. Prefer idempotency over forced atomicity for streaming. For high-rate ingestion (Kafka, queues), idempotent retries plus insert deduplication are more robust than forcing every batch into one part.

Common Issues

1. "Some rows showed up, others didn't." Almost always a multi-partition insert: each partition's part committed separately. Split the batch by partition.

2. A single insert created many parts. The batch exceeded max_insert_block_size or parallel parsing split it. This both breaks atomicity and can trigger Too many parts.

3. Duplicate rows after a client retry. The original insert actually succeeded server-side before the client timed out. Atomicity does not prevent duplicates from retries — use insert deduplication for that.

4. MV target diverged from the base table. Expected, since Table ↔ MV is not atomic. Make the MV deterministic and reconcile via deduplication tokens or periodic checks.

How Pulse Helps

Diagnosing whether an insert was truly atomic means correlating part creation, partition boundaries, and block-size settings across your cluster — usually after the fact, when data already looks inconsistent. Pulse monitors ClickHouse part creation and system.part_log, surfaces inserts that fragmented across partitions or blocks, and flags rising part counts before they turn into "Too many parts" failures. When a load behaves unexpectedly, Pulse helps you see how many parts each insert produced and whether your partitioning and block-size settings are working against the atomicity you expect — so you can fix the batching strategy instead of guessing.

Frequently Asked Questions

Q: Is every ClickHouse INSERT atomic? A: No. An INSERT is atomic only when it produces a single data part — that is, when all rows go to one partition of one MergeTree-family table and fit within one block (under max_insert_block_size, ~1M rows by default). Multi-partition or oversized inserts produce multiple parts that commit independently.

Q: How does the partition key affect atomicity? A: An insert is split into one part per partition it touches. If your PARTITION BY is fine-grained (e.g. by day) and a batch spans several days, that batch is no longer atomic as a whole. A table with no PARTITION BY has a single partition, so partition boundaries never split an insert.

Q: Can I make a large insert atomic? A: Yes, if it still targets one partition. Disable input_format_parallel_parsing, set min_insert_block_size_bytes=0, and raise min_insert_block_size_rows (and max_insert_block_size for INSERT SELECT) high enough to force one block. This costs memory and yields one large part, so use it only when all-or-nothing is required.

Q: Are inserts into a table with a materialized view atomic? A: No. There is no atomicity between the source table and its materialized views — the base write and each MV write are separate operations. Make MV logic deterministic and use an insert deduplication token so retries are safe.

Q: Does atomicity prevent duplicate rows on retry? A: No. If a client retries after a timeout, the original insert may already have committed, producing duplicates. Atomicity is about all-or-nothing within one statement, not deduplication across retries — use insert deduplication for that.

Q: How is this different from insert_quorum? A: Single-partition atomicity governs whether one statement commits all-or-nothing on a single node. `insert_quorum` governs durability across replicas — how many replicas must acknowledge before the insert is deemed successful. They address different concerns and are often used together.