// data lakes and query engines · 23 October 2026 · 5 min read
The small file problem, measured on disk and in Azure
Same 2 million rows, same answer. What changed, what compaction fixed, what VACUUM quietly broke, and what it looks like from Azure.
Why are lots of small files bad? It’s a classic data engineering interview question, and most answers stop at “overhead”. I wanted to see how big the overhead is, so I ran it.
Same 2 million rows. Same query. Same answer. One Parquet file took 0.012 seconds. The same rows split into 5,000 files took 0.618 seconds.
The test
I saved 2 million rows as 1, 10, 100, 1,000 and 5,000 Parquet files, then ran one total query over each folder with DuckDB. Each number is the median of 5 runs, on one laptop, with a warm cache.
- 1 file: 0.012 s
- 100 files: 0.017 s
- 1,000 files: 0.077 s
- 5,000 files: 0.618 s
The first jump is small. The last one is not. Nothing changed except the number of files.
Why it’s slower
Every Parquet file has its own footer, and the reader has to open each file and read that footer before it touches any data. Think of a library: the same books in one box, or in 5,000 envelopes. Opening the envelopes costs more than reading the pages.
The files were also bigger in total: 30.3 MB as 1 file, 37.9 MB as 5,000. Probably because each file carries its own metadata and compresses on its own.
OPTIMIZE makes it faster, and the table bigger
Small files usually come from jobs that write often. So I built a Delta table from 500 small appends, one commit each, like a job that writes every few seconds. I used the open-source delta-rs library, not Databricks.
Then I ran OPTIMIZE, which merges small files into bigger ones. 500 files became 1. The same query went from 0.093 seconds to 0.015 seconds.
But disk use went up, from 33.9 MB to 42.8 MB. The old files are still there, because Delta keeps them so you can time travel to the earlier version. I read the pre-OPTIMIZE version afterwards and got all 2,000,000 rows back from the 500 original files. Compacting does not free space.
VACUUM frees the space, and ends time travel
VACUUM deletes the files no table version needs any more. It also deletes your way back. On my fresh table, VACUUM with the default retention would have deleted nothing, because the default is 7 days and my files were minutes old.
To see it work, I set the retention to 0 and switched off the safety check. Do not do that on a real table. VACUUM then deleted all 500 old files, disk use dropped to 8.9 MB, and reading the old version failed with an error. The answer to the current table was the same as before.
The size drop had two causes
After compaction the table shrank from 33.9 MB to 8.9 MB. It’s tempting to credit the merge. So I compacted the same 500 files three ways.
- Default compaction: 8.9 MB
- Snappy forced: 16.6 MB
- ZSTD forced: 9.0 MB
Merging alone cut the size roughly in half. The rest came from the compaction switching the compression codec from Snappy to ZSTD, which the appends had not used. It’s the same trap I hit when I compared Delta and Iceberg files: a size gap that looks like a format difference is often only a compression default. Check the codec before you credit the merge.
The same test on Azure, where I guessed wrong
My local numbers came from files on my own disk, which is the kindest place for small files. So I made a temporary Azure Blob Storage account in US East, uploaded the same five layouts, and ran the same query from my laptop in Pakistan over a home connection. Median of 5 runs again, each on a fresh DuckDB connection.
- 1 file: 3.72 s
- 100 files: 10.32 s
- 1,000 files: 9.53 s
- 5,000 files: 35.17 s
I had written that the gap would probably be bigger on object storage. In ratio it was smaller: 5,000 files were about 9.5 times slower than one file, against about 50 times locally. That’s because the one big file now has to cross the internet, and 30 MB takes a few seconds on its own.
In seconds it was much bigger. The extra time for 5,000 files was about 31 seconds, against about 0.6 seconds on local disk. The ratio shrank because the baseline grew. The penalty did not shrink at all.
The numbers are noisy, since the internet is. 100 files came out slower than 1,000, which is the connection, not a real effect. Read the shape, not the decimals.
Writing hurts too. Uploading with the Azure CLI took 44 seconds for the one 30 MB file, 4 minutes 12 seconds for 1,000 files, and 20 minutes for 5,000. Same data each time. Every file is one more request.
I deleted the storage account afterwards. I did not measure what the test cost, since Azure’s cost data lags by about a day, but it held about 170 MB for a few hours.
A float sum that changed between runs
The first version of my test failed for an odd reason. I was summing a float column over the same files, and the total was slightly different from run to run, even on a single file. The decimal version of the same column gave one answer every time. My guess: parallel workers add the numbers in a different order each time, and floats round differently depending on the order.
I’ve worked on payment systems, where a total that changes between runs is a bug, not a rounding note. Keep money in decimals.
What I didn’t test
I ran the local numbers on a laptop with a warm cache, and the Azure numbers from one home connection to one region. I did not test S3 or Google Cloud Storage, a nearer region, or a reader that sits in the same region as the storage, where the per-file delay should be smaller. Treat the 50 times as “big”, not as a rule.
I also only ran the Delta commands. OPTIMIZE and VACUUM are Delta Lake names. Iceberg does the same jobs under other names: rewrite_data_files to compact, and expire_snapshots to clear old versions, both listed in its Spark procedures. The ideas carry over. The commands do not.
A good interview answer
Say what breaks (opening files and planning the query), how you would fix it (compact into bigger files), and what you’d measure first. Then say what the fix costs: extra disk until you VACUUM, and time travel once you do.
The code, the numbers and the tests are in the small-files repo. You can run the local parts in a couple of minutes. The Azure part needs your own storage account.
p.s. Every interesting result here came from checking something I had assumed.
Muhammad Mairaj is a Senior Backend Engineer with 8 years building payment and data systems. He writes about data engineering, measured. More about him.