Home / Data Platform / Databricks & Spark

Data PlatformHow-toBeginner

Introduction to Spark shuffle partitions: what spark.sql.shuffle.partitions does and how AQE sizes them for you

What a shuffle is, why the default of 200 partitions is often wrong, how Adaptive Query Execution coalesces and splits partitions, and simple rules for setting partition counts and file sizes.

Deepak Bhardwaj4 min read

Applies toApache Spark 3.x (Databricks, Fabric, Synapse, EMR, open source), PySpark and Spark SQL

What a shuffle is

Some operations need rows with the same key on the same executor:

  • groupBy / aggregations
  • join (unless one side is broadcast)
  • distinct, repartition, window functions with partitionBy

Spark moves data across the network so that happens. That move is a shuffle, and the data on the other side is split into shuffle partitions. Each shuffle partition becomes one task in the next stage.

Stage 1 (read files) ──shuffle write──► [ partition 0 | 1 | 2 | ... | N-1 ] ──► Stage 2 (N tasks)

The setting: spark.sql.shuffle.partitions

The number of shuffle partitions for DataFrame and SQL operations is spark.sql.shuffle.partitions, default 200.

spark.conf.get("spark.sql.shuffle.partitions")
spark.conf.set("spark.sql.shuffle.partitions", 400)

Why 200 is often wrong:

Data shuffled With 200 partitions Effect
50 MB 0.25 MB per partition 200 tiny tasks; scheduling overhead dominates; 200 tiny output files
2 TB 10 GB per partition Huge tasks, spill to disk, out-of-memory, one slow stage

A good target is roughly 100-200 MB per shuffle partition, and at least as many partitions as total executor cores so every core has work.

Adaptive Query Execution (AQE) does most of this now

Since Spark 3.2, AQE is on by default (spark.sql.adaptive.enabled=true). After each shuffle, AQE looks at the real sizes and:

  1. Coalesces small partitions into bigger ones (fixes the "too many tiny tasks" case).
  2. Splits skewed partitions in joins (fixes one giant partition).
  3. Switches join strategy, for example to broadcast join when one side turns out to be small.

Key settings:

spark.conf.get("spark.sql.adaptive.enabled")                          # true
spark.conf.get("spark.sql.adaptive.coalescePartitions.enabled")       # true
spark.conf.get("spark.sql.adaptive.advisoryPartitionSizeInBytes")     # target size, e.g. 64MB
spark.conf.get("spark.sql.adaptive.skewJoin.enabled")                 # true

With AQE, think of spark.sql.shuffle.partitions as the starting (maximum) count. Set it high enough for your largest shuffle; AQE coalesces down for smaller ones.

Tip

On Databricks, spark.sql.shuffle.partitions = auto lets the platform pick the initial number too (Databricks-specific; check your runtime docs). On open-source Spark, set a number.

See what happened in the Spark UI

  1. SQL / DataFrame tab → open the query.
  2. Look for AQEShuffleRead (or CustomShuffleReader in older versions) nodes: they show "number of partitions" after coalescing and "number of skewed partitions".
  3. Stages tab → for the stage after a shuffle, check Shuffle Read Size in the task summary. Median vs max shows skew.

From code you can see the plan:

df = orders.groupBy("customer_id").agg({"amount": "sum"})
df.explain(mode="formatted")

Exchange hashpartitioning(customer_id, 200) shows the shuffle and its partition count.

repartition vs coalesce

Method Shuffle? Use for
df.repartition(n) Yes Increase partitions, or balance data evenly
df.repartition(n, "col") Yes Co-locate rows by key before a heavy operation or partitioned write
df.coalesce(n) No Reduce partitions cheaply before writing (may create uneven tasks) 
# Write about 8 files instead of 200 tiny ones
(daily_summary.coalesce(8)
   .write.mode("overwrite").format("delta").save("/lake/gold/daily_summary"))

Warning

coalesce(1) funnels all data through one task. Fine for a few MB; a disaster for GBs. For Delta tables, prefer optimized writes / OPTIMIZE over manual coalescing.

Simple rules

  1. Leave AQE on.
  2. For jobs that shuffle hundreds of GB or more, set spark.sql.shuffle.partitions to about shuffle_size / 128 MB, rounded up to a multiple of total cores.
  3. For small jobs, don't lower it by hand; AQE coalesces.
  4. If one task runs far longer than the rest, it's skew, not partition count. Look at the skew-join settings or salt the key.
  5. Control output file counts separately from shuffle partitions (coalesce before write, optimized writes, or OPTIMIZE).

Quick symptom table

Symptom Likely cause First thing to try
Thousands of tasks taking milliseconds Too many partitions, AQE off Enable AQE coalescing
Few tasks, heavy spill, OOM Too few partitions for data size Raise spark.sql.shuffle.partitions
One task 50× longer than median Key skew AQE skew join; salt hot keys; broadcast small side
Thousands of tiny output files Shuffle partitions = output files Coalesce before write or optimize table

Prevent problems later

  • Log input size, shuffle size and duration per job run so you can spot drift as data grows.
  • Set partition settings per job, not cluster-wide, when jobs differ a lot in size.
  • Review the Spark UI of your top five longest jobs monthly; most wins come from a few jobs.

Related fixes