UPSERT on MergeTree: cheap updates are not a cheap existence check

“Insert this row, or if its key already exists, update it” is the most ordinary write in transactional databases, and ClickHouse has never had a direct answer for it. When lightweight UPDATE arrived I thought the missing piece was finally within reach, and I filed #86779: a native UPSERT (or INSERT ... ON CONFLICT DO UPDATE) for MergeTree that inserts when no row matches the sorting key and otherwise applies a lightweight update. The use cases I listed were low-latency row updates for device state and order status, and removing the read-then-write, FINAL and argMax workarounds.

A maintainer answered the same day and the issue was closed as a duplicate a day later. The answer is short, but it contains the whole design argument, so this page unpacks it.

The design tension

Take the request apart. An upsert is two operations: decide whether the key exists, then apply either an insert or an update. Lightweight updates made the second operation cheap. They write a small patch part and let reads and merges apply it, instead of rewriting column files. That is real progress, and it is what made me think of the request. But the first operation was never the cheap one on MergeTree.

Two properties of the engine collide with it. Parts are immutable, so there is no in-place “find the row and change it”. And the primary index is sparse: it stores one entry per granule (8192 rows by default), not one per row. It is built to skip most of the table for a range, not to answer “is this exact key present?” for millions of keys per second.

You can watch that with EXPLAIN. On a million-row table keyed by id, one existence check is cheap:

CREATE TABLE big (id UInt64, v UInt32) ENGINE = MergeTree ORDER BY id;
INSERT INTO big SELECT number, 0 FROM numbers(1000000);
EXPLAIN indexes = 1 SELECT v FROM big WHERE id = 123456;

        Condition: (id in [123456, 123456])
        Parts: 1/1
        Granules: 1/123
        Search Algorithm: binary search

One granule of 123, so about 8192 rows read to confirm a single key. Now check the keys of a 1000-row batch, as an upsert of that batch would have to, and a 10-row batch for contrast:

EXPLAIN indexes = 1 SELECT count() FROM big
WHERE id IN (SELECT rand64() % 1000000 FROM numbers(1000));

        Condition: (id in 1000-element set)
        Parts: 1/1
        Granules: 123/123

With 10 random keys the same plan reads 10/123 granules. With 1000 random keys it reads every granule. This is one part and a table with one key column; a real table has many parts, and the check has to cover all of them. The ingest path that was an append has become a read of the table. That is the practical meaning of Alexey’s sentence.

What the maintainers said

The whole answer, from alexey-milovidov:

While it's possible to implement upserts using lightweight updates, it is not going to be efficient.
It requires another mechanism: https://github.com/ClickHouse/ClickHouse/issues/70589

#70589 is “RFC: Unique key constraint”, opened by alexey-milovidov himself in October 2024 and still open. Its design is worth reading, because it is the mechanism he means. The proposal is a CONSTRAINT ... UNIQUE (a, b) TYPE impl_engine_name backed by a set of all unique key tuples, held in memory as 128-bit hashes or in a RocksDB table. Three decisions in it stand out:

• The set is ephemeral. It is not persistent; on restart it is rebuilt by reading the key columns from the data. The RFC’s reason: inserting into the table and into the set is not atomic, so after an unclean restart the old state cannot be trusted, and rebuilding from the parts is the only safe source of truth.
• Consistency by optimistic transactions. For non-replicated tables a mutex serializes inserts. For replicated tables each server keeps its own copy of the set, and an insert commits to Keeper only if the set of data parts did not change underneath it; otherwise it rolls back and retries, independently per partition.
• It names the cost of the statements I asked for. The RFC says that REPLACE, MERGE and ON DUPLICATE KEY UPDATE could be implemented, but would require running a SELECT before the INSERT and guaranteeing that the SELECT sees the same fresh set of parts, which “increases the time of optimistic transaction and makes INSERTs rather slow”.

That last point is the direct answer to my request. In the same RFC thread, asked whether the RFC was accepted, he wrote that it is “the only viable way to implement unique key constraints” and “not currently in work”, and that it needs “good knowledge of ClickHouse internals in addition to a higher than entry-level of C++”. Other commenters have raised cheaper shapes: iskakaushik suggested a bloom filter as a first-level lookup with an on-disk hash table behind it to cut memory, and JingYanchao described a per-part SSTable-style unique index with a bitmap marking duplicates, after finding an in-memory hash table “consumes a huge amount of memory”. In April 2026 murphy-4o asked to take the work and promised a detailed RFC. As of writing the issue is still open.

The workarounds and what each costs

There is no single drop-in, so it comes down to where you want to pay.

Approach	Where the cost lands	Failure mode
Read, then write, in the application	Extra round trip per row	Race between the read and the write
ReplacingMergeTree with a version	Read time (FINAL, argMax)	Duplicates visible until a merge runs
CoalescingMergeTree	Merge time	Partial rows visible until a merge; “latest” is insertion order
Lightweight UPDATE on a known key	Small patch part	Silently does nothing if the key is absent

The first and second rows are familiar. Here is ReplacingMergeTree showing the gap between what is stored and what is true, with a version column and three inserts, run on 26.4:

CREATE TABLE r (id UInt32, v String, ver UInt32) ENGINE = ReplacingMergeTree(ver) ORDER BY id;
INSERT INTO r VALUES (1,'a',1);
INSERT INTO r VALUES (1,'z',2);
INSERT INTO r VALUES (2,'b',1);
SELECT 'raw', count() FROM r;
SELECT 'final', count() FROM r FINAL;
SELECT id, argMax(v, ver) FROM r GROUP BY id ORDER BY id;

raw     3
final   2
1       z
2       b

Three stored rows, two logical rows; each insert made its own part, and OPTIMIZE TABLE r FINAL collapses them to two physical rows in one part. The correct answer is available at read time, which is the price. FINAL does merge work inside every query that uses it; see merges, OPTIMIZE and FINAL and latest row per key.

The last row is the one a naive reading of my request would hit. A lightweight update on a key that is not there is not an error. It matches no rows and writes nothing useful:

CREATE TABLE m (id UInt32, v String) ENGINE = MergeTree ORDER BY id
SETTINGS enable_block_number_column = 1, enable_block_offset_column = 1;
INSERT INTO m VALUES (1, 'a'), (2, 'b');
UPDATE m SET v = 'z' WHERE id = 1;
UPDATE m SET v = 'new' WHERE id = 99;
SELECT * FROM m ORDER BY id;
SELECT name, rows, part_type FROM system.parts WHERE table = 'm' AND active ORDER BY name;

1   z
2   b
all_1_1_0                                       2   Compact
patch-9834becdf7e2e15ad7c410ffd6a5a63c-all_3_3_0_2   1   Compact

Key 1 was updated, key 99 never appeared, and there is no INSERT fallback. The second query shows how the update is stored: the original part plus a one-row patch-... part. Those patch parts are applied when you read and when parts merge, which is why the update is instant to every reader. (The table needs the block-number and block-offset columns enabled; without them the update is refused with an error that points at MODIFY SETTING enable_block_number_column = 1.) For the mutation machinery around it, see mutations, lightweight delete and TTL.

An upsert is therefore “ask whether key 99 exists, then pick the right row of this table”. The workable ClickHouse-native form of that question is to push it to read time: always insert a new version, never update, and let the engine reconcile.

What it teaches about ClickHouse’s boundaries

ClickHouse’s write path is an append, and that is a promise, not an accident: an insert never reads existing data, so ingest speed does not degrade as the table grows. Every proposal that adds a check against existing data to that path moves the cost from reads to writes. The unique-key RFC accepts that trade explicitly (an in-memory set per replica, optimistic retries, serialization per partition), which is why it is a large piece of work and not a syntax addition.

It also explains why the engines that exist today all reconcile after the fact. ReplacingMergeTree, CollapsingMergeTree, SummingMergeTree and CoalescingMergeTree defer the answer to merge time and let FINAL or aggregation compute it on read. A pull request open as I write, #116075, adds a VersionedCoalescingMergeTree that combines the partial-row merging of CoalescingMergeTree with the explicit version column of ReplacingMergeTree, so out-of-order writers get the right result. It is a good example of the direction: better reconciliation, not an existence check.

For the broader question of which requests are worth filing, and why this one still earned its answer, see feature requests worth making.

Where it stands

The issue is closed as a duplicate; #70589 remains open. In 26.4 there is no UPSERT and no ON CONFLICT. What I do today is the boring pattern: ReplacingMergeTree with a version column and a FINAL or argMax read where the latest row matters, and a lightweight UPDATE only when I know the row exists. The day a unique-key mechanism lands, an UPSERT becomes a thin layer on top of it: look up with the set, then insert or patch. Until then the honest summary is that lightweight updates made the second half of an upsert cheap, and nothing yet makes the first half cheap.