11.2 Data Skew and Partition Tuning: repartition vs coalesce

In a distributed computing framework like Apache Spark, performance depends fundamentally on uniform parallelism. When data is distributed evenly across cluster partitions, all executor cores work at equal capacity and complete their assigned tasks in roughly the same timeframe. However, when data distribution is uneven—a condition known as data skew—overall pipeline runtime is determined by the single slowest task. Managing data skew and configuring appropriate partition counts are among the most critical performance tuning responsibilities in Spark development.

---

1. The Nature and Root Causes of Data Skew

Data skew occurs when a disproportionate number of records share the same partition key. Because Spark routes all records with the same hash key to the same physical partition during a shuffle operation (hash(key) % numPartitions), any imbalance in key frequency produces an equivalent imbalance in partition size.

Common Real-World Causes of Skew

1. High-Frequency Default or Null Values: In transactional datasets, unpopulated or missing attributes are frequently assigned sentinel values (e.g., user_id = -1, "UNKNOWN", or null). If 30% of a billion-row dataset contains null in the join key, null groups can concentrate in one partition; ordinary equality inner joins may filter null keys, while grouping or null-safe joins can retain them, creating a catastrophic bottleneck.
2. Power-Law Distributions (Super-Users & Mega-Merchants): In e-commerce or social platforms, a tiny fraction of entities account for a massive share of total activity. Joining an orders table on merchant_id when top merchants generate millions of orders while average merchants generate dozens causes severe executor imbalance.
3. Low-Cardinality Grouping Keys: Partitioning by geographic attributes (such as country_code or state) when one jurisdiction (such as California or the United States) represents 80% of total volume creates persistent skew that cannot be resolved by simply increasing partition counts.

---

2. Programmatic Skew Remediation: Key Salting

When data skew cannot be resolved through filtering or automatic optimization, key salting provides a deterministic programmatic solution. Salting breaks down a single massive key group into multiple smaller, uniformly distributed subgroups by appending random integers to the key.

The Salting Algorithm Walkthrough

Consider joining a skewed primary DataFrame (large_df) with a smaller dimension DataFrame (dim_df) on customer_id, where customer_id = 'VIP_001' accounts for 40% of all transactions.

import pyspark.sql.functions as F

SALT_FACTOR = 4  # Split skewed keys into 4 distinct sub-buckets

# Step 1: Add a random salt integer (0 to SALT_FACTOR - 1) to the skewed dataset
salted_large_df = large_df.withColumn(
    "salt", 
    F.concat(F.col("customer_id"), F.lit("_"), (F.rand() * SALT_FACTOR).cast("int"))
)

# Step 2: Replicate the lookup dimension dataset across all salt values using explode()
salted_dim_df = dim_df.withColumn(
    "salt_array", 
    F.array([F.lit(i) for i in range(SALT_FACTOR)])
).withColumn(
    "salt_num", 
    F.explode(F.col("salt_array"))
).withColumn(
    "salt", 
    F.concat(F.col("customer_id"), F.lit("_"), F.col("salt_num"))
).drop("salt_array", "salt_num")

# Step 3: Perform the join on the salted key
joined_df = salted_large_df.join(salted_dim_df, on="salt", how="inner")

# Step 4: Drop the temporary salt column
final_df = joined_df.drop("salt")

How Salting Eliminates the Straggler

• Instead of all records for 'VIP_001' hashing to a single partition, they are distributed across four distinct keys: 'VIP_001_0', 'VIP_001_1', 'VIP_001_2', and 'VIP_001_3'.
• These salted keys can spread work across partitions; hash collisions and available task slots mean four keys do not guarantee four different partitions or simultaneous tasks.
• The dimension table is replicated four times, which introduces minimal memory overhead if the dimension table is relatively compact, while potentially reducing skew; validate the result and join cardinality.

---

3. Adaptive Query Execution (AQE) Dynamic Skew Join

Beginning in Apache Spark 3.0 (and enabled by default in Spark 3.2+), Adaptive Query Execution (AQE) provides native, automatic data skew remediation during runtime query execution.

# Verify AQE configuration
spark.conf.set("spark.sql.adaptive.enabled", "true")
spark.conf.set("spark.sql.adaptive.skewJoin.enabled", "true")

How AQE Detects Skewed Partitions

In standard execution, partition sizes are fixed once the shuffle exchange writes map output files to disk. With AQE enabled, Spark pauses query execution after the map stage completes and inspects runtime partition statistics before launching the reduce stage. A partition is dynamically flagged as skewed if both of the following threshold conditions are met:

1. Relative Size Threshold: Partition size > spark.sql.adaptive.skewJoin.skewedPartitionFactor $\times$ median(partition sizes). (Default factor: 5.0).
2. Absolute Size Threshold: Partition size > spark.sql.adaptive.skewJoin.skewedPartitionThresholdInBytes. (Default threshold: 256 MB, or 268435456 bytes).

Runtime Splitting of Skewed Partitions

When AQE identifies a skewed partition in a join:

1. It dynamically splits the skewed partition into smaller sub-partitions (e.g., splitting a 1.2 GB partition into five ~250 MB sub-partitions).
2. It pairs each sub-partition with the corresponding partition from the other side of the join (reading the non-skewed partition multiple times as needed).
3. In the physical plan, the join operator is annotated as SortMergeJoin(isSkew=true) or ShuffledHashJoin(isSkew=true).
4. This transformation occurs completely transparently without requiring developers to rewrite code or manually introduce salting logic.

---

4. Partition Sizing Guidelines and spark.sql.shuffle.partitions

Configuring the correct number of partitions is one of the most critical performance tuning knobs in Apache Spark.

Sizing Rule of Thumb: 100 MB to 200 MB per Partition

Treat 100 MB to 200 MB as an illustrative starting point, not an official universal memory-safe size:

• Partitions Too Large (> 500 MB to 2 GB): Large partitions exceed executor task memory, triggering frequent disk spills (Spill (Memory) / Spill (Disk)), heavy garbage collection pressure, or fatal java.lang.OutOfMemoryError crashes.
• Partitions Too Small (< 10 MB to 20 MB): Small partitions create excessive metadata overhead. Scheduling 100,000 tiny tasks overwhelms the driver with task serialization and scheduler coordination, while writing outputs creates the infamous "small file problem" on cloud storage.

Configuring spark.sql.shuffle.partitions

The configuration parameter spark.sql.shuffle.partitions controls the number of output partitions generated after wide transformations (joins, aggregations, distinct operations). Its default value is 200.

# Adjust shuffle partitions for a 300 GB dataset
# Target: 300,000 MB / 150 MB ≈ 2,000 partitions
spark.conf.set("spark.sql.shuffle.partitions", 2000)

• Small Datasets (< 2 GB total): 200 shuffle partitions is excessive. A 100 MB dataset split into 200 partitions creates 500 KB partitions, causing task scheduling overhead to eclipse actual compute time. A value of 8 to 32 is far more appropriate.
• Massive Datasets (> 1 TB total): 200 shuffle partitions is disastrous. 1 TB divided into 200 partitions creates 5 GB partitions that can spill or exhaust memory, depending on operators and resources.

---

5. repartition() vs. coalesce() Mechanics

PySpark provides two distinct methods for modifying the partition count of an existing DataFrame: repartition() and coalesce(). Selecting the incorrect method can introduce massive unnecessary network shuffles or fail to reduce partition counts altogether.

The Operational Danger of coalesce() with Increased Counts

If you invoke df.coalesce(n) with a number $n$ that is greater than the current partition count, Spark silently ignores the request and retains the existing partition count:

# df currently has 10 partitions
increased_df = df.coalesce(50)
print(increased_df.rdd.getNumPartitions())
# Output: 10 (No change occurred! Coalesce cannot increase partitions)

# Correct method to increase partitions:
properly_increased_df = df.repartition(50)
print(properly_increased_df.rdd.getNumPartitions())
# Output: 50

Repartitioning by Column Expression

In addition to specifying an integer count, repartition() allows specifying one or more partitioning columns:

# Repartition by column hash
repartitioned_df = df.repartition(200, "region", "country")

This operation hashes the specified columns, ensuring that all records sharing identical (region, country) values land in the same physical partition. If a subsequent operation performs df.groupBy("region", "country") or joins on those exact columns, Spark recognizes that the data is already partitioned correctly and may reuse compatible partitioning rather than adding another exchange; verify the actual plan.

Official reference

Spark SQL performance tuning — source checked October 16, 2026. The examples state their runtime assumptions.

AQE changes more than skew partitions

AQE uses runtime statistics after query stages complete. Its major benefits include coalescing small post-shuffle partitions, splitting eligible skewed join partitions, and converting a planned sort-merge join to a broadcast hash join when a supported build side proves small enough. This can avoid sorting and some downstream transfer, although it cannot undo shuffle work already completed. It can also choose a shuffled hash join when per-partition build sizes and settings permit.

Inspect both initial and final adaptive plans and the actual task distribution. AQE does not guarantee that every operator is adaptive, that an aggregate with one enormous key becomes balanced, or that a broadcast build side fits available memory. The static and adaptive broadcast thresholds are separate settings. Turning on AQE is a way to use runtime evidence, not a replacement for correct joins, sane keys, bounded collections and appropriate source parallelism.