Every table on object storage drifts toward having too many files that are too small. This is not a configuration mistake, it is the natural consequence of writing continuously to a storage system where each write produces its own objects. Left alone, a table that was fast when it was built becomes slow in a way that no query change will fix, and the cause is not visible in any query plan.

Where the files come from

A streaming job committing every minute writes at least one file per partition per commit. With twelve partitions and a one minute trigger, that is seventeen thousand files a day from a single job that may only be moving a few gigabytes.

A batch job writing with two hundred output tasks writes two hundred files per run, whatever the volume. If the run handled four hundred megabytes, the files average two megabytes each.

Change data capture is worse than either. Each batch of updates touches whatever partitions the changed rows fall into, and a hundred changed rows spread across ninety days of history produces ninety tiny files.

Backfills add their own. A backfill that corrects one column across a year of data rewrites that year, and if its parallelism is set for throughput rather than for output size, it leaves thousands of files behind.

None of these are errors. Each job did exactly what it was configured to do, and the file count is a side effect of parallelism and commit frequency rather than of data volume.

The four costs, separately

People talk about the small file problem as one thing. It is four, and they scale differently, so it helps to keep them apart.

Planning cost grows with the number of files the query must consider. The engine lists or reads metadata for every candidate file, applies whatever pruning it can, and builds a set of splits. This happens on one machine before any work is distributed.

Open cost grows with the number of files actually read. Each file means a request to the storage service, a footer read to find the schema and the row group layout, and then a data read. The first two of those are fixed overhead that does not shrink as the file shrinks.

Metadata cost grows with the number of files the table tracks, whether a query reads them or not. Every file has an entry describing its path, its size, its row count, and its per column statistics. A million file table carries a million such entries, and every commit has to read and rewrite some portion of them.

Scheduling cost grows with the number of tasks the file count produces. Most engines map a split to a task, so ten thousand small files becomes ten thousand tasks, each with task setup, scheduling latency, and result collection that is unrelated to how much data it processed.

What the planner pays

Planning is the cost people miss, because it does not appear as a task anywhere in the engine's own reporting. It is simply the gap between submitting the query and seeing the first stage start.

On a table with a few thousand files, that gap is a second or two. On a table with half a million, it can be a minute, and it is a minute spent on a single thread fetching and deserialising statistics. The query has not read a byte of data yet.

It also gets worse under concurrency, because every query pays it independently. Twenty analysts running queries against the same badly sized table produce twenty concurrent planning passes, each one fetching the same metadata.

What the executors pay

Executor cost is more intuitive but still surprising in magnitude. A read of a well sized file amortises the request overhead across hundreds of megabytes. A read of a one megabyte file does not.

Measure it once on your own storage and the shape is always the same. Request latency is tens of milliseconds and does not depend much on size. Columnar footer parsing is a few milliseconds. So a one megabyte read spends a meaningful fraction of its time on overhead, and a five hundred megabyte read spends almost none.

That is the real reason small files are slow. Not that the bytes are harder to read, but that the fixed cost per file is paid once per file.

What the metadata pays

The metadata cost is the one that sneaks up, because it is paid by writers rather than by readers, and writers do not complain the way analysts do.

Every commit reads the current file list, computes a new one, and writes it back. The size of that work is set by how many files the table has, not by how many the commit adds. A commit adding three files to a table with eight hundred thousand files does real work proportional to eight hundred thousand.

This is why a table that has gone unmaintained has slow writes as well as slow reads, and why the first thing compaction fixes is often commit latency rather than query time.

Why nobody notices for months

The degradation is gradual and uniform, which is the worst combination for being noticed.

Nothing breaks. No query fails, no job errors, no alert fires. Each week is a little slower than the last, by an amount well inside normal variance.

There is no single bad query to blame, because every query on the table got slower by roughly the same factor. When everything is slower, nothing stands out as the cause.

And the usual response makes it worse. Queries are slow, so somebody adds executors, which produces more output tasks, which produces more files. The table that was already badly sized is now being written to by a job with higher parallelism.

The numbers worth measuring

Three numbers tell you where a table stands, and all three are cheap to collect from the table's own metadata.

Total live file count, which bounds planning and metadata cost. Median file size, which tells you whether reads are amortising their overhead. And the share of files under some floor, say thirty two megabytes, which tells you whether the problem is a long tail or the whole table.

Collect those weekly for every table a platform owns and the ranking is always surprising. The tables people complain about are rarely the worst ones by these measures, because complaints track query volume rather than table health. The worst tables are usually ones nobody queries interactively, and they are the ones whose nightly jobs have quietly grown from twenty minutes to three hours.

What healthy looks like, for comparison

It is worth having a picture of the target, because otherwise any number looks defensible.

A healthy analytical table has a median file size in the hundreds of megabytes, a file count in the thousands rather than the hundreds of thousands, and a long tail of small files that is bounded by whatever the most recent writes produced rather than by the whole history of the table.

The last part is the one to internalise. Small files are not a defect, they are the normal product of a recent write. A streaming table will always have small files at its head, because it just wrote them, and that is fine. The defect is small files that are a week old, because it means nothing came along behind the writer to consolidate them.

So the question to ask about a table is not whether it has small files. It is how old the oldest small file is. If the answer is hours, maintenance is working. If the answer is months, the table has no maintenance and the file count will keep climbing until somebody notices the bill or the latency.

That reframing also tells you what to alert on. Not file count, which is a number without a natural threshold, and not median size, which moves slowly. Alert on the age of the oldest file below your size floor, because it directly measures whether the consolidation loop is running at all, and it goes bad within a day of that loop stopping rather than within a quarter.