Predictive Optimization & Table Maintenance
Databricks · Architecture, Performance & Cost
Theory
Delta tables need regular housekeeping. OPTIMIZE compacts many small files into fewer large ones and applies clustering. VACUUM deletes data files no longer referenced by the table and older than the retention period (7 days by default), which saves storage but limits time travel. ANALYZE TABLE ... COMPUTE STATISTICS collects statistics the optimizer uses to choose join strategies. Liquid clustering (CLUSTER BY columns, or CLUSTER BY AUTO to let Databricks pick keys) replaces partitioning and ZORDER for most tables. Predictive optimization runs OPTIMIZE, VACUUM and ANALYZE automatically on Unity Catalog managed tables, deciding when each is worth doing from usage, so you do not schedule maintenance jobs yourself. It can be enabled or disabled at account, catalog or schema level.
Example: after ALTER CATALOG main ENABLE PREDICTIVE OPTIMIZATION, main.sales.orders is compacted and vacuumed automatically.
Why we use it
Streaming and frequent small writes create thousands of tiny files and stale versions, slowing queries and growing storage. Maintenance fixes that, and predictive optimization removes the need to guess schedules or write cleanup jobs.
When to use it
Use Unity Catalog managed tables with predictive optimization and liquid clustering as the default. Run OPTIMIZE or VACUUM by hand only for external tables or special cases. Avoid over-partitioning small tables; partitions under about 1 GB usually hurt more than help.
How it works
- Writes create small files and old versions
- Is it a Unity Catalog managed table with predictive optimization on? Yes: Databricks schedules OPTIMIZE, VACUUM, ANALYZE. No: Schedule them in a job yourself.
- OPTIMIZE compacts files and applies liquid clustering
- VACUUM deletes unreferenced files older than retention
- ANALYZE refreshes statistics for the optimizer
- Queries scan fewer, larger, better-clustered files
Syntax
ALTER CATALOG|SCHEMA <name> ENABLE | DISABLE | INHERIT PREDICTIVE OPTIMIZATION;
OPTIMIZE <table> [FULL];
VACUUM <table> [RETAIN <n> HOURS];
ANALYZE TABLE <table> COMPUTE STATISTICS [FOR ALL COLUMNS];
CREATE TABLE ... CLUSTER BY (<cols>) | CLUSTER BY AUTO; ALTER TABLE <t> CLUSTER BY (<cols>);
Check: DESCRIBE DETAIL <table>; system.storage.predictive_optimization_operations_historyExample
-- Let Databricks maintain every managed table in the catalog
ALTER CATALOG main ENABLE PREDICTIVE OPTIMIZATION;
-- Liquid clustering on the columns used in filters
ALTER TABLE main.sales.orders CLUSTER BY (order_date, region);
-- or let Databricks choose keys from query patterns
ALTER TABLE main.sales.customers CLUSTER BY AUTO;
-- Manual maintenance (external tables or one-off fixes)
OPTIMIZE main.sales.orders;
VACUUM main.sales.orders; -- default 7-day retention
ANALYZE TABLE main.sales.orders COMPUTE STATISTICS FOR ALL COLUMNS;
DESCRIBE DETAIL main.sales.orders; -- numFiles, sizeInBytes, clusteringColumnsKey points
- Predictive optimization works on Unity Catalog managed tables only
- It runs OPTIMIZE, VACUUM and ANALYZE when they are worth the cost
- VACUUM removes old files, so time travel beyond retention stops working
- Liquid clustering replaces partitioning and ZORDER for most new tables
- CLUSTER BY AUTO picks clustering keys from your query patterns
- Operations history is in system.storage.predictive_optimization_operations_history