Recommended Free Tools
ReplacingMergeTree supports update-style ingestion by inserting a new version of a row, not by editing an existing row in place. Background merges eventually reconcile rows with the same ORDER BY key. Until then, a normal query may return multiple versions; SELECT ... FINAL applies the replacement rules while reading so the result reflects the reconciled state.
How do upserts work in ReplacingMergeTree?
ClickHouse’s MergeTree-family engines write immutable data parts: new data is added as new parts rather than changing existing rows in place. ReplacingMergeTree turns this append-only write pattern into update-style behavior by identifying replacement candidates with the table’s ORDER BY sorting key. That key must represent the logical identity of the row you intend to replace; a version column is not the unique key.
A two-version example
- Insert a row for logical key
Kwith version1. - When the row changes, insert another row with the same
ORDER BYkey and version2. - Until a background merge reconciles the relevant parts, an ordinary
SELECTcan return both rows. - During replacement, a configured version column tells the engine to retain the row with the greatest version. Without a version column, which row survives depends on merge order, so an explicit version is safer for update-style data.
Background deduplication is asynchronous, not an immediate guarantee that every read sees one row per key. ReplacingMergeTree is therefore not a transactional in-place upsert.
When should I use SELECT FINAL?
Use SELECT ... FINAL when a query needs the replacement logic applied before background merges have completed. It applies the engine’s replacement rules to the rows read by that query; it does not edit on-disk parts or wait for a physical merge to finish.
SELECT *
FROM events FINAL
WHERE event_id = 42;The exact query cost depends on the parts and workload involved. There is no universal overhead percentage that applies across schemas and server versions. Treat FINAL as a read-correctness choice: use it where the result must reflect the reconciled state, rather than assuming every query needs it.
Partition-aware FINAL
ClickHouse provides the setting do_not_merge_across_partitions_select_final for partition-aware processing. It is safe only when every version of a logical key is guaranteed to remain in the same partition. If versions can land in different partitions, processing partitions independently cannot reconcile those versions together. The partition key and ingestion rules must preserve that invariant before enabling the setting.
Does FINAL trigger a merge?
No. The word FINAL has different roles in a SELECT and in an OPTIMIZE command:
| Operation | What it does | When to use it |
|---|---|---|
SELECT ... FINAL |
Applies replacement logic on the query read path; does not materialize a merge. | When that query needs a reconciled result before background merging has done the work. |
OPTIMIZE TABLE ... FINAL |
Requests a physical merge: ClickHouse reads active parts and writes merged output, consuming I/O and write work. | For deliberate maintenance when a physical merge is warranted, not as the routine way to make reads correct. |
For recurring upsert reads, consider the combined effect of query-time replacement, background merge state, data layout, and workload. Scheduling forced FINAL merges simply to prevent duplicate results can shift substantial work to the storage and write path without replacing the need to choose an appropriate read strategy.
Outdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchPC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11How do FINAL, ordinary reads, and argMax compare?
| Approach | Result before background merges finish | Cost and fit |
|---|---|---|
Ordinary SELECT |
May return multiple versions of a key. | Does not apply replacement on the read path; suitable when duplicates are acceptable or the query explicitly handles them. |
SELECT ... FINAL |
Applies ReplacingMergeTree replacement logic to the rows read. | Adds query-time work; suitable when the result needs engine-level replacement semantics. |
Aggregation such as argMax |
Can select a value associated with the greatest version, if the query’s grouping and tie-handling match the intended result. | Useful for queries whose aggregation semantics fit the desired output; it is not automatically equivalent to row-level replacement for every query. |
There is no established workload-independent break-even point between FINAL and aggregation. Compare them using the actual query semantics, schema, part distribution, and workload.
When is ReplacingMergeTree a good fit?
It can suit update-style ingestion, duplicate handling, and change-data-capture (CDC) when records have a stable logical key and a reliable ordering or version rule. For out-of-order changes, a version tied to the source’s commit order helps ensure the newest intended state wins. A deletion marker or delete workflow also needs explicit design: replacement behavior does not mean a row is physically erased immediately.
If data is strictly append-only and has no update or delete semantics, an ordinary MergeTree may be more appropriate. The choice is about the data’s lifecycle and query requirements, not simply whether the table accepts inserts.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.What should you decide before using it?
- Logical identity: Does the
ORDER BYkey identify exactly the row versions that should replace one another? - Version ordering: Is there a column that reliably ranks changes, including late-arriving or out-of-order events?
- Partition placement: Can all versions of a key remain in one partition if you plan to use partition-aware FINAL?
- Read correctness: Which queries need the current deduplicated state before background merges complete?
- Work placement: Should replacement work happen during reads, through query-specific aggregation, or as part of background merging?
- Deletes: Is the deletion representation and downstream query behavior explicit, rather than relying on an assumption of immediate physical removal?
ClickHouse’s documented behavior and settings can evolve; verify details against the release and configuration you deploy.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Quick Recap
Sources
- ClickHouse: Updates in ClickHouse
- ClickHouse documentation: ReplacingMergeTree
- ClickHouse: Handling Updates and Deletes in ClickHouse
- ClickHouse: ClickHouse Fully Supports UPDATE Queries
- ClickHouse: Implementing Updates
- ClickHouse documentation: OPTIMIZE Statement
- ClickHouse: When to Use OPTIMIZE TABLE … FINAL
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
