Raising the parallel worker limit is a natural response to a slow index build. Yet a larger number in the configuration does not necessarily bring more workers into the operation.

Christophe Pettus discusses this gap in “All Your GUCs in a Row”: max_parallel_maintenance_workers is a ceiling. Tuning it requires understanding the conditions that let an operation use parallel workers.

For PostgreSQL 18, separating the configured cap, the requested worker count, and the workers actually running makes the next tuning decision much clearer.

Separate the per-command cap from shared worker capacity

max_parallel_maintenance_workers defaults to 2; setting it to 0 disables parallel workers for the relevant maintenance commands. A value of 2 applies to each command, rather than limiting all maintenance to two workers combined.

Workers also need capacity under max_parallel_workers and the supplying max_worker_processes pool. If capacity is unavailable, an operation proceeds with fewer workers than requested. The PostgreSQL 18 worker settings describe this relationship.

Failing to reach the cap in isolation and failing to obtain workers during concurrent work therefore call for different investigations.

Memory can reduce the worker request for an index build

PostgreSQL 18 supports parallel builds for B-tree, GIN, and BRIN indexes. Parallel GIN creation was added in the PostgreSQL 18 release notes, so older versions need different assumptions.

Automatic worker selection considers table size and maintenance_work_mem. It requires a 32MB share for each worker and another for the leader. The CREATE INDEX documentation gives this rule.

These are calculated thresholds, 32MB × (workers + 1), not recommended settings or launch guarantees. The default 64MB accommodates at most one worker under normal automatic selection.

The maintenance memory budget applies to the whole command. Adding workers does not add that budget again for each worker; CPU and I/O capacity still matter.

An explicit table parallel_workers setting bypasses the normal selection and its memory constraint. It also affects parallel scans, so a build-specific adjustment should include resetting the setting afterward.

VACUUM distributes index work, not the heap scan

The index-build memory calculation does not transfer directly to VACUUM. Ordinary VACUUM parallelizes index vacuum and cleanup phases, rather than splitting the heap scan across workers.

A single index cannot be divided among several workers. Eligible indexes must support parallel vacuum and satisfy min_parallel_index_scan_size. One enormous index alone cannot produce parallel vacuum workers. The VACUUM PARALLEL specification defines these work units.

Consequently, VACUUM (PARALLEL 4) does not guarantee four workers. Eligible indexes and configuration limits constrain it, and workers start and exit around the relevant phases. Check the phase before interpreting an absence of workers.

pg_restore combines job concurrency with workers inside each command

After checking one command, consider how many run together. pg_restore -j uses concurrent sessions for work including data loading and index creation. Each job uses a separate connection, as documented for the pg_restore jobs option.

If three jobs simultaneously build indexes and each requests two workers, the combined request is six workers. Including the leaders gives nine participating processes for those commands. This is a conditional calculation, not a measurement.

Concurrent commands also have separate memory budgets. Record both job count and worker settings, then compare restore duration and CPU/I/O load rather than maximizing either setting independently.

Use running workers and progress to choose the next adjustment

First record the effective settings in the session that will perform the operation. These statements only read configuration.

-- Record session maintenance settings and shared worker limits.
SHOW max_parallel_maintenance_workers;
SHOW maintenance_work_mem;
SHOW max_parallel_workers;
SHOW max_worker_processes;

During index creation, inspect pg_stat_activity from a separate monitoring session. Group rows whose backend_type is parallel worker by leader_pid, matching the leader to the intended command. The pg_stat_activity column definitions explain these fields. Viewing details across sessions also requires appropriate monitoring-role privileges.

Combine this with command and phase in the index creation progress view. A phase such as waiting for writers before build points toward investigating the blocking activity. One observation without workers cannot establish that the entire operation ran serially.

When utilization falls below the cap, distinguish conditions that reduce the request from shared capacity that prevents launches. When enough workers run but duration does not improve, investigate CPU and storage capacity. The practical benefit of understanding this setting is choosing an adjustment that addresses the reason work is slow.

Source

• Title: Christophe Pettus: All Your GUCs in a Row: max_parallel_maintenance_workers
• URL: https://postgr.es/p/9v7