The Small Files Problem

A file is small when its fixed costs outweigh its bytes. Every file read from object storage costs requests to open it and read its footer, plus a fetch for every column chunk, and each request pays network latency whatever its size. Writers create small files: each writer task produces its own file for every partition it touches on every commit. Take a clickstream table events that is partitioned by hour with a 256MB file target. A micro-batch job commits to it every minute, and each batch brings 30MB of new data spread across 20 writer tasks.

• 28,800
• 1.5MB
• 2.6M

A query over 1 day reads 43.2GB in any layout, but this layout pays the per-file overhead once for each file the day produced, against 169 times for the same 43.2GB compacted to 256MB files. Planning slows as well, because manifests hold an entry with column stats for every file, so planning time grows with table age even for a query that scans 1 hour. Tiny files also compress less well, since each one restarts its dictionaries and encodings.

Reading the symptoms from metadata

The files metadata table lists every data file in the current snapshot with its size and row count, so diagnosis is a query:

SELECT
  COUNT(*) AS file_count,
  ROUND(AVG(file_size_in_bytes) / 1048576.0, 1) AS avg_file_mb,
  COUNT_IF(file_size_in_bytes < 16777216) AS files_under_16mb
FROM "events$files"

Read the signals together. An average far below target with thousands of files per partition means writer fan-out, and planning time that climbs month over month while bytes scanned stay flat points at file count rather than data volume.

clicks has 400 files averaging 300MB. sessions has 90,000 files averaging 2MB, and its planning time doubled in 3 months while bytes scanned stayed flat. Which table needs compaction first?

• clicks, because 300MB files are past the target and force each task to read too much
• Neither, because bytes scanned are unchanged and scanned bytes drive query cost
• sessions, but expire its old snapshots first so the metadata shrinks before compacting
• sessions, since 2MB files and planning growth with flat bytes point to file count

Compaction

Compaction reads the small files of a partition, writes them back as target-size files, and commits a replace snapshot that removes the old files and adds the new ones. For 1 hour of events it swaps 1,200 files of 1.5MB for 7 files of about 256MB. The rows are identical, so no answer changes; queries already running keep their pinned snapshot, and the 1,200 files are deleted only when that snapshot expires. Table formats ship this as a built-in procedure, often exposed as an OPTIMIZE command. It takes a table and a target size, and a partition filter narrows it to the partitions you want rewritten.

Two choices decide whether compaction helps or fights the writers. First, compact closed partitions, such as the 09:00 hour once the clock is safely past 10:00. Appends only add files, so they rarely conflict with compaction. If a delete or update touches a file that compaction is replacing, though, whichever commits later has to redo its work. Second, sort while rewriting: sorting each hour by user_id gives every output file a narrow user_id range, so a filter on 1 user skips most files using their min/max stats.

A dashboard query on events starts at 11:00:00 on snapshot 900 and runs 40 seconds. At 11:00:15, compaction of the 09:00 hour commits snapshot 901. What does the query return?

• A mix of small and compacted 09:00 files, since the file list changed during the scan
• The complete result of snapshot 900, read from the small files that compaction replaced
• An error, because compaction deleted files the query had already planned to read
• The result of snapshot 901, since the engine re-plans when a newer snapshot commits

About this lesson

This section is part of the Storage Layers: 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.