Two Delta tables receive identical INSERT INTO commits. One relies on scheduled OPTIMIZE, the other has auto compaction enabled and rewrites eligible small files synchronously after qualifying writes. Compare who pays for fragmentation, and when.
Each INSERT INTO writes the same files to both tables. Auto compaction triggers only once the number of small files (below half the target size) reaches minNumFiles.
New Parquet files produced by each commit (optimize write is off in this model).
Files below half the target size count as small and are eligible for compaction.
Small-file count that must accumulate before auto compaction fires (Fabric default 50).
Target size compaction packs toward; minimum compacted size is half this value.
Illustrative model — not measured Fabric performance. Base commit latency = 45 + (3 × files added) + (0.06 × MB added) ms. Synchronous auto compaction adds a spike capped at about two-thirds of that commit's base write latency. Manual OPTIMIZE maintenance cost = 80 + (6 × files rewritten) + (0.18 × MB rewritten) ms and is not user-facing.
Auto compaction disabled — small files persist until you run maintenance.
Qualifying commits synchronously rewrite small files into larger before returning.
Fragmentation the table carries after every commit and compaction. The dashed line marks the auto compaction trigger threshold.
User-facing latency per commit. The scheduled table stays flat because rewrites are deferred; auto compaction spikes on the commits where it synchronously compacts. Manual OPTIMIZE runs are maintenance events and are excluded here.
Scheduled OPTIMIZE: every commit returns fast, but small-file debt accumulates between runs and someone has to orchestrate the cleanup.
Auto compaction: fragmentation stays bounded with zero scheduled maintenance, but qualifying commits return slower because compaction is synchronous.
Auto compaction is off by default. Turn it on for every write in your Spark session, or per table with a table property.
# Compact small files synchronously after writes in this session
spark.conf.set("spark.databricks.delta.autoCompact.enabled", "true")-- Enable on an existing table
ALTER TABLE your_table
SET TBLPROPERTIES ('delta.autoOptimize.autoCompact' = 'true');Tune the target with spark.databricks.delta.autoCompact.maxFileSize (default 128 MB). Auto compaction runs as a synchronous OPTIMIZE after a successful write.