PostgreSQL: max_parallel_maintenance_workers sets a ceiling, not real parallelism

Few PostgreSQL parameters generate as much silent confusion as max_parallel_maintenance_workers. It looks simple: it sets how many parallel processes a maintenance operation can use. In practice, it's a ceiling that's rarely reached, because three or four other rules get in front of it. That's the central argument of the post "All Your GUCs in a Row: max_parallel_maintenance_workers", by Christophe Pettus, one more chapter in a series that has already covered maintenance_work_mem, max_logical_replication_workers, and max_files_per_process.

The parameter has user context (it can be set per session), accepts values from 0 to 1024, with 0 disabling maintenance parallelism, and the default is 2. It arrived in PostgreSQL 11 together with parallel B-tree index builds. PostgreSQL 13 brought parallel index vacuuming, 17 extended it to BRIN, and 18 to GIN. Hash, GiST, and SP-GiST still build serially through 18 and through beta 3 of 19 (which Pettus had access to for the post's tests).

A ceiling per command, not a pool

The most common mistake, which Pettus himself admits having made in a 2023 post, is treating this GUC as a global limit on simultaneous maintenance workers across the whole cluster. It isn't. It's a per-command limit. Pettus ran three index builds at the same time with the parameter set to 2 and found six live workers, two under each leader process. The real ceiling for the total number of active workers comes from two other parameters: max_parallel_workers and max_worker_processes.

This changes the math for anyone administering a large database with multiple concurrent maintenance workloads. Running REINDEX in parallel across several tables at once, or firing off pg_restore with -j, can easily blow past the available worker pool, even when each individual command is "within its limit".

How Postgres decides how many workers to request

The densest part of the post explains the three-step calculation an index build performs even before looking at this parameter:

1. The table-size ladder, the same one the planner uses to decide on parallel scans: one worker starting at 8MB of heap, and one more each time the table size triples. It's a slow ladder. A 782MB table requested five workers with the parameter set to 8, and kept requesting five with the parameter set to 1024. A 17GB table reaches eight workers, and a terabyte reaches eleven. In practice, until the table passes 4TB, setting this GUC to 64 makes no difference at all compared to setting it to 12.
2. The ceiling of max_parallel_maintenance_workers itself, which trims what the ladder requested.
3. The memory floor: each participant, including the leader, needs to be left with at least 32MB of maintenance_work_mem. At the default of 64MB, an index build is entitled to exactly one worker, no matter how high the parameter in question is set. In other words: on a default install, without touching maintenance_work_mem, the index never even reaches this GUC's own default of 2 workers. The formula is N workers require 32MB × (N + 1): 160MB for four workers, 224MB for six, 288MB for eight.

There's also a shortcut: the storage parameter parallel_workers, set per table, skips both the ladder and the memory floor (but not this GUC's ceiling). In Pettus's test, with parallel_workers set to 6, the global parameter at 8, and 64MB of memory, the build requested all six workers, leaving each one about 9MB to sort data with, which is little. Since this same storage parameter also affects parallel query plans against the table, the advice is to set it for the index build and reset it right after.

The result of all this is just a request. Whether the workers actually materialize depends on the pool available at that moment, and any shortfall in delivery is silent: the command runs with whatever it got, with no error or warning.

What never parallelizes

Some builds don't even get to request workers. Temporary tables always build serially. The same goes for any index whose expression or predicate calls a function that isn't marked PARALLEL SAFE. Since CREATE FUNCTION defaults to PARALLEL UNSAFE, this covers most homegrown IMMUTABLE functions that teams create without reviewing this detail. A simple SQL function that ends up being inlined by the planner escapes this check, which makes the behavior look random until you know the rule.

This decision path applies to more commands than just CREATE INDEX: REINDEX (in both CONCURRENTLY forms, where only the table's first scan is parallel), ALTER TABLE ... ADD PRIMARY KEY, ADD UNIQUE, and the index rebuilds at the end of CLUSTER and VACUUM FULL also requested workers normally in the 18.6 version Pettus tested.

VACUUM follows different logic

VACUUM uses the parameter differently. Only the index phases run in parallel, the unit of work is an entire index (not a fraction of one), and the leader process already claims one index for itself. The number of workers is the count of indexes with at least min_parallel_index_scan_size (512kB), minus one, capped by the PARALLEL option if it was passed, and capped again by this GUC. On a table with four indexes, VACUUM (PARALLEL 8) at the default launches two workers.

There's no 32MB rule here, so a plain manual VACUUM on that same table already launches two workers on a default install, without anyone explicitly asking for it. A table with a single, enormous index gets no workers at all, no matter the configuration, and the heap scan is never parallel.

One point that changes maintenance planning: autovacuum completely ignores this parameter through PostgreSQL 18. It never uses parallel workers, so max_parallel_maintenance_workers only affects VACUUM typed manually (or triggered by vacuumdb). PostgreSQL 19 introduces a separate parameter, autovacuum_max_parallel_workers (sighup context, default 0), which doesn't interact with the old one. In Pettus's test on 19 beta 3, with the classic parameter at 0 and the new one at 2, autovacuum planned and launched two workers while a manual VACUUM (PARALLEL 4) running at the same time was fully serial.

Tuning in practice and checking whether it worked

The numbers Pettus already recommended in 2023 still hold: 2 for small machines, 4 above eight cores, 6 for hardware substantially larger than that. They only make sense if there's memory available to actually reach them, and a global range of 256MB to 1GB of maintenance_work_mem covers the floor for any of these values. Since the parameter's context is user, the recommendation for a large, one-off build is to set both parameters within the session itself, without touching the cluster's globals.

The point where this usually goes wrong is pg_restore -j. Each job is its own session running its own command, so four jobs with the parameter set to 4 can request sixteen workers from a pool that, in a default configuration, has seven to offer. In the post's test, four jobs requested three workers each, and the maximum alive at the same time was seven, split three, three, and one. The practical solution is something like:

PGOPTIONS='-c max_parallel_maintenance_workers=2 -c maintenance_work_mem=1GB' pg_restore ...

This adjusts both parameters only for the restore's sessions. The rule of thumb is to keep jobs × (workers + 1) close to the number of available cores, and to make sure max_parallel_workers covers jobs × workers. If that also requires raising max_worker_processes, that change requires a server restart.

After adjusting, it's worth checking whether the number actually changed. VACUUM (VERBOSE) reports it directly: "launched 2 parallel vacuum workers for index vacuuming (planned: 2)". Index builds are more discreet: SET client_min_messages = debug1 shows a line saying the index is being built "with request for N parallel workers", or "serially" (this is the request, not the delivery). To know what was actually launched, you need to count rows with backend_type = 'parallel worker' in pg_stat_activity, grouped by leader_pid, while the build is running.

As Pettus himself sums it up, "raising it is easy. Getting a command to use what you raised it to is the harder problem": raising the value is easy, the hard part is getting the command to actually use what was made available. For anyone administering large databases, the practical lesson is not to trust the GUC's value as an indicator of real parallelism: always measure what actually happened, whether via VACUUM (VERBOSE) or by querying pg_stat_activity during the maintenance window.