Lakehouse Table Maintenance: OPTIMIZE and VACUUM

Nobody gets promoted for running OPTIMIZE. It is the layer of the platform that has no dashboard, no stakeholder and no demo, and it is also the layer that decides whether a query that ran in seconds last quarter still runs in seconds now. If you own a lakehouse, table maintenance is your job, because it is structurally nobody else’s.

Why does a table get slower when nothing changed?

Because the query changed shape even though the SQL didn’t. A Delta or Iceberg table is a log of files plus a transaction history, and almost every write pattern that is good for ingestion is bad for the file layout. Micro-batch streaming writes small files. Incremental merges rewrite only the files touched by matched keys, leaving behind an uneven distribution of file sizes. Deletes produce tombstoned files or deletion vectors that the reader must reconcile. Each of these is individually reasonable and collectively corrosive.

The cost shows up in three places. Planning time grows because the engine must read more metadata to work out which files to scan. Scan time grows because more file-open operations are required for the same logical row count. And subsequent writes get more expensive, because a MERGE that has to rewrite a thousand small files does more work than one that rewrites fifty large ones. The third effect is the one that catches people out — the degradation compounds through the write path, not just the read path, so the pipeline that produces the table slows down alongside the reports that consume it.

What is actually being maintained?

Four separate things that get bundled under one word.

File layout — the number and size distribution of data files in each partition. This is what compaction addresses.

Data skipping effectiveness — whether the min/max statistics on each file let the engine eliminate files without opening them. Clustering, Z-ordering and column ordering all serve this, and they are orthogonal to file size.

History and retention — how many old file versions remain reachable, how long time travel works, and what gets physically removed. VACUUM addresses this.

Metadata and log — checkpoint frequency, log file accumulation, and in Fabric the V-Order write optimisation that affects read performance for the Power BI path.

Conflating these produces maintenance jobs that do the expensive thing and skip the cheap one. Rewriting an entire table nightly to fix a skew problem that a clustering key would have solved is a reliable way to spend more on maintenance than the queries it accelerates.

Where does partitioning make maintenance worse?

When the partition column was chosen for ingestion convenience rather than for query predicates. A table partitioned by ingest date and queried by business date carries all the maintenance cost of partitioning with none of the pruning benefit, and the small-file problem is multiplied by the partition count because each partition gets its own trickle of writes.

Over-partitioning is the more common failure than under-partitioning, because partitions look free until maintenance has to touch all of them. A table split by date and region and source system has a cross-product of directories, most holding a handful of rows, and every compaction pass has to enumerate them.

How often should compaction run?

There is a trough, and both sides of it are expensive. Compact too rarely and every read and every merge pays the small-file tax. Compact too often and you are rewriting files that were already large enough, burning compute to produce a layout barely different from the one you had.

The practical answer is not a schedule but a predicate. Maintain the partitions that received writes since the last maintenance pass, and skip the rest. A maintenance driver that reads the table’s own commit history to decide what to touch converges on a sensible frequency without anyone tuning a cron expression.

-- driver: which partitions have been written since the last pass?
WITH touched AS (
SELECT DISTINCT
  get_json_object(operationParameters, '$.partitionBy') AS part_spec,
  timestamp
FROM (DESCRIBE HISTORY silver.orders)
WHERE operation IN ('MERGE', 'WRITE', 'STREAMING UPDATE')
  AND timestamp > (SELECT last_run_ts FROM ops.maintenance_log
                   WHERE table_name = 'silver.orders')
)
SELECT count(*) AS commits_since_last_pass FROM touched;

-- act only on the scoped window
OPTIMIZE silver.orders
WHERE business_date >= current_date() - INTERVAL 7 DAYS;

The snippet is a sketch of the shape, not a drop-in. The point is that the decision to maintain should be derived from table state, so that a dormant table costs nothing and a hot table gets attention proportional to its churn.

What breaks when VACUUM retention is set by a guess?

Two things, in opposite directions.

Set retention too short and you break readers. A long-running query that resolved its file list at the start of execution will fail when those files disappear underneath it. You also break time travel — and with it, the restore path that your recovery runbook quietly depends on. If anyone has written “we can roll back to yesterday’s version” into an operational document, retention is a contract, not a knob.

Set it too long and storage accumulates versions nobody will ever read, and the transaction log grows to the point where planning gets slower — the exact problem maintenance was meant to solve.

External readers are the one most likely to be missed. Fabric shortcuts and external catalogue registrations let other systems read your files without appearing anywhere in your lineage graph. VACUUM is a physical delete; it does not care who was reading.

Who pays for maintenance compute?

Someone, and if it isn’t attributed it will be found during a cost review and cut. Maintenance jobs are a textbook case of work that benefits consumers and is billed to the platform. Tag the maintenance cluster or capacity usage separately from ingestion and serving, so that the line item has a name and an owner. The conversation you want is “this is what we spend to keep query cost flat”, not “what is this unattributed notebook doing at 3am”.

This also disciplines the maintenance itself. Once compaction has its own cost line, blanket full-table OPTIMIZE across every table in a schema becomes visibly indefensible, and scoping follows naturally.

How do you know maintenance is working?

Not by whether the job succeeded. A compaction job that touches nothing succeeds. Capture a small set of per-table observations after each pass and persist them: file count, total bytes, mean file size, number of partitions touched, and the duration of the pass itself. Then watch the derivative. A table whose mean file size trends down between passes is telling you the maintenance window is too infrequent for its write pattern. A table whose pass duration grows while its row count is flat is telling you something about log or partition accumulation rather than data volume.

The same snapshot gives you the argument you need when someone asks whether maintenance can be skipped this week. Without it, the only evidence is a slow report and a guess.

What does this look like as a scheduled job?

As a first-class pipeline, not a notebook that one person remembers to run. It needs the same properties you would demand of any other production asset: idempotent, so a re-run is harmless; scoped, so cost is proportional to churn; logged, so each pass leaves evidence; and sequenced against ingestion, so maintenance and writes are not competing for the same files and the same capacity.

The asymmetry is worth stating plainly. Deferred maintenance does not announce itself. It presents as a general sense that the platform has got slower, which gets attributed to data volume, or to the warehouse engine, or to whoever wrote the last transformation. The actual cause is a layout decision compounding quietly across thousands of commits. The teams that avoid this are not doing anything clever; they are doing something boring, on a schedule, with the cost attributed and the retention written down.