Delta table maintenance: OPTIMIZE vs VACUUM vs V-Order vs Optimized Write
Verdict: OPTIMIZE compacts existing small files; Optimized Write prevents them on write; VACUUM only deletes unreferenced files and too-short retention breaks readers; V-Order is a write-time read optimisation, disable it on write-heavy rarely-read tables.
| Criterion | OPTIMIZE | VACUUM | V-Order | Optimized Write |
|---|---|---|---|---|
| What it does | Compacts many small files into fewer larger files | Deletes files no longer referenced by the Delta log | Writes files in a sorted layout for faster reads | Merges or splits partitions before writing to avoid small files |
| When to run | After frequent small or incremental writes slow reads | To reclaim storage from obsolete files | On tables read far more than written | On high-frequency streaming ingestion |
| Main risk | None; safe in-place compaction | Retention below 7 days breaks concurrent reads and time travel | About 15% extra write time; wrong on write-heavy rarely-read tables | None; automatic sizing |
| Applies to | Delta tables only, not legacy Hive Parquet/ORC/CSV | Delta tables; disable retentionDurationCheck to go under 7 days | Warehouses on by default; newer workspaces have it off | Enable via spark.databricks.delta.optimizeWrite.enabled |
Rules
- OPTIMIZE compacts small files; run it after streaming or micro-batch writes fragment a table.
- VACUUM only removes files no longer referenced by the Delta log; it does not compact active small files.
- Setting VACUUM retention below seven days risks deleting files that concurrent reads or time-travel queries still reference.
- To VACUUM under the seven-day default, set spark.databricks.delta.retentionDurationCheck.enabled to false.
- Disable V-Order on write-heavy, rarely-queried tables: it adds about 15% write time for up to 50% read compression.
- Table maintenance applies only to Delta tables; legacy Hive Parquet, ORC, AVRO or CSV tables are unsupported.
Traps
- VACUUM does not compact small files; choosing it for a small-file problem is wrong.
- Automatic checkpointing, not V-Order, summarises the transaction log to cut metadata read cost.
- Enabling V-Order to fix an ingestion bottleneck worsens it; V-Order increases write overhead.
- Optimized Write, not a longer trigger interval alone, automatically merges or splits partitions.