# Delta Lake OPTIMIZE, liquid clustering and VACUUM: keep a table fast and small

Every write to a Delta table adds files, so a table written often ends up with many small ones, and a query must open each. OPTIMIZE rewrites small files into larger ones. Liquid clustering, CLUSTER BY on the table, makes OPTIMIZE also group rows by the columns you filter on, so Delta can skip the files that cannot hold a match. VACUUM deletes data files that no version within the retention period, 7 days by default, still needs; after it, time travel to older versions fails.

- Page: https://tecyfy.com/data-engineering/course/delta-lake-and-iceberg/optimize-and-vacuum
- Course: Data engineering course (https://tecyfy.com/data-engineering/course), Module 4: Tables: Parquet, Delta Lake and Iceberg, OPTIMIZE, clustering and VACUUM
- Parts of this module: The transaction log and time travel (https://tecyfy.com/data-engineering/course/delta-lake-and-iceberg); MERGE (https://tecyfy.com/data-engineering/course/delta-lake-and-iceberg/merge); Schema enforcement and evolution (https://tecyfy.com/data-engineering/course/delta-lake-and-iceberg/schema); OPTIMIZE, clustering and VACUUM (https://tecyfy.com/data-engineering/course/delta-lake-and-iceberg/optimize-and-vacuum); Iceberg and UniForm (https://tecyfy.com/data-engineering/course/delta-lake-and-iceberg/iceberg-and-uniform); Build it: an incremental load (https://tecyfy.com/data-engineering/course/delta-lake-and-iceberg/build-it)

At the end of this part you can:

- Explain why a table written often gets slower, and fix it with OPTIMIZE
- Cluster a table with CLUSTER BY so a filter reads fewer files
- Run VACUUM safely, and say what it costs time travel

[Part 1](/data-engineering/course/delta-lake-and-iceberg) showed that Delta never edits a file: every write, update and merge adds new ones. That keeps old versions readable, and it has 2 costs. Tables written often fill up with small files, and the folder keeps every file any version ever used. This part fixes both, on a table big enough to see it.

## A day of small writes

Orders often arrive in small batches, every hour or every minute. Here are module 3's 2 million orders, loaded as 24 hourly appends into 2 tables, 1 of them clustered by `customer_id`. Save this as `layout.py`:

```python
from pyspark.sql import functions as F
from delta_session import delta_spark

spark = delta_spark()
# 2 million orders from 50,000 customers, as in module 3.
orders = spark.range(2_000_000).select(
    F.col("id").alias("order_id"),
    (F.col("id") % 50_000).alias("customer_id"),
    ((F.col("id") % 9_000) / 100).cast("decimal(10,2)").alias("amount"),
)
COLUMNS = "order_id BIGINT, customer_id BIGINT, amount DECIMAL(10,2)"


def load(table, cluster_by=""):
    """A day of orders, appended once an hour: 24 small writes."""
    spark.sql(f"DROP TABLE IF EXISTS {table}")
    spark.sql(f"CREATE TABLE {table} ({COLUMNS}) USING DELTA {cluster_by}")
    for hour in range(24):
        orders.filter(F.col("order_id") % 24 == hour).write.format("delta").mode("append").saveAsTable(table)


def report(table, when):
    files = spark.sql(f"DESCRIBE DETAIL {table}").first().numFiles
    read = len(spark.table(table).filter("customer_id = 42").inputFiles())
    print(f"{table:13} {when:15} {files:3} files, customer 42 reads {read}")


load("hourly")
load("hourly_c", "CLUSTER BY (customer_id)")
spark.conf.set("spark.databricks.delta.optimize.maxFileSize", 2 * 1024 * 1024)
for table in ["hourly", "hourly_c"]:
    report(table, "after the day")
    spark.sql(f"OPTIMIZE {table}")
    report(table, "after OPTIMIZE")
```

```text
hourly        after the day    96 files, customer 42 reads 96
hourly        after OPTIMIZE    8 files, customer 42 reads 8
hourly_c      after the day    96 files, customer 42 reads 96
hourly_c      after OPTIMIZE    7 files, customer 42 reads 1
```

`inputFiles()` lists the files a query would open after Delta has skipped the ones it can. Each append wrote 4 files, 1 per task, so after a day both tables hold 96 small files, and a query for 1 customer opens all of them. Opening a file costs time whatever its size, so a table that grows this way gets slower every day. Locally, `USING DELTA` is needed in `CREATE TABLE` because plain Spark's default format is Parquet; on Databricks it is the default.

## Compact with OPTIMIZE

`OPTIMIZE` rewrites the small files into larger ones, in 1 new commit. Delta aims for large files, so on a table of 20 MB it would make 1. The script sets the target to 2 MB so you can see what happens on a large table, where there are always many files.

Compaction alone helps the plain table: 8 files instead of 96. But customer 42's orders are in every one of them, so the query still opens all 8. Running OPTIMIZE again does nothing, since there is nothing left to compact.

## Cluster with CLUSTER BY

The clustered table's OPTIMIZE did more. With `CLUSTER BY (customer_id)`, it also sorted the rows so that nearby customers share files. The log keeps the lowest and highest value of each column for every file, and a filter on `customer_id = 42` skips the 6 files whose range cannot hold it. It reads 1 file of 7.

That is liquid clustering, which Databricks recommends for every new table. Choose up to 4 columns, the ones queries filter on most. You can change the columns later with `ALTER TABLE hourly_c CLUSTER BY (customer_id, order_id)`, and only new clustering work follows the new keys; `OPTIMIZE ... FULL` reclusters everything. It replaces both partitioning and `ZORDER`, and cannot be combined with them.

On Databricks, Unity Catalog managed tables tune their own file size, 256 MB for tables under 2.56 TB. Writes above a size threshold are clustered as they are written. Predictive optimization, where it is on, runs OPTIMIZE for you, and `CLUSTER BY AUTO` lets it choose the keys from the queries it sees. It needs the Premium plan or above, and Databricks' documentation does not say whether Free Edition includes it. Running OPTIMIZE yourself after a large load is harmless either way: on a table with nothing to compact or cluster, it does nothing.

## Delete old files with VACUUM

OPTIMIZE removed 96 files from the current version, but not from the disk: versions 1 to 24 still need them. `VACUUM` deletes data files that the current version does not use and that were removed more than 7 days ago. Save this as `vacuum.py`:

```python
import re
from delta_session import delta_spark

spark = delta_spark()
print("files older than 7 days:", spark.sql("VACUUM hourly_c DRY RUN").count())

# Only to see what VACUUM does: never shorten the retention on a table someone uses.
spark.conf.set("spark.databricks.delta.retentionDurationCheck.enabled", "false")
spark.sql("VACUUM hourly_c RETAIN 0 HOURS")

print("rows at version 24:", spark.sql("SELECT count(*) FROM hourly_c VERSION AS OF 24").first()[0])
try:
    spark.sql("SELECT sum(amount) FROM hourly_c VERSION AS OF 24").first()
except Exception as error:
    print("sum at version 24:", re.search(r"\[[A-Z_.]+\]", str(error)).group())
print("rows now:", spark.sql("SELECT count(*), sum(amount) FROM hourly_c").first())
```

```text
files older than 7 days: 0
Deleted 96 files and directories in a total of 1 directories.
rows at version 24: 2000000
sum at version 24: [FAILED_READ_FILE.FILE_NOT_EXIST]
rows now: Row(count(1)=2000000, sum(amount)=Decimal('89920000.00'))
```

With the default retention, VACUUM finds nothing: the files were removed minutes ago. With no retention, it deletes all 96, and version 24, the table before OPTIMIZE, is gone. Look at how it fails. The count still works, because Delta answers a plain count from the row counts in the log without opening a file. Only a query that reads the data finds the files missing. A check that counts rows is not proof an old version is readable.

Never shorten the retention on a table in use. A running write puts its files in the folder before it commits, VACUUM sees files no version lists, and deletes them. Keep the 7 days, which is also how far back you can restore. In open source Delta, VACUUM never runs on its own, so schedule it; on Databricks, predictive optimization runs it where it is on.

1 more thing changes files on Databricks. With deletion vectors on, a table property or a workspace setting, DELETE, UPDATE and MERGE mark changed rows instead of rewriting files, which makes small changes much faster; OPTIMIZE later rewrites the files for good.

[Part 5](/data-engineering/course/delta-lake-and-iceberg/iceberg-and-uniform) covers the other open table format, Apache Iceberg, and when a Delta table should be readable as one.

## Check yourself

1. The orders table is clustered by customer_id and was just compacted with OPTIMIZE. Which query gains most from the clustering?

   A. SELECT count(*) FROM orders
   B. SELECT * FROM orders WHERE customer_id = 42
   C. SELECT * FROM orders WHERE amount > 50
   D. SELECT customer_id, sum(amount) FROM orders GROUP BY customer_id

2. You run VACUUM orders RETAIN 0 HOURS on a table that a nightly job is writing. What can go wrong?

   A. The job's new files, written but not yet committed, are listed in no version, so VACUUM can delete them and the commit then points to files that are gone
   B. Nothing, since VACUUM never touches files of the current version
   C. VACUUM deletes the transaction log, so the table loses its history
   D. VACUUM fails, because a table cannot be vacuumed while it is written

### Answers

1. B. Clustering groups rows with nearby customer_id values into the same files, and the log keeps each file's lowest and highest customer_id. A filter on 1 customer then skips every file whose range cannot hold it. A count reads no data files at all when the log's row counts can answer it, a filter on amount gains nothing because amount is not a clustering key, and a group by every customer must read every file anyway.
2. A. A write puts its data files in the folder before it commits. Until the commit, no version lists them, so VACUUM with no retention treats them as unused and deletes them, and the table is broken when the commit lands. That is why Delta refuses a retention under 7 days unless you turn its check off, and why you should leave the default alone. VACUUM removes data files, not the log.

## Questions

### What does OPTIMIZE do in Delta Lake?

OPTIMIZE rewrites a table's small files into fewer large ones, in a new commit, so queries open fewer files. On a table with liquid clustering it also groups the rows by the clustering columns. The old files stay on disk, still needed by earlier versions, until VACUUM deletes them. Running it again on a compacted table does nothing.

### What is liquid clustering?

Liquid clustering is Delta Lake's way of laying out a table by up to 4 columns, set with CLUSTER BY when you create the table or later with ALTER TABLE. OPTIMIZE then writes rows with nearby values into the same files, so filters on those columns skip most files. You can change the columns without rewriting the table, and it replaces partitioning and ZORDER, which it cannot be combined with.

### How long does Delta keep old files before VACUUM deletes them?

VACUUM deletes data files that the current version does not use and that were removed more than 7 days ago, the delta.deletedFileRetentionDuration default. A shorter retention fails unless you turn off spark.databricks.delta.retentionDurationCheck.enabled, which you should not do on a table that is in use. VACUUM does not run on its own in open source Delta Lake.

### Do I need to run OPTIMIZE and VACUUM on Databricks?

On Unity Catalog managed tables with predictive optimization on, Databricks runs OPTIMIZE, VACUUM and ANALYZE for you, on serverless compute it bills as jobs. Predictive optimization needs the Premium plan and is on by default for accounts created on or after 11 November 2024. Without it, schedule OPTIMIZE and VACUUM yourself.

## Sources

- [Optimizations](https://docs.delta.io/latest/optimizations-oss.html)
- [Use liquid clustering for Delta tables](https://docs.delta.io/latest/delta-clustering.html)
- [Table utility commands, VACUUM](https://docs.delta.io/latest/delta-utility.html)
- [Use liquid clustering for tables](https://docs.databricks.com/aws/en/delta/clustering)
- [Control data file size](https://docs.databricks.com/aws/en/delta/tune-file-size)
- [Predictive optimization for Unity Catalog managed tables](https://docs.databricks.com/aws/en/optimizations/predictive-optimization)
- [Remove unused data files with vacuum](https://docs.databricks.com/aws/en/delta/vacuum)
- [Deletion vectors](https://docs.databricks.com/aws/en/delta/deletion-vectors)