repartition versus coalesce for performance?

Summary

• repartition() redistributes data to hit the partition count you ask for. The Spark RDD guide says it "always shuffles all data over the network".
• DataFrame coalesce() only lowers the partition count. It is a narrow dependency, so there is no shuffle: each new partition claims several current partitions.
• A drastic coalesce, such as down to one partition, can leave your computation running on fewer nodes. repartition() adds a shuffle step but keeps the upstream work parallel.
• In Spark SQL, Adaptive Query Execution (on by default) already coalesces small shuffle partitions, and the REBALANCE hint targets output files that are too small or too big.
• On Databricks with optimized writes, don't run coalesce(n) or repartition(n) just before a write to control file counts.

repartition versus coalesce for performance?

Both methods change the number of partitions, and the main difference is the shuffle. repartition() redistributes the data. coalesce() merges existing partitions as a narrow dependency. Spark runs one task for each partition. The RDD guide says "Typically you want 2-4 partitions for each CPU in your cluster", and that Spark normally tries to set the number of partitions automatically based on your cluster.

What is the difference between repartition and coalesce?

	repartition(numPartitions, *cols)	coalesce(numPartitions)
Shuffle	Yes. The RDD guide says it "always shuffles all data over the network", and the DataFrame docs call it "a shuffle step"	No. Each of the new partitions claims several of the current partitions
Raise the partition count	Yes, "either more or fewer partitions" (RDD guide)	No. A larger number "will stay at the current number of partitions"
Partitioning columns	Optional. The DataFrame result is "hash partitioned"	Not accepted
Main cost	Shuffle: "disk I/O, data serialization, and network I/O"	Fewer partitions means fewer tasks, and possibly fewer nodes doing the work

The table describes the DataFrame methods. The RDD version of coalesce has a shuffle=False parameter, described as "whether to add a shuffle step". Both DataFrame methods support Spark Connect since Spark 3.4.0.

When does coalesce help, and when does it hurt?

The RDD guide describes coalesce as "Useful for running operations more efficiently after filtering down a large dataset". The PySpark docs also warn about a drastic coalesce: for example, numPartitions = 1 "may result in your computation taking place on fewer nodes than you like". Their fix is to call repartition(), which "will add a shuffle step, but means the current upstream partitions will be executed in parallel".

from pyspark.sql import functions as sf

df = spark.range(0, 64, 1, 9)  # 9 partitions

# Narrow dependency: no shuffle
df.coalesce(3).select(sf.spark_partition_id().alias("partition")).distinct().sort("partition").show()

# Adds a shuffle step, upstream partitions still run in parallel
df.repartition(3).select(sf.spark_partition_id().alias("partition")).distinct().sort("partition").show()

spark_partition_id() is non-deterministic, because it depends on data partitioning and task scheduling.

How many partitions does Spark SQL use for joins and aggregations?

spark.sql.shuffle.partitions sets the number of partitions for shuffles in joins and aggregations, and its default is 200. Adaptive Query Execution (AQE) has been on by default since Apache Spark 3.2.0. With spark.sql.adaptive.coalescePartitions.enabled (default true), Spark coalesces contiguous shuffle partitions "to avoid too many small tasks". The tuning guide says you "do not need to set a proper shuffle partition number to fit your dataset" once you set a large enough initial number with spark.sql.adaptive.coalescePartitions.initialPartitionNum.
Skew is a separate problem. The guide says data skew "can severely downgrade the performance of join queries", and spark.sql.adaptive.skewJoin.enabled (default true) splits skewed sort-merge join tasks into roughly evenly sized ones.

Which SQL hint matches each method?

Hint	What the docs say
COALESCE	Equivalent to coalesce. Reduces the number of partitions
REPARTITION	Equivalent to repartition. Takes a partition number, column names, or both
REPARTITION_BY_RANGE	Equivalent to repartitionByRange. Takes column names and an optional partition number
REBALANCE	Hint only. Best effort: splits skewed partitions to avoid "too small/big files" when writing a table. Ignored if AQE is not enabled

SELECT /*+ COALESCE(3) */ * FROM t;
SELECT /*+ REPARTITION(3, c) */ * FROM t;
SELECT /*+ REBALANCE */ * FROM t;

How does Databricks change the advice?

• Optimized writes. The Databricks docs say: "If you use optimized writes, Databricks recommends that you don't run coalesce(n) or repartition(n) just before a write to control the number of files written." Optimized writes are enabled by default for MERGE, UPDATE with subqueries and DELETE with subqueries, and for CTAS and INSERT on SQL warehouses. For Delta tables, spark.databricks.delta.optimizeWrite.enabled controls them. Because data is shuffled before it is written, you might still see higher write latency.
• Unity Catalog managed tables. The Databricks file-size docs say Databricks automatically tunes file sizes for them, and that manual tuning recommendations "don't apply" to them.
• Auto-optimized shuffle. Setting spark.sql.shuffle.partitions to auto makes Databricks determine the number from the query plan and the input data size. The default stays 200.
• No RDD APIs on standard or serverless compute. Use the DataFrame methods above instead of RDD calls.

FAQs

Can coalesce increase the number of partitions?

No. For a DataFrame, requesting a larger number leaves the count where it is. Use repartition() to increase it.

Is coalesce(1) a safe way to write one file?

Not always. It may leave the computation on a single node. The docs suggest repartition() when you want the upstream work to stay parallel.

How do I handle skewed data?

The Spark docs describe two AQE-based answers: skew-join handling for sort-merge joins, and the REBALANCE hint for output partitions. Both depend on AQE being enabled.