Mustafa ERBAY

Posted on Aug 2 • Originally published at mustafaerbay.com.tr

PostgreSQL 18 Async I/O: What Changes in Real Workloads?

#postgres #sistemmimarisi #software

One of the most significant innovations introduced with PostgreSQL 18, Asynchronous I/O (Async I/O) support, has the potential to fundamentally change how database servers interact with storage units. This feature aims to significantly boost performance, especially in high I/O-intensive workloads, by enabling the parallelization of block read operations. For PostgreSQL, which traditionally used blocking I/O, this represents a major step forward in managing storage latencies.

For developers and system administrators, Async I/O offers new ways to overcome I/O bottlenecks, particularly in modern infrastructures like fast NVMe SSDs or Storage Area Networks (SAN). In this article, we will delve into how Async I/O works in PostgreSQL 18, what changes it brings to real workloads, and how we can best leverage this new capability.

What is Async I/O and How Does It Differ from the Traditional Approach?

Async I/O (Asynchronous Input/Output) is a mechanism that allows an application to initiate a data read or write operation from a storage unit and continue with other tasks while that operation is in progress. In the traditional, synchronous (blocking) I/O model, an application must wait until the I/O operation is complete. This waiting time can severely degrade an application's overall performance, especially with slow storage units or high-latency network storage systems.

In database systems like PostgreSQL, blocking I/O causes a process to pause when a query or background task wants to read or write a page from disk. These pauses can lead to thousands of queries waiting in a queue and inefficient use of CPU resources during heavy workloads. Async I/O, on the other hand, allows database processes to continue with other operations instead of waiting for I/O to complete, by sending multiple I/O requests to the operating system simultaneously and receiving notifications when these requests are finished.

This fundamental difference makes Async I/O indispensable, especially in modern server architectures and storage technologies. While synchronous I/O leads to CPU cores being wasted waiting for I/O, Async I/O makes these waiting times available for other computational tasks. This results in higher throughput and lower latency.

How Does Async I/O Work in PostgreSQL?

PostgreSQL 18 primarily manages Async I/O through the io_method configuration parameter. This parameter offers three different options: sync, worker, and io_uring. By default, the worker method is used.

worker method (Default): This method uses dedicated background processes to manage I/O operations. When a backend process needs data, it sends requests to these worker processes instead of waiting for disk access.
io_uring method: This method provides high-performance asynchronous I/O operations using the io_uring interface in Linux. io_uring is a modern interface developed in the Linux kernel, offering lower overhead and more flexibility compared to traditional libaio or posix aio APIs. PostgreSQL delegates data read operations from disk directly to the operating system using these low-level APIs. This method eliminates the need for worker processes by interacting directly with the kernel via shared ring buffers.
sync method: This preserves the traditional synchronous I/O behavior from before PostgreSQL 18.

When the database server wants to read a data page from disk, it adds the I/O request to the relevant mechanism (worker queue or io_uring queue) according to the io_method parameter. This allows the PostgreSQL process to process other queries or focus on other tasks without waiting for the I/O to complete. When the I/O operation is finished, the operating system (or worker process) sends a notification to PostgreSQL. Upon receiving this notification, PostgreSQL processes the result of the corresponding I/O operation and resumes the waiting query or task.

Asynchronous I/O support in PostgreSQL 18 currently supports read operations for sequential scans, bitmap heap scans, and maintenance operations like VACUUM. Asynchronous I/O support for write operations may be extended in future releases.

ℹ️ Importance of io_uring

io_uring is available on Linux kernel versions 5.1 or higher and plays a critical role in Async I/O performance. If your PostgreSQL server uses an older Linux kernel, you may not fully benefit from all the advantages offered by the io_uring method. Checking your operating system's kernel version and updating it if necessary will help you get the best performance from this new feature.

This mechanism provides significant benefits, especially in OLTP (Online Transaction Processing) workloads with intense random read operations. Instead of a process getting stuck for each page read request, multiple I/O requests can be processed concurrently. This masks disk access times for data not found in the database buffer cache, reducing query latencies and increasing overall transaction throughput.

What are the Effects of Async I/O in Real Workloads?

Async I/O in PostgreSQL 18 has the potential to create significant performance changes in real-world workloads. These changes vary depending on the speed of the storage infrastructure, the nature of the workload, and the optimization of database settings.

1. Random I/O Performance (OLTP Workloads)

In OLTP systems, the database typically accesses small, random data blocks (e.g., index lookups, single-row updates). In traditional synchronous I/O, the process waiting for each disk read or write leads to high latencies. With Async I/O, PostgreSQL can initiate multiple random read requests simultaneously. This allows I/O requests to better utilize the true parallel processing capacity of the storage device. As a result, query latencies decrease, transactions per second (TPS) increase, and user experience improves. This effect becomes even more pronounced with storage units offering high IOPs (Input/Output Operations Per Second) like NVMe SSDs. The effective_io_concurrency parameter currently only affects bitmap heap scans.

2. Sequential I/O Performance (Analytics and Reporting)

Async I/O also benefits sequential I/O-intensive workloads such as scanning large tables, ETL (Extract, Transform, Load) processes, or complex analytical queries. When PostgreSQL reads large amounts of data sequentially from disk, it doesn't have to wait for the current I/O operation to complete before reading the next data block. By sending multiple read requests ahead, disk read bandwidth is utilized more efficiently. This shortens data loading times and helps analytical queries complete faster.

3. WAL Write Performance and Durability

WAL (Write-Ahead Log) write operations are critical for database durability and typically require synchronized fsync() calls. Since Async I/O in PostgreSQL 18 primarily targets read operations, it does not directly provide asynchronous support for WAL write operations. However, making read I/Os asynchronous can free up system resources by allowing better integration with other background I/O activities. Especially in systems with high transaction volumes, delays in writing the WAL buffer to disk affect transaction commit times. Async I/O can reduce overall system load by making read operations more efficient and indirectly help WAL write operations be queued and processed with less contention.

4. Checkpoint Processes

Checkpoints are critical moments when dirty buffers are written to disk and the WAL is cleaned. These I/O-intensive processes can often cause short-term stalls on the system. Async I/O speeding up read operations can shorten checkpoint times by leaving more resources for the checkpointer's other tasks and reduce the severity of stalls that negatively impact the database's overall responsiveness. While developing an ERP for a manufacturing company, I personally observed that intense checkpoints caused noticeable slowdowns on operator screens; Async I/O has the potential to mitigate such momentary performance drops.

Configuration and Monitoring: Maximizing Benefits from Async I/O

To get the most out of Async I/O in PostgreSQL 18, proper configuration and continuous monitoring are essential. Although this feature is enabled by default, fine-tuning may be necessary based on your workload and infrastructure.

Configuration Parameters

Some important postgresql.conf parameters that can affect Async I/O performance are:

io_method: Introduced with PostgreSQL 18, this parameter controls how asynchronous I/O is performed. Options are sync, worker (default), and io_uring. io_uring can be the most efficient option on Linux systems, while the worker method can be used on other platforms.
io_workers: When io_method is set to worker, this parameter determines the maximum number of background worker processes that will perform asynchronous I/O operations.
effective_io_concurrency: This parameter determines how many I/O operations PostgreSQL will attempt to perform concurrently from disk. Increasing this value (e.g., in the range of 100-200) is generally beneficial for modern SSDs. However, setting too high a value can overload the storage system and have an adverse effect. This value should be adjusted according to your storage system's actual parallel I/O capacity. It's important to remember that this setting currently only affects bitmap heap scans.
wal_writer_delay: Determines how often the WAL writer writes to disk. The default value is 200 milliseconds (200ms). With Async I/O, WAL write operations might become more efficient, so experimenting with this parameter could yield different results.
checkpoint_timeout: Determines the maximum time between automatic WAL checkpoints. The default value is five minutes (5min).
max_wal_size: Determines the maximum size WAL is allowed to grow during automatic checkpoints. The default value is 1 GB. With more efficient I/O, checkpoints might complete faster, which could allow for more frequent checkpoints or create less overhead at the current frequency.

Proper adjustment of these parameters ensures that PostgreSQL makes the best use of its Async I/O capabilities. It is crucial to carefully monitor system performance after each change.

Monitoring and Verification

Various tools can be used to monitor whether Async I/O is active and providing the expected benefits:

pg_stat_io: This system view, introduced with PostgreSQL 16 and significantly enhanced in PostgreSQL 18 with byte-level statistics, WAL I/O tracking, and per-backend monitoring capabilities, provides statistics for various I/O activities. You can track read and write counts, latencies, and other metrics related to Async I/O here.
```
SELECT * FROM pg_stat_io;
```
Operating System Tools:
- iostat -xdm 1: Shows disk I/O statistics (read/write speed, average time per operation). await and util values are important for understanding how efficiently Async I/O uses the disk.
- vmstat 1: Used to monitor overall system performance, especially I/O wait (wa) times. In a system where Async I/O is effective, wa values are expected to decrease.
- pidstat -d 1: Shows disk I/O statistics on a per-process basis. Useful for monitoring disk activities of PostgreSQL processes.
PostgreSQL Logs: Logging slow queries with settings like log_min_duration_statement and examining their I/O profiles can provide valuable insights into the impact of Async I/O.

⚠️ Risk of Misconfiguration

Setting parameters like effective_io_concurrency unnecessarily high can overload the storage system and increase latencies by bloating I/O queues. Always make changes in small steps and test each step carefully.

Trade-offs and Considerations Brought by Async I/O

While Async I/O offers many performance advantages, it also comes with some trade-offs and points to consider. No feature is "free," and there is always a balance.

1. Increased Memory Usage and CPU Overhead

Async I/O may require additional memory and CPU resources on the operating system side to manage I/O requests and completion notifications. While modern APIs like io_uring minimize this overhead, this resource consumption should not be overlooked under very high concurrent I/O loads. Especially if the system is already experiencing CPU or memory bottlenecks, the additional load from Async I/O can negatively impact overall performance.

2. Operating System and Kernel Dependency

PostgreSQL's Async I/O implementation is tightly coupled with underlying operating system capabilities. On Linux, the performance and stability of io_uring can vary depending on the kernel version used. In older kernel versions, io_uring may not be present at all or may not deliver the desired performance. On other operating systems (e.g., Windows or macOS), Async I/O is executed through different APIs, so performance characteristics may differ. It's important to ensure that your distribution and kernel version fully support io_uring or equivalent AIO APIs.

3. Debugging and Troubleshooting Complexity

Due to its asynchronous nature, troubleshooting I/O issues can be more complex than with synchronous I/O. The relationship between when an I/O request starts, how long it takes, and when it completes is not as linear as in the synchronous case. This can make it harder to find the root cause, especially when performance issues or I/O errors arise. Advanced monitoring and profiling tools may be needed.

4. Scenarios with Limited Benefit

Async I/O is not a miraculous solution for every workload. Since Async I/O in PostgreSQL 18 primarily targets read operations, it may not provide a direct performance boost in write-intensive workloads. If your database server is already experiencing a CPU or memory bottleneck rather than an I/O one, the contribution of Async I/O will be limited. For example, for CPU-bound queries that perform complex calculations or sort many rows, Async I/O does not provide a direct performance increase. Similarly, in systems using slow or older storage units (e.g., traditional HDDs), the parallelism offered by Async I/O may not be fully utilized because the storage unit itself cannot process multiple requests quickly at the same time.

ℹ️ No Hardware Independence

Async I/O shows its true potential when used with modern and fast storage hardware (NVMe SSDs, high-performance SANs). Using Async I/O on a slow storage unit may not provide a significant performance increase; it could even degrade performance due to additional overhead.

Understanding these trade-offs is critical when deciding when and how to use Async I/O. As always, testing your system with your own workload is the most reliable approach.

Use Cases and Future Perspective

Async I/O in PostgreSQL 18 shines in specific use cases and elevates database performance to a new level. This innovation also lays an important foundation for future PostgreSQL developments.

Prominent Use Cases

High-Concurrency OLTP Systems: Systems like e-commerce sites, banking applications, or manufacturing ERPs with numerous short-lived transactions and random read requests will benefit most from Async I/O. In such environments, I/O latencies directly impact user experience and transaction volume. Async I/O masks these latencies, providing a smoother transaction flow. In high-transaction volume systems like the backend of my own side product's financial calculators, I've seen countless times how critical I/O performance is.
Large Data Warehouses and Analytical Workloads: In data warehouse environments where complex queries and reports run on very large tables, parallelizing sequential disk reads can significantly reduce query completion times. This enables faster preparation of business intelligence (BI) reports and enhances real-time analytical capabilities.
Real-time Dashboards and Monitoring Systems: Real-time dashboards or system monitoring applications that are continuously updated and require reading data from disk can provide more up-to-date data with lower latencies thanks to Async I/O. This is a critical advantage, especially in operational environments where quick decisions need to be made.
Data Loading (ETL) and Backup/Restore Operations: In situations where large amounts of data are transferred to the database or backup/restore operations are performed, Async I/O can accelerate disk read operations, shortening the duration of these processes. This is particularly important for maintaining data integrity and achieving RTO (Recovery Time Objective) targets.

Future Perspective

The integration of Async I/O into PostgreSQL not only solves existing performance issues but also opens new doors for future developments. For example:

Smarter Buffer Management: With Async I/O, interactions between PostgreSQL's shared buffer cache and disk I/O can be further optimized.
Enhanced Parallel Query Execution: When combined with parallel query execution capabilities, Async I/O can enable even better utilization of the potential in multi-core systems.
Optimization in Cloud Environments: The synergy of Async I/O with high-performance storage units offered by cloud providers (e.g., AWS gp3, Azure Premium SSD) will increase the efficiency of cloud-based PostgreSQL deployments.

These developments will continuously strengthen PostgreSQL's ability to handle modern and demanding workloads.

Conclusion

The Async I/O capabilities introduced with PostgreSQL 18 have the potential to significantly boost database performance, especially in I/O-intensive read workloads. When combined with modern storage technologies, it promises lower latencies, higher transaction throughput, and smoother operational processes. However, to fully benefit from this technology, correct configuration, continuous monitoring, and careful consideration of potential trade-offs are necessary.

For system administrators and database architects, this is a good opportunity to review their existing infrastructure and evaluate the advantages offered by Async I/O when planning a migration to PostgreSQL 18. In my own experience, I've seen that such low-level I/O optimizations can create much larger impacts than any improvement at the software layer. Therefore, Async I/O should be seen not just as a feature, but as an important part of PostgreSQL's future performance roadmap. In the next article, we can explore how to monitor I/O metrics in detail with pg_stat_io in PostgreSQL.

DEV Community