Introduction:
In PostgreSQL, Asynchronous parameters are essential for optimizing how the database performs background operations, manages disk I/O, and executes tasks using parallel workers. These parameters directly influence the efficiency of maintenance activities (such as vacuuming and indexing) and the speed of query execution, particularly in environments with high workloads or large datasets.
These parameters control how PostgreSQL:
- Performs background operations (like
VACUUMandindexing), - Manages concurrent I/O tasks,
- Executes queries using parallel workers.
Proper tuning of these settings ensures:
- Faster query performance through parallel execution,
- Optimized maintenance operations,
- Better utilization of system resources, especially on multi-core and SSD-backed servers.
Key Parameters:
1) effective_io_concurrency : plays a crucial role in optimizing background I/O operations in PostgreSQL. This parameter determines how many I/O operations can be initiated in parallel by background processes such as vacuuming and bitmap heap scans.
It is recommended to tune this value based on the underlying hardware, especially the type of storage and the number of available CPU cores.
Show the current value :
postgres=# show effective_io_concurrency ;
effective_io_concurrency
--------------------------
1
(1 row)How to set effective_io_concurrency
You can modify the parameter using the ALTER SYSTEM command:
postgres=# ALTER SYSTEM SET effective_io_concurrency to 2;
ALTER SYSTEMTo apply the changes to the PostgreSQL server, reload it.
Verifying the Change
After reload, re-check the parameter:
postgres=# show effective_io_concurrency ;
effective_io_concurrency
--------------------------
2
(1 row)Impact :
Higher Value:
Allows PostgreSQL to issue more concurrent read requests, improving performance during operations like VACUUM, bitmap heap scans, and index-only scans on systems with high I/O throughput.
Lower Value: Limits concurrent I/O operations, which is safer on traditional HDDs or slow disks. However, it may result in slower maintenance and query operations if the disk subsystem can handle more parallelism.
2) maintenance_io_concurrency: controls the number of concurrent I/O operations that maintenance tasks in PostgreSQL can perform. This includes operations like autovacuum, manual vacuum, and index creation.
Tuning this parameter allows PostgreSQL to better utilize modern hardware capabilities during heavy maintenance operations, especially when fast storage (like SSDs) and multiple CPUs are available.
Show the current value :
postgres=# show maintenance_io_concurrency ;
maintenance_io_concurrency
----------------------------
10
(1 row)How to set maintenance_io_concurrency
You can modify the parameter using the ALTER SYSTEM command:
postgres=# ALTER SYSTEM SET maintenance_io_concurrency to 5;
ALTER SYSTEMTo apply the changes to the PostgreSQL server, reload it.
Verifying the Change
After reload, re-check the parameter:
postgres=# show maintenance_io_concurrency ;
maintenance_io_concurrency
----------------------------
5
(1 row)Impact:
Higher Value: Allows PostgreSQL to execute multiple I/O operations during vacuum or index creation in parallel, reducing the total maintenance time. This is especially effective on high-performance storage systems and helps maintain table health more efficiently.
Lower Value: Safer for slower disks (like HDDs), but can cause maintenance tasks to run longer, potentially leading to table bloat or increased autovacuum lag if the workload is high.
3) max_worker_processes: parameter defines the total number of background worker processes that the system can use. These workers are required for various internal operations such as parallel query execution, autovacuum workers, logical replication, and other background tasks. Properly configuring this parameter is crucial to ensuring that PostgreSQL can utilize all available system resources effectively without overcommitting CPU or memory.
A general recommendation is to set max_worker_processes equal to the number of CPU cores available, which allows PostgreSQL to fully utilize parallel processing features when needed.
Show the current value :
postgres=# show max_worker_processes ;
max_worker_processes
----------------------
8
(1 row)How to set max_worker_processes
You can modify the parameter using the ALTER SYSTEM command:
postgres=# ALTER SYSTEM SET max_worker_processes to 4;
ALTER SYSTEMTo apply the changes to the PostgreSQL server, restart it.
Verifying the Change
After the restart, re-check the parameter:
postgres=# show max_worker_processes ;
max_worker_processes
----------------------
4
(1 row)Impact:
Higher Value: Enables PostgreSQL to run more background and parallel tasks, improving performance for complex queries and replication, but may increase system resource usage.
Lower Value: Reduces system load, which is suitable for resource-constrained environments, but may limit the benefits of parallelism or background processing.
4)max_parallel_workers: parameter specifies the maximum number of parallel workers that are available to support parallel queries specifically. Parallel query execution can significantly speed up processing for certain types of operations, like sequential scans, joins, and aggregates. This parameter ensures that parallelism is used effectively without exhausting your resources.
Set this value to half or more of max_worker_processes, depending on how often your workload benefits from parallel execution.
Show the current value :
postgres=# show max_parallel_workers;
max_parallel_workers
----------------------
8
(1 row)How to set max_parallel_workers
You can modify the parameter using the ALTER SYSTEM command:
postgres=# ALTER SYSTEM SET max_parallel_workers to 2;
ALTER SYSTEMTo apply the changes to the PostgreSQL server, restart it.
Verifying the Change
After the restart, re-check the parameter:
postgres=# show max_parallel_workers;
max_parallel_workers
----------------------
2
(1 row)Impact :
Higher Value: Allows more parallel workers across all queries, boosting performance on systems with heavy analytical workloads and multiple cores.
Lower Value: Conserves resources and avoids excessive CPU consumption but may reduce the performance of parallelizable queries.
5)max_parallel_maintenance_workers: controls the number of parallel workers available for maintenance operations in PostgreSQL.This setting influences maintenance tasks like CREATE INDEX and certain VACUUM operations. If these sorts of tasks seem slow and there’s available CPU headroom, increasing max_parallel_maintenance_workers over the recommended value might help, although it’s not recommended to raise it over a third of CPU to ensure you’re leaving resources available for regular query processing.
Show the current value :
postgres=# show max_parallel_maintenance_workers;
max_parallel_maintenance_workers
----------------------------------
2
(1 row)How to set max_parallel_maintenance_workers
You can modify the parameter using the ALTER SYSTEM command:
postgres=# ALTER SYSTEM SET max_parallel_maintenance_workers to 1;
ALTER SYSTEMTo apply the changes to the PostgreSQL server, reload it.
Verifying the Change
After reload, re-check the parameter:
postgres=# show max_parallel_maintenance_workers;
max_parallel_maintenance_workers
----------------------------------
1
(1 row)Impact:
Higher Value: Improves the performance of index creation and vacuuming on large tables by enabling more workers.
Lower Value: Reduces CPU usage during maintenance operations but may lead to longer execution times for tasks like CREATE INDEX.
6) max_parallel_workers_per_gather: This parameter controls how many parallel workers a single query node can use during parallel execution. It directly affects how much parallelism is available to a single query.
Show the current value :
postgres=# show max_parallel_workers_per_gather;
max_parallel_workers_per_gather
---------------------------------
2
(1 row)How to set max_parallel_workers_per_gather
You can modify the parameter using the ALTER SYSTEM command:
postgres=# ALTER SYSTEM SET max_parallel_workers_per_gather to 1;
ALTER SYSTEMTo apply the changes to the PostgreSQL server, reload it.
Verifying the Change
After reload, re-check the parameter:
postgres=# show max_parallel_workers_per_gather;
max_parallel_workers_per_gather
---------------------------------
1
(1 row)Impact:
Higher Value: Increases the performance of complex individual queries by allowing them to use more parallel workers. Best suited for OLAP-style workloads.
Lower Value: Preserves parallel workers for multiple concurrent queries, improving concurrency in mixed workloads but potentially slowing down complex queries.
Conclusion:
PostgreSQL’s asynchronous behavior parameters are essential for tuning performance and ensuring efficient resource utilization, especially in systems handling large datasets or high-concurrency workloads. Parameters like effective_io_concurrency, maintenance_io_concurrency, and max_worker_processes directly influence how PostgreSQL handles I/O operations and background tasks. When configured properly, they can significantly enhance maintenance operations like VACUUM, indexing, reduce query execution times through parallelism, and optimize the use of modern hardware, such as SSDs and multi-core CPUs.
Understanding the impact of these parameters and adjusting them based on your hardware and workload allows for a better overall system performance, stability, and scalability. This document has demonstrated how to inspect, modify, and verify each parameter, along with best practices and practical insights into their impact.