max_parallel_workers_per_gather
max_parallel_workers_per_gather is the per-query, per-node cap on how many parallel WORKER processes a single Gather (or Gather Merge) node may use to run a plan…
Tier 1 — Lab-Proven — Measured behavior and safety boundaries are available below.
Measured Effect
summary
MEASURED the speedup curve on a CPU-bound aggregate over a 20,000,000-row table (SELECT sum(sqrt(x)+ln(x+2)) FROM par). For mpwpg = 0, 1, 2, 4, 8: SET the GUC, then read 'Workers Planned' and 'Workers Launched' from EXPLAIN ANALYZE (deterministic) and the median-of-3 runtime (directional). The table carried parallel_workers=8 so this GUC was the binding limit until the cluster pool (max_parallel_workers=8) ran dry.
speedup curve
| mpwpg | planned | launched | node | median ms | speedup |
|---|---|---|---|---|---|
| 0 | 0 | 0 | serial | 3614.8 | 1.00x |
| 1 | 1 | 1 | parallel | 1552.5 | 2.33x (leader also works, so 1 worker ≈ 2 processes) |
| 2 | 2 | 2 | parallel | 1006.6 | 3.59x |
| 4 | 4 | 4 | parallel | 839.1 | 4.31x (SWEET SPOT on this 8-vCPU box) |
| 8 | 8 | 7 | parallel | 1196.0 | 3.02x (REGRESSED — Launched 7 < Planned 8: pool ran dry, and 8 processes oversubscribe 8 vCPUs) |
reading
Three deterministic facts plus a clear runtime curve. (1) Workers Launched equals Planned up to 4 (1→1, 2→2, 4→4) but at mpwpg=8 only 7 of 8 planned workers actually started — the shared pool (max_parallel_workers=8) could not supply all 8 because slots were taken elsewhere. (2) The leader process participates, so 1 worker already gives ~2.33× (it behaves like 2 parallel processes), not 2×. (3) Speedup rises near-linearly to the SWEET SPOT at 4 workers (4.31×, the fastest run at 839 ms) then REGRESSES at 8 workers (back to 1196 ms, slower than 2 workers) — once you ask for more processes than the 8-vCPU box can truly run in parallel, coordination + CPU oversubscription cost more than they save. The optimum was roughly core-count, not 'as many as possible'.
honesty note
Workers Planned/Launched are exact, reproduced counts. Wall times are median-of-3 on a noisy 8-logical-CPU box (background loadavg ~5), so absolute milliseconds are directional — but the SHAPE (monotonic improvement to ~core count, then regression; leader participation making 1 worker ≈ 2×; Launched < Planned at the pool limit) reproduced. Absolute speedups are specific to this CPU-bound aggregate on 20M rows; an I/O-bound or selective query would parallelize less.
Reproduced Danger
Sizing / Safe Range
Workload Guidance
Unlock Incident Pro
The free sections tell you what to do. Pro is the measured proof — the exact failure signature, safe-ceiling formula, and per-workload defaults, each captured on real PG 14–18 runs.
- Reproduced Danger
- Sizing / Safe Range
- Workload Guidance
max_parallel_workers_per_gather is the per-query, per-node cap on how many parallel WORKER processes a single Gather (or Gather Merge) node may use to run a plan in parallel. When the planner builds a parallel plan, the part below the Gather (a parallel sequential scan, parallel hash join, partial aggregate, etc.) is run by N background workers PLUS the leader process — the leader also does work, so the effective parallelism is roughly N+1. This GUC sets the ceiling on N for each Gather node. Default is 2 (so up to 2 workers + the leader). It is only a CEILING: the actual worker count is also limited by the cluster-wide pool (max_parallel_workers, itself ≤ max_worker_processes), by the table's parallel_workers reloption, and by the planner's own cost estimate of whether more workers help. Set it to 0 to disable intra-query parallelism entirely for a session. It is the main per-session lever for turning a long analytic scan/join/aggregate from single-threaded into multi-core.
- name
max_parallel_workers_per_gather
- setting default
2
- unit
(none — a count of parallel worker processes per Gather node)
- vartype
integer
- category
Resource Usage / Worker Processes
- context
user
- min val
0
- max val
1024
- enumvals
(n/a — integer)
- boot val
2
- reset val
2
- pending restart
false
- short desc
Sets the maximum number of parallel processes per executor node.
- ground truth note
Captured literally from pg_settings on PG 14-18 (-18) on 2026-06-19. setting=2, boot_val=2, reset_val=2 on EVERY version (default_changed=false, boot_fallback_changed=false). context=user (any session/role/database can set it, no restart, no superuser). vartype=integer, min_val=0, max_val=1024, unit empty. category='Resource Usage / Worker Processes', short_desc='Sets the maximum number of parallel processes per executor node.' Default and bounds identical across 14, 15, 16, 17, 18.
Rock-stable across versions: default=2, min=0, max=1024, context=user, vartype=integer on PG 14, 15, 16, 17 and 18 — no version cliff (default_changed=false, boot_fallback_changed=false). The MECHANISM (per-Gather worker cap, leader also participates, capped by the cluster pool) is the same on all five. Parallel query has gained new parallel-aware plan nodes over the years (e.g. parallel hash, parallel append, more parallel-safe paths) so a given query may parallelize on a newer version where it did not before, but this GUC's value, bounds and meaning are unchanged. Per-version detail in _guc_matrix_machine.
context=user, the most flexible scope: postgresql.conf + reload (cluster-wide), ALTER DATABASE x SET (per database), ALTER ROLE r SET (per role), or SET max_parallel_workers_per_gather = N (per session) — NO restart, NO superuser. Takes effect on the next plan, so the typical pattern is to SET it per session for a heavy analytic/reporting query and leave the cluster default at 2 for mixed OLTP. The lab set it per session with SET before each EXPLAIN ANALYZE. Crucial: it is only a ceiling — raising it does nothing unless the planner CHOOSES a parallel plan (table big enough, query parallel-safe) AND the cluster pool (max_parallel_workers) has free worker slots. Per-table you can also pin it with ALTER TABLE t SET (parallel_workers = N); the lab used that on par so the GUC, not the table heuristic, was the binding cap. BOUNDARY PROOF (captured literal): an out-of-range / invalid value is rejected at set time -- ALTER SYSTEM SET max_parallel_workers_per_gather = -1; -> ERROR: -1 is outside the valid range for parameter "max_parallel_workers_per_gather" (0 .. 1024). It is context=user, so a plain SET max_parallel_workers_per_gather = ... in a session succeeds (session-scoped, no restart); change it cluster-wide via postgresql.conf or ALTER SYSTEM + reload.
Managed providers (Azure Database for PostgreSQL Flexible Server, Amazon RDS / Aurora, Cloud SQL, Crunchy, Supabase, Neon, Heroku) all expose max_parallel_workers_per_gather because it is context=user — SET it per session/role/database with no superuser and no restart, and use the server-parameter/ALTER SYSTEM path for the global default. The default is 2 everywhere. The pool parameters that bound it (max_parallel_workers, and especially max_worker_processes which needs a restart) are also provider-exposed and often pre-sized to the instance's vCPU count — so on a small managed instance, raising the per-gather cap above the instance core count just causes the regression and pool-starvation seen in the lab. Same playbook as self-hosted: low global cap, raise per session for analytics, size the pool to vCPUs × concurrency, and mind the (workers+1) × work_mem memory multiplier on memory-limited tiers.
Common Pitfalls
- Assuming every parallel query always gets its workers — under concurrency the shared pool is rationed and a query can silently get ZERO workers and run serial with no error (Workers Launched: 0).
- Setting it higher than the core count expecting more speed — past ~core count it REGRESSES (8 workers slower than 4 on an 8-vCPU box).
- Raising it without raising max_parallel_workers / max_worker_processes — the per-gather cap can never exceed the pool, so the extra request is ignored (Planned capped at the pool size).
- Raising work_mem AND parallelism together without doing the memory math — peak ≈ (workers+1) × work_mem per node; this is a common cause of 'we turned on parallelism and started OOM-ing'.
- Setting a high GLOBAL default — one accidental big query then drains the pool and starves all other parallel queries; prefer a low global cap + per-session raises.
- Forgetting it is only a CEILING — raising it does nothing if the planner does not choose a parallel plan (table too small, query not parallel-safe, parallel costs too high).
Evidence and References
- PostgreSQL docs: Runtime Config — Resource Consumption → Asynchronous Behavior (max_parallel_workers_per_gather, max_parallel_workers, max_worker_processes).
- PostgreSQL docs: Parallel Query (how Gather/Gather Merge, the leader, and workers cooperate; parallel-safe labelling).
- PostgreSQL docs: Parallel Plans and Parallel Safety; min_parallel_table_scan_size, parallel_setup_cost, parallel_tuple_cost.
- PostgreSQL docs: CREATE TABLE / ALTER TABLE storage parameter parallel_workers (per-table worker count).
- PostgreSQL docs: work_mem (each worker is allocated work_mem independently — parallel memory multiplication).
- Lab capture 2026-06-19: , 20M-row table; concurrency pool-exhaustion + over-parallel regression + per-worker memory; pg_settings ground truth PG 14-18 (-18).
Technical Reference
- Category
- Resource Usage / Worker Processes
- Type
- integer ((none; a count — the maximum number of parallel worker processes a single Gather/Gather-Merge executor node may request, on top of the leader process which also participates))
- Blast radius
- query
- Severity
- Low
- Context
- user
- Versions
- 14, 15, 16, 17, 18
- Reviewed
- 2026-06-19
Severity scale: 1 Minimal · 2 Low · 3 Moderate · 4 High · 5 Critical.