The small files problem

Why does a query slow to a crawl when the data it reads is split into too many pieces?

A table holds ten gigabytes. On Monday a query over it returns in four seconds. Three months later the table still holds roughly ten gigabytes — the same rows, the same schema, nothing added but the ordinary daily churn — and the same query takes six minutes.

Nothing got bigger. What could possibly have changed? The tempting answer is that the storage system degraded somehow, or that the cluster is busier. But the far more common cause is stranger and more interesting: the same amount of data is now spread across a hundred times as many files, and reading a byte turns out to cost much less than reading a file.

Begin with a question that sounds too simple. What does it actually cost to read one file from object storage? Not per byte — per file, before any bytes move.

There is a request to issue and a response to wait for. There is a name to resolve and permissions to check. If the file is columnar, there is a footer to fetch, parse, and interpret before you know where anything is. The query engine has to plan a task for it, schedule that task onto a worker, and later collect the result. Each of those is small. None of them scales with the file's size.

That is the whole mechanism, and everything else follows from it. Let us call it a fixed per-file cost. If a file holds 500 MB, that fixed cost is amortised over a huge amount of useful work and disappears into the noise. If the same file holds 50 KB, the fixed cost may be larger than the read itself — you have paid for the ceremony and got almost no data for it.

So ask: what happens to total time as you hold the data constant and increase the file count? The bytes read stay the same. The fixed cost is paid once per file, so it scales linearly with the count. Split ten gigabytes into ten thousand files instead of a hundred, and you have multiplied the ceremony by a hundred while changing the payload not at all. That is your six minutes.

Now the more revealing question: why do files get small on their own? Nobody chooses this. It emerges from how writes work in these systems.

Object storage is essentially append-only — you cannot edit the middle of an existing file, you can only write a new one. So every streaming micro-batch writes its own file. Every partitioned write produces one file per partition per writer task, which is a product, not a sum: twenty writers into fifty date partitions is a thousand files from a single job even if each holds a handful of rows. Every update or delete in a table format like Iceberg, Delta Lake, or Hudi either rewrites a data file or adds a small delete file beside it. Each of these is individually reasonable. The accumulation is nobody's decision.

Does this only hurt the read path? No, and this is where the cost compounds. The table's own metadata — the manifest listing which files exist, with their statistics — grows with the file count too. Planning a query means reading that metadata to decide which files to touch, so the planning phase slows down before a single data file is opened. Different machinery on different platforms, same shape of problem.

What is the fix, then? Compaction: periodically rewrite many small files into fewer large ones, and atomically swap the table's pointer to the new set. Modern table formats do this as a maintenance operation, sometimes automatically. And here is the part worth noticing — compaction reads and rewrites data that was already correct. It produces no new information at all. It is pure overhead whose only purpose is to restore a good physical layout.

Which raises the obvious counter-question: if bigger files are better, why not one enormous file? Because you lose parallelism — a query engine splits work by file (or by row group within a file), so a single object cannot be read by many workers efficiently, and you lose the ability to skip. The target is a range, not a maximum. The commonly cited sweet spot sits somewhere around 128 MB to 1 GB per file, and the reason it is a range rather than a number is that the right value depends on your engine's split size, your storage's request latency, and how selective your queries are. Treat any specific figure as a starting point to measure from, not a law.

Three things sharpen the picture.

First, "small" is not an absolute size — it is small relative to the fixed cost of touching a file in your system. On local NVMe with a lightweight format, files that would be disastrous on remote object storage are merely suboptimal. The problem is a ratio, so it gets worse whenever storage moves further away from compute.

Second, over-partitioning is the usual root cause rather than a separate issue. Partitioning by day is fine; partitioning by day and hour and customer guarantees that most partitions hold almost nothing, and each still costs a file. If you find yourself compacting constantly, the layout above the files is often what needs changing.

Third, a misconception worth correcting: this is not a symptom of "too much data". It is a symptom of data being divided badly, and it can afflict a small table severely while a very large, well-compacted table runs fine. The remedy is rarely more compute. Throwing workers at the problem buys parallelism against a cost that was never about throughput.

• Partitioning strategy, and why fine-grained partitions manufacture this problem.
• How Iceberg, Delta Lake, and Hudi each schedule compaction and swap file sets atomically.
• Row groups inside a Parquet file — the same ratio argument one level down.
• Copy-on-write versus merge-on-read, and how delete files change the arithmetic.

Every term the collection defines is gathered in the glossary.