Skip to content
Tutorials Center
My learning

Topics

Lesson 11 of 35 ยท Tables and MergeTree Engines

Replacing and Collapsing Engines

Handle updates and duplicates in ClickHouse with ReplacingMergeTree, CollapsingMergeTree and VersionedCollapsingMergeTree, including FINAL, version columns and pitfalls.

ClickHouse tables are append-only, but real data changes: a user changes their email, an order moves from "pending" to "shipped", a sensor re-sends a reading. You cannot cheaply update rows in place, so ClickHouse offers engines that keep the newest version of a row by inserting new versions and cleaning up old ones later, during merges. The most used is ReplacingMergeTree. Its cousins CollapsingMergeTree and VersionedCollapsingMergeTree handle cancel-and-replace patterns.

ClickHouse cannot run on this site. The engine DDL and FINAL queries are read-only. The runnable blocks simulate "keep the latest version per key" with SQLite window functions, which is the logic FINAL applies.

ReplacingMergeTree

SQL
CREATE TABLE users
(
    user_id    UInt64,
    email      String,
    plan       LowCardinality(String),
    updated_at DateTime
)
ENGINE = ReplacingMergeTree(updated_at)
ORDER BY user_id;

The ORDER BY columns are the identity: rows with the same user_id are treated as versions of one thing. The argument updated_at is the version column: when two rows share the key, the one with the highest version survives a merge. If you give no version column, the last inserted row survives.

To "update" a user you simply insert a new row:

SQL
INSERT INTO users VALUES (1, '[email protected]', 'free', '2026-05-01 10:00:00');
INSERT INTO users VALUES (1, '[email protected]', 'pro',  '2026-05-02 09:30:00');

Both rows exist until a background merge combines the parts. Merges happen eventually, not on demand, so for a while a query can see both rows.

The catch: queries before merges

There are three ways to get correct answers:

SQL
-- 1) FINAL: merge duplicates at query time (correct, slower)
SELECT * FROM users FINAL WHERE user_id = 1;

-- 2) argMax: pick the latest value per key yourself
SELECT user_id, argMax(plan, updated_at) AS plan
FROM users
GROUP BY user_id;

-- 3) Force a merge (rare, heavy, not for routine use)
OPTIMIZE TABLE users FINAL;

FINAL has become much faster in recent versions (it can use multiple threads and skip parts that cannot overlap), but on very large tables it still costs more than a plain SELECT. argMax over the key is often the best production pattern.

Deletes with an is_deleted column

SQL
ENGINE = ReplacingMergeTree(updated_at, is_deleted)

With a UInt8 is_deleted column, inserting a row with is_deleted = 1 marks the key as deleted; FINAL hides it, and OPTIMIZE ... FINAL CLEANUP (or the table setting clean_deleted_rows) can physically remove it during merges.

Seeing the logic with SQLite

The query below does what FINAL does: for each key, keep the row with the greatest version.

SQL Stand-in: latest version per key, like FINAL (SQLite)

Press Run (or Ctrl + Enter) to see the result here.

SQL Stand-in: argMax(plan, updated_at) pattern (SQLite)

Press Run (or Ctrl + Enter) to see the result here.

(The second block uses an SQLite-specific rule: a bare column in a query with a single MAX() takes its value from the row holding the maximum. ClickHouse spells it argMax(plan, updated_at).)

CollapsingMergeTree

Sometimes you cannot send "the new version" but you can send "cancel the old row and add the new one". CollapsingMergeTree uses a Int8 sign column: +1 is a state row, -1 cancels an earlier identical +1 row.

SQL
CREATE TABLE cart
(
    user_id UInt64,
    items   UInt32,
    sign    Int8
)
ENGINE = CollapsingMergeTree(sign)
ORDER BY user_id;

INSERT INTO cart VALUES (7, 2, 1);                 -- state: 2 items
-- later the cart changes to 3 items: cancel the old state, add the new
INSERT INTO cart VALUES (7, 2, -1), (7, 3, 1);

During a merge, pairs of +1 and -1 rows with the same key cancel out, leaving (7, 3, 1). Queries should not rely on merges having happened, so you aggregate with the sign:

SQL
SELECT user_id, sum(items * sign) AS items
FROM cart
GROUP BY user_id
HAVING sum(sign) > 0;

The catch: the -1 row must repeat the old state exactly, so you must know it. And rows must arrive in a sensible order, or collapsing can fail to pair up. For that reason VersionedCollapsingMergeTree adds a version column so out-of-order arrivals still collapse correctly: ENGINE = VersionedCollapsingMergeTree(sign, version).

SQL Stand-in: sum with a sign column (SQLite)

Press Run (or Ctrl + Enter) to see the result here.

Which engine to choose

Situation Engine
You can send the full new row, with a version or timestamp ReplacingMergeTree
You also need deletes ReplacingMergeTree(version, is_deleted)
You send cancel/new pairs from a change stream CollapsingMergeTree
Pairs may arrive out of order VersionedCollapsingMergeTree
You never update plain MergeTree

Change-data-capture tools that mirror OLTP tables into ClickHouse commonly produce ReplacingMergeTree tables with a version and a deleted flag.

Important behaviours to remember

  • Deduplication happens only inside a partition, and only between parts that get merged. Identical keys in different partitions never collapse. Keep the partition key from changing between versions (for example do not partition by updated_at).
  • The key is the ORDER BY, so choose it as the real identity (user_id), not as a query-friendly composite you cannot rebuild for old rows.
  • Duplicates are visible between merges, so dashboards must use FINAL, argMax or GROUP BY.
  • Retried inserts of the same block can also be deduplicated automatically in replicated tables; that is a different feature (insert deduplication).

Common beginner mistakes

  • Expecting duplicates to vanish immediately after an insert.
  • Forgetting a version column and relying on insertion order.
  • Using Replacing engines to enforce uniqueness. They do not.
  • Partitioning by something that changes between versions of a row.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. When does ReplacingMergeTree remove older versions of a row?

    Deduplication happens when parts are merged, so duplicates can be visible until then.
  2. 2. How can a query always see only the latest version of each key?

    FINAL applies merge logic at query time; argMax picks the value of the latest version per group.
  3. 3. What does the sign column mean in CollapsingMergeTree?

    Pairs of +1 and -1 rows with the same key cancel out during merges.
  4. 4. Which engine tolerates out-of-order arrival of collapsing rows?

    The version column lets rows pair up regardless of arrival order.

Summary

  • ClickHouse does not update in place; engines keep the newest row via merges.
  • ReplacingMergeTree(version) keeps the highest version per ORDER BY key, eventually.
  • Query with FINAL, argMax or GROUP BY for correct results before merges finish.
  • Collapsing engines use a sign column (and optionally version) for cancel-and-replace streams.
  • Deduplication works only within a partition and never enforces uniqueness.

Next: Summing and aggregating engines