ClickHouse FINAL Demystified: Query Execution vs Storage Operations

ClickHouse developers frequently confuse SELECT FINAL and OPTIMIZE TABLE FINAL, assuming both perform data merging. SELECT FINAL only temporarily applies merge logic during query execution to display results, while OPTIMIZE TABLE FINAL physically rewrites data on disk—a critical distinction for managing MergeTree tables effectively. Understanding this difference is essential for avoiding performance bottlenecks and ensuring data consistency in production environments.