Compaction and the Small File Problem

A micro-batch job running every five minutes produces at least 288 files a day per writer task. After a quarter that is roughly 26,000 files for one table, and with eight parallel tasks, 200,000. Nothing is broken. The data is correct, the schema is stable, the partitioning is sensible. Queries have simply become four times slower every month and nobody changed anything.

This is the small file problem, and it is the most common pathology in production lakehouse tables.

Why small files cost so much more than their size suggests

The cost is per file, not per byte, and it compounds across four layers.

Planning: the query engine must list or read manifest entries for every file, then open each one to read its Parquet footer before it can decide whether to skip it. On object storage that is a separate round trip per file at tens of milliseconds each. Ten thousand files is ten thousand footer reads.

Encoding: Parquet's compression and encoding work on column chunks within a row group. The default row group size is 128 MB and the Parquet documentation recommends 512 MB to 1 GB. A 2 MB file has one undersized row group per column, so dictionary encoding never fills, run-length encoding sees short runs, and the compression ratio falls. The same logical data occupies noticeably more bytes in many small files than in a few large ones.

Statistics: min/max and null-count statistics in the footer let an engine skip a file entirely. Those statistics are only useful if the file covers a narrow value range. Many small files written from unsorted micro-batches each span the full range of the sort key, so every file's statistics overlap and none can be pruned.

Metadata: the table format's own manifests grow linearly in file count. Commit time, snapshot size and the cost of planning all scale with it.

Compaction is a rewrite, with the economics of one

Compaction reads small files and writes larger ones, then commits a snapshot that replaces the inputs with the outputs. Delta Lake's OPTIMIZE targets 1 GB output files by default, a value chosen from observed customer workloads and adjustable through spark.databricks.delta.optimize.maxFileSize; Iceberg's rewrite_data_files procedure is the equivalent, with a target file size property.

Two second-order choices matter more than the command.

Sort order during compaction. Plain bin-packing just concatenates. Sorting or Z-ordering the rewritten data on the columns that appear in filters is what makes the new statistics selective, and it is where most of the query speedup comes from. It also costs a shuffle, so it is meaningfully more expensive than bin-packing.

Which files to touch. A policy that rewrites every file below the target size is a write-amplification trap: with a 1 GB target, a perfectly healthy 900 MB file gets rewritten every time a 1 KB file lands next to it. The fix is a minimum-size floor well below the target, plus marking files that have already met a large fraction of the target so they are not recompacted as the target drifts.

The interaction with snapshots

Compaction does not free space by itself. The old files remain, because old snapshots still reference them and time travel depends on that. Space comes back only when snapshot expiry (Iceberg) or VACUUM (Delta) removes files no longer reachable from any retained snapshot. Compaction therefore temporarily increases storage: the pre-compaction bytes and the post-compaction bytes coexist for the whole retention window.

Run compaction daily with a seven-day retention and the table carries an extra copy of its hot partitions at all times. That is usually the right trade, but it should be a decision rather than a surprise on the bill.

When it breaks

Compaction competes with the writers it is cleaning up after. A streaming job appending to the same partition that compaction is rewriting will lose commit races, and under merge-on-read both are rewriting delete state. Serialising them by partition, or scheduling compaction against partitions the writer has finished with, is the usual answer.

Compacting into too-large files hurts parallelism. A 4 GB file cannot be split across readers unless its row groups can be assigned independently, and even then a single file limits the useful concurrency of a scan. The 512 MB to 1 GB range is not arbitrary; it balances per-file overhead against parallel scan width.

Deletes make compaction mandatory, not optional. Under merge-on-read, compaction is the only thing that physically removes deleted rows. A table that is compacted for file size but never for delete state keeps reading dead rows off disk forever, and for regulated deletions it may not actually have deleted anything.

Auto-compaction inside the write path adds latency to ingestion. Moving compaction into the writer is operationally simpler and makes the tail of every micro-batch slower. Whether that matters depends on whether the writer's latency budget is a service-level objective or a preference.