FINAL in ClickHouse Isn’t as Expensive as It Used to Be

For a long time, the advice surrounding the FINAL modifier in ClickHouse was incredibly straightforward: Avoid it whenever possible. Honestly, that rule of thumb existed for very good reasons. Older versions of ClickHouse could make FINAL excruciatingly expensive depending on your table size, partitioning, the number of active parts, background merge states, and query patterns. Because of this, engineering teams started treating FINAL almost like an architectural red flag.

But modern ClickHouse has changed dramatically over the last few years. The engineering behind the database has evolved, and the historical conversation around FINAL deserves a bit more nuance today.

Why FINAL Existed in the First Place

To understand why FINAL was historically considered an anti-pattern, you first need to understand how ClickHouse handles data under the hood.

In engines like ReplacingMergeTree, CollapsingMergeTree, and VersionedCollapsingMergeTree, ClickHouse does not immediately rewrite or update rows in place when new data arrives. Instead, inserts create entirely new data parts, background merges reconcile rows asynchronously over time, and deduplication happens quietly in the background.

Because of this asynchronous lifecycle, queries can temporarily return duplicate rows, older versions of a record, or intermediate states.

SELECT *
FROM users
FINAL;

When you append FINAL to a query, you are forcing ClickHouse to apply that background merge logic right then and there during query execution. This means the query must read more data, perform real-time deduplication work, and consume significantly more CPU and memory. In older deployments, this behavior caused major performance degradation, leading to the community-wide warning against it.

The Historical Problem vs. Modern Optimization

Historically, FINAL became incredibly painful on large datasets, particularly when partitions were oversized, merges lagged behind ingestion, or queries scanned massive date ranges. Developers would often add FINAL to "fix" duplicate rows without understanding why those duplicates existed in the first place, leading to sluggish queries, spikes in memory usage, and unnecessary query overhead. The community rightfully advised engineers to design better schemas and steer clear of the modifier.

While that advice still carries weight, the implementation of FINAL itself has drastically improved. Recent ClickHouse releases introduced multiple major optimizations, including:

• Parallel execution of the final merge across multiple threads.
• Partition-aware optimizations that skip data blocks entirely if they don’t require merging.
• Improved memory management that prevents queries from blowing up cluster resources.
• Smarter merge execution that drastically reduces unnecessary data reads.

FINAL is simply no longer the performance monster it used to be. In recent discussions and office hours across the ClickHouse ecosystem, utilizing FINAL for latest-state queries is no longer treated as an automatic failure—a stance that would have sounded incredibly controversial a few years ago.

FINAL vs. argMax(): The Tradeoff Has Shifted

To avoid the historical overhead of FINAL, ClickHouse users routinely relied on complex query patterns like argMax() to fetch the latest state of a record:

SELECT
    id,
    argMax(status, version)
FROM users
GROUP BY id;

For older versions and massive scale workloads, this aggregation pattern made total sense. However, because modern ClickHouse executes FINAL so much more efficiently, the tradeoff is no longer one-sided. In many latest-state query scenarios, choosing FINAL is now simpler, easier to maintain, and completely reasonable depending on your filters and partition design.

Old Rule: Always use argMax() or complex GROUP BY clauses.
Modern Reality: Weigh FINAL based on actual workload performance, not old assumptions.

So… Is FINAL Safe to Use Now?

Nuance matters here. The takeaway isn’t that FINAL is suddenly bad, nor is it magically free. FINAL is much more practical in modern ClickHouse, but workload design still dictates its validity.

Where FINAL Makes Sense Today

There are completely legitimate use cases where FINAL is reasonable to deploy:

• Latest-state queries on tightly bound datasets.
• Smaller, well-defined partitions where the merge surface area is minimal.
• Low-latency operational analytics where query simplicity trumps marginal performance gains.
• Deduplicated views over highly mutable datasets.

Where FINAL Can Still Hurt

Even with modern parallel processing, FINAL isn't magic. It can still degrade performance when:

• Scanning massive analytical datasets across dozens of partitions.
• Querying tables where background merges are heavily delayed or part counts have exploded.
• Operating over poorly designed schemas with incorrect partitioning keys.

For example, running a query like this over a massive historical log table can still force a substantial, expensive amount of real-time processing:

SELECT *
FROM massive_events_table
FINAL
WHERE timestamp >= now() - INTERVAL 30 DAY;

A Crucial Distinction: SELECT vs. OPTIMIZE

It is also important to separate query-time modifiers from storage-level mutations. New users frequently confuse these two operations:

• SELECT * FROM users FINAL; — This applies merge logic on the fly during query execution. The underlying data on disk remains unchanged.
• OPTIMIZE TABLE users FINAL; — This forces a heavy, immediate storage-level merge of the physical data parts on disk.

While the former is a query runtime behavior that is now highly optimized, the latter is a heavy, storage-blocking operation that should still be used with extreme caution on production datasets.

Final Thought: Let Your Evolution Match the Engine

One of the most interesting aspects of working with ClickHouse is how quickly operational best practices evolve alongside the engine itself. Advice that was absolutely correct three years ago can become completely outdated today.

Instead of aggressively avoiding FINAL based on legacy community threads, the modern approach is to understand how your data is partitioned, profile the query, and evaluate if the simplicity of FINAL makes sense for your specific workload. Stop treating it like an architectural disaster, and start treating it like the optimized tool it has become.