Delta Small Files and Bin-Packing OPTIMIZE

OPTIMIZE is supposed to fix small files, and quite often it runs for an hour, reports success, and leaves the file count exactly where it was. Nothing is broken. Bin-packing compaction works inside one partition directory at a time, and it throws away any group of files that turned out to contain only one file. If your table is partitioned finely enough, those two rules together mean there is no work it is permitted to do.

The panel below is a streaming ingest table. Choose how the writes are laid out, then read the decision log: it prints which files became candidates, how they were grouped, and which groups were discarded before anything was written. Read it once as it loads, then make three moves — partition scheme to five-minute, micro-batches per hour to 12, output files per micro-batch to 1 — and watch OPTIMIZE do nothing at all.

As it loads — hourly partitions, 60 micro-batches an hour, 8 files each — one hourly directory holds 480 files averaging 1.0 MB. They pack into two bins, nothing is dropped, and the table's 11,520 files become 48; the day's scan drops from 454 tasks to 96. Now make the three moves. Twelve micro-batches an hour writing one file each is exactly one file per five-minute directory, so every bin holds one file, every bin is dropped, and the hero readout sits at 288 — the same 288 it started from, with numFilesAdded: 0 in the log. Running OPTIMIZE hourly instead of nightly will not change it, and neither will any target file size: walk the target from 128 MB to 1 GB and the readout stays at 288 at all four settings, because a bin of one is dropped whatever the bin was allowed to hold.

The two rules that decide everything

Delta's optimize planner does four things in order, and the second and fourth are where compaction quietly stops.

1. Pick candidates. Keep files smaller than optimize.minFileSize, which in open-source Delta defaults to 1 GiB, plus any file whose ratio of deleted rows exceeds optimize.maxDeletedRowsRatio, default 0.05. That second clause is a separate mechanism — it is how deletion vectors eventually get applied to disk — and it means a large file with 6% of its rows logically deleted is a candidate even though it is not small.

2. Group by partition. Candidates are grouped by their partition values. Every later step happens inside one group. There is no step anywhere that considers two files from different partition directories together, and no setting that adds one. This is the rule people do not know they are relying on.

3. Bin-pack. Within a partition, files are sorted ascending by size and packed first-fit until adding the next file would exceed the target. Ascending order matters: it puts the tiny files together, which is usually what you want, and it also means a file larger than the target always ends up alone at the end of the sequence.

4. Drop the useless bins. The filter is one line:

bins.filter { bin =>
  bin.size > 1 ||                                  // more than one file, or
  bin.size == 1 && optimizeContext.reorg.nonEmpty || // REORG rewrites anyway
  isMultiDimClustering                             // ZORDER / liquid clustering
}

A bin holding one file is discarded, because rewriting one file into one file of the same size would burn compute and produce no improvement. Sensible in isolation. Combined with rule 2, it is the reason a finely partitioned table is uncompactable: if a directory only ever receives one file, its bin always has one file, and that file is never touched again for as long as the table exists.

Notice the two escapes in that same filter, both visible in the panel. REORG TABLE sets reorg.nonEmpty and rewrites singletons regardless — tick the box and the dropped-bin count goes to zero, at the cost of rewriting every candidate file whether or not it helps. And isMultiDimClustering is true for ZORDER and for liquid clustering, which is a second, quieter reason those two commands cost so much more: they never drop a bin.

Why the target file size so often changes nothing

Leave the scheme at five-minute partitions, put micro-batches per hour back to 60 and output files per micro-batch to 4, and walk the target file size from 128 MB to 1 GB. The readout reads 288 at all four settings. Then set the data rate to 20,000 MB per hour and walk it again: 4,608, 2,304, 1,152, 576. In the first case the target does not matter at all; in the second it decides everything.

The reason is that the target is an upper bound on a bin, not a goal. A bin can only contain what the partition contains. If a directory holds four files of 60 MB each, raising the target from 256 MB to 1 GB changes the result from "one bin of four" to "one bin of four". If the same directory holds four files of 400 MB each, a 256 MB target puts each one in its own bin and drops all four, while a 1 GB target packs two per bin and halves the file count.

Databricks makes this worse in a way that is documented but easy to miss. On managed tables the target is autotuned from the current table size: 256 MB for tables below 2.56 TB, growing linearly to 1 GB at 10 TB, then flat. The documentation adds, plainly, that "when the target file size for a table grows, existing files are not re-optimized into larger files by the OPTIMIZE command. A large table can therefore always have some files that are smaller than the target size." Your 256 MB files from last year are, by the current 1 GB target, small — and they will still never be merged, because each of them alone plus one more would blow the bin, so they pack two at a time or not at all depending on their exact sizes.

The two automatic mechanisms have their own numbers, and they are not the same numbers. Optimized writes (delta.autoOptimize.optimizeWrite) shuffles data before writing so that each partition gets fewer, larger files, targeting 128 MB when set to true. Auto compaction (delta.autoOptimize.autoCompact) runs after a successful write, on the same cluster, also targeting 128 MB, and only when a directory already holds at least autoCompact.minNumFiles small files — 50 by default. That trigger is the auto-compaction equivalent of the single-file bin rule: a five-minute partition receiving four files per batch will not reach 50 within the window auto compaction looks at, so it never fires, and the table quietly accumulates.

What the small files actually cost

"Reading many files is slow" is true and is the smallest part of it. Three costs are larger, and none of them appear in a scan's byte counter.

Task overhead in the planner. Spark decides how many tasks a scan needs by adding a fixed per-file charge to every file: spark.sql.files.openCostInBytes, default 4 MB, documented as "the estimated cost to open a file, measured by the number of bytes could be scanned in the same time." Files are then packed into tasks of at most spark.sql.files.maxPartitionBytes, default 128 MB. So 100,000 files of 1 MB are not 100 GB of work; they are 100,000 × 5 MB = 500 GB of charged bytes, and roughly 3,900 tasks to read 100 GB. The panel's task readout uses exactly this arithmetic. This is the same accounting that governs how Spark splits files into partitions in general.

Metadata on every table open. A checkpoint holds one row per live file. A table with four million files makes every reader on every cluster parse four million rows before the query planner has a file list — driver time, invisible in the stage timeline. That is the expensive half, and it is covered in the transaction log lesson.

The compaction commit itself. Watch the tombstone readout: it counts one remove action for every file the run rewrote. Standing where the last section left you — five-minute partitions, 4 files per micro-batch, 20,000 MB an hour, a 1 GB target — the panel rewrites 5,760 files into 576 and the readout says 5,760. Those tombstones stay in the table state for delta.deletedFileRetentionDuration, one week by default, so VACUUM can honour readers holding older snapshots. For a week after a large compaction your checkpoint is wider than it was before, and the old files still occupy storage. Compaction is a bet that the steady state is worth a week of extra metadata; on a table you compact nightly, that week never ends.

The fix, and why it is a layout change rather than a schedule change

Every lever inside OPTIMIZE is bounded by the partition directory. So the only durable fix is to stop creating directories that are too small to compact.

A useful sizing rule falls straight out of the panel: a partition should receive, per compaction window, enough data to fill at least two bins. Fewer than two files and nothing happens; fewer than about ten and you are compacting the same bytes repeatedly for little gain. At 3,000 MB an hour with a 256 MB target, that means an hourly partition is comfortable, a five-minute partition is not, and a daily partition is generous. Databricks' own guidance — do not partition a table below about 1 TB, and expect at least 1 GB per partition value — is this rule with the numbers rounded.

Go back to the uncompactable table — five-minute partitions, 12 micro-batches an hour, one file each, 500 MB an hour — where the panel reads 288 files in and 288 files out with 288 bins dropped. Now set the scheme to no partition columns and change nothing else. The same 288 files are now one bin-packing scope, no bin holds a single file, and the readout goes to 50. The scan drops from 103 tasks to 96. Nothing about the data changed; only what the packer was allowed to look at. This is the mechanical argument for liquid clustering that gets lost behind the query-performance argument. Clustering keys give you locality without directories, so the compactor's scope is the whole table, and the thing that was structurally impossible becomes routine.

Two things that are not fixes. Running OPTIMIZE more often does not change which bins are legal — it changes how often you discover that there are none. And coalesce(n) or repartition(n) immediately before a write fights optimized writes rather than helping it; Databricks explicitly recommends against it when optimized writes are on, because both are trying to decide the same thing and the manual one wins.

Checking it yourself

Start with the ratio, not the count:

DESCRIBE DETAIL my_table;
-- numFiles, sizeInBytes  ->  sizeInBytes / numFiles

Below roughly 16 MB average you are paying more in per-file charge than in bytes. Then find out whether OPTIMIZE is allowed to help, by reading operationMetrics on the last OPTIMIZE row of DESCRIBE HISTORY. The fields that answer the question are numFilesAdded, numFilesRemoved and numRemovedBytes. A run with a large numFilesAdded and a similar numFilesRemoved is working. A run with numFilesAdded: 0 found no legal bin at all, and the next thing to check is how many files each partition receives.

The direct measurement is a group-by on the partition columns:

SELECT input_file_name() AS f, count(*) AS rows
FROM my_table WHERE event_date = '2026-08-12'
GROUP BY f;

Count the distinct files per partition value. Two or fewer and the single-file rule is your answer. Also check operationParameters.auto on OPTIMIZE rows: true means auto compaction ran, false means somebody ran the command. If you see none of the former, auto compaction is either off or never reaching its 50-file trigger.

Finally, SHOW TBLPROPERTIES my_table and look for delta.targetFileSize. If it is set, autotuning is off for this table and the value is whatever somebody chose, possibly years ago against a table a hundredth of the current size.

Read next: clustering keys instead of partition columns, which is this problem's structural answer, and the transaction log, where the file count is actually charged. If your compaction job keeps losing races against ingest, that is Delta's optimistic concurrency control, not this.