When packing ruins clustering, and defragmentation

How can a size-driven compaction make clustering worse, and is defragmentation still needed?

The interaction: size-driven packing can ruin clustering

Size-driven bin-packing picks files that are adjacent in time (they were written close together). Clustering wants files adjacent in key. If you pack blindly, you merge files from disjoint key ranges into one file with a huge range. Overlap depth goes up, and pruning is worse after compaction than before.

Small example. Two small files: A holds customer_id 1-10, and B holds 9,000-9,010. They are neighbors in time. A blind compaction makes one file with range 1-9,010. A query for customer 500 could skip both files before; now it must read the merged one. The merge made the result worse.

Packing by time widens the key range

The target selection must be jointly size-aware and key-aware. This is why a naive "compact small files" job sometimes makes queries slower and nobody can explain why.

Defragmentation

On a local disk, defragmentation reorders blocks to reduce seek distance. On object storage this is largely vestigial: there is no in-place file, no block allocator and no seek penalty. What survives under the name is either:

row-group misalignment inside files (a row group is one internal chunk of a Parquet file); compaction handles it, or
logical spread of a key across many files; clustering handles it.

Page-level bloat from MVCC version chains (the old row versions that databases keep so that concurrent readers see a consistent view) is real, but it belongs to the storage engine of a database, not to table-file maintenance. The cleanest approach is to fold this item into the two above.

Case, resolvedConstructed case · Nadia at Stackwise

2.1 million files for 1.2 TB

The table held 2.1 million data files for about 1.2 TB, and the last small-file compaction had made the dashboard slower, not faster. Where it started: Layout restoration →

The numbers. 1.2 TB in 2.1 million files is about 570 KB per file. With F = 50 ms and b = 100 MB/s, a 0.57 MB file takes 5.7 ms to stream, so overhead is 50 / (50 + 5.7) ≈ 90%. Nine tenths of every scan is waiting. At the target size of 256 MB, overhead is 50 / (50 + 2,560) ≈ 2%, and the same table is about 1.2 TB / 256 MB ≈ 4,700 files.

Why so many files. Her ingest has 40 writers and 60 partitions, committing every minute. 40 × 60 = 2,400 files per minute from almost no data. Volume was never the cause.

Why the last compaction made things worse. The "compact small files" job picked the oldest 10,000 small files, which were adjacent in time and covered nearly every customer. Its output files each spanned almost the full customer_id range, so overlap depth rose from about 8 to about 60.

She makes three changes: she pre-shuffles by partition key so each partition is written by one writer; she raises the commit interval from 1 to 5 minutes (Kofi accepts 5 minutes of delay); and she changes compaction to sort by customer_id inside each partition, targeting 256 MB files. Dashboards drop from 90 s to 6 s.