Deletion Vectors and Merge-on-Read

You deleted 40% of a table. The DELETE returned in seconds, which felt like a win. The table is the same size on disk, and the query you ran to check reads exactly as many bytes as it did yesterday.

That is deletion vectors working correctly. With delta.enableDeletionVectors set, a DELETE does not rewrite a single Parquet file. It writes a compressed bitmap of the row indexes that are now invalid, and commits a remove and an add for the same path — the second one carrying a deletion vector descriptor. The protocol calls the result a logical file: a data file plus an optional set of rows that are no longer in the table.

Nothing about the physical file changes. So the cost does not disappear; it moves to every reader, forever, until something rewrites the file. The instrument below is a table of 24 value-ordered files under a retention delete. Move the cutoff and watch what the write costs, then what the read costs.

Push the cutoff to 400 in merge-on-read: the query now returns zero rows and still reads 384 MB, because the three files it needs are physically intact and still advertise the range they were written with. At 350 it returns half the rows for the same 384 MB. Switch to copy-on-write and the same query reads nothing at all, because the DELETE physically removed those files — but look at what that DELETE wrote. Neither column of this trade is free, and the default on new Databricks tables picks the cheap-write side for you.

What is actually on disk

A deletion vector is a set of 64-bit row indexes — positions in a Parquet file counting from zero — serialised as a RoaringBitmap. The descriptor stored in the log names one of three storage types: u for a separate file beside the data, i for the bitmap inlined into the log entry itself when it is small enough, and p for an absolute path. Along with it the writer records cardinality, the number of rows the vector invalidates.

Two consequences follow directly from that shape. The bitmaps are tiny — a dense run of deleted rows run-length encodes to almost nothing, which is why the delete is fast. And the data file is not merely un-shrunk, it is untouched: it is still the same object in cloud storage, so nothing about its layout, its compression or its per-file statistics reflects the deletion.

The table protocol raises the bar to read: deletion vectors require reader version 3 and writer version 7 with deletionVectors in both feature lists. A client that does not implement the feature must refuse the table rather than read it — because reading the Parquet files and ignoring the vectors would silently return deleted rows. This is why enabling it is a one-way door for external consumers, and why DROP FEATURE deletionVectors exists and requires purging first.

Tight bounds and wide bounds

This is the part that surprises people who already knew everything above. Per-file statistics carry a flag, stats.tightBounds. When it is true, the recorded minValue and maxValue are values that actually exist in the file. When a deletion vector is added, the writer can no longer promise that — the row holding the minimum may be one of the deleted ones — so the bounds become wide: minValue is merely less than or equal to every live value, and maxValue greater than or equal to every live value.

Wide bounds are still correct for data skipping. A file whose bounds do not overlap the predicate still cannot contain a match, so it is still safely pruned. What they are not is precise. A file whose every row has been deleted still advertises the exact range it advertised before, so any query overlapping that range opens it, reads 128 MB, materialises 512 rows, checks all 512 against the bitmap and returns zero. Those are the red bars in the simulation, and they are the reason a heavily deleted table gets slower at reads even though it is returning less data.

The same effect shows up in the row counts. The protocol requires that a logical file with a deletion vector still carries the correct numRecords for the data file — the physical count, before deletion. Anything reading numRecords to estimate a scan, including the optimizer, is reading a number that describes bytes rather than rows you will get.

Why anyone turns this on

The write side genuinely is dramatic. A DELETE matching one row in a 1 GB file rewrites that entire gigabyte under copy-on-write, and there is no smaller unit available, because a Parquet file is immutable. Retention deletes and GDPR erasure requests scattered across a large table are the canonical case: a few thousand rows, hundreds of gigabytes rewritten. Move the cutoff slider in copy-on-write mode and watch the bytes-written readout to see the shape of that.

The second reason is concurrency, and it is often the actual reason. Row-level conflict resolution needs to know which rows each writer touched, and deletion vectors are the mechanism that records it — which is why the row-level concurrency path requires them, along with Databricks Runtime 14.3 LTS or later and an unpartitioned table. Teams frequently enable deletion vectors to stop MERGE conflicts and only later notice the read profile changed.

Both benefits are real. The mistake is treating the feature as free rather than as a shift of cost from write time to read time, which is the same trade a Postgres dead tuple makes — cheap to create, paid for by every sequential scan until something rewrites the page.

Reclaiming it

Only a rewrite turns a deletion vector back into space. Two commands do it:

• OPTIMIZE rewrites files it selects, and files carrying deletion vectors are rewritten without the deleted rows and with tight bounds restored. This is the normal path, and it means a table with scheduled OPTIMIZE self-heals on a lag.
• REORG TABLE … APPLY (PURGE) rewrites specifically the files that have deletion vectors, regardless of size. This is what you run before dropping the feature, or when erasure has to be physical rather than logical — which matters for GDPR, because until the purge the deleted rows are still bytes in an object you own.

Neither reclaims storage on its own. The rewrite creates new files and marks the old ones removed; the old objects survive until VACUUM passes the retention window, which defaults to seven days. So the honest sequence from "the rows are logically gone" to "the bytes are gone" is delete, then OPTIMIZE or REORG, then VACUUM after retention — three steps, and the simulation's purge checkbox only models the middle one. Tick it and the red bars disappear; the table on disk falls to what the live rows justify.

Checking it yourself

DESCRIBE HISTORY gives you the write side: operationMetrics.numDeletionVectorsAdded and numDeletionVectorsRemoved on the DELETE and OPTIMIZE rows tell you whether a delete took the cheap path and whether maintenance has caught up. If vectors added keeps climbing and vectors removed stays at zero, nothing is rewriting your files.

For the read side, compare two numbers in the query profile that should not differ: bytes read against the size of the data you got back. Then confirm the cause directly from the log, which is the only place the physical truth lives:

-- files carrying a deletion vector, and how much of each is dead
SELECT get_json_object(add, '$.path')                        AS file,
       get_json_object(add, '$.size')                        AS bytes,
       get_json_object(add, '$.stats.numRecords')            AS physical_rows,
       get_json_object(add, '$.deletionVector.cardinality')  AS dead_rows,
       get_json_object(add, '$.stats.tightBounds')           AS tight
FROM   json.`/path/to/table/_delta_log/*.json`
WHERE  get_json_object(add, '$.deletionVector') IS NOT NULL;

A row where dead_rows equals physical_rows is a file that contains nothing and still costs a full read every time a predicate overlaps its stale bounds. Sum bytes across that result and you have the exact size of the OPTIMIZE you have been postponing.

Next: the statistics these bounds belong to, and the add/remove pair that makes a logical file different from a file.