Spark’s small-file crossover is 4 MB a file, not 40,000 files

Forty thousand files is not a number Spark cares about. It prices every file open as a published, fixed quantity of scanning — large next to what streaming sinks produce. Divide a table’s bytes by it and you get the file count at which opening and reading cost the same. Which side of it you sit on decides everything else. A bigger cluster won’t move you across it.

Spark charges four megabytes of scanning for every file it opens. The crossover sits at 4 MB of mean file size, not at any file count: below it, most of the job is metadata. Listing only overtakes reading forty times later, around 100 KB a file. On a Delta or Iceberg table, reads never list.

Where a Spark job’s planner budget goes on 40,000 small files

On 40,320 files holding 14 GB, Spark’s planner charges 91.8% of the job to opening files and 8.2% to reading them.

Kettlebridge owns those files, and Kettlebridge is invented: a 31-person company whose product manages moorings and berths, in a market we’ve never sold into. The file count, the row size and the writer cadence are all picked numbers — pick different ones and the divisions still work. An earlier piece on full reloads and change data capture put the same orders table at 400 GB and 2 KB a row; what lands out of that stream is the subject here, at about 1 KB a row once Parquet has compressed it.

Call the landing volume 2 GB a day: about two million rows change daily, and at roughly 1 KB a row that is 2,000,000 KB, or 1,953 MB. Call it 2,048 MB, which is the one place the chain rounds up. Four writers each close a file a minute, so a day produces 1,440 × 4 = 5,760 files and a week produces 40,320. A week of volume is 2 × 7 = 14 GB, and 14 × 1,024 = 14,336 MB.

Now the division. 14,336 MB over 40,320 files is 0.3556 MB a file, or 364 KB, since 0.3556 × 1,024 = 364.1. Spark adds a flat 4 MB to every file before it plans, so the planner works with 0.3556 + 4 = 4.3556 MB each, and 4 / 4.3556 = 91.8% of that is the open.

Eight per cent of it is data.

What spark.sql.files.openCostInBytes is, and why the default is 4 MB

spark.sql.files.openCostInBytes is Spark’s price for opening a file, quoted in bytes of scanning, and it ships at 4194304 — four megabytes.

The definition repays reading in full; its second half is why this piece has a last section. Spark’s SQL performance tuning page gives it, in the Tuning Partitions table directly under Caching Data, as “The estimated cost to open a file, measured by the number of bytes that could be scanned in the same time. This is used when putting multiple files into a partition. It is better to over-estimate, then the partitions with small files will be faster than partitions with bigger files (which is scheduled first). This configuration is effective only when using file-based sources such as Parquet, JSON and ORC.” and marks it present since 2.0.0. Hold on to “It is better to over-estimate”. I’ll come back for it.

The row above it holds the number everybody quotes instead: spark.sql.files.maxPartitionBytes, default 134217728 bytes, or 128 MB, defined as “The maximum number of bytes to pack into a single partition when reading files.” One caps a read split. The other prices a file handle.

Apache Iceberg publishes the same figure under another name. read.split.open-file-cost also defaults to 4194304, and the Iceberg configuration page calls it “The estimated cost to open a file, used as a minimum weight when combining splits.” Three rows above it in the same Read properties table, read.split.target-size defaults to 134217728. Two engines, the same pair of numbers.

Adding the open cost and dividing is not new — the padded-size formula is in the field in several places and I’m not claiming it. The step I haven’t seen made is to stop at a file size instead of a partition count. Call that number open-cost parity: the mean file size at which opening a file costs an engine as much as reading its contents. The tolerance isn’t mine. Spark publishes it, Iceberg publishes the same 4,194,304, and the matching count is table bytes / 4 MB. Kettlebridge’s files aren’t uniform, obviously; a micro-batch landed in the evening rush is fatter than one at 04:00. The mean is what the planner sums.

Run the table through Spark’s accounting. 40,320 opens at 4 MB each is 161,280 MB before a byte of data moves; add the 14,336 MB that exists and the planner sees 175,616 MB, or 175,616 / 1,024 = 171.5 GB. Then 175,616 / 14,336 = 12.25. A 14 GB table, priced at 171.5.

At what file count does listing overtake reading?

Below about 100 KB a file on a flat prefix, which for Kettlebridge’s 14 GB would take roughly 143,000 files — forty times past the open-cost crossover that had already bitten.

Work it out from the list call. Amazon’s S3 API reference for ListObjectsV2 says of max-keys that “By default, the action returns up to 1,000 key names.” Continuation is serial: each call needs the NextContinuationToken from the one before, so 40,320 files take at least 41 calls in sequence — the one part of a Spark job you cannot widen by adding executors. Take 100 ms for a round trip and 1,000 MB/s of aggregate scan throughput, both chosen.

So listing costs 100 ms / 1,000 keys = 0.1 ms a file, and reading one costs s MB / 1,000 MB/s; for s in megabytes, s milliseconds. The two are equal at s = 0.1 MB, about 102 KB, or 14,336 / 0.1 = 143,360 files here. Parity sits at 4 MB, so 4 / 0.1 = 40: the crossover that matters arrives forty times earlier. Kettlebridge at 0.3556 MB sits 3.56x above the listing crossover and 4 / 0.3556 = 11.25x below parity.

Listing isn’t the bottleneck here.

Spark does have a listing cliff, documented twice with two different descriptions. spark.sql.sources.parallelPartitionDiscovery.threshold defaults to 32 in both. Four rows below openCostInBytes, in the Tuning Partitions table quoted in the section above, it “Configures the threshold to enable parallel listing for job input paths”; in the Runtime SQL Configuration table on configuration.html the same flag with the same since-1.5.0 stamp is “The maximum number of paths allowed for listing files at driver side”, and past that count it “tries to list the files with another Spark distributed job”. One reads like a switch, the other like a hard cap. The flag is the switch. That job fans out across paths, not across the continuation pages of one prefix, so it does nothing for the 41 serial calls above. Apache Iceberg’s performance page describes the same manoeuvre from the other side: its first claim for scan planning is “Lower latency SQL queries – by eliminating a distributed scan to plan a distributed scan”. Iceberg plans from a manifest list that indexes its manifests; Delta reads its file list from the transaction log. Neither lists a prefix. The Delta operation you will actually hit that still enumerates a directory is VACUUM, and Delta 3.3 gave even that a log-reading VACUUM LITE mode.

Check your own small-file crossover in two divisions

Divide the table’s bytes by 4 MB to get the file count at which opening equals reading, then divide your actual file count by that to see how far past it you are.

For Kettlebridge: 14,336 / 4 = 3,584 files is parity, and 40,320 / 3,584 = 11.25, so the table carries 11.25 times the files its bytes justify. Both come out of metadata the table already keeps.

# Delta Lake: the two divisions, straight from the table's own metadata.
# 4194304 is spark.sql.files.openCostInBytes, unchanged from its default.
d = spark.sql("DESCRIBE DETAIL delta.`s3://kettlebridge/orders_changes`").first()
parity_files = d.sizeInBytes / 4194304
print(d.numFiles, parity_files, d.numFiles / parity_files)

# Iceberg: the same two numbers, out of the files metadata table.
#   SELECT count(*), sum(file_size_in_bytes) / 4194304
#   FROM kettlebridge.orders_changes.files

A second route reaches the same answer by counting tasks. Spark sets maxSplitBytes to min(defaultMaxSplitBytes, max(openCostInBytes, bytesPerCore)), so with 175,616 MB of accounted work the 128 MB cap binds under 175,616 / 128 = 1,372 cores. Each file adds 4.3556 MB to a task, and the packing loop tests the fit on file.length alone while the accumulator charges file.length + openCostInBytes, so thirty files land in one task at 30 × 4.3556 = 130.67 MB against a 128 MB setting. Then 40,320 / 30 = 1,344 tasks. The same 14,336 MB in 128 MB files is 14,336 / 128 = 112 files at 128 + 4 = 132 MB accounted each, or 14,784 MB, so the cap binds only under 14,784 / 128 = 115.5 cores — and on a cluster that small, 112 tasks. And 1,344 / 112 = 12.0 against the accounting route’s 12.25, two per cent apart.

Now derate the estimate the argument rests on, because Spark says it is inflated deliberately. At 2 MB, parity moves to a 2 MB mean, which Kettlebridge’s 0.3556 MB is still 2 / 0.3556 = 5.6x below. At 1 MB the gap is 2.8x, at 0.5 MB it is 1.4x, and the call only flips at 4 / 0.3556 = 11.25x of derating, the same 11.25 from above and further than any documented default I know of has been wrong.

Below roughly 3,584 files for this table (one file for every 4 MB) compaction isn’t worth scheduling and I’d leave it alone. I run those two divisions at the start of most lakehouse reviews in our data engineering work; they take just a minute and decide whether anything else is worth doing.

Delta OPTIMIZE and Iceberg rewrite_data_files aim at different file sizes

Delta Lake’s auto compaction targets 128 MB and Iceberg’s rewrite_data_files targets 512 MB, and neither is a read-side setting.

OPTIMIZE changes layout and nothing else. Delta Lake’s file management documentation describes bin-packing compaction as improving reads by “coalescing small files into larger ones”, and is blunt about the limit: it “makes no data related changes to the table, so a read before and after an OPTIMIZE has the same results”. Auto compaction arrived in Delta Lake 3.1.0, and its options table says: “By default will use 128 MB as the target file size.”

One quirk there is worth your minute. A Note in Delta’s own compaction section tells you to set spark.databricks.delta.optimize.repartition.enabled=true to use repartition(1) instead of coalesce(1) “for better performance when compacting many small files”, which is the only situation anybody runs OPTIMIZE for. The page never prints the default, which is the tell: a setting you are told to switch on for the common case is off in it.

Iceberg’s Spark procedures page opens rewrite_data_files by saying why it bothers: “Iceberg tracks each data file in a table. More data files leads to more metadata stored in manifest files, and small data files causes an unnecessary amount of metadata and less efficient queries from file open costs.” The grammar is theirs.

Four published defaults from the same stack and what each one sizes. Three are on-disk targets; the fourth is a size in memory, before Parquet compresses anything.

Nothing in that stack reconciles 128 with 512 for you, and the last row doesn’t match its own column: Delta documents optimizeWrite.binSize as controlling “the target in-memory size of each output file”, so a 512 MiB bin lands on disk as whatever Parquet squeezed it to. And rewrite_data_files has a definition of small that catches almost everything: min-file-size-bytes defaults to 75% of the target, so 0.75 × 512 = 384 MB, and files under that “will be considered for rewriting regardless of any other criteria”. The ceiling is 180%, or 1.8 × 512 = 921.6 MB. Open the General Options table and check both: it says why a table of 300 MB files keeps getting rewritten.

Compaction is not free, and hourly compaction is usually too often

A compaction run is worth making no sooner than target size divided by arrival rate, which at 2 GB a day is 1.5 hours for a 128 MB target and six hours for a 512 MB one. 2 GB a day is 2,048 MB. A 128 MB target needs 128 / 2,048 = 0.0625 of a day, or 1.5 hours; a 512 MB target needs 512 / 2,048 = 0.25 of a day, six hours. Run it hourly and each pass has 2,048 / 24 = 85 MB to work with, so it writes 85 MB files that miss Delta’s 128 MB target and fall far under Iceberg’s 512 MB one. The next pass rewrites them. You pay for the same bytes twice and get files nobody asked for. I wouldn’t set that up; hourly earns its place only where you drop the target to match what an hour produces.

Storage is the other half. Delta’s delta.deletedFileRetentionDuration defaults to interval 7 days and VACUUM doesn’t run itself, so a week of superseded files sits beside the compacted ones: 14 GB of originals plus 14 GB of output, about 28 GB where there was 14. That is what the right call costs.

What compaction actually bought Kettlebridge: a 3x speedup, not 12x

Spark’s planner says Kettlebridge’s job carried 12.25x the work its bytes imply; on the clock, compaction takes 45.8 seconds down to 14.4, which is 3.2x.

The gap is Parquet. The Apache Parquet file format documentation puts the file metadata after the data, behind a four-byte length and a PAR1 magic number, and says “Readers are expected to first read the file metadata to find all the column chunks they are interested in.” Call it two round trips a file, a footer read and then a data read. That is a floor I chose rather than a count anybody publishes, and a reader that fetches the tail, then the footer, then the column chunks takes three.

Lay the seconds out. 40,320 files × 2 = 80,640 round trips; at 100 ms with 256 opens in flight (say 64 executors at four threads each), that is 80,640 × 0.1 / 256 = 31.5 seconds of latency, and the scan is 14,336 / 1,000 = 14.3 seconds. Together, 45.8. Compacted into 14,336 / 128 = 112 files, latency becomes 112 × 2 × 0.1 / 256 = 0.09 seconds and the job is 14.4. Then 45.8 / 14.4 = 3.2.

Something here I had backwards for a while. A bigger cluster does cut the clock: double it to 128 executors and 512 threads, and if throughput doubles with the cores, latency is 80,640 × 0.1 / 512 = 15.75 seconds against a scan of 14,336 / 2,000 = 7.2, so the job finishes in 22.9 instead of 45.8. What it cannot touch is the split. 31.5 / 14.3 = 2.2 before, 15.75 / 7.2 = 2.2 after — identical, because both terms divide by the same concurrency. You have halved the clock, doubled the bill, and you are still spending 69% of every core waiting on file headers. Compaction moves the split rather than the clock: at 112 files it is 0.09 seconds of waiting against 14.3 of scanning, 99% data, and from there more cores buy speed on real work. Above parity, cores win; below it, they buy faster waiting.

So the planner’s 12.25x and the clock’s 3.2x are not the same quantity and should not be divided into each other. Spark said so in the definition quoted earlier: “It is better to over-estimate”. openCostInBytes is a scheduling weight in virtual bytes, picked so a task of small files finishes ahead of a task of big ones. It was never a stopwatch.

Double the volume and the answer holds: 4 GB a day on the same cadence is 28,672 / 40,320 = 0.7111 MB a file, still 4 / 0.7111 = 5.6x below parity. Halve it and 7,168 / 40,320 = 0.1778 MB is 22.5x below, still above the 0.1 MB listing crossover. You can’t grow out of a small-file problem and you can’t shrink into a listing one.

What I can’t hand you is the seconds. The 100 ms round trip is a round number I chose, not a figure anybody publishes for a LIST call; two round trips a file is a floor rather than a count; and I have not timed either against this stack. Move any one of them and the 69% moves with it. What does not move is the shape, because both terms divide by the same concurrency, so no cluster size changes which of them dominates. Only the file count does. If a runtime or a bill is climbing while the data stays flat, layout is the usual culprit rather than price, and the two divisions above will tell you inside a minute whether layout is what you have.