Best Practices for Parallel Queries in RDS for PostgreSQL

Parallel query execution is a feature in PostgreSQL that allows a single SQL query to be broken into smaller tasks that are processed simultaneously by multiple background worker processes. Instead of executing a query entirely in a single backend process, PostgreSQL can distribute parts of the query, such as scans, joins, aggregations, or sorting, across multiple CPU cores. The leader process coordinates this execution and gathers the results from the parallel workers.

However, for most production workloads, especially high-concurrency OLTP systems, we recommend disabling automatic parallel query execution. While parallelism can accelerate queries on large datasets in analytics or reporting workloads, it introduces significant risks that often outweigh the benefits in busy production environments.

Parallel execution also introduces significant overhead. Each parallel worker is a full PostgreSQL backend process, which requires process forking (copying memory structures and initializing process state) and authentication (consuming connection slots from your max_connections limit). Each worker also consumes its own memory, including work_mem for sorting and hashing operations, with multiple workers per query, memory usage multiplies quickly (e.g., 4 workers × 64MB work_mem = 256MB per query). As a result, parallel queries can consume considerably more system resources than single-process queries. If not tuned properly, they may lead to CPU saturation (multiple workers overwhelming available processing capacity), increased context switching (the operating system frequently switching between numerous worker processes, adding overhead and reducing throughput), or connection exhaustion (since each parallel worker consumes a connection slot, a single query with 4 workers uses 5 connections total, 1 leader + 4 workers, which can quickly exhaust your connection pool under high concurrency, preventing new client connections and causing application failures). These issues are particularly severe under high-concurrency workloads where multiple queries may attempt parallel execution simultaneously.

PostgreSQL decides whether to use parallelism based on cost estimates. In some cases, the planner may automatically switch to a parallel plan if it appears cheaper even when it's not ideal in practice. This can happen if index statistics are outdated or if bloat makes sequential scans appear more attractive than index lookups. Because of this behavior, automatic parallel plans can sometimes introduce regressions in query performance or system stability.

To get the most benefit from parallel queries in RDS for PostgreSQL, it's important to test and tune them based on your workload, monitor system impact, and disable automatic parallel plan selection in favor of query-level control.

Configuration Parameters

PostgreSQL uses several parameters to control the behavior and availability of parallel queries. Understanding and tuning these is critical to achieving predictable performance:

Key Considerations

Identify Parallel Queries Usage

Queries may flip to parallel plans based on data distribution or statistics. For example:

SELECT count(*) FROM customers WHERE last_login < now() - interval '6 months';

This query might use an index for recent data, but switch to a parallel sequential scan for historical data.

You can log query execution plans by loading the auto_explain module. To learn more, see Logging execution plans of queries in the AWS knowledge center.

You can monitor CloudWatch Database Insights for Parallel Query related wait events. To learn more about Parallel Query related wait events, go through IPC:parallel wait events

From PostgreSQL version 18, you can monitor parallel worker activity using new columns in pg_stat_database and pg_stat_statements:

These metrics help identify discrepancies between planned and actual parallelism, which can indicate resource constraints or configuration issues. Use the following queries to monitor parallel execution:

For Database-level parallel worker metrics:

SELECT datname, parallel_workers_to_launch, parallel_workers_launched
FROM pg_stat_database
WHERE datname = current_database();

For Query-level parallel worker metrics

SELECT query, parallel_workers_to_launch, parallel_workers_launched
FROM pg_stat_statements
ORDER BY parallel_workers_launched;

How to Control Parallelism

There are several ways to control query parallelism, each designed for different scenarios and requirements.

To disable automatic parallelism globally, modify your parameter group to set:

max_parallel_workers_per_gather = 0;

For persistent, user-specific settings, the ALTER ROLE command provides a way to set parameters that will apply to all future sessions for a particular user.

For example:

ALTER ROLE username SET max_parallel_workers_per_gather = 4; ensures that every time this user connects to the database, their sessions will use this parallel worker setting when required.

Session-level control can be achieved using the SET command, which modifies parameters for the duration of the current database session. This is particularly useful when you need to temporarily adjust settings without affecting other users or future sessions. Once set, these parameters remain in effect until explicitly reset or until the session ends. The commands are straightforward:

SET max_parallel_workers_per_gather = 4;
-- Run your queries
RESET max_parallel_workers_per_gather;

For even more granular control, SET LOCAL allows you to modify parameters for a single transaction. This is ideal when you need to adjust settings for a specific set of queries within a transaction, after which the settings automatically revert to their previous values. This approach helps prevent unintended effects on other operations within the same session.

Diagnosing Parallel Query Behavior

Use EXPLAIN (ANALYZE, VERBOSE) to confirm whether a query used parallel execution:

To disable parallelism temporarily for comparison:

SET max_parallel_workers_per_gather = 0;
EXPLAIN ANALYZE <your_query>;
RESET max_parallel_workers_per_gather;