Optimize table

Optimize table maintenance rewrites one table's files so future queries scan fewer, better-organized Parquet files. Use it after large ingests, many small appends, or update-heavy workloads that leave data spread across many files.

Optimization is housekeeping for an Altertable catalog table. It does not change the logical rows in the table; it changes the physical layout used by the query engine.

SQL example

Run OPTIMIZE TABLE from SQL after a large ingest or a period of frequent small writes:

Copy
OPTIMIZE TABLE my_catalog.main.events;

The command returns optimization work by tier, such as cold, warm, and hot, including the number of files considered and rewritten.

What optimization does

For the target table, optimization can:

• Compact many small files into larger files.
• Apply the table's current sort order when rewriting data.
• Improve file-level statistics used for pruning.
• Reduce metadata and planning overhead for frequently queried tables.

When to run it

Optimize tables that receive frequent small writes or show slower query planning over time. It is most useful for large tables that are queried repeatedly by dashboards, Ask AI, scheduled tasks, or external BI tools.

Skip optimization for tiny tables or tables that are about to be fully replaced. The rewrite costs compute now to improve repeated reads later.

Learn more

• SQL Explorer: run maintenance SQL interactively.
• Partitioning: split data by partition keys.
• Sorted tables: configure physical sort order.
• Performance: understand cache behavior and benchmarking.