ClickHouse FINAL: when to use it (and how to avoid the cost)

June 16, 2026·5 min read

#ClickHouse#FINAL#performance

FROM ... FINAL is the easy answer to 'I need the latest version of each row' on a ReplacingMergeTree — but on hot query paths it is one of the most common silent costs in ClickHouse.

Bar chart comparing query cost of FROM FINAL versus argMax — argMax is much cheaper — When you only need the latest row per key, skip the full FINAL merge.

What FINAL does

ReplacingMergeTree deduplicates rows only when parts merge in the background. Until that happens, multiple versions of a key can live in different parts. FINAL forces a merge-on-read so the query sees the deduplicated result.

Why it's expensive

FINAL reads and merges across all relevant parts at query time, every time. On a large hot table that is far more work than a normal read — and it scales with how fragmented the table is.

Replace it with argMax

When you only need the latest value per key, argMax(value, version) returns it without merging every part.

sql

-- Instead of: SELECT * FROM orders FINAL WHERE user_id = 42
SELECT argMax(status, _version) AS status,
       argMax(total,  _version) AS total
FROM orders
WHERE user_id = 42
GROUP BY user_id;

When FINAL is still fine

For occasional ad-hoc queries, small tables, or when you genuinely need full row-level dedup across many columns, FINAL is acceptable. The problem is using it on every read of a large, hot table.

Full playbook

See the FINAL-removal pattern and other query fixes in Diagnosing slow queries.