skip to content

In ClickHouse, why is OPTIMIZE TABLE ... FINAL a poor routine deduplication strategy?

level: seniorimportance: should knowfreq 52%

answer

  1. it rewrites data, it does not annotate it
  2. the background scheduler refuses this on purpose
  3. one enormous part per partition afterwards
  4. new inserts can no longer be folded in
  5. fix reads at query time, not with maintenance

basics

~20 s

It merges every partition down to a single part, rewriting the whole table's data on each run, and leaves parts too large for normal background merges. Use query-time FINAL or argMax instead, and keep OPTIMIZE for one-off, per-partition cleanups.

solid answer

~50 s

`OPTIMIZE TABLE t FINAL` forces a merge of **all** parts in each partition into one, ignoring the size limits that normally protect the background merge scheduler. That means reading and rewriting every byte of the table, every time you run it — enormous write amplification for the sake of removing a handful of duplicate rows. It also poisons future merges. The single giant part it produces exceeds the maximum size the scheduler will merge, so new inserts accumulate as small parts beside it that can only be folded in by rewriting the whole thing again. A scheduled `OPTIMIZE ... FINAL` therefore gets more expensive over time, and it competes with ordinary merges for the same background thread pool. The right tools are read-time: `SELECT ... FINAL`, or `GROUP BY key` with `argMax`. Reserve `OPTIMIZE` for genuine one-offs — after a backfill, scoped with `OPTIMIZE TABLE t PARTITION ... FINAL` on a partition that will receive no more writes.

code

sql · 16 lines
sql
-- Anti-pattern: scheduled full-table rewrite to "keep it deduplicated"
OPTIMIZE TABLE users FINAL;

-- Better: resolve at read time
SELECT user_id, argMax(name, updated) AS name
FROM users
GROUP BY user_id;

-- Or expose it so nobody reads the raw table
CREATE VIEW users_current AS
SELECT user_id, argMax(name, updated) AS name
FROM users
GROUP BY user_id;

-- Legitimate maintenance: one closed partition, once, after a backfill
OPTIMIZE TABLE users PARTITION '2026-01' FINAL;

go deeper

for a junior

Know that OPTIMIZE forces a merge rather than flagging rows, and that it is a maintenance command, not something a query should depend on.

for a middle

Explain that FINAL merges every partition into one part by rewriting all its data, and that background merges are size-bounded precisely to keep write amplification finite.

for a senior

Demonstrate the operational picture: merge-pool starvation, delayed and rejected inserts, and the giant part that makes each subsequent run more expensive. Offer read-time resolution as the real fix.

for a principal

Own the policy. Decide whether the platform exposes raw tables or resolving views, where deduplication cost is paid, and how maintenance windows are scoped so a routine command cannot take ingestion down.

## What the statement actually does In ClickHouse, `OPTIMIZE TABLE t` asks the server to run an unscheduled merge. Adding `FINAL` changes it from "merge if useful" to "merge everything in each partition into exactly one part, even if the scheduler would never choose to". For a `ReplacingMergeTree`, `CollapsingMergeTree` or `SummingMergeTree`, that is also when the variant's row-collapsing logic runs across the whole partition — which is precisely why people reach for it as a deduplication button. ```sql OPTIMIZE TABLE users FINAL; -- whole table OPTIMIZE TABLE users PARTITION '2026-01' FINAL; -- one partition ``` ## Why the background scheduler does not do this on its own Ordinary merges are deliberately bounded. The scheduler picks parts to merge based on size and count, and refuses to merge beyond a maximum resulting size (`max_bytes_to_merge_at_max_space_in_pool`). This is not laziness — it is the mechanism that keeps write amplification finite. Merging is a full rewrite of the participating data, so if the engine merged every part into one every time, ingesting N bytes would cost far more than N bytes of I/O. `OPTIMIZE ... FINAL` overrides exactly that protection. ## The three costs **Full rewrite, every run.** Every byte of every partition is read, decompressed, merged, recompressed and written. On a multi-terabyte table this is hours of I/O and CPU to remove rows that may amount to a fraction of a percent of the data. **Merge-pool starvation.** The rewrite runs on the same background merge and mutation thread pool that keeps ordinary ingestion healthy. While it runs, normal merges are starved, active part counts climb, and inserts can start being delayed and then rejected. A scheduled `OPTIMIZE ... FINAL` on a busy table is a self-inflicted ingestion incident. **A part that can never merge again.** After the run each partition holds one enormous part. New inserts land beside it as small parts. Because the giant part exceeds the scheduler's size ceiling, background merges will not fold new data into it, so those small parts accumulate and only merge among themselves. The next `OPTIMIZE ... FINAL` therefore has to rewrite the giant part all over again. The strategy gets strictly worse the longer you run it. There is a second-order effect too: `OPTIMIZE` is not free to reason about operationally. It occupies the issuing session while the merge runs, its behaviour on replicated tables depends on how long you are willing to wait for replicas, and by default a no-op does nothing silently rather than telling you. ## What to do instead **Resolve at read time.** `SELECT ... FINAL` merges the matching rows during the query and is a legitimate production pattern on reasonably-merged tables; modern versions parallelise it well. Alternatively resolve explicitly with aggregation: ```sql SELECT user_id, argMax(name, updated) AS name FROM users GROUP BY user_id; ``` Expose whichever you choose through a view so consumers cannot read the raw table by accident. **Design so duplicates are cheap.** Get the sorting key right so duplicates actually collapse; partition so that all versions of a key land in the same partition; batch inserts so parts are large enough to merge efficiently in the first place. **Push the resolution downstream.** If a rollup or a materialized target already aggregates by the key, duplicates in the source may not matter at all. ## Where OPTIMIZE is genuinely right It is a maintenance tool, not a query tool. Legitimate uses: - **After a one-off backfill**, on the partitions you just wrote, before opening the table to readers. - **On a closed partition** — yesterday's or last month's data that will never receive another insert — where the rewrite happens once and the resulting part is stable. Always scope with `PARTITION` so you rewrite that data only. - **Occasionally, deliberately, with a human watching**, on a table whose ingestion has been paused. ## How to answer Lead with the write amplification — a full rewrite per run — then the merge-pool contention, then the trap of the unmergeably large part that makes the next run worse. Finish by naming the read-time alternatives and the narrow, partition-scoped case where `OPTIMIZE` is the correct tool. Candidates who propose it in a cron job are telling the interviewer they have not operated a large ClickHouse table.

  • Why does the background merge scheduler refuse to merge parts beyond a maximum size?
    Because a merge is a full rewrite of the participating data. Without a ceiling, every new part would eventually be folded into the largest one, so ingesting a byte would cost repeatedly rewriting the whole table. The size limit bounds write amplification, trading a few extra parts at read time for sane and predictable write cost. `OPTIMIZE ... FINAL` deliberately bypasses that trade.
  • How does running OPTIMIZE ... FINAL affect concurrent ingestion?
    It consumes the same background merge and mutation threads that ordinary merges need, so while it runs, normal merging falls behind. The active part count climbs, and once it crosses ClickHouse's thresholds inserts are first delayed and then rejected outright. On a busy table this turns a maintenance command into an ingestion outage.
  • Is SELECT ... FINAL always the right alternative?
    Not always. `FINAL` must read overlapping key ranges together, so it does more work per row than a plain scan and hurts most on full-table scans or on tables with many unmerged parts. On selective queries against a well-merged table it is usually fine and far cheaper than rewriting the table. Where it is too slow, `GROUP BY` with `argMax` on just the needed columns is the fallback.
  • When is OPTIMIZE ... FINAL genuinely the right command to run?
    When the data being rewritten is finished. After a one-off backfill, or on a partition that will receive no further inserts, a single scoped `OPTIMIZE TABLE t PARTITION ... FINAL` collapses duplicates once and produces a stable part. Scope it to the partition, run it while ingestion pressure is low, and do not put it on a schedule.

It is like re-typesetting an entire book to correct two typos — and then binding it so tightly that tomorrow's errata can only be added by re-typesetting the whole thing again.

saying these in an interview costs you the question

  • Schedules OPTIMIZE ... FINAL in cron to keep a table deduplicated
  • Assumes OPTIMIZE only touches the duplicate rows
  • Ignores that it competes with ordinary merges for the thread pool
  • Does not realise the resulting giant part stops merging normally
  • Runs it unscoped on a multi-terabyte table during peak ingestion

context

resolving links…