What is the small file problem?

A table stored as many small files instead of fewer large ones becomes slower to query and larger on disk, even though the data is identical. Each file carries a footer and metadata that must be opened and parsed, compression works worse on smaller blocks, and the per-file fixed cost gets paid once per file regardless of how little data each one holds. Measured on 2 million rows, splitting the same data from 1 file into 5,000 cost 2.6 times the storage and made a column-pruned read 45 times slower.

How small is too small for a Parquet file?

Measured on this dataset the knee is in the hundreds of files, not the tens. At 10 files averaging 3.2 MB there was no penalty at all — the full scan was marginally faster than a single file, because multiple files parallelise. At 100 files averaging 428 KB the column-pruned read was already 2.2 times slower. At 46 KB per file it was 12.9 times slower. Target file sizes in the hundreds of megabytes if you can; the damage starts once average file size falls into the hundreds of kilobytes.

Do small files make a table bigger on disk?

Yes, and by more than most people expect. The same 2 million rows occupied 24.9 MB as one file and 64.9 MB as 5,000 — a 2.6 times increase for identical data. Compression and dictionary encoding operate on blocks, so smaller blocks mean less context to compress against, and every file carries its own footer and schema. You are paying for storage twice: once in bytes, again in the extra bytes you have to read back.

Does compaction always help?

No, and running it on a table that does not need it is pure cost. At 10 files there was nothing to gain, because there was no measurable penalty to remove. Compaction rewrites data, which costs compute, invalidates caches and churns storage. The question is not whether your table has multiple files but whether average file size has dropped into the range where the penalty starts — which on this data was the hundreds of kilobytes.

Why do streaming and CDC pipelines create small files?

Because they commit on a time interval rather than a size threshold. A job that writes every minute produces 1,440 files a day regardless of whether a minute's worth of data is a megabyte or a kilobyte, and quiet periods produce the smallest files. Frequent small commits are also how merge-on-read tables accumulate delete files. The fix is to decouple write frequency from file size: commit often for freshness, then compact on a schedule that targets a file size rather than a file count.

Is the small file problem worse on object storage?

Considerably, and these numbers understate it. This benchmark ran on local disk, where opening a file is cheap. On S3 or equivalent, each file is an HTTP request with latency in the tens of milliseconds, and that cost is paid per file with no relation to file size. Treat the measurements here as a floor: the shape of the curve is right, the magnitude on object storage is larger.

The Small File Problem, Measured: When Compaction Pays

Q: Do small files hurt full scans or selective queries more?

Selective queries, by roughly five to one. Going from 1 file to 5,000 made a full scan 8.8 times slower but a two-of-eleven-column read 45.3 times slower. That is the uncomfortable part: column pruning is the main reason to use a columnar format at all, and fragmentation attacks it specifically. A full scan amortises per-file overhead across a lot of useful work; a selective read pays the same overhead for far less.

“Small files kill query performance, so compact your tables” is one of the most repeated claims in lakehouse writing. It is repeated on this site too — the word compaction appears in about twenty places here, and until today not one of them was backed by a measurement.

So here is the measurement. Same 2,000,000 rows, same eleven columns, same zstd codec, written once as a single file and then split across 10, 100, 1,000 and 5,000 files. Nothing varies except the file count. The script is published and runs in about a minute.

The results

Files	Avg file size	Total on disk	Full scan	2 of 11 columns	DuckDB aggregation
1	24 MB	24.9 MB	0.289 s	0.028 s	0.026 s
10	3.2 MB	32.4 MB	0.244 s	0.028 s	0.028 s
100	428 KB	43.9 MB	0.292 s	0.061 s	0.039 s
1,000	46 KB	47.2 MB	0.887 s	0.354 s	0.169 s
5,000	12.7 KB	64.9 MB	2.544 s	1.247 s	0.954 s
		2.6× more	8.8× slower	45.3× slower	36× slower

Three things in there are worth separating, because they are usually collapsed into one vague warning.

Log scale, because a linear one would hide everything below a thousand files. Fragmentation attacks column pruning specifically.

1. Selective reads suffer about five times more than full scans

Full scans got 8.8× slower. Reading two columns of eleven got 45.3× slower.

That gap is the real finding, and it is the opposite of reassuring. Column pruning is the main reason to use a columnar format at all. A full scan amortises the per-file cost across a lot of useful work — you were going to read every byte anyway. A selective read pays the same per-file overhead for a fraction of the payload, so the overhead dominates sooner.

Put differently: fragmentation attacks the specific thing you chose Parquet for. Measured as the benefit that column pruning still delivers:

Files	Pruning still pays
1	10.5× faster than a full scan
10	8.6× faster
100	4.8× faster
1,000	2.5× faster
5,000	2.0× faster

Pruning never stops helping — it just stops helping much. You keep the format and lose most of the advantage.

A correction, since this site’s rule is that published numbers are measured numbers. The first run of this benchmark showed the 5,000-file pruned read at 4.51 s, slower than its own full scan — pruning going negative. It was a striking result and it was about to be the headline here. It does not replicate: three further runs at five trials each gave 1.257, 1.242 and 1.252 s, within one percent of each other. The original was an outlier, almost certainly memory pressure during the first large write. The honest finding is the duller one in the table above.

2. The same data costs 2.6× the storage

This one I nearly missed, because I had assumed total bytes would be constant and wrote that assumption into the script’s own caveats before checking it.

They are not constant. 24.9 MB as one file, 64.9 MB as 5,000 — identical rows, 2.6 times the disk.

The cause is not mysterious. Compression and dictionary encoding work on blocks, so a smaller block has less context to compress against: a dictionary of country codes that pays for itself across a million rows is dead weight across four hundred. On top of that every file carries its own footer, schema and statistics.

You therefore pay twice. Once in storage, and again in the read path, because part of that 8.8× slowdown is simply more bytes to move. This benchmark does not separate “more bytes” from “per-file overhead”, and I would not trust anyone who claims a clean split without isolating it deliberately.

3. Under a hundred files, there is nothing to fix

Look at the 10-file row again. The full scan was 0.244 s against 0.289 s for a single file — marginally faster, because several files parallelise where one does not.

At 100 files averaging 428 KB, the pruned read is 2.2× slower: measurable, rarely urgent. The damage becomes serious between there and 1,000 files, as average size falls through the tens of kilobytes.

So “compact everything” is as wrong as ignoring the problem. Compaction rewrites data. It costs compute, churns storage, invalidates caches, and on a table format it generates new snapshots that themselves need expiring. Running it against a table with forty healthy files is pure cost for no benefit.

The useful question is not how many files does this table have but what is the average file size, and has it fallen into the range where the penalty starts. On this data that range began in the hundreds of kilobytes.

Why tables end up here

Almost always because write frequency is decoupled from file size, and nobody reconnected them.

A streaming or CDC job that commits every minute writes 1,440 files a day whether a minute carries a megabyte or a kilobyte, and the quietest hours produce the worst files. Over-partitioning does the same thing structurally: partition by day and region and product and most partitions hold almost nothing. And frequent small commits are exactly how merge-on-read tables accumulate delete files alongside the data.

None of these is a mistake at the moment it is made. Each is a reasonable freshness decision whose storage consequence shows up months later.

What to actually do

Measure average file size, not file count. It is the number that predicts the penalty. File count alone tells you nothing without total volume.
Set a target size and compact toward it, rather than compacting on a fixed schedule. Hundreds of megabytes is a common target and a reasonable default.
Commit for freshness, compact for size. They are different jobs with different triggers and should not be the same job.
Expect it to be worse than this on object storage. These numbers are local disk, where opening a file is nearly free. On S3 every file is an HTTP request with latency in the tens of milliseconds, paid per file regardless of size. Treat this curve as a floor.
If selective queries matter to you, care sooner. They degrade five times faster than the full scans you might be benchmarking with.

The honest limits of this

One dataset shape, eleven columns, zstd, default row-group sizes, local disk, warm page cache, single writer, no concurrency. A single 2-million-row file still contains many row groups internally, so the one-file case is not an artificially perfect baseline.

What transfers is the shape: selective reads degrade far faster than full scans, storage inflates meaningfully, and the knee sits in the hundreds of files rather than the tens. The absolute seconds are a property of a two-core machine and are worth nothing to you. Rerun it on your own data, which takes about a minute and will tell you something the generic advice cannot.