Managing Data Updates in ClickHouse with ReplacingMergeTree

The Challenge of Mutating OLAP Data

ClickHouse is optimized for immutable data. Standard inserts are fast, but updating or deleting individual rows is resource-intensive because it requires rewriting entire data parts. When you need to handle frequently updating records—such as user profiles or current order statuses—traditional UPDATE mutations cause significant performance degradation.

The ReplacingMergeTree engine solves this by treating updates as new insertions. Instead of modifying a row, you insert a new version of that row. ClickHouse then cleans up the old versions asynchronously during background merges, ensuring that only the most recent record persists over time.

Prerequisites

• A running ClickHouse instance (version 21.x or newer recommended).
• Administrative access to create tables and execute OPTIMIZE commands.
• A defined unique identifier for your records to serve as the sorting key.

Implementing ReplacingMergeTree

To use this engine, you must define a sorting key (which identifies the record) and optionally a version column (which determines which record is the newest).

Run the following in clickhouse-client or your preferred SQL interface:

CREATE TABLE user_profiles (
    user_id UInt64,
    email String,
    last_login DateTime,
    version UInt64
) ENGINE = ReplacingMergeTree(version)
ORDER BY user_id;

Configuration breakdown:

• ReplacingMergeTree(version): The version argument tells ClickHouse to keep the row with the highest value in that column. If omitted, the engine keeps an arbitrary row among duplicates, so a monotonically increasing version is strongly recommended.
• ORDER BY user_id: This defines the sorting key. Deduplication only happens for rows that share the exact same sorting key.

Handling the Asynchronous Nature of Merges

A critical detail of ReplacingMergeTree is that deduplication is asynchronous. When you insert a new version of a row, the old version remains in the table until ClickHouse merges the data parts in the background. A standard SELECT may therefore return duplicate rows for the same ID.

Option 1: On-the-Fly Deduplication with FINAL

To get the most recent data immediately without waiting for a merge, use the FINAL modifier. This forces ClickHouse to collapse the rows during query execution.

SELECT * FROM user_profiles FINAL WHERE user_id = 123;

Risk: FINAL is CPU- and memory-intensive. On tables with billions of rows it can significantly increase query latency. Use it for targeted lookups rather than massive aggregations.

Option 2: Manually Triggering a Merge

For testing or maintenance, you can force a merge to clear out old versions. This operation changes the state of data parts on disk.

OPTIMIZE TABLE user_profiles FINAL;

Permission required: This needs the OPTIMIZE privilege. It is a heavy I/O operation and should not be run frequently in production.

Verification and Testing Workflow

To verify that your deduplication logic works as expected, follow this sequence:

1. Insert conflicting data: Insert two records with the same user_id but different versions.

   INSERT INTO user_profiles VALUES (1, 'old@example.com', '2023-01-01 00:00:00', 1);
   INSERT INTO user_profiles VALUES (1, 'new@example.com', '2023-01-02 00:00:00', 2);

2. Check for duplicates: Run a standard SELECT * FROM user_profiles. You should see two rows, because a background merge has likely not occurred yet.
3. Verify current state: Run SELECT * FROM user_profiles FINAL. You should see only one row—the one with version 2.
4. Force cleanup: Run OPTIMIZE TABLE user_profiles FINAL.
5. Confirm permanent removal: Run a standard SELECT again. Only the latest version should remain.

ReplacingMergeTree vs. Standard Mutations

Limitations and Recovery

Limitations:

• Cross-part merges: Rows are only replaced when their data parts merge. If the same key exists in two different parts, both versions coexist until those parts are merged—there is no guaranteed schedule for this.
• No real-time guarantees: Do not rely on background merges for transactional consistency; use FINAL or an equivalent grouping query when correctness matters at read time.
• Sorting key is fixed: You cannot change the ORDER BY key of an existing table; you must create a new table and migrate data.

Rollback: Since ReplacingMergeTree relies on inserts, you cannot undo a merge once OPTIMIZE FINAL has collapsed the rows. To correct a bad update, insert a new row with a higher version number containing the correct data.