Answer to the exact questions
1. Will spark.sql.adaptive.advisoryPartitionSizeInBytes alone produce a stable partition count across identical inputs?
No. The flag is interpreted as a *size target* per partition. The runtime shuffle size, statistics gathered during the stage, and any data skew are used to decide how many partitions to emit. Even with identical input data, the exact shuffle size can vary slightly (e.g., due to nondeterministic hash functions or compression), so the resulting partition count can change from run to run.
2. Is there a configuration that caps or fixes the AQE coalesced partition count while keeping AQE join and pruning optimizations enabled?
Yes, but only as a *cap* (minimum and maximum bounds). Spark 3.x exposes two parameters:
spark.sql.adaptive.coalescePartitions.minPartitionNum – the smallest number of partitions that AQE is allowed to emit.spark.sql.adaptive.coalescePartitions.maxPartitionNum – the largest number of partitions AQE may emit.
These values are respected only when AQE is enabled for shuffles. Within the min/max window, the actual count is still determined by the size target and runtime statistics.
If you need an *exact* count, the only deterministic way is to turn off AQE for that shuffle stage (or for the whole job). That forces Spark to use spark.sql.shuffle.partitions and skips the coalescing logic.
Why the count is non‑deterministic
During a shuffle stage, Spark first materializes a set of intermediate partitions defined by spark.sql.shuffle.partitions. After the shuffle, AQE inspects the actual data size of each partition and compares it to the target size derived from spark.sql.adaptive.advisoryPartitionSizeInBytes. It then merges (coalesces) adjacent partitions until the size target is met or until it reaches the configured min/max bounds. Because the actual data size can vary between runs (compression, hash collisions, etc.), the merge decisions can differ, leading to a different final partition count.
Steps to enforce a bounded partition count in your job
Enable AQE for the job (or leave it enabled if you want other AQE optimizations):
spark.conf.set("spark.sql.adaptive.enabled", true)
Set a realistic target size (e.g., 128 MiB):
spark.conf.set("spark.sql.adaptive.advisoryPartitionSizeInBytes", 134217728)
Define the bounds you want:
spark.conf.set("spark.sql.adaptive.coalescePartitions.minPartitionNum", 10)
spark.conf.set("spark.sql.adaptive.coalescePartitions.maxPartitionNum", 50)
Run the job and verify the final partition count:
- Open the Spark UI → SQL tab → click the stage that performed the shuffle.
- Look for the “Coalesced shuffle partitions” metric; it shows the number of partitions after AQE coalescing.
If the count is still outside your desired range, adjust the min/max values or the target size until the desired balance between file size and count is achieved.
When to disable AQE for deterministic file counts
If downstream systems (e.g., Hive, Presto, or a file‑based pipeline) require an exact number of output files, you must disable AQE for that stage:
// Disable AQE globally (or use a session‑level config if you only need it off for a single job)
spark.conf.set("spark.sql.adaptive.enabled", false)
Consequences:
- Small‑file problem: If the dataset is smaller than
spark.sql.adaptive.advisoryPartitionSizeInBytes, Spark will still emit many tiny files because it cannot coalesce.
- Skew handling: AQE’s ability to detect and mitigate skewed partitions is lost, which can hurt performance on highly uneven data.
In many production scenarios, the trade‑off of a bounded but not fixed count is acceptable, and the min/max bounds give you a predictable envelope for downstream processing.
What to verify after configuration changes
- Check the
Coalesced shuffle partitions metric in the Spark UI for each shuffle stage.
- Run the job twice with the same input data and confirm that the partition count stays within the min/max bounds.
- If you set
minPartitionNum to a value higher than the number of partitions that would naturally be produced, Spark will still emit at least that many partitions, potentially creating empty or very small ones.
Missing diagnostic detail
To tailor the recommendation precisely, could you tell whether you need a *fixed* number of partitions or just a *maximum* bound? The approach differs: a fixed count requires disabling AQE, while a bound can be achieved with minPartitionNum and maxPartitionNum and leaving AQE enabled.
apachespark