PG18 Hacktober : 31 Days of New Features : Asynchronous I/O – Part II

Welcome to the 2nd part of the blog post on PG18 Asynchronous I/O. Here’s the first part of the blog post that talked about the feature highlights.

The introduction of Asynchronous I/O (AIO) in PostgreSQL 18, featuring the kernel-native io_uring method, was one of the most anticipated performance enhancements. AIO promises to unblock processes waiting on disk reads, leading to dramatically better concurrency and lower latency, especially under high-load OLTP (Online Transaction Processing) scenarios.

However, initial testing often yields a confusing and counter-intuitive result: the seemingly “old” default worker method can significantly outperform io_uring for high-bandwidth tasks like sequential scans.

This post dissects the architectural reasons behind this performance anomaly and provides clear guidance on how to configure the io_method parameter in PostgreSQL 18.

Step 1: Enabling io_uring at the OS level

Before PostgreSQL can utilize io_uring, it must be enabled in the Linux kernel. Many operating systems default to disabling this feature.

First, check the current status:

[root@ip-172-31-17-7 ~]# cat /proc/sys/kernel/io_uring_disabled
1

If the result is 1, it is disabled. You must set it to 0 using sysctl.

[root@ip-172-31-17-7 ~]# sudo sysctl -w kernel.io_uring_disabled=0
kernel.io_uring_disabled = 0
[root@ip-172-31-17-7 ~]# cat /proc/sys/kernel/io_uring_disabled
0

With the kernel parameter set, we can now proceed to the PostgreSQL tests.

Step 2: The benchmark results

To isolate the performance difference, a simple sequential scan (SELECT COUNT(*)) was run against a large table (200,000,000 rows) using the default worker method and the newly enabled io_uring method. Crucially, the OS cache was cleared before each run to ensure the test measured disk I/O performance.

Configuration	PostgreSQL Command	Result (Time)
io_method = 'worker' (Default)	SHOW io_method;	8.617 ms
io_method = 'io_uring'	ALTER SYSTEM SET io_method = 'io_uring';	27.538 ms

Raw Test Output

Test 1: io_method = worker

[postgres@ip-172-31-17-7 ~]$ psql -c "show io_method;"
io_method
-----------
worker
(1 row)

-- OS Cache cleared here (sync; echo 3 | sudo tee /proc/sys/vm/drop_caches)

[postgres@ip-172-31-17-7 ~]$ psql -d pgbench_test_18 -c "\timing on" -c "select count(*) FROM pgbench_accounts;"
Time: 8616.561 ms (00:08.617)

Test 2: io_method = io_uring

[postgres@ip-172-31-17-7 ~]$ psql -c  "ALTER SYSTEM SET io_method = 'io_uring';"
ALTER SYSTEM
[postgres@ip-172-31-17-7 ~]$ -- Server restarted, cache cleared

[postgres@ip-172-31-17-7 ~]$ psql -c "show io_method;"
io_method
-----------
io_uring
(1 row)

[postgres@ip-172-31-17-7 ~]$ psql -d pgbench_test_18 -c "\timing on" -c "select count(*) FROM pgbench_accounts;"
Time: 27538.013 ms (00:27.538)

The result is clear: io_uring was over three times slower (27.538 ms vs. 8.617 ms) for this sequential scan.

Step 3: The Performance Anomaly Explained

The key to understanding this unexpected slowdown lies in realizing that disk I/O is only one part of the data retrieval process.

When PostgreSQL reads a data block, the process involves three major steps:

1. Actual I/O: Reading the data from disk into the kernel’s page cache.
2. Data Integrity: Verifying the block’s checksum (standard in PG 18).
3. Data Copy: Moving the data from the kernel’s page cache into PostgreSQL’s Shared Buffers (memcpy).

For bandwidth-heavy operations like a full table scan, the CPU cost of checksumming and memcpy is the dominant factor, creating a user-space bottleneck.

The Worker Method: Load Distribution

The worker method utilizes a pool of I/O Worker processes separate from the backend process requesting the data.

• When a backend needs a block, it offloads the request to an I/O Worker.
• The Worker process performs all three steps (I/O, checksum, and memcpy).
• Because the system can run multiple backend processes and multiple I/O Workers concurrently, the significant CPU cost of the high-bandwidth checksumming and copying is effectively distributed across the shared worker pool. This inherent parallelization allows the worker pool to manage a large volume of data copying and verification more efficiently than a single process.

The io_uring Method: Single-Process Bottleneck

The io_uring method aims for maximum kernel-level efficiency by integrating directly with the backend process’s I/O queue.

• The backend process initiates the I/O using io_uring.
• Crucially, the backend process itself performs the CPU-intensive checksum and memcpy steps.
• For a heavy sequential scan, the efficiency gained at the kernel level is nullified because the single backend process is immediately bottlenecked on its own CPU usage for verifying and moving the data.

In this specific sequential scan test, the worker method acts as a vital CPU load distributor, explaining its superior performance.

Step 4: Key Takeaways and Recommendations

The AIO feature in PostgreSQL 18 is a massive architectural change, but it’s important to note its current limitations: AIO only supports read operations; major activities like writes, checkpoints, and WAL activities still rely on synchronous I/O.

For AIO performance tuning in PostgreSQL 18, use the following guidelines:

1. io_method setting

• Retain the default setting of worker for most general-purpose workloads. The worker method is robust, portable, and often superior for high-bandwidth read tasks due to its load distribution capabilities.
• Only switch to io_uring if extensive testing proves it is superior for your specific, low-latency, random-read patterns (e.g., highly concurrent OLTP lookups). Do not assume it is faster by default.
• Avoid using sync unless you need behavior identical to PostgreSQL 17.

2. io_workers Tuning

Regardless of your chosen io_method, the number of I/O workers determines your parallel capacity.

• The default io_workers value is often too low for modern, high-core count hardware.
• Increase this value based on your server’s total CPU cores. A recommended starting point is 25% of your total core count, with the option to increase it up to 100% of the cores on systems with extremely high I/O demands.

By understanding the division of labor between kernel I/O and user-space data processing (checksumming and copying), DBAs can correctly configure PostgreSQL 18 to leverage its new AIO capabilities without sacrificing performance on high-throughput workloads.

Summary

In this blog post, we looked at:

• How to enable and configure AIO (backend_flush_after, shared_buffers, maintenance_io_concurrency, etc.)
• Monitoring AIO activity (pg_stat_io, pg_stat_aio)
• Performance tuning for io_uring vs worker configuration options
• Benchmark results comparing sync vs async reads

What’s next in the PG18 Hactober?
We’re going to look at Virtual generated columns, they are computed on the fly when queried, meaning they do not occupy any physical storage. This provides a huge benefit for performance and storage efficiency, especially. Stay tuned!