Compaction Strategies

Concepts covered: Small File Problem

Compaction reads many small files and commits fewer large ones in their place. You decide how output rows are laid out, which files are selected, and when the job runs. Each decision has a price measured in bytes rewritten, and the interviewer expects that price stated.

Bin-packing or sorting the output

Bin-pack

Groups input files by size into output files near 256 MB
No shuffle: a task concatenates the rows of its group
Per hour of trip_events: read 1.25 GB, write 1.25 GB
Fixes request and listing cost, and metadata cost with it
Leaves column statistics as wide as the inputs were

Sort or cluster

Shuffles the partition's rows by a key such as city_id, then writes
Each output file covers a narrow key range, so min/max pruning works
Pays a full shuffle plus sort memory for every partition it rewrites
A 1-city filter on a 5-file hour opens 1 file instead of 5
Later appends arrive unsorted, so the order decays until the next run

Pick bin-packing by default and sorting when a filter on one key is frequent and selective. For filters on 2 or 3 columns at once, a space-filling curve interleaves the bits of each key so files are narrow on all of them, though on any single column they are wider than a single-key sort would make them. Ask which predicates the top queries use, because sorting on a key no query filters on adds a full shuffle with no read benefit.

Select files from metadata, not from the clock

A file is a candidate when it is under 75% of target, here 192 MB. A partition qualifies when it holds at least 5 candidates, so a partition with 1 straggler is left alone. Files already near target are never selected, and that rule is what keeps repeated runs from rewriting the same bytes.

SELECT
    partition_hour,
    count(*) AS small_files,
    sum(file_size_bytes) / 1048576.0 AS small_mb,
    CAST(ceil(sum(file_size_bytes) / 268435456.0) AS integer) AS files_after
FROM trip_events_files
WHERE file_size_bytes < 201326592
GROUP BY partition_hour
HAVING count(*) >= 5
ORDER BY count(*) DESC

Selecting by metadata is what catches late data. A driver's phone that was offline uploads its events 3 days later, and each minute batch then writes a small file into an hour that a clock-driven 'compact the hour that closed last' job finished long ago. That job never returns to it. The query above finds the old hour as soon as it collects 5 small files, because it ranks partitions by small-file count and ignores their age.

Cadence and write amplification

Schedule	Bytes rewritten per byte ingested	Small files a reader may hit	Contention with writers
Every 10 min, rewrite the whole open hour	3.5	about 2,000	Every run races the minute appends
Every 10 min, candidates under 192 MB only	1.0	about 2,000	Every run races the minute appends
Once, after the hour closes	1.0	up to 12,000	Late writers only
Once a day	1.0	up to 288,000	Late writers only

The 3.5 comes from rewriting a growing hour: 6 runs rewrite 1/6, then 2/6, up to 6/6 of it, which sums to 21/6. The size threshold removes that cost. After the first run the hour holds 1 file of 213 MB, above the 192 MB cutoff, so the next run selects only the 10 minutes of new small files. Whether frequent runs are worth it depends on what readers tolerate, and the per-request figures price it: 2,000 small files cost about 0.8 seconds of request wait per scan, 12,000 cost roughly 4.8.

Sizing the job and the target

The hourly bin-pack reads 12,000 files, so the compactor pays the small-file price itself: 24,000 requests, 48 seconds of wait on 10 slots, then 1.25 GB written. Across 24 runs that is about 19 minutes of request wait a day. Cap each run's input, say at 20 GB, so a 60 GB backlog after a 2-day outage drains in 3 bounded runs instead of one run that loses all its work to a single conflict.

Target size is a tradeoff too. Larger files cut per-file overhead further, but a 1 GB file gives a small query fewer splits to parallelise, and in tables that apply updates by rewriting whole files, deleting 1 row rewrites the full 1 GB. Tables with frequent row-level changes therefore sit nearer 128 MB, while append-only fact tables like trip_events can go higher.

Do

Select files under 75% of target from metadata, whatever their age
Require at least 5 candidates before rewriting a partition
Sort only on columns the top queries filter on
Cap input bytes per run so backlogs drain in bounded chunks

Avoid

Rewrite the whole open partition every few minutes
Choose partitions to compact by wall-clock hour alone
Sort on a key that no predicate uses
Run 1 job across a whole backlog and lose it all to a conflict

About this lesson

This section is part of the Small Files and Compaction: Advanced lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.