Setting the file. One moment.
Chapter 04 · Clickhouse Best Practices
Subchapter 4.14
rules/insert-optimize-avoid-final.mdMarkdown2 KBView on GitHub
Impact: HIGH
OPTIMIZE TABLE ... FINAL forces immediate merge of all parts into one part per partition. This is resource-intensive and rarely necessary. ClickHouse already performs smart background merges.
Note: OPTIMIZE FINAL is not the same as FINAL. The FINAL modifier in SELECT queries may be necessary for deduplicated results in ReplacingMergeTree and is generally fine to use.
Incorrect (OPTIMIZE FINAL after inserts):
-- Running OPTIMIZE FINAL after every batch insert
INSERT INTO events SELECT * FROM staging_events;
OPTIMIZE TABLE events FINAL; -- Expensive and unnecessary!
-- Scheduled OPTIMIZE FINAL jobs
-- Cron: 0 * * * * clickhouse-client -q "OPTIMIZE TABLE events FINAL"Correct (let background merges work):
-- Let background merges handle optimization
INSERT INTO events SELECT * FROM staging_events;
-- Done! ClickHouse merges automatically
-- For ReplacingMergeTree deduplication, use FINAL in queries
SELECT * FROM events FINAL WHERE user_id = 123;
-- Instead of running OPTIMIZE FINAL to deduplicateProblems with OPTIMIZE FINAL:
When OPTIMIZE FINAL may be acceptable:
Better alternatives:
| Need | Alternative |
|---|---|
| Deduplicate ReplacingMergeTree | Use FINAL modifier in SELECT |
| Reduce part count | Rely on background merges |
Reference: Avoid OPTIMIZE FINAL (opens in a new tab)