ReplacingMergeTree: Update & Deduplication in ClickHouse

Concrete problem: “I need to update rows in ClickHouse”

ClickHouse is built for immutable, append‑only data. In many OLAP scenarios you still receive incremental changes from a transactional source (e.g. CDC streams). You want the latest version of each key to be visible in queries, but you can’t afford to delete and re‑insert rows on every change. That’s where ReplacingMergeTree comes in – it lets you insert “updates” as new rows and relies on background merges to keep only the newest version per sorting key.

How ReplacingMergeTree works

The engine extends MergeTree by adding a version column. During a merge, rows that share the same sorting key are compared; the row with the highest version value is kept, the others are dropped. The process is fully automated – you don’t need to run any special cleanup queries.

Key points:

• Only rows with exactly the same sorting key are considered for replacement.
• Duplicates are physically removed only when a background merge runs, or when you force a merge with OPTIMIZE TABLE … FINAL.
• Queries can be forced to see the latest state immediately by adding the FINAL modifier.

Hands‑on example

Below is a minimal, reproducible workflow. Replace <cluster_name> with your cluster (or omit if you’re running on a single node). All commands should be executed from the ClickHouse client with a user that has INSERT and SELECT privileges on the target database.

# 1. Create a table that keeps the highest version per user
CREATE TABLE analytics.user_events (
    event_id UInt64,
    user_id UInt64,
    event_ts DateTime,
    payload String,
    version UInt64
) ENGINE = ReplacingMergeTree(user_id, version, 8192)
PARTITION BY toYYYYMM(event_ts)
ORDER BY (user_id, event_ts);

# 2. Insert two “updates” for the same user
INSERT INTO analytics.user_events VALUES
    (1, 1001, '2026-10-13 08:00:00', 'first click', 1),
    (2, 1001, '2026-10-13 08:05:00', 'second click', 2);

# 3. Normal SELECT – you’ll see both rows because no merge has happened yet
SELECT * FROM analytics.user_events WHERE user_id = 1001 ORDER BY event_ts;

# 4. SELECT with FINAL – forces a merge‑on‑read, showing only the newest row
SELECT * FROM analytics.user_events FINAL WHERE user_id = 1001 ORDER BY event_ts;

# 5. Force a physical merge so the duplicate is removed from storage
OPTIMIZE TABLE analytics.user_events FINAL;

After step 4 you should observe that only the row with version = 2 is returned. Step 5 will trigger a background merge that permanently removes the older row from the disk. Note that the FINAL keyword can be expensive on large tables because it forces the engine to merge all parts that contain the requested rows.

Trade‑offs and limitations

• Non‑deterministic merge timing: You can’t predict exactly when a duplicate will disappear. If you need instant consistency, rely on the FINAL modifier or trigger a manual OPTIMIZE.
• Performance cost of FINAL: The merge‑on‑read operation scans all relevant parts, which can increase latency and CPU usage. Use it sparingly and consider adding an index on user_id if you query frequently.
• Sorting key requirement: Only rows that match the entire sorting key are considered for replacement. If you need to update a subset of columns, you must include those columns in the sorting key or redesign your schema.
• No support for arbitrary primary‑key updates: The engine can’t update a row that has a different sorting key. In that case you’ll need to delete the old row and insert a new one.

Actionable next steps

1. Model your schema so the primary key (the sorting key) uniquely identifies the logical row you want to keep. Add a version column (or use a timestamp) to signal recency.

2. Use FINAL only for ad‑hoc queries that require instantaneous up‑to‑date data. For regular reporting, rely on background merges.

3. Monitor merge activity with system.merges to ensure that merges are happening at the expected rate. If you notice stale data, consider increasing the merge_tree_max_bytes_to_merge_at_once setting or triggering OPTIMIZE manually.

4. Test the flow in a staging environment using the commands above. Verify that after a merge the older rows are gone and that queries with FINAL return the correct result.

By following this pattern you can keep a ClickHouse OLAP store in sync with a transactional source while respecting the engine’s immutable data model. ReplacingMergeTree is a powerful tool when used with an understanding of its merge semantics and performance characteristics.