Back to Databricks & Delta Lake
Q6Question 6 of 7

What is the difference between Databricks `OPTIMIZE` and `VACUUM`

Key Concepts
  • OPTIMIZE compacts many small data files into fewer larger ones to improve read performance; it does not change the data.
  • ZORDER BY co-locates related data within files so Delta's min/max statistics can skip files on filtered queries.
  • VACUUM permanently deletes old unreferenced data files beyond the retention period to reclaim storage.
  • Vacuuming below the retention window breaks time travel because it destroys the files that historical versions relied on.
  • Storage temporarily grows after OPTIMIZE because old files linger until VACUUM removes them past the retention period.
  • The healthy pattern is: OPTIMIZE regularly for read speed, VACUUM periodically for storage, and set retention to match audit requirements.

Definition

OPTIMIZE compacts small files and can ZORDER for faster reads, while VACUUM permanently deletes old unreferenced files to save storage, and vacuuming below the retention window breaks time travel.

OPTIMIZE and VACUUM are two Delta Lake maintenance commands that solve different problems.

  • OPTIMIZE – Compacts many small data files into fewer larger ones to improve read performance. Optionally applies ZORDER BY to co-locate related data for better data skipping.
  • VACUUM – Permanently deletes data files no longer referenced by the current table version and older than the retention threshold (default 7 days).
  • ZORDER – Physically reorders data within files so rows with similar values of the ZORDER columns sit together, improving min/max-based file skipping.
  • Retention danger – Vacuuming below the retention period removes files that historical versions still need, breaking time travel and in-flight readers.

OPTIMIZE is for read speed; VACUUM is for storage cost. Both are part of healthy Delta table maintenance.

Imagine the National Bank of India stores trade records as loose slips in filing drawers. OPTIMIZE is a clerk who gathers thousands of tiny loose slips and binds them into a few thick, well-organized folders so finding records is far faster. The ZORDER part is sorting those folders by the fields you search most, like date and symbol, so you can skip whole folders. VACUUM is a different clerk: the shredder operator who goes through the archive room and permanently shreds old discarded drafts older than the safety window. If you shred too aggressively, below the safety window, you destroy the old drafts that time travel relied on to reconstruct the records from last week.

Understanding the Concepts

What is the small-files problem?

Delta Lake tables accumulate many small files over time from streaming writes, frequent small batches, or high-cardinality partitioning. Many small files hurt reads because the engine pays metadata and task-scheduling overhead per file.

PYTHON
# Check file count and sizes for a Delta table
from delta.tables import DeltaTable

dt = DeltaTable.forPath(spark, "/data/bank/transactions")
detail = dt.detail().select("numFiles", "sizeInBytes").collect()[0]
print(f"Files: {detail['numFiles']}, Size: {detail['sizeInBytes'] / (1024**3):.2f} GB")

# Example: 50,000 small files of 1 MB each = slow reads
# After OPTIMIZE: 500 files of 100 MB each = fast reads

How does OPTIMIZE work?

OPTIMIZE reads the small files in each partition and rewrites them into fewer larger files, typically around the ideal size of a few hundred megabytes. The row contents are identical: it is a compaction operation that does not change the data.

SQL
-- Basic compaction
OPTIMIZE banking_prod.transactions;

-- Compact only a specific partition
OPTIMIZE banking_prod.transactions
WHERE txn_date = '2025-06-15';
DIAGRAM
Before OPTIMIZE:
  Partition txn_date=2025-06-15/
  ├── part-00001.parquet  (2 MB)
  ├── part-00002.parquet  (1 MB)
  ├── part-00003.parquet  (3 MB)
  ├── ... (1000 small files)
  └── part-01000.parquet  (1 MB)

After OPTIMIZE:
  Partition txn_date=2025-06-15/
  ├── part-00001-compacted.parquet  (256 MB)
  ├── part-00002-compacted.parquet  (256 MB)
  └── part-00003-compacted.parquet  (128 MB)
  (plus old files still on disk until VACUUM)

How does ZORDER work?

ZORDER BY physically reorders the data within files so that rows with similar values of the ZORDER columns sit together. This improves Delta's per-file min/max statistics, enabling queries to skip files whose range does not match the filter.

SQL
-- OPTIMIZE with ZORDER on the columns you filter most
OPTIMIZE banking_prod.transactions
ZORDER BY (account_id, txn_date);
DIAGRAM
Without ZORDER (random distribution):
  File 1: account_ids A-Z, dates Jan-Dec → min/max spans everything
  File 2: account_ids A-Z, dates Jan-Dec → no file can be skipped

With ZORDER (co-located):
  File 1: account_ids A-D, dates Jan-Mar → tight min/max
  File 2: account_ids E-H, dates Jan-Mar → tight min/max
  Query: WHERE account_id = 'ACC-1001' → skip all files except File 1
PYTHON
# Verify data skipping improvement
spark.sql("""
    SELECT account_id, amount
    FROM banking_prod.transactions
    WHERE account_id = 'ACC-1001'
""").explain()
# After ZORDER, the physical plan shows fewer files scanned

How does VACUUM work?

VACUUM permanently deletes data files that are no longer referenced by the current table version and are older than the retention period. It never touches files the current version still references.

SQL
-- VACUUM with default 7-day retention
VACUUM banking_prod.transactions;

-- VACUUM with explicit retention
VACUUM banking_prod.transactions RETAIN 30 DAYS;
PYTHON
# Check what VACUUM would delete (dry run)
spark.sql("VACUUM banking_prod.transactions RETAIN 30 DAYS DRY RUN").show()

Why is vacuuming below the retention dangerous?

Time travel to an older version requires the files that version pointed to. If you vacuum away files newer than the retention, you destroy files that recent historical versions still need, making time travel queries fail.

SQL
-- DANGER: vacuuming with 0 hours retention
-- Delta REFUSES by default with this error:
-- "Are you sure you want to vacuum files older than 0 hours?
--  This may break time travel and concurrent readers."

-- You must explicitly override the safety check:
SET spark.databricks.delta.retentionDurationCheck.enabled = false;
VACUUM banking_prod.transactions RETAIN 0 HOURS;
-- DO NOT do this on production banking tables
TEXT
What happens when you vacuum too aggressively:

  Version 3 references files: [f1, f2, f3]
  Version 5 (current) references: [f4, f5]

  VACUUM RETAIN 0 HOURS:
  Deletes f1, f2, f3 (not referenced by current version)

  SELECT * FROM table VERSION AS OF 3;
  ERROR: FileNotFoundException: f1 was deleted by VACUUM

What is the interaction between OPTIMIZE and VACUUM?

OPTIMIZE creates new compacted files and leaves the old small files as unreferenced history. Storage temporarily grows. VACUUM is what eventually reclaims that space once the old files age past retention.

DIAGRAM
Timeline:
  Day 1: Table has 1000 small files (10 GB)
  Day 1: OPTIMIZE → 10 compacted files (10 GB)
           Old 1000 files still on disk → storage = 20 GB
  Day 8: VACUUM RETAIN 7 DAYS → deletes old 1000 files
           Storage = 10 GB (back to normal)
SQL
-- Healthy maintenance schedule
-- Run OPTIMIZE daily for read speed
OPTIMIZE banking_prod.transactions
ZORDER BY (account_id, txn_date);

-- Run VACUUM weekly for storage
VACUUM banking_prod.transactions RETAIN 30 DAYS;

What about auto-compaction and optimized writes?

Databricks offers automatic features that reduce the small-files problem at write time, so you need less manual OPTIMIZE.

SQL
-- Enable auto-compaction: Databricks compacts after writes
ALTER TABLE banking_prod.transactions
SET TBLPROPERTIES ('delta.autoOptimize.autoCompact' = 'true');

-- Enable optimized writes: better file sizes during writes
ALTER TABLE banking_prod.transactions
SET TBLPROPERTIES ('delta.autoOptimize.optimizeWrite' = 'true');

What should a senior engineer know?

  • OPTIMIZE is safe and non-destructive: run it anytime. VACUUM is destructive and irreversible for the files it removes.
  • Size the retention to match your business and compliance needs. Banks often need 30+ days for audit.
  • Never shorten retention just to save storage: the cost of losing time travel is far higher.
  • Predictive optimization (Databricks feature) automatically manages OPTIMIZE and VACUUM for you.
  • ZORDER helps columns you filter by equality; it does not help range queries as much as sorting.
PYTHON
# Monitor table health
dt = DeltaTable.forPath(spark, "/data/bank/transactions")
history = dt.history(10)  # Last 10 operations
history.select("version", "timestamp", "operation", "operationMetrics").show(truncate=False)

Visual Overview

DIAGRAM
  OPTIMIZE (read speed):

  Before:                    After:
  ┌────┐┌────┐┌────┐        ┌──────────────────┐
  │1 MB││1 MB││2 MB│  ──►   │ 256 MB compacted  │
  └────┘└────┘└────┘        └──────────────────┘
  ┌────┐┌────┐┌────┐        ┌──────────────────┐
  │1 MB││3 MB││1 MB│  ──►   │ 256 MB compacted  │
  └────┘└────┘└────┘        └──────────────────┘
  (1000 small files)         (4 large files)

  VACUUM (storage):

  Day 1: OPTIMIZE runs
  ┌─────────────┐  ┌─────────────┐
  │ Old files   │  │ New compacted│
  │ (unreferenced│  │ files       │
  │  but on disk)│  │ (current)   │
  └─────────────┘  └─────────────┘
   Storage: 2x

  Day 8: VACUUM RETAIN 7 DAYS
  ┌─────────────┐
  │ Old files   │ ──► DELETED
  └─────────────┘
   Storage: 1x (reclaimed)

Key Takeaway

OPTIMIZE and VACUUM are two Delta Lake maintenance commands that solve different problems. OPTIMIZE is about read speed: it compacts many small files into fewer larger ones, fixing the small-files problem from streaming writes and frequent batches. ZORDER BY co-locates data by the columns you filter most, so Delta's min/max statistics can skip files that cannot match your query. OPTIMIZE is non-destructive and safe to run anytime.

VACUUM is about reclaiming storage. Because Delta is versioned, every write leaves old files on disk for time travel and concurrent readers. Over time these pile up. VACUUM permanently deletes files no longer referenced by the current version and older than the retention period. The critical rule is that vacuuming below the retention window is dangerous: it destroys files that historical versions relied on, breaking time travel queries.

At the National Bank of India, our streaming job wrote thousands of tiny files daily, slowing analytical queries. We scheduled OPTIMIZE with ZORDER by trade date and account_id, which compacted files and made filtered queries skip most of them. Storage grew temporarily after each OPTIMIZE, so we scheduled VACUUM with 30-day retention to match the audit team's requirement for time travel. We never shortened the retention just to save storage.

The mental model is: OPTIMIZE for speed, VACUUM for space, and the retention period is a safety boundary that protects time travel and must match your compliance window.

Remember one sentence: OPTIMIZE compacts files for read speed, VACUUM reclaims storage by deleting old files, and the retention period protects time travel and must never be shortened below your audit requirements.

---

Share
Use ← → arrow keys to navigate