Parallel Query: How PostgreSQL Splits Work Across Workers

The one thing to understand first

Historically each PostgreSQL query ran in a single backend process. Parallel query lets the planner split eligible work across multiple worker processes that execute parts of the plan concurrently, then combine results. The machinery lives in src/backend/executor/nodeGather.c, execParallel.c, and the parallel-aware scan nodes.

Parallelism is a cost-based decision, not a guarantee: the planner only goes parallel when the table is big enough to repay worker startup, and it can only launch as many workers as the shared pool has free. "Planned 4, launched 2" is the signature of a pool too small for your concurrency.

The Gather node

A parallel plan has a Gather (or Gather Merge) node. Below it is the parallel portion run by the leader plus its workers; above it is serial. At execution, the leader launches background workers, each runs its copy of the sub-plan, and produces tuples into a shared queue; Gather collects them. Gather Merge preserves sort order by merging each worker's ordered stream.

Gather  (workers planned: 4, launched: 4)
  ->  Parallel Seq Scan on big_table
        Filter: (status = 'active')

Parallel-aware scans divide the data

A Parallel Seq Scan is not four full scans — workers cooperatively claim blocks from a shared counter so each block is read once, in total. Similarly there are parallel index scans, parallel bitmap heap scans, parallel hash joins (workers build a shared hash table), and partial aggregates. The partial results are combined by a Finalize Aggregate above the Gather.

How the planner decides

Parallelism is costed like everything else. A table must be large enough to be worth the worker startup overhead, governed by:

• min_parallel_table_scan_size / min_parallel_index_scan_size — size thresholds below which parallelism is not considered.
• parallel_setup_cost and parallel_tuple_cost — model the overhead of launching workers and shipping tuples.
• The number of workers scales with table size, capped by max_parallel_workers_per_gather.

The worker pools

Workers come from a shared pool with three nested limits:

• max_worker_processes — total background workers for the whole cluster.
• max_parallel_workers — how many of those may be used for parallel query.
• max_parallel_workers_per_gather — cap per individual Gather node.

If the pool is exhausted, a plan plans for N workers but launches fewer — visible as workers planned: 4, launched: 2. Persistent under-launching means the pool is too small for the concurrency.

What blocks parallelism

• Parallel-unsafe functions — a function marked PARALLEL UNSAFE (the default for functions that write or use sequences) forces a serial plan.
• Writing queries — most data-modifying statements are not parallelised (with specific exceptions like parallel index build and some CTAS paths).
• Cursors and certain locks can disable it.
• max_parallel_workers_per_gather = 0 disables it entirely.

Layer 3 — Watch it happen on your own database

EXPLAIN (ANALYZE, VERBOSE)
SELECT status, count(*) FROM big_table GROUP BY status;

-- Finalize Aggregate
--   -> Gather  (workers planned: 4, launched: 4)
--        -> Partial Aggregate
--             -> Parallel Seq Scan on big_table

The shape to recognise: a Gather with Partial Aggregate below it and Finalize Aggregate above. Compare workers planned to workers launched — if launched is consistently lower, the cluster's worker pool is exhausted under your real concurrency, and you are leaving parallelism on the table. Force a serial baseline with SET max_parallel_workers_per_gather = 0 to measure the actual speedup parallelism is buying.

Layer 4 — The levers this hands you

-- Encourage parallelism for a reporting session
SET max_parallel_workers_per_gather = 8;
SET parallel_setup_cost  = 200;     -- lower if workers start cheaply
SET parallel_tuple_cost  = 0.01;

-- Mark a pure function as parallel-safe so it doesn't block plans
ALTER FUNCTION score(numeric) PARALLEL SAFE;

• The three nested pool limits — max_worker_processes (cluster total) ⊃ max_parallel_workers ⊃ max_parallel_workers_per_gather (per node). Size them to match real concurrency or you get under-launching.
• Cost thresholds — min_parallel_table_scan_size and parallel_setup_cost / parallel_tuple_cost decide when parallelism is worth it; lower them for analytics sessions.
• Function safety labels — a PARALLEL UNSAFE function (the default for anything writing or using sequences) forces a serial plan; mark pure functions PARALLEL SAFE.
• Writes are mostly serial — data-modifying statements (with narrow exceptions like parallel index build) won't parallelise.

Layer 5 — What an Oracle DBA should expect vs what they get

Oracle DBAs know parallel execution well, but the controls and defaults are different:

• Conservative by default, not automatic. Oracle can auto-DOP whole statements (PARALLEL_DEGREE_POLICY=AUTO); PostgreSQL is deliberately cautious and parallelises only large scans/joins/aggregates that clear explicit size and cost thresholds.
• Worker pool ≈ parallel_max_servers. The nested max_parallel_workers* limits play the role of Oracle's parallel server pool; "planned vs launched" is the Postgres equivalent of downgraded DOP.
• **No table-level PARALLEL degree attribute.** You don't set a degree on the table; you tune session GUCs and let cost decide.
• No hints. Where Oracle uses /*+ PARALLEL(t,8) */, Postgres uses SET max_parallel_workers_per_gather and the cost knobs — there is no per-query hint.

Key takeaway

A parallel plan puts a Gather/Gather Merge over parallel-aware scan and partial-aggregate nodes; workers cooperatively claim blocks so data is read once in total. The planner only goes parallel above size/cost thresholds, and can only launch workers the shared pool has free. Tune the three nested worker limits, the cost GUCs, and function safety labels — and watch workers launched to confirm you're actually getting the parallelism you planned.