Parallel Query, Parallel Workers, Gather/Gather Merge, and Parallel Safety

Learning outcomes

ServiceHub's monthly report takes 12 seconds. A developer sees 16 CPU cores and sets max_parallel_workers_per_gather=16, expecting an eightfold speedup. Instead latency barely changes and concurrent API traffic slows. Parallel query is coordinated multi-process execution with startup, tuple-transfer, memory, CPU, and I/O costs—not a core-count multiplier.

1. Inspect the worker budget before reading a plan

SELECT name, setting, unit, context, sourceFROM pg_settingsWHERE name IN (  'max_worker_processes',  'max_parallel_workers',  'max_parallel_workers_per_gather',  'max_parallel_maintenance_workers',  'parallel_leader_participation',  'min_parallel_table_scan_size',  'min_parallel_index_scan_size',  'parallel_setup_cost',  'parallel_tuple_cost')ORDER BY name;

A query may request workers under max_parallel_workers_per_gather, but workers come from the cluster-wide max_parallel_workers pool, which itself cannot exceed available max_worker_processes. At execution time the requested workers may simply be unavailable.

2. Encourage one parallel analytical plan in a reversible session

BEGIN;SET LOCAL max_parallel_workers_per_gather = 4;SET LOCAL min_parallel_table_scan_size = 0;SET LOCAL parallel_setup_cost = 0;SET LOCAL parallel_tuple_cost = 0;EXPLAIN (ANALYZE, BUFFERS, SETTINGS, TIMING OFF, SUMMARY ON)SELECT tenant_id,       category,       count(*) AS events,       sum(amount) AS total_amountFROM app.ch22_perfGROUP BY tenant_id, categoryORDER BY tenant_id, category;ROLLBACK;

Look for a Gather or Gather Merge and, when aggregation is parallelized, Partial Aggregate below it plus a finalization step above it. The planner is free to choose a different legal plan; the local cost/size settings here are an educational nudge, not production recommendations.

3. Gather and Gather Merge solve different ordering problems

Gather reads tuples from workers without preserving a global sorted order. Gather Merge receives sorted streams from workers and merges them while preserving the required ordering. The leader must also consume tuples from workers, which can become meaningful work by itself.

BEGIN;SET LOCAL max_parallel_workers_per_gather = 4;SET LOCAL min_parallel_table_scan_size = 0;SET LOCAL parallel_setup_cost = 0;SET LOCAL parallel_tuple_cost = 0;EXPLAIN (ANALYZE, BUFFERS, SETTINGS, TIMING OFF)SELECT customer_id, occurred_at, amountFROM app.ch22_perfWHERE category IN ('repair','inspection')ORDER BY occurred_at;ROLLBACK;

Do not memorize that this query “must” produce Gather Merge. Whether ordering is produced below or above Gather depends on path costs, indexes, row estimates, worker availability and the exact server build/settings.

4. Workers Planned is not Workers Launched

EXPLAIN (ANALYZE, BUFFERS, SETTINGS, VERBOSE, TIMING OFF)SELECT sum(amount), avg(amount), count(*)FROM app.ch22_perfWHERE sort_key < 950000;

In an actual parallel plan, EXPLAIN ANALYZE reports the planned and launched worker counts. If fewer workers launch, the leader and remaining workers execute more of a plan that was costed expecting more help. A busy server can therefore make a parallel plan perform differently from an isolated benchmark.

5. Leader participation adds another process to the resource budget

With parallel_leader_participation=on, the leader can execute the parallel plan under Gather/Gather Merge while also reading worker tuples. A plan with four launched workers can therefore have five processes doing query work. Chapter 22 Lesson 1's work_mem multiplication must count this.

BEGIN;SET LOCAL max_parallel_workers_per_gather = 4;SET LOCAL parallel_leader_participation = on;EXPLAIN (ANALYZE, SETTINGS, TIMING OFF)SELECT tenant_id, sum(amount)FROM app.ch22_perfGROUP BY tenant_id;SET LOCAL parallel_leader_participation = off;EXPLAIN (ANALYZE, SETTINGS, TIMING OFF)SELECT tenant_id, sum(amount)FROM app.ch22_perfGROUP BY tenant_id;ROLLBACK;

Neither setting is universally faster. The leader can help scan/aggregate, or it can be more valuable draining tuples quickly. Compare under the actual plan and concurrency.

6. Parallel safety is a correctness contract

PostgreSQL classifies operations as parallel safe, parallel restricted, or parallel unsafe. User-defined functions default to unsafe because PostgreSQL cannot infer arbitrary side effects. Unsafe operations disable parallel query for the containing query.

SET ROLE servicehub_owner;CREATE OR REPLACE FUNCTION app.ch22_bucket(integer)RETURNS integerLANGUAGE sqlIMMUTABLESTRICTRETURN $1 % 16;RESET ROLE;SELECT p.oid::regprocedure, p.proparallelFROM pg_proc AS pWHERE p.oid = 'app.ch22_bucket(integer)'::regprocedure;

BEGIN;SET LOCAL max_parallel_workers_per_gather = 4;SET LOCAL min_parallel_table_scan_size = 0;SET LOCAL parallel_setup_cost = 0;SET LOCAL parallel_tuple_cost = 0;EXPLAIN (SETTINGS)SELECT app.ch22_bucket(tenant_id), sum(amount)FROM app.ch22_perfGROUP BY app.ch22_bucket(tenant_id);ALTER FUNCTION app.ch22_bucket(integer) PARALLEL SAFE;EXPLAIN (SETTINGS)SELECT app.ch22_bucket(tenant_id), sum(amount)FROM app.ch22_perfGROUP BY app.ch22_bucket(tenant_id);ROLLBACK;

The label is justified because this specific SQL function only computes deterministic integer arithmetic and has no database/sequence/temp-table/session/external side effect. Marking an unsafe function SAFE merely to obtain a plan is a correctness bug.

7. Controlled worker-count comparison

Run the same aggregate several times at each setting, alternate test order, and record both plan shape and execution time. The example table is deliberately modest so worker startup/coordination can be a visible fraction of total work.

BEGIN;SET LOCAL min_parallel_table_scan_size = 0;SET LOCAL parallel_setup_cost = 0;SET LOCAL parallel_tuple_cost = 0;SET LOCAL max_parallel_workers_per_gather = 0;EXPLAIN (ANALYZE, SETTINGS, TIMING OFF, SUMMARY ON)SELECT sum(amount * amount), count(*)FROM app.ch22_perfWHERE event_id <= 30000;SET LOCAL max_parallel_workers_per_gather = 2;EXPLAIN (ANALYZE, SETTINGS, TIMING OFF, SUMMARY ON)SELECT sum(amount * amount), count(*)FROM app.ch22_perfWHERE event_id <= 30000;SET LOCAL max_parallel_workers_per_gather = 4;EXPLAIN (ANALYZE, SETTINGS, TIMING OFF, SUMMARY ON)SELECT sum(amount * amount), count(*)FROM app.ch22_perfWHERE event_id <= 30000;ROLLBACK;

For a short/selective workload, the forced-cheap parallel startup model can produce parallel plans whose coordination cost makes them no faster—or slower—than serial execution. Exact results are machine-dependent. The lesson is to record the counterexample rather than assuming more workers monotonically reduce latency.

8. Observe parallel processes while a longer query runs

SELECT pid, leader_pid, backend_type,       application_name, state,       wait_event_type, wait_eventFROM pg_stat_activityWHERE backend_type IN ('client backend','parallel worker')  AND (application_name LIKE 'ch22_%' OR leader_pid IS NOT NULL)ORDER BY COALESCE(leader_pid,pid), backend_type;

Parallel workers are real server processes and appear as such. They compete with unrelated queries for the worker pool, CPU scheduler, memory bandwidth and storage throughput.

9. Parallel query is not parallel maintenance

max_parallel_maintenance_workers governs supported utility work such as some CREATE INDEX/VACUUM operations. It is distinct from a query's Gather workers, and the memory rule for parallel maintenance differs: maintenance_work_mem is treated as a command-wide limit rather than repeated once per utility worker.

Authoritative references

Performance settings are hardware-, concurrency-, plan-, operating-system-, and version-sensitive. These primary sources define the mechanisms used in this lesson.

• PostgreSQL 18 — Parallel Query
• PostgreSQL 18 — When Parallel Query Can Be Used
• PostgreSQL 18 — Parallel Plans
• PostgreSQL 18 — Parallel Safety
• PostgreSQL 18 — Resource Consumption / Worker Processes