Implementing Idempotent Writes with ClickHouse ReplacingMergeTree

The Problem: Duplicate Events in High-Throughput Streams

In distributed systems, "at-least-once" delivery guarantees often result in duplicate records reaching the database. Traditional relational databases handle this via UNIQUE constraints, but in ClickHouse, such constraints do not exist because they would kill the performance of massive batch inserts. If you need to ensure that only the most recent version of a record is retained without manually deleting old rows, you face a trade-off between write speed and read-time uniqueness.

The ReplacingMergeTree engine solves this by deduplicating rows with the same sorting key during background merges. The critical takeaway is that ReplacingMergeTree provides eventual consistency, not immediate uniqueness. It is a tool for cleaning up data over time, not a replacement for a primary key constraint.

Minimal Design for Deduplication

To implement a basic deduplication layer, you need a table defined with a natural key (the unique identifier for the entity) and a versioning mechanism. The versioning column ensures that if two updates for the same ID arrive, the one with the higher version is kept.

-- Run on ClickHouse server as a user with CREATE TABLE permissions
CREATE TABLE user_profiles (
    user_id UInt64,
    email String,
    last_login DateTime,
    version UInt64
) 
ENGINE = ReplacingMergeTree(version)
ORDER BY user_id;

In this configuration, user_id is the sorting key. If multiple rows with the same user_id exist, ClickHouse will eventually keep only the one with the highest version value. If the version argument is omitted, the engine simply keeps the last row inserted into the part.

Trust and Data Boundaries

It is vital to understand where the "truth" resides in this architecture. Because ClickHouse writes data in immutable parts, a new INSERT creates a new part containing the duplicate row. The deduplication only happens when ClickHouse merges these parts in the background.

• Write Boundary: The database accepts all duplicates. There is no "Upsert" at the point of entry.
• Read Boundary: A standard SELECT may return multiple versions of the same row if they reside in different parts.
• Consistency Boundary: To guarantee a unique result at query time, you must use the FINAL modifier or a GROUP BY on the sorting key.

Operational Checks and Verification

Since deduplication is asynchronous, you cannot rely on a simple count to verify it is working. Use the following methods to monitor and force the process.

Checking for Deduplication Lag

High part counts indicate that merges are lagging, meaning more duplicates are currently visible in your data. Run this on the server to check the number of active parts per partition:

SELECT partition, count() as part_count 
FROM system.parts 
WHERE table = 'user_profiles' AND active = 1 
GROUP BY partition;

Manual Verification of Deduplication

To verify the engine is correctly identifying the version column, insert two rows with the same key but different versions, then force a merge:

-- Insert duplicate key with lower version
INSERT INTO user_profiles VALUES (1, '[contact removed]', '2026-10-01 10:00:00', 1);
-- Insert duplicate key with higher version
INSERT INTO user_profiles VALUES (1, '[contact removed]', '2026-10-01 11:00:00', 2);

-- Force a merge to clean up duplicates (Caution: Resource intensive on large tables)
OPTIMIZE TABLE user_profiles FINAL;

-- Verify only version 2 remains
SELECT * FROM user_profiles WHERE user_id = 1;

Failure Modes and Limitations

Several conditions can break the expected behavior of ReplacingMergeTree:

• Version Overflow: If the version column (e.g., UInt32) overflows or is incorrectly mapped to a NULL value, the engine may retain the wrong row or fail to deduplicate.
• Write Amplification: Using an extremely wide ORDER BY key increases the CPU and I/O cost of the merge process, potentially leading to "merge storms" where the system spends more resources cleaning data than ingesting it.
• Read Performance: Using SELECT ... FINAL forces ClickHouse to merge parts in memory during the query. On large datasets, this can lead to severe latency or memory exhaustion.

When to Change the Design

The ReplacingMergeTree approach is unsuitable if your requirements shift toward the following:

• Read-Your-Writes Consistency: If the application requires that a subsequent read immediately reflects the latest update without using FINAL, move the deduplication logic to the application layer or use a Key-Value store as a cache.
• Frequent Partial Updates: If you only need to update one column of a wide row, ReplacingMergeTree is inefficient because it requires re-inserting the entire row. Consider CollapsingMergeTree or using ALTER TABLE ... UPDATE (though the latter is a heavy mutation).
• Sub-second Deduplication: If duplicates must be gone within milliseconds, the background merge cycle is too slow.