max_parallel_maintenance_workers — PostgreSQL configuration parameter

Sets the maximum number of parallel workers that can be started by a single utility command.

At a glance

What it does

Sets the maximum number of parallel workers that can be started by a single utility command. Currently, the parallel utility commands that support the use of parallel workers are CREATE INDEX when building a B-tree, GIN, or BRIN index, and VACUUM without FULL option. Parallel workers are taken from the pool of processes established by max_worker_processes, limited by max_parallel_workers. Note that the requested number of workers may not actually be available at run time. If this occurs, the utility operation will run with fewer workers than expected. The default value is 2. Setting this value to 0 disables the use of parallel workers by utility commands.

Note that parallel utility commands should not consume substantially more memory than equivalent non-parallel operations. This strategy differs from that of parallel query, where resource limits generally apply per worker process. Parallel utility commands treat the resource limit maintenance_work_mem as a limit to be applied to the entire utility command, regardless of the number of parallel worker processes. However, parallel utility commands may still consume substantially more CPU resources and I/O bandwidth.

(Description quoted from the official PostgreSQL documentation.)

How to apply a change

Can be set per session with SET, per role/database with ALTER ROLE/DATABASE ... SET, or globally in postgresql.conf.

Inspect the current value and source with SHOW max_parallel_maintenance_workers; or SELECT name, setting, unit, context, source FROM pg_settings WHERE name = 'max_parallel_maintenance_workers';.

Tuning guidance

Raise it to speed up CREATE INDEX and VACUUM on large tables when cores are free; it draws from the same pool as max_parallel_workers, so size the pool accordingly.

Reference

PostgreSQL documentation — max_parallel_maintenance_workers.