Parallel and background workers

For DBAs and operators

The background worker framework

A background worker is registered either at start-up by a library in shared_preload_libraries (static, e.g. pg_cron's scheduler, TimescaleDB's launcher, pg_prewarm's autoprewarm leader) or dynamically by a running process (parallel query, logical replication workers, autoprewarm's per-database workers). The postmaster forks it into a free slot of max_worker_processes. When no slot is free, a dynamic registration fails; parallel query copes with that silently.

On a default PostgreSQL 18 server the logical replication launcher already occupies one slot, so 7 remain. Logical replication apply/tablesync/parallel apply workers, autoprewarm, and extension workers take more. Raising max_worker_processes needs a restart and must be at least as high on a standby as on its primary.

Parallel query workers

1. The planner chooses a Gather (or Gather Merge) node with N planned workers, based on table size (min_parallel_table_scan_size), costs (parallel_setup_cost, parallel_tuple_cost) and max_parallel_workers_per_gather.
2. At execution the leader creates a DSM segment (plan, parameters, snapshot, tuple queues, shared scan state) and registers N dynamic workers. Each gets a slot only if both max_parallel_workers and max_worker_processes allow.
3. Workers attach to the DSM, restore the leader's snapshot and transaction state, and run their part of the plan. The leader also executes the plan unless parallel_leader_participation = off.
4. At the end workers exit and their slots are free again. Parallel workers never outlive the query.

Finalize Aggregate (actual rows=1.00 loops=1)
  ->  Gather (actual rows=1.00 loops=1)
        Workers Planned: 2
        Workers Launched: 0
        ->  Partial Aggregate (actual rows=1.00 loops=1)
              ->  Parallel Seq Scan on t (actual rows=2000000.00 loops=1)

That plan (run with max_parallel_workers = 0) was still "parallel", but the leader did all the work: loops=1 instead of 3.

How to see them

SELECT pid, leader_pid, backend_type, query
  FROM pg_stat_activity
 WHERE backend_type IN ('parallel worker', 'background worker')
    OR backend_type LIKE 'logical replication%';

ps shows postgres: parallel worker for PID 1913; extension workers show their own backend_type (e.g. pg_cron launcher). A leader waiting for workers shows wait event ExecuteGather (IPC) or MessageQueueReceive.

Support notes

• Sporadic slow queries during busy hours with Workers Launched lower than planned: the pool is exhausted. Raise max_worker_processes and max_parallel_workers together (restart needed for the first), and keep CPU count in mind.
• Logical replication workers that do not start, with WARNING: out of background worker slots HINT: You might need to increase "max_worker_processes". (logged by the process that tried to start the worker: the launcher for apply workers, an apply worker for tablesync and parallel apply workers) mean the slots are used up; count them with the query above. Extensions report a failed dynamic start in their own way; static registrations beyond the pool are refused at start-up with LOG: too many background workers
• max_parallel_maintenance_workers (default 2) caps parallel CREATE INDEX and VACUUM index processing, again from the same pool.
• Each worker is a full process with its own work_mem per operation: a parallel hash join may use (workers + 1) × work_mem × hash_mem_multiplier.
• DSM lives in /dev/shm by default (dynamic_shared_memory_type = posix); a tiny /dev/shm in containers gives ERROR: could not resize shared memory segment "/PostgreSQL.1234" to 8388608 bytes: No space left on device
• If a parallel worker crashes (signal), the postmaster performs a crash restart as for any backend; an error in a worker is re-raised in the leader and the query fails.