# OPTIMIZE, Z-ORDER, Liquid Clustering and VACUUM

> Table maintenance for Delta Lake: compacting small files, co-locating data for faster filters, and removing old files safely.

- Source: https://akshaythakare.com/notes/databricks/optimize-zorder-vacuum
- Topic: Databricks
- Level: advanced
- Reading time: 2 min
- Tags: databricks, delta-lake, performance, maintenance
- Updated: 2026-10-11

## Small files

Streaming and frequent small batches create many small files, which makes every read slow.

```sql
OPTIMIZE silver.events;                       -- compact into larger files
OPTIMIZE silver.events WHERE event_date >= current_date() - 7;
```

## Z-ORDER: co-locate related values

```sql
OPTIMIZE silver.events ZORDER BY (customer_id);
```

Z-ordering arranges data so that rows with similar values of the chosen columns share files. File-level min/max statistics then let queries that filter on `customer_id` skip most files.

## Liquid clustering

On recent Databricks runtimes, **liquid clustering** replaces partitioning plus Z-ORDER for new tables:

```sql
CREATE TABLE silver.events (…) CLUSTER BY (event_date, customer_id);
```

Clustering keys can be changed later without rewriting the table layout by hand.

## VACUUM: remove old files

Delta keeps old file versions for time travel. `VACUUM` deletes files no longer referenced by any version within the retention period:

```sql
VACUUM silver.events RETAIN 168 HOURS;   -- 7 days, the default threshold
```

> **Warning: Do not shorten retention casually**
>
> Vacuuming with a very short retention can delete files that a running query or a lagging reader still needs, and it removes time travel beyond that point.

| Command | Fixes | Run |
| --- | --- | --- |
| `OPTIMIZE` | Small files | Daily, or after heavy writes |
| `ZORDER` / clustering | Poor data skipping | With OPTIMIZE, on filter columns |
| `VACUUM` | Storage growth | Weekly |