The small files problem eats your read throughput

The table is 400 GB. The cluster has plenty of cores. The query touches three columns out of ninety and filters to a single day. It should take under a minute, and it takes forty.

Then somebody runs a file count against the storage path and gets 1.2 million objects, averaging a little over 300 KB each. That is the entire explanation, and no amount of extra compute is going to fix it, because the job is not spending its time on your data. It is spending its time on paperwork.

The fixed toll per file

Reading one file from an object store is not a free operation that scales smoothly down to zero as the file gets smaller. It is a sequence of fixed costs:

A listing entry. Before anything is read, the engine must discover which files exist. On S3 a LIST returns at most 1,000 keys per request, so a million files is at least a thousand sequential-ish API calls before a single byte of data is touched. Deeply nested partition directories multiply this, because each prefix gets listed separately.

A metadata read. Parquet stores its schema, row-group boundaries, and column statistics in a footer at the end of the file. To read any Parquet file at all, the engine must first fetch that footer — a separate request, against a separate byte range, before the real read begins.

A connection and a round trip. Every object-store GET is an HTTPS request whose first-byte latency is measured in tens of milliseconds. TCP never gets out of slow start on a 300 KB payload, so you pay full latency and never reach full bandwidth.

A scheduled task. In Spark, file splits become tasks, and each task has JVM-level setup, serialization, and scheduling overhead measured in milliseconds. A million tiny tasks is a scheduler problem long before it is a data problem.

Add those together and a 300 KB file spends most of its wall-clock budget on overhead. A 256 MB file pays the exact same overhead, once, and then streams for long enough that the fixed cost disappears into the noise. This is why throughput on a small-file table can be an order of magnitude below what the same bytes deliver when they are packed properly.

Parquet stops being Parquet

The overhead is only half of it. The other half is that a tiny Parquet file throws away the reasons you chose Parquet.

Parquet organises data into row groups — horizontal slices, conventionally around 128 MB — and within each row group, into column chunks with min/max statistics. That structure is what powers the two optimisations analytical engines rely on: read only the columns you asked for, and skip entire row groups whose statistics prove they cannot match your filter.

A 300 KB file has one row group holding a few thousand rows. There is nothing to skip; the row group either matches or it does not, and either way you have already paid to open the file and read the footer to find out. Predicate pushdown, the feature that makes columnar formats fast, does essentially nothing.

Compression suffers too. Parquet’s dictionary and run-length encodings build their dictionaries per column chunk, so they need volume to find repetition. The same data split across thousands of tiny chunks compresses measurably worse than the same data in a few large ones, which means you are also storing and transferring more bytes than you need to.

Where the files come from

Nobody sets out to write a million small files. Four mechanisms produce them quietly.

Streaming micro-batches. A structured streaming job with a thirty-second trigger writes at least one file per partition per trigger. That is 2,880 triggers a day; with even a modest number of output partitions you are into six figures of files per day, forever.

Over-partitioning. partitionBy on a high-cardinality column is the classic. Partitioning by date gives 365 directories a year; partitioning by (date, customer_id) with ten thousand customers gives 3.65 million, each holding a handful of rows. The file count is roughly partitions × writer tasks, and both terms grow.

Default shuffle partitions at write time. A job that ends with a wide transformation writes one file per output partition. With spark.sql.shuffle.partitions at its default of 200, a job producing 50 MB of output writes 200 files of 250 KB each — and it does this every single run.

Row-level upserts and CDC. Every MERGE in a Delta or Iceberg table rewrites the files touched by the merge. High-frequency merges against scattered keys rewrite many files into many new small files, and the transaction log grows on every commit.

Compaction is maintenance, not an incident response

The fix for files that already exist is compaction: read many small files, write few large ones, atomically swap them in.

On Delta Lake that is OPTIMIZE, which bin-packs a table or a subset of partitions into files on the order of hundreds of megabytes to a gigabyte. On Iceberg it is the rewrite_data_files procedure, whose target file size defaults to 512 MB. On a plain Parquet directory with no table format, it is a read, a repartition to the file count you want, and an overwrite — with all the atomicity risks that implies, which is one of the better arguments for adopting a table format in the first place.

The important word is routine. Compaction should be a scheduled job with an owner, running nightly or hourly against the tables that accumulate files, in the same way vacuuming and statistics collection are routine on a database. Teams that treat it as something you do after a performance complaint end up doing it under pressure, on a table so fragmented that the compaction itself struggles to run.

Two operational notes that bite people. First, compaction rewrites data, so it competes for the same cluster and the same storage-API budget as your pipelines — schedule it accordingly. Second, on Delta and Iceberg the old files remain for time-travel until you expire snapshots or run VACUUM, so compaction increases storage before it decreases it, and a retention window shorter than your longest-running reader will break that reader.

Stop creating them

Compaction is the cure. Sizing the write is the prevention, and it is cheaper.

Target a file size, not a partition count. Before writing, estimate output volume and repartition to roughly total_bytes / target_size files, aiming somewhere in the 128 MB to 1 GB band. Many engines will do this for you if asked: Databricks offers optimized writes and auto-compaction as table properties, and Iceberg will bin-pack toward its target size on write.

Partition on low-cardinality columns only. A good rule is that every partition should hold at least a few hundred megabytes. If a candidate partition column would produce partitions smaller than that, it is a clustering or sort key, not a partition key. Iceberg’s hidden partitioning and Delta’s liquid clustering both exist precisely so you can get pruning without directory explosion.

Lengthen streaming triggers, or compact behind them. A five-minute trigger writes a tenth as many files as a thirty-second one. When the latency requirement is real and the trigger cannot move, run a compaction job behind the stream on a schedule and accept the two-tier design.

Watch the number. File count per table, and average file size per partition, belong on the same dashboard as row counts and freshness. Both are cheap to compute and both trend badly for weeks before anyone notices the query got slow.

The framing that makes this obvious

Storage systems bill you for bytes. Query engines bill you for objects.

Those two facts pull in opposite directions, and the small-files problem is what happens when a pipeline optimises for the first and forgets the second. The engineer who writes one file per event has produced a perfectly correct, perfectly durable, perfectly indexed dataset that is close to unreadable at scale.

Count your files before you tune your cluster.

Learn it as a system

Start with Columnar storage and Parquet to see why row groups and footer statistics are the mechanism you are destroying, then read Partitioning — runtime and on disk for the difference between a partition in memory and a directory on storage, and how the two multiply into a file count. Finish with Delta Lake — ACID on top of Parquet for the transaction log, OPTIMIZE, and the retention rules that make compaction safe.