Parallel Seq Scan Size Thresholds #

A 40 GB table gets three workers; a 320 GB table gets five. Doubling the data does not double the parallelism, and no setting in postgresql.conf obviously explains the numbers. The worker count comes from a size-based formula that grows logarithmically, and knowing it is the difference between tuning parallelism deliberately and changing settings hopefully.

Worker mechanics are covered in parallel query execution. This page is about how many workers a scan is granted in the first place.

The Cost-Model Condition #

For a sequential scan, the planner computes a worker count from the relation’s size:

So the default configuration gives at most two workers to any query, and the size formula only matters once max_parallel_workers_per_gather is raised. The planner also costs parallelism: parallel_setup_cost (1000) per plan and parallel_tuple_cost (0.1) per row transferred through the Gather discourage parallel plans for small or highly selective queries.

Workers by relation size A scale of relation sizes. At 8 megabytes one worker is considered. At 24 megabytes two. At 72 megabytes three. At 216 megabytes four. At 650 megabytes five. The count grows by one each time the size triples, and is capped by max_parallel_workers_per_gather. 8 MB 1 worker 24 MB 2 workers 72 MB 3 216 MB 4 650 MB 5 workers capped by max_parallel_workers_per_gather, default 2

Annotated EXPLAIN Evidence #

Default cap in force:

SHOW max_parallel_workers_per_gather;   -- 2

EXPLAIN (ANALYZE)
SELECT count(*) FROM events WHERE kind = 'purchase';
Finalize Aggregate  (actual time=12104.2..12104.2 rows=1 loops=1)
  ->  Gather  (actual rows=3 loops=1)
        Workers Planned: 2  Workers Launched: 2
        ->  Partial Aggregate  (actual rows=1 loops=3)
              ->  Parallel Seq Scan on events  (actual rows=133340703 loops=3)
                    Filter: (kind = 'purchase'::text)
Execution Time: 12106.1 ms
-- a 320 GB table, and only two workers: the cap, not the size formula

With the cap raised for the session:

SET max_parallel_workers_per_gather = 8;

  ->  Gather  (actual rows=7 loops=1)
        Workers Planned: 6  Workers Launched: 6
        ->  Partial Aggregate  (actual rows=1 loops=7)
              ->  Parallel Seq Scan on events  (actual rows=57146015 loops=7)
Execution Time: 4210.6 ms
-- the size formula granted six; seven participants including the leader

Per-table override:

ALTER TABLE events SET (parallel_workers = 12);
  ->  Gather  (actual rows=9 loops=1)
        Workers Planned: 12  Workers Launched: 8
-- planned 12, launched 8: the global pool (max_parallel_workers) ran out
Which limit bound this plan? Three items are annotated. Workers planned equal to max_parallel_workers_per_gather means the per-gather cap decided. Workers planned matching the size formula means relation size decided. Workers launched below workers planned means the global pool was exhausted at execution time. Workers Planned: 2 (cap = 2) the per-gather cap decided Workers Planned: 6 on 320 GB the size formula decided Planned 12, Launched 8 global pool exhausted three different limits, three different fixes

Step-by-Step Resolution #

  1. Read the three numbers: Workers Planned, Workers Launched, and max_parallel_workers_per_gather. They identify which limit bound the plan.

  2. Raise the per-gather cap for analytical roles, not globally for the whole server:

    ALTER ROLE reporting SET max_parallel_workers_per_gather = 8;

  3. Size the global pool against CPU count and concurrency. max_parallel_workers is shared by every session, so a pool of 8 supports one eight-worker query at a time.

  4. Override per table where a specific relation deserves more or fewer workers than its size suggests:

    ALTER TABLE events SET (parallel_workers = 8);

  5. Lower the thresholds only deliberately. Reducing min_parallel_table_scan_size makes small scans parallel, which usually costs more in setup than it saves.

  6. Budget memory per participant. Each worker’s hash and sort nodes get their own allowances, as described in parallel hash join and shared hash tables.

  7. Verify under production concurrency, where launches fall short far more often than in a quiet test.

Diminishing returns from more workers With no workers the scan takes 34 seconds. With two workers it takes 12.1 seconds. With four it takes 6.4. With six it takes 4.2. With eight it takes 3.8, showing the curve flattening as coordination and I/O limits dominate. serial 34.0 s 2 workers 12.1 s 4 workers 6.4 s 6 workers 4.2 s 8 workers 3.8 s past the storage's throughput limit, extra workers add little

Before and After #

-- BEFORE: default max_parallel_workers_per_gather = 2 on a 320 GB table
Workers Planned: 2  Workers Launched: 2                         Execution Time: 12106.1 ms

-- AFTER: ALTER ROLE reporting SET max_parallel_workers_per_gather = 8
Workers Planned: 6  Workers Launched: 6                         Execution Time: 4210.6 ms

Why the formula is logarithmic #

Tripling the relation size for each additional worker looks conservative, and it is deliberately so. Parallel scans are usually limited by storage throughput rather than CPU: at some point every additional worker is waiting for the same devices, and the coordination overhead — starting workers, transferring tuples through the Gather, and combining partial results — grows with their number. The logarithmic rule gives large tables meaningfully more parallelism without assuming that a table ten times larger can absorb ten times the workers.

Where the assumption does not fit — a table that is entirely in cache, or storage with very high parallel throughput — the per-table parallel_workers setting is the right override, because it applies to that relation’s real characteristics rather than to every table on the server. Measuring the curve, as in the chart above, shows where the returns flatten for your hardware; setting the count just past that point captures the benefit without consuming pool slots other queries need.

Setting the pool against the machine #

Three settings bound parallelism at the server level, and they need to be sized together. max_worker_processes is the absolute ceiling on background processes of every kind, including logical replication workers and extensions. max_parallel_workers carves the share available to queries out of that ceiling. max_parallel_workers_per_gather limits how much of the share one query may claim.

A workable arrangement on a machine with sixteen cores serving mixed traffic: max_worker_processes = 16, max_parallel_workers = 8, and max_parallel_workers_per_gather = 4 for general roles with a higher value for a dedicated reporting role. That leaves half the cores for ordinary backends, lets two large reports run at full width simultaneously, and keeps a single query from consuming the entire pool.

The numbers that matter in practice come from observation rather than theory: if Workers Launched regularly falls short of Workers Planned during business hours, the pool is too small for the concurrency; if the machine’s CPU sits idle while reports run serially, the per-gather cap is too low.

Common Pitfalls #

Leaving the per-gather cap at 2. The size formula never gets a chance. Diagnostic signal: Workers Planned: 2 on huge tables. Fix: raise it for analytical roles.

Raising the cap without the pool. Plans ask for workers that do not exist. Diagnostic signal: Workers Launched below planned. Fix: size max_parallel_workers too.

Parallelising small scans. Setup cost exceeds the benefit. Diagnostic signal: parallel plans on tables of a few megabytes. Fix: leave the minimum sizes alone.

Forgetting memory multiplies. Each participant has its own allowances. Diagnostic signal: memory spikes with parallel reports. Fix: budget per participant.

Frequently Asked Questions #

How does PostgreSQL decide the number of parallel workers? #

From the relation’s size: a scan needs at least min_parallel_table_scan_size to be considered, and the worker count increases by one each time the size triples. The result is capped by max_parallel_workers_per_gather and by the global worker pool.

Why does my large table only get two workers? #

Because max_parallel_workers_per_gather defaults to 2, which caps the size-based formula. Raising it — ideally for the roles that run analytical queries — lets larger tables get more.

Should I set parallel_workers on a table? #

It is the right tool when a specific relation’s characteristics differ from what the size formula assumes, for example a fully cached table that can support more workers than its size suggests.

Up: Parallel Query Execution