FINAL vs argMax in ClickHouse

ReplacingMergeTree removes older row versions during background merges, not when they are inserted. Until those merges finish, a normal SELECT can return several versions of the same logical row.

Two common query-time fixes are FINAL and argMax. They can produce the same logical result when each key has a unique version, but they pay for that result differently.

Why duplicates remain

CREATE TABLE account_state
(
    id UInt64,
    version UInt64,
    status LowCardinality(String),
    amount Decimal(18, 2)
)
ENGINE = ReplacingMergeTree(version)
ORDER BY id;

Every insert creates an immutable part. Newer versions may therefore sit in different parts until ClickHouse merges them. Background merging makes storage converge, but it does not guarantee deduplicated reads at any particular moment.

What FINAL pays for

SELECT
    id,
    status,
    amount
FROM account_state FINAL;

FINAL applies the table engine's replacement logic while reading. ClickHouse compares rows from part ranges that may contain the same sorting keys and keeps the winning version. Its cost therefore follows how much relevant data still overlaps across parts.

Each part is already sorted by the table's ORDER BY key. When key ranges overlap, FINAL performs a k-way merge: it weaves those sorted parts into one ordered stream so versions of the same key become adjacent.

Part A: (1, v1)  (2, v1)           (4, v1)
Part B: (1, v2)           (3, v1)  (4, v2)
                         ↓ k-way merge
Stream: (1, v1)  (1, v2)  (2, v1)  (3, v1)  (4, v1)  (4, v2)

ClickHouse can then compare (1, v1) with (1, v2) and keep the winner before advancing. If two part ranges cannot contain the same key, they do not need this coordination.

Recent ClickHouse releases made this much cheaper: non-overlapping key ranges can avoid replacement work, processing is parallel, and the vertical algorithm resolves duplicates from key columns before reading the remaining columns. The old rule to never use FINAL is no longer useful.

Partition setting. do_not_merge_across_partitions_select_final = 1 lets partitions be finalized independently. Use it only when every version of a logical row is guaranteed to stay in the same partition.

What argMax pays for

SELECT
    id,
    latest.1 AS status,
    latest.2 AS amount
FROM
(
    SELECT
        id,
        argMax(tuple(status, amount), version) AS latest
    FROM account_state
    GROUP BY id
);

argMax turns deduplication into hash aggregation. Threads can scan blocks independently, build local states, and merge those states at the end. Unlike FINAL, it does not need to weave overlapping parts into a sorted stream; each worker only keeps the largest version it has seen for each key.

The tradeoff is that the query still processes every candidate row and keeps state for every distinct key. Background merges do not provide a shortcut. Memory grows with key cardinality, while CPU and state size also grow with the values being retained.

Using one argMax(tuple(...), version) keeps selected fields from the same winning row and avoids maintaining a separate aggregate state for each field.

Correctness before speed

Filtering a mutable value before argMax changes the answer. If the latest row is inactive, filtering first can resurrect an older active version:

-- Wrong: filters versions before choosing the latest row.
SELECT
    id,
    argMax(amount, version) AS amount
FROM account_state
WHERE status = 'active'
GROUP BY id;

-- Right: choose the latest row first, then filter entities.
SELECT
    id,
    latest.2 AS amount
FROM
(
    SELECT
        id,
        argMax(tuple(status, amount), version) AS latest
    FROM account_state
    GROUP BY id
)
WHERE latest.1 = 'active';

When each wins

Prefer FINAL when:

• Most relevant ranges are already merged or do not overlap.
• Key cardinality is too large for a comfortable hash table.
• The query returns many columns from the winning row.
• Exact engine replacement or deletion semantics matter.

Prefer argMax when:

• The table is hot, duplicate-heavy, and spread across many parts.
• Cardinality is moderate and aggregation has enough memory.
• Only a small tuple of values is needed from the latest row.

Measure the real table

Benchmark both forms against the same snapshot and filters. Part count, key-range overlap, duplicate ratio, cardinality, selected columns, and partition layout can change the winner. Compare elapsed time, rows read, peak memory, and EXPLAIN PIPELINE; a toy table with one part hides the work that makes this decision interesting.

Rule of thumb

FINAL cost follows the part ranges that still need reconciliation. argMax cost follows all candidate rows, the number of keys, and the state retained per key. Use that model to choose a first option, then measure it.