In PostgreSQL 18, which index types can be built with parallel workers, and what decides how many workers a CREATE INDEX actually gets?
answer
- not every access method
- a per-command worker cap
- the table's size drives the request
- memory share per participant
- a table storage parameter override
basics
~20 sIn PostgreSQL 18, B-tree, BRIN and GIN builds can use parallel workers. A size-based cost model, or the table's parallel_workers setting, picks the count, capped by max_parallel_maintenance_workers (default 2), the free worker pool and 32MB of maintenance_work_mem per participant.
solid answer
~40 sPostgreSQL 18 builds B-tree, BRIN (parallel since 17) and GIN (new in 18) indexes in parallel; GiST, SP-GiST and hash builds stay serial. A cost model based on the table's size proposes a worker count, capped by `max_parallel_maintenance_workers` (default 2, settable per session). The count is then cut until each participant, the leader included, has at least 32MB of `maintenance_work_mem`, so at the 64MB default only one worker is launched. Workers come from the `max_worker_processes` pool (default 8, restart only) and are limited by `max_parallel_workers` (default 8), so a busy server may start fewer. `ALTER TABLE ... SET (parallel_workers = N)` bypasses the cost model and the memory check, still capped by the GUC. Index expressions and predicates must be parallel safe, and with `CONCURRENTLY` only the first table scan runs in parallel.
code
sql · 9 lines-- Session-level settings for this build only
SET max_parallel_maintenance_workers = 4;
SET maintenance_work_mem = '2GB';
CREATE INDEX CONCURRENTLY orders_seller_created_idx
ON marketplace.orders (seller_id, created_at);
RESET maintenance_work_mem;
RESET max_parallel_maintenance_workers;go deeper
Know that PostgreSQL can use several processes to build some index types faster, and that max_parallel_maintenance_workers is the setting that caps it.
Name the access methods that build in parallel in 18 (B-tree, BRIN, GIN), the default cap of 2, and why the 64MB memory default leaves only one worker.
Tune a big build: session-level SET of the worker cap and memory, the worker pool limits, why CONCURRENTLY parallelises only its first scan, and when extra workers just steal CPU and I/O from production.
Weigh faster builds against foreground load: when to spend cores and memory to shorten a risky build window, and when a slower, gentler build is the better trade.
## Which index builds run in parallel A **parallel index build** spreads the table scan of `CREATE INDEX` (and, for B-tree, the sort) across a **leader** backend and helper processes called **parallel workers**; the leader takes part in the work too. Only some access methods support it: | Access method | Parallel build in 18 | Notes | |---|---|---| | B-tree | yes | available in all supported releases | | BRIN | yes | added in 17 | | GIN | yes | new in 18 | | GiST, SP-GiST, hash | no | always built by one process | On a PostgreSQL 16 server only B-tree builds use workers, so a GIN index on a `jsonb` column of `orders` can only get workers once the server runs 18. ## What the workers actually do For a B-tree, each participant reads its share of the table through a parallel heap scan and sorts the index entries it collected into runs; the leader then merges those runs and writes the finished tree. BRIN and GIN follow the same shape with their own per-participant work and a merge by the leader. The parallel part is therefore the **scan and the sort**, which on a 400 GB table is most of the elapsed time; writing the final index is still done by the leader alone. ## How the worker count is chosen For each build, PostgreSQL works through these steps: 1. If `max_parallel_maintenance_workers` is 0, the build is serial. 2. If the table is temporary, or an index expression or `WHERE` predicate is not **parallel safe**, the build is serial. 3. If the table has the storage parameter `parallel_workers` set, that number is used, capped by `max_parallel_maintenance_workers`, and nothing else is considered. 4. Otherwise the size-based heuristic used for parallel table scans proposes a count (bigger tables get more), capped by `max_parallel_maintenance_workers`. 5. The count is reduced until `maintenance_work_mem` divided by the participants (workers plus the leader) is at least **32MB** each. 6. At run time the workers must actually be free in the `max_worker_processes` pool and under `max_parallel_workers`; if they are not, the build runs with fewer, or none. Step 5 surprises people: with the 64MB default, two participants get 32MB each, so the build launches **one** worker even though the cap says two. ## The settings involved | Setting | Default | Who can change it | Role in a build | |---|---|---|---| | `max_parallel_maintenance_workers` | 2 | any session (`SET`) | cap per utility command | | `max_parallel_workers` | 8 | any session | cluster-wide cap on active parallel workers | | `max_worker_processes` | 8 | server restart | the pool every background worker comes from | | `maintenance_work_mem` | 64MB | any session | memory for the whole build, shared by participants | | `parallel_workers` (table storage parameter) | unset | `ALTER TABLE` | overrides the cost model for builds and parallel scans | Setting `max_parallel_maintenance_workers` to 0 disables parallel workers for utility commands altogether; the same setting also caps parallel `VACUUM` of indexes. ## Parallelism with CONCURRENTLY and REINDEX - `CREATE INDEX CONCURRENTLY` can use workers without special restrictions, but **only its first table scan** runs in parallel; the validation pass is serial. - Plain `REINDEX` and `REINDEX CONCURRENTLY` build through the same code path and can use workers as well. - While a build runs, `pg_stat_activity` lists its helpers with `backend_type` `parallel worker` and `leader_pid` set to the building backend. ## Using it on the orders table - On a 400 GB table the cost model asks for more workers than the default cap allows, so the defaults (cap 2, 64MB) give one worker. - Raise `max_parallel_maintenance_workers` and `maintenance_work_mem` in the **session** that runs the build, then `RESET` them; how much memory a server can spare is a tuning question of its own. - Prefer the session settings over `ALTER TABLE orders SET (parallel_workers = ...)`: the storage parameter also changes how many workers parallel queries on `orders` plan for, until someone resets it. - More workers help only while the build is CPU-bound. Once the disk is the bottleneck they add nothing, and they compete with production queries for CPU and I/O throughout.
- How can you see how many workers a running build actually got?Look in `pg_stat_activity` while it runs: each helper appears with `backend_type` `parallel worker` and `leader_pid` equal to the building backend's `pid`. Counting those rows shows the workers really launched, which can be fewer than planned when the `max_worker_processes` pool or `max_parallel_workers` is exhausted.
- Why should a parallel_workers storage parameter set for a build be reset afterwards?Because `parallel_workers` is not specific to index builds: the planner also uses it for every parallel scan of the table. Left in place, it changes query plans on `orders` long after the build. The manual recommends resetting it once the build is tuned; session-level `SET` avoids the problem.
- Only B-tree indexes can be built in parallel.
- max_parallel_workers_per_gather controls how many workers an index build uses.
- Raising max_parallel_maintenance_workers alone guarantees more build workers.
- CREATE INDEX CONCURRENTLY can never use parallel workers.
- GiST indexes gained parallel builds in PostgreSQL 18.
Assumes PostgreSQL 18: GIN parallel builds are new in 18 and BRIN's arrived in 17, so on 16 only B-tree builds use workers.