Can the 5% deletion-vector threshold used by OPTIMIZE be configured?

[dario66952]

Hello everyone, we have a compliance requirement to guarantee that deleted records are physically removed from the underlying Parquet files.

Our current flow is:

1. DELETE  creates deletion vectors (DVs)

2. OPTIMIZE  may rewrite files and materialize the deletions

3. VACUUM  removes obsolete files

From Delta Lake, OPTIMIZE only considers files for DV-driven rewriting when the deleted-row ratio exceeds optimize.maxDeletedRowsRatio, which defaults to 5%:

https://github.com/delta-io/delta/pull/1578

This means files with a small number of deleted rows may keep those rows physically present even after OPTIMIZE.

We can guarantee physical removal with:

REORG TABLE <table> APPLY (PURGE);

followed by VACUUM, but running REORG ... APPLY(PURGE) after every deletion request is expensive.

Is there a supported Databricks configuration to lower the OPTIMIZE DV threshold, ideally to 0, so that any file with a DV is rewritten? We are on AWS

If not, is there a recommended way to guarantee physical deletion for compliance without running REORG TABLE ... APPLY(PURGE) after every deletion?

[ThomazNeto · Accepted solution]

Hi,

Short version: I couldn't find that knob anywhere in the Databricks docs. maxDeletedRowsRatio is an OSS Delta conf, and Databricks doesn't document it, so even if it's accepted on a given runtime you'd be relying on undocumented behavior for a compliance control. I wouldn't.

More to the point, the docs are explicit that OPTIMIZE is not the tool for this: "File compaction events don't have strict guarantees for resolving changes recorded in deletion vectors" and "Some changes recorded in deletion vectors might not be physically applied if target data files are not candidates for file compaction." The GDPR page says the same in one line: for tables with DVs, after deleting records "you must also run REORG TABLE ... APPLY (PURGE) to permanently delete underlying records."
https://docs.databricks.com/aws/en/tables/features/deletion-vectors
https://docs.databricks.com/aws/en/ldp/gdpr

The good news is REORG is cheaper than it sounds. Per the docs, "APPLY (PURGE) only rewrites files that contain soft-deleted data", it's idempotent, and you can scope it with a WHERE on partition columns. Two things make it cheaper still: set spark.databricks.delta.reorg.purgeMode to rows so it only looks at files with soft-deleted rows instead of scanning every footer, and don't run it per delete request. Batch the requests into a control table, run the DELETE/MERGE once a day, then one REORG APPLY (PURGE) and one VACUUM with the retention threshold set to the purge completion time. That's the flow the docs describe, and a daily SLA for physical erasure is what most DPOs accept.
https://docs.databricks.com/aws/en/sql/language-manual/delta-reorg-table

If the table is small or delete-heavy and you really want physical removal on every DELETE, the other option is to not use deletion vectors on that table at all (delta.enableDeletionVectors = false). Then DELETE rewrites the files itself and you're back to VACUUM only. You give up the write performance DVs bring, which is exactly the trade-off.

One caveat on OPTIMIZE FULL (DBR 16+): it "Rewrites all data files in the table", so it would clear DVs as a side effect, but it's a full rewrite and the docs never position it for this. REORG is the documented path.