OPTIMIZE TABLE ... FINAL in ClickHouse: when to use it, when to avoid it, and how merges work

Short summary

OPTIMIZE TABLE ... FINAL forces a complete physical rewrite of active ClickHouse parts, consuming significant I/O and CPU—don't schedule it on a cron job without understanding the cost. The operation creates oversized parts that bypass the background merge scheduler's guardrails, leading to duplicate accumulation and slower queries. Instead, fix the root cause by improving ingestion: batch inserts into larger blocks and target roughly one insert per second to keep part counts healthy.

• •OPTIMIZE TABLE ... FINAL bypasses merge scheduler guardrails and consumes significant resources—avoid scheduling it without understanding the cost
• •Forced merges create oversized parts that can accumulate duplicates, degrade query performance, and increase maintenance costs
• •Fix the root cause by improving ingestion patterns: batch into larger blocks and target ~1 insert per second to prevent 'too many parts' errors

Generated with AI, which can make mistakes.