ReplacingMergeTree — Emulating Updates and Deletes with INSERTs

In one line

ReplacingMergeTree is an engine that reduces rows with the same sorting key to one at merge time. The row with the larger version column wins, and if the winning row has a deletion marker, that key is treated as nonexistent. Because you cannot know when merges will happen, a query that needs an exact answer must apply the same rule at query time with FINAL or argMax.

Why this was needed

Once you move a production database's user table to an analytics database, changes soon follow: "the plan changed", "the user left". In a row-oriented database that is one line of UPDATE or DELETE, but as we saw in the earlier modules, MergeTree parts are not modified once written. To change one row, you have to rewrite the column files of the part holding that row, and doing that for every change makes writes explode.

So the idea is flipped. Do not change; insert one more line as a new version. A user leaving is also inserted as a version that says "the user left". The official guide (Working with the ReplacingMergeTree engine) describes this as "imitating updates with immutable INSERTs". Clearing away the old versions is put on top of the background merging that happens anyway. Writes are always appends, so they are fast, and the price is that "until a merge happens, the old versions are visible too".

How it works

The engine declaration is ReplacingMergeTree(ver, is_deleted). The rules set by the reference documentation are these.

• "The same row" is decided by ORDER BY. Not by PRIMARY KEY. Rows with the same sorting key value are one group.
• Within a group, the row with the largest ver remains. If ver is the same, the row inserted last remains (on the lab server, inserting 'a' then 'b' with the same ver left 'b').
• is_deleted can be used only when there is a ver, and if the winning row has 1, that key drops out of queries. But a merge does not erase the deletion-marker row and leaves it. This is so that even if a lower version arrives later, the deletion wins.
• To erase even the deletion rows at merge time, turn on the table setting allow_experimental_replacing_merge_with_cleanup = 1 and issue OPTIMIZE ... FINAL CLEANUP. Issued without the setting, it is rejected with "Experimental merges with CLEANUP are not allowed".

The numbers measured with this lab's data (100,000 users · 30,058 updated rows · 5,108 deleted rows) show the rules as they are. If you insert with merges stopped, count() is 135,166 rows, and with FINAL it is 94,892 users. Even after merging with OPTIMIZE ... FINAL, there are 100,000 rows — one per user, but 5,108 of them are deletion-marker rows, so counting without FINAL is still wrong. Only after a CLEANUP merge does it become 94,892 rows.

FINAL applies the merge rule while querying. You can produce the same answer with argMax too.

SELECT plan, count() FROM (
  SELECT user_id, argMax(plan, ver) AS plan, argMax(is_deleted, ver) AS d
  FROM rmt.users GROUP BY user_id
) WHERE d = 0 GROUP BY plan;

argMax(x, ver) is the x of the row with the largest ver. Because the query itself says what to pick, it works even where FINAL cannot be used (other engines, results combining several tables).

One more thing — duplicates inside one INSERT shrink at the moment they are inserted. Because of the default setting optimize_on_insert = 1, when I inserted three versions in one INSERT, the part was 100,000 rows from the start. To see "before merging", insert the versions separately and stop that table's merges with SYSTEM STOP MERGES. If you issue OPTIMIZE on a stopped table, it is rejected with "Cancelled merging parts".

What it looks like in the field

The most common incident is putting a changing column in the sorting key. If you set it like ORDER BY (user_id, plan) for query performance, a user whose plan changed gets a different sorting key and becomes "a different row". In the lab, a table built this way gave 115,062 rows (95,919 users) even counted with FINAL — the old-plan rows are never merged, and the deletion marker cannot cover the old-plan row either, so deleted users remain alive. This is why the guide insists that "the ORDER BY columns must not change".

The second is resending after CLEANUP. It is common for a pipeline to resend an old batch (reprocessing, offset rewind). In a table where the deletion markers remain, ver 3 beats ver 1, so it stays firm, but in a table where CLEANUP erased the deletion rows, there is no opponent to compare against and the old rows come back to life as they are. In the lab, when I reinserted the initial-load rows of users 1–1000, 56 users came back to life only in the CLEANUPed table. The guide says to use CLEANUP "only when you can be sure old versions will not come back in".

The third is the cost of FINAL. FINAL merges at query time, so the more the filtering condition hits the sorting key, the cheaper it is. The guide also mentions do_not_merge_across_partitions_select_final, which makes it process per partition when the partition key does not change from row to row.

What you will do in the next lab

You create rmt.users as ReplacingMergeTree(ver, is_deleted) and insert three batches of change records with merges stopped. You compare the row counts without and with FINAL, and get the current number of users per plan with argMax, without FINAL. You move the data into two new tables version by version, do a normal merge on one and a CLEANUP merge on the other, count the remaining rows, and see what a table with plan in the sorting key brings back to life. Finally you reinsert old rows and record how the two tables react differently.