Spark Partitions and File Sizes: repartition, coalesce and the Small-Files Problem

Two different "partitions"

• In-memory partitions: chunks of a DataFrame that tasks process in parallel. One task per partition per stage.
• Storage partitions: directories on disk, like sales/order_date=2026-10-01/, created by partitionBy.

Each in-memory partition writes at least one file per storage partition it contains. That is where the small-files problem comes from.

repartition vs coalesce

# 200 shuffle partitions × 30 dates could write up to 6,000 files
df.write.partitionBy("order_date").parquet(path)
 
# cluster rows by the storage partition first: roughly one file per date
(df.repartition("order_date")
   .write.partitionBy("order_date")
   .parquet(path))

Why small files hurt

• Every file means metadata to list and open; query planning slows down.
• Columnar formats compress and skip data poorly in tiny files.
• Object stores add per-request latency and cost.

A common target is files of roughly 128 MB to 1 GB each.

Tools that help

• Adaptive Query Execution (spark.sql.adaptive.enabled, on by default in Spark 3.2+) coalesces small shuffle partitions automatically.
• spark.sql.files.maxRecordsPerFile caps rows per file to avoid giant files.
• Table formats: Delta Lake OPTIMIZE (and Iceberg/Hudi equivalents) compact small files after the fact.

A quick checklist

1. Choose storage partition columns with modest cardinality (dates, not user IDs).
2. Repartition by those columns before writing.
3. Let AQE size shuffle partitions; tune spark.sql.shuffle.partitions only when needed.
4. Compact regularly if many small batches append to the same table.