Decoding PostgreSQL Parallelism: The Hidden Realities of max_parallel_workers and max_parallel_workers_per_gather

Database tuning is often an exercise in matching documentation to reality. For PostgreSQL administrators, two configuration parameters frequently cause confusion: max_parallel_workers and max_parallel_workers_per_gather. Despite their intuitive-sounding names, neither parameter operates quite the way its title suggests. Understanding the nuances of these settings is crucial for maintaining optimal performance, avoiding resource starvation, and ensuring that expensive queries utilize system hardware efficiently without destabilizing the broader database cluster.

To demystify these parameters, we must examine how PostgreSQL manages its background worker pools, how the query planner calculates parallelism, and why certain common configuration adjustments fail to yield the expected results.

Main Facts

At the core of PostgreSQL’s parallel execution model are several interrelated configuration settings, worker pools, and system limits. Misunderstanding even one of these parameters can lead to sub-optimal query plans, exhausted server resources, or silent performance degradation.

The True Meaning of the Parameters

• max_parallel_workers_per_gather: Despite implying a strict global or session-wide threshold for all parallel activity, this parameter defines the maximum number of workers the planner is allowed to request for a single plan node (such as a Gather or Gather Merge node). The planner will frequently request fewer workers based on the size of the table being scanned.
• max_parallel_workers: Documented as a cluster-wide limit for parallel operations, this parameter is enforced in a counter-intuitive way. The system compares a cluster-wide count of active parallel workers against a value that every individual session is allowed to configure for itself.

Worker Pool Hierarchies

PostgreSQL divides its background worker capacity across distinct pools, each serving different subsystems:

1. max_worker_processes: This represents the absolute pool of background workers available across the entire server. It is shared with logical replication (max_logical_replication_workers), custom extensions, and parallel maintenance operations. Crucially, this is the only parameter among the primary parallelism controls that requires a database restart to change.
2. max_parallel_workers: This defines how much of the total max_worker_processes pool parallel operations may consume at any given time. This includes parallel queries, parallel index builds, and parallel VACUUM processes (which draw on max_parallel_maintenance_workers, joined recently by autovacuum_max_parallel_workers).
3. max_parallel_workers_per_gather: This governs the allocation for a single Gather or Gather Merge node within an execution plan.

Chronology

The architecture of parallel query execution in PostgreSQL did not arrive all at once; it evolved incrementally across major releases, leaving behind legacy behaviors and default values that administrators must navigate today.

The Evolution of Parallel Processing

• PostgreSQL 9.6 (Parallel Query Introduced): PostgreSQL introduced parallel query capabilities, accompanied by the debut of max_parallel_workers_per_gather. At its inception, the default value for this parameter was set conservatively to 0, effectively keeping parallel execution disabled by default.
• PostgreSQL 10 (Mainstream Adoption): Recognizing the maturity of the feature, the PostgreSQL global development group turned parallel query on by default by raising max_parallel_workers_per_gather to 2. To prevent parallel queries from monopolizing every available background worker slot on a server, developers introduced the max_parallel_workers parameter in this same release.
• PostgreSQL 18 (Observability Improvements): Historically, when a parallel worker could not be launched due to resource exhaustion, the query would execute silently with fewer (or zero) workers, writing no errors to the server logs. PostgreSQL 18 addresses this diagnostic blind spot by introducing parallel_workers_to_launch and parallel_workers_launched metrics into system views like pg_stat_database and pg_stat_statements, making worker starvation immediately visible.

Supporting Data

The mechanics of how PostgreSQL decides to scale a query—and how those decisions interact with user-defined limits—rely on predictable mathematical formulas and specific execution behaviors.

How the Planner Requests Workers

Outside of the query optimizer, no component of PostgreSQL reads max_parallel_workers_per_gather. Instead, when evaluating a sequential table scan, the planner calculates a worker count based strictly on the expected data volume:

• Base Threshold: One worker is assigned at min_parallel_table_scan_size (which defaults to 8MB).
• Scaling Factor: An additional worker is added every time that data volume triples.
  • 24 MB: 2 workers
  • 72 MB: 3 workers
  • 216 MB: 4 workers
  • 648 MB: 5 workers
  • 1.9 GB: 6 workers
  • 5.7 GB: 7 workers
  • 17 GB: 8 workers
  • 154 GB: 10 workers
  • 32 TB (Maximum PostgreSQL table size): 14 workers

This internal formula has remained largely unchanged since version 9.6, with core developers acknowledging that the algorithm could benefit from a more sophisticated approach. Once the planner calculates this baseline figure, max_parallel_workers_per_gather acts strictly as an upper cap.

Session-Level Controls vs. Cluster-Wide Limits

A fascinating design choice in PostgreSQL is how session-level parameters interact with shared memory counters.

The max_parallel_workers setting is consulted in a single line of executable code within the PostgreSQL source tree: inside RegisterDynamicBackgroundWorker(), which rejects new parallel worker requests when active workers have hit the ceiling. However, the optimizer never looks at this parameter.

Consequently, setting max_parallel_workers = 0 does not disable parallel query. Instead, it generates parallel execution plans that no workers show up to help execute. The query leader is forced to run the entire operation alone, relying on a plan whose cost was modeled under the false assumption of assistance.

Furthermore, because max_parallel_workers is a user-context parameter, any low-privileged role can execute a simple SET max_parallel_workers = 1024; in their session. While a global shared memory check prevents actual system-wide over-allocation beyond hard limits like max_worker_processes, it creates significant administrative friction. If an unprivileged user or an unvetted reporting script inflates this parameter within a session, it can starve background processes—such as logical replication slots and autovacuum workers—of necessary execution slots.

Official Responses and Administrative Best Practices

Database architects and core contributors have long advised caution when tuning concurrency parameters. Because improper configuration can quietly degrade database performance or disrupt critical background maintenance tasks, standard operational guidelines have emerged within the PostgreSQL community.

Key Operational Recommendations

1. Turn Parallel Query Off Correctly: If an administrator wishes to disable parallel query entirely, altering max_parallel_workers is the wrong approach. The correct setting to modify is max_parallel_workers_per_gather = 0.
2. Tune in Reverse Alphabetical Order:
   • max_worker_processes must be configured first because it requires a server restart and represents the ultimate hard limit. Set this value to equal max_parallel_workers plus max_logical_replication_workers, plus any slots required by extensions, plus a healthy buffer of 4 to 8 spare processes.
   • max_parallel_workers should generally be set to 2 to 3 times the physical core count on smaller systems, scaling down to roughly 1.5 times the core count on high-core-density machines (32 cores or more).
   • max_parallel_workers_per_gather should remain at its conservative default of 2 globally for OLTP-heavy workloads, but can be adjusted upward using role-specific settings (e.g., ALTER ROLE reporting SET max_parallel_workers_per_gather = 6;) for analytical workloads.
3. Handle Massive Tables via Storage Parameters: Rather than inflating global or session-level concurrency caps to accommodate a single massive table, administrators should use table-level storage parameters:

   ALTER TABLE large_table SET (parallel_workers = 16);

   This ensures that parallelism is targeted precisely where it provides a benefit, without inviting resource contention across smaller tables.

Implications

The disconnect between how PostgreSQL parameters are documented and how they behave carries significant implications for database reliability and performance engineering.

Performance Degradation and Silent Failures

When administrators copy-paste high concurrency values from online blog posts into production environments or nightly reporting scripts, they frequently introduce hidden bottlenecks. On older versions of PostgreSQL (v17 and earlier), when worker pools were exhausted, queries ran in a degraded state without throwing explicit errors. Database teams would only discover the issue reactively after end-users complained about slow report generation, leaving them to manually parse pg_stat_activity to track down rogue leader/worker process hierarchies.

Improved Observability in Modern Deployments

With the introduction of enhanced statistics tracking in PostgreSQL 18, administrators finally have native telemetry to monitor pool health continuously. By tracking discrepancies between parallel_workers_to_launch and parallel_workers_launched via pg_stat_statements, teams can proactively identify when background worker pools are undersized.

Ultimately, mastering PostgreSQL parallelism requires looking past misleading parameter names. By respecting hard process limits, properly sizing worker pools, and targeting parallelism at the specific table level rather than relying on blunt session-wide overrides, database administrators can harness the full processing power of modern multi-core hardware while preserving the stability of the entire cluster.