1. Home
  2. Knowledge Base
  3. Optimizing Queries in ClickHouse with OPTIMIZE and OPTIMIZE FINAL

Optimizing Queries in ClickHouse with OPTIMIZE and OPTIMIZE FINAL

ClickHouse is a popular open-source columnar database management system known for its high performance and efficient query execution. To maintain optimal performance and manage storage resources effectively, ClickHouse provides the OPTIMIZE and OPTIMIZE FINAL commands. These commands are essential for cleaning up and optimizing data in ClickHouse tables. In this knowledge base article, we will explore how to use these commands and provide examples to illustrate their functionality.

Understanding OPTIMIZE

The OPTIMIZE command in ClickHouse is used to perform various optimization tasks on tables, such as merging parts, cleaning up obsolete data, and restructuring the data for better query performance. It is especially useful for maintaining tables that experience frequent inserts, updates, or deletes.

Understanding OPTIMIZE FINAL

The OPTIMIZE FINAL command in ClickHouse is an extended version of the OPTIMIZE command. It performs the final steps of optimization, which include merging all the parts of the table and effectively reducing the table to a single part, ensuring the most compact and efficient storage.


Using OPTIMIZE for Table Maintenance

Suppose you have a table named my_table in a database called my_db that experiences frequent inserts and updates. To optimize this table and clean up obsolete data, you can use the following command:

OPTIMIZE TABLE my_db.my_table FINAL;

This command will perform a final optimization of the entire table, removing any outdated data and merging the parts for better performance.

Using OPTIMIZE FINAL for Ultimate Optimization

If you want to perform the most comprehensive optimization on your table, you can use OPTIMIZE FINAL:

OPTIMIZE FINAL TABLE my_db.my_table;

This command will perform the final optimization, merging all the parts into a single part for the most efficient storage and query performance. The original table will still exist, but the optimized data will be stored in a new table.

Was this article helpful?


In the spirit of freedom, independence and innovation. ChistaDATA Corporation is not affiliated with ClickHouse Corporation 

Need Support?

Can't find the answer you're looking for?
Contact Support

ChistaDATA Inc. Knowledge base is licensed under the Apache License, Version 2.0 (the “License”)

Copyright 2022 ChistaDATA Inc

Licensed under the Apache License, Version 2.0 (the “License”); you may not use this file except in compliance with the License. You may obtain a copy of the License at


Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an “AS IS” BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License.