Running OPTIMIZE and VACUUM on Delta tables

Delta tables can accumulate many small files from frequent writes, which slows down query performance; OPTIMIZE compacts these small files into larger ones, and VACUUM removes stale, unreferenced data files to reduce storage costs. Together they are the core maintenance commands for keeping Delta tables performant and cost-efficient on Azure Databricks.

1 · Learn the must-know

• 1OPTIMIZE compacts small files into larger ones (up to 1GB by default) using bin-packing, improving read performance without changing table data or history.
• 2ZORDER BY can be combined with OPTIMIZE (e.g., OPTIMIZE table ZORDER BY col) to co-locate related data in the same files, improving data-skipping performance for filters on those columns.
• 3VACUUM permanently deletes files no longer referenced by the Delta transaction log and older than a retention threshold, defaulting to 7 days (168 hours).
• 4Running VACUUM with a retention period shorter than the default requires disabling the safety check (spark.databricks.delta.retentionDurationCheck.enabled=false) and can break time travel or concurrent readers/writers relying on those files.
• 5Predictive optimization (where available) can automatically run OPTIMIZE and VACUUM on Unity Catalog managed tables based on usage patterns, reducing the need for manually scheduled maintenance jobs.
• 6Auto Optimize (optimizeWrite and autoCompact table properties) can reduce small-file creation at write time, complementing scheduled OPTIMIZE jobs rather than replacing the need for periodic VACUUM.

2 · Check your understanding