# PostgreSQL 18 Asynchronous I/O: A Complete Guide [PostgreSQL 18](https://www.postgresql.org/) introduces asynchronous I/O support for read operations, marking a significant shift in how the database handles disk access. This feature addresses a longstanding limitation where PostgreSQL relied entirely on operating system readahead mechanisms that couldn't predict database-specific access patterns effectively. In previous versions, PostgreSQL issued read requests one at a time, waiting for each to complete before moving to the next. The operating system attempted to anticipate which data blocks might be needed, but without insight into query execution plans or table structures, these predictions often missed their target. Asynchronous I/O changes this by allowing PostgreSQL to submit multiple read requests concurrently. The database now controls readahead decisions based on query plans, anticipating data needs with much greater accuracy. In environments with high storage latency—particularly cloud platforms with network-attached storage—this can deliver performance improvements of 2-3x for read-intensive workloads. PostgreSQL 18's asynchronous I/O currently **supports sequential scans, bitmap heap scans, and maintenance operations** like VACUUM. These operations benefit most because they access data in predictable patterns that the database can anticipate effectively. This article guides you through configuring and testing PostgreSQL 18's asynchronous I/O on your own system. You'll learn how to measure its impact, understand when it helps, and configure it appropriately for your hardware and workload characteristics. [ad-logs] ## Prerequisites Before proceeding with this article, ensure you have PostgreSQL 18 installed on a test system where you can safely experiment: ```command psql --version ``` ```text [output] psql (PostgreSQL) 18.0 (Ubuntu 18.0-1.pgdg24.04+3) ``` This guide assumes familiarity with basic PostgreSQL administration including editing configuration files, running SQL queries, and restarting the database server. You'll also need sufficient disk space to create test tables larger than your available memory, since asynchronous I/O benefits manifest primarily when queries read from disk rather than cache. ## Getting started with PostgreSQL 18 asynchronous I/O Start by creating a test database and examining your current configuration. This establishes a baseline before enabling any new features. Create a dedicated test database: ```command sudo -u postgres createdb aio_test ``` Connect to the database: ```command sudo -u postgres psql aio_test ``` Check your current I/O method: ```sql SHOW io_method; ``` ```text [output] io_method ----------- worker (1 row) ``` PostgreSQL 18 defaults to the `worker` I/O method, which uses dedicated background processes for asynchronous I/O. This represents a significant improvement over PostgreSQL 17's synchronous approach. Check the other key I/O parameters: ```sql SHOW effective_io_concurrency; SHOW maintenance_io_concurrency; SHOW io_workers; ``` ```text [output] effective_io_concurrency -------------------------- 16 (1 row) maintenance_io_concurrency ---------------------------- 16 (1 row) io_workers ------------ 3 (1 row) ``` The default of 16 for `effective_io_concurrency` increased from 1 in PostgreSQL 17, reflecting asynchronous I/O's capabilities. You'll learn how to tune these parameters for your specific hardware shortly. ## Understanding the I/O methods PostgreSQL 18 provides three I/O methods, controlled by the `io_method` parameter. Understanding each helps you choose appropriately for your environment. ![PostgreSQL 18 asynchronous I/O methods diagram: worker method (default), io_uring method (best performance), and sync method (PostgreSQL 17 behavior) ](https://imagedelivery.net/xZXo0QFi-1_4Zimer-T0XQ/3487f9f8-9c63-4915-6303-fd8aa85eaf00/lg1x =2486x1540) ### The worker method The `worker` method (the default) uses dedicated background processes to handle I/O operations. When a backend process needs data, it submits requests to these worker processes rather than blocking while waiting for disk access. Check how many I/O workers are running on your system. Open a new terminal window (separate from your PostgreSQL session) and run: ```command ps aux | grep "io worker" | grep -v grep ``` ```text [output] postgres 890 0.0 0.1 231192 5824 ? Ss 08:44 0:00 postgres: 18/main: io worker 2 postgres 891 0.0 0.3 231324 15168 ? Ss 08:44 0:00 postgres: 18/main: io worker 0 postgres 892 0.0 0.1 231324 6848 ? Ss 08:44 0:00 postgres: 18/main: io worker 1 ``` The three worker processes (labeled `io worker 0`, `io worker 1`, and `io worker 2`) correspond to `io_workers = 3` in your configuration. These processes remain active throughout the database's runtime, handling I/O requests from all connections and databases. Unlike regular backend processes that serve individual client connections, I/O workers are shared resources that process read requests submitted by any backend. ### The io_uring method The `io_uring` method uses Linux's modern I/O interface (kernel 5.1+) for even more efficient asynchronous operations. This method eliminates the need for worker processes by interacting directly with the kernel through shared ring buffers. Check if your kernel supports io_uring: ```command uname -r ``` ```text [output] 6.8.0-85-generic ``` Your kernel version 6.8 is well above the minimum requirement of 5.1, so io_uring is available. However, your PostgreSQL binary must also be compiled with `--with-liburing` support. First, find the path to your PostgreSQL binary. You can get this from the running postgres process: ```command ps aux | grep "bin/postgres" | grep -v grep | head -1 ``` ```text [output] postgres 860 0.0 0.8 231192 34304 ? Ss 08:44 0:00 /usr/lib/postgresql/18/bin/postgres -D /var/lib/postgresql/18/main -c config_file=/etc/postgresql/18/main/postgresql.conf ``` The path `/usr/lib/postgresql/18/bin/postgres` is your PostgreSQL binary location. Now check if it has liburing support: ```command ldd /usr/lib/postgresql/18/bin/postgres | grep uring ``` ```text [output] liburing.so.2 => /lib/x86_64-linux-gnu/liburing.so.2 (0x00007c1c0951b000) ``` The presence of `liburing.so.2` confirms your PostgreSQL binary was compiled with `io_uring` support. This means you can use `io_method = 'io_uring'` for potentially better performance than the worker method. The io_uring method typically provides lower overhead since it interacts directly with the kernel rather than using intermediary worker processes. If the command returns no output, your binary doesn't have `io_uring` support, but the worker method will still provide good asynchronous I/O performance. ### The sync method The `sync` method disables asynchronous I/O entirely, reverting to PostgreSQL 17's synchronous behavior. This exists primarily for troubleshooting and performance comparison, allowing you to measure the impact of asynchronous I/O by toggling it off. ## Creating a test workload To measure asynchronous I/O's impact accurately, you need a dataset large enough to force disk reads. Return to the terminal where your PostgreSQL session is running. Start by checking PostgreSQL's buffer cache allocation: ```sql SHOW shared_buffers; ``` ```text [output] shared_buffers ---------------- 128MB ``` Create a test table significantly larger than your shared buffers: ```sql CREATE TABLE io_benchmark ( id SERIAL PRIMARY KEY, data TEXT, value NUMERIC, created_at TIMESTAMP DEFAULT NOW() ); ``` Populate it with sample data: ```sql INSERT INTO io_benchmark (data, value) SELECT md5(random()::text), random() * 1000 FROM generate_series(1, 10000000); ``` This takes a few minutes to complete. Once finished, update the table statistics: ```sql ANALYZE io_benchmark; ``` Verify the table size: ```sql SELECT pg_size_pretty(pg_total_relation_size('io_benchmark')); ``` ```text [output] pg_size_pretty ---------------- 1029 MB (1 row) ``` The table is roughly 8x larger than shared buffers, ensuring queries will read from disk. ## Understanding when asynchronous I/O helps Before diving into performance testing, it's crucial to understand that asynchronous I/O doesn't universally improve all workloads. The benefits concentrate in specific scenarios. ![Diagram comparing PostgreSQL 17's synchronous I/O where backend waits for each disk block sequentially (Block 1, Block 2, Block 3) versus PostgreSQL 18's asynchronous I/O where all three disk blocks are read concurrently](https://imagedelivery.net/xZXo0QFi-1_4Zimer-T0XQ/d84105fb-6e93-4bd2-a20d-dd0e1e209e00/orig =2778x1400) **Where async I/O provides dramatic improvements:** - Cloud environments with network-attached storage (AWS EBS, Azure Managed Disks) - Storage with high latency (1-5ms per read) - Sequential scans of tables exceeding available memory - Systems without parallel worker processes **Where benefits are modest or minimal:** - Fast local NVMe or SATA SSDs with sub-millisecond latency - Systems already using parallel workers effectively - Tables that fit in memory (shared buffers + OS cache) - Small systems with limited CPU cores So your results on local storage will likely differ, and that's perfectly normal. ## Benchmarking synchronous I/O performance Before enabling asynchronous I/O, establish a performance baseline using synchronous I/O. This requires properly clearing caches to ensure queries actually read from disk rather than memory. Switch to your second terminal (not the PostgreSQL session) and check your system's available memory: ```command free -h ``` ```text [output] total used free shared buff/cache available Mem: 3.7Gi 677Mi 156Mi 147Mi 3.3Gi 3.1Gi Swap: 0B 0B 0B ``` Notice the `buff/cache` column showing 3.3GB—this is Linux's page cache, which stores recently accessed file data in RAM. After running a query once, subsequent executions read partially from this cache rather than disk, skewing measurements. To get accurate cold-cache measurements, you must clear the OS cache before each test. In your second terminal, create a helper script: ```command cat << 'EOF' | sudo tee /usr/local/bin/clear-pg-cache.sh #!/bin/bash systemctl stop postgresql@18-main sync echo 3 > /proc/sys/vm/drop_caches systemctl start postgresql@18-main sleep 2 EOF ``` ```command sudo chmod +x /usr/local/bin/clear-pg-cache.sh ``` This script stops PostgreSQL, clears the OS cache, and restarts the database. Now establish your synchronous I/O baseline by temporarily disabling async I/O. In your second terminal, clear the cache: ```command sudo /usr/local/bin/clear-pg-cache.sh ``` Once PostgreSQL restarts, connect to the database in your PostgreSQL session terminal: ```command sudo -u postgres psql aio_test ``` Now run the following commands to test synchronous I/O performance: ```sql SET effective_io_concurrency = 0; ``` ```sql \timing on ``` ```sql SELECT COUNT(*), AVG(value) FROM io_benchmark WHERE value > 500; ``` ```text [output] count | avg ---------+---------------------- 4999932 | 750.1352998950516530 (1 row) Time: 2912.988 ms (00:02.913) ``` This represents a true cold cache read—2913ms with all data read from disk. Run the same query again to see warm cache performance: ```sql SELECT COUNT(*), AVG(value) FROM io_benchmark WHERE value > 500; ``` ```text [output] count | avg ---------+---------------------- 4999932 | 750.1352998950516530 (1 row) Time: 1678.946 ms (00:01.679) ``` The warm cache run is ~42% faster (1679ms vs 2913ms) because the OS cached much of the data from the first run. This demonstrates why cache clearing matters—without it, you're not measuring disk I/O performance. Get the full execution plan with a cold cache. First, exit your PostgreSQL session: ```sql \q ``` Then clear the cache: ```command sudo /usr/local/bin/clear-pg-cache.sh ``` Once PostgreSQL restarts, reconnect to the database: ```command sudo -u postgres psql aio_test ``` Now run the following commands: ```sql SET effective_io_concurrency = 0; ``` ```sql EXPLAIN (ANALYZE, BUFFERS, TIMING) SELECT COUNT(*), AVG(value) FROM io_benchmark WHERE value > 500; ``` ![Screenshot of the performance output](https://imagedelivery.net/xZXo0QFi-1_4Zimer-T0XQ/5c4eb056-badf-404f-fb51-0b0d0eabd900/lg2x =1642x1408) ```text [output] QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------- Finalize Aggregate (cost=167665.04..167665.05 rows=1 width=40) (actual time=2777.946..2789.924 rows=1.00 loops=1) Buffers: shared read=104214 -> Gather (cost=167664.82..167665.03 rows=2 width=40) (actual time=2776.622..2789.865 rows=3.00 loops=1) Workers Planned: 2 Workers Launched: 2 Buffers: shared read=104214 -> Partial Aggregate (cost=166664.82..166664.83 rows=1 width=40) (actual time=2729.100..2729.101 rows=1.00 loops=3) Buffers: shared read=104214 -> Parallel Seq Scan on io_benchmark (cost=0.00..156297.33 rows=2073496 width=11) (actual time=38.503..2345.798 rows=1666644.00 loops=3) Filter: (value > '500'::numeric) Rows Removed by Filter: 1666689 Buffers: shared read=104214 Planning: Buffers: shared hit=54 read=24 Planning Time: 14.020 ms JIT: Functions: 17 Options: Inlining false, Optimization false, Expressions true, Deforming true Timing: Generation 5.506 ms (Deform 1.537 ms), Inlining 0.000 ms, Optimizatio n 9.534 ms, Emission 99.525 ms, Total 114.565 ms Execution Time: 3102.813 ms ``` The key baseline metrics: - **Cold cache execution time:** 3103ms - **Buffers read from disk:** : 104,214 blocks - **Parallel workers:** 2 workers launched PostgreSQL already uses parallel execution with 2 workers, providing some concurrency even with synchronous I/O. This is important context for interpreting your async I/O results. ## Testing asynchronous I/O performance Now enable async I/O and compare performance using the same cold-cache methodology. Exit the Postgres session with `\q` and clear the cache: ```command sudo /usr/local/bin/clear-pg-cache.sh ``` Once PostgreSQL restarts, switch back to your first terminal and connect to the database: ```command sudo -u postgres psql aio_test ``` Enable async I/O and run the test query: ```sql SET effective_io_concurrency = 32; ``` ```sql \timing on ``` ```sql SELECT COUNT(*), AVG(value) FROM io_benchmark WHERE value > 500; ``` ```text [output] count | avg ---------+---------------------- 4999932 | 750.1352998950516530 (1 row) Time: 2220.764 ms (00:02.221) ``` The execution time dropped from 2913ms to 2221ms, a 24% improvement. This is a real gain, but modest compared to the 2-3x improvements seen in cloud environments with higher-latency network storage. Your fast local SSD responds in microseconds, and parallel workers already provide some concurrency, leaving less room for async I/O to improve performance. ## Testing the io_uring method Earlier you confirmed that your system supports `io_uring`, which can provide even better performance than the worker method by interacting directly with the kernel. To use `io_uring`, you need to change the `io_method` setting, which requires restarting PostgreSQL. Exit your PostgreSQL session with `\q`, then edit the configuration file: ```command sudo nano /etc/postgresql/18/main/postgresql.conf ``` Locate the existing `io_method` line, which is usually commented out. Uncomment this line and change it to: ```ini [label postgresql.conf] ... #io_combine_limit = 128kB # usually 1-128 blocks (depends on OS) [highlight] io_method = io_uring [/highlight] #io_max_concurrency = -1 # Max number of IOs that one process ... ``` Save the file and restart PostgreSQL: ```command sudo systemctl restart postgresql@18-main ``` Verify the change took effect: ```command sudo -u postgres psql -c "SHOW io_method;" ``` ```text [output] io_method ----------- io_uring (1 row) ``` Now test performance with io_uring. Clear the cache: ```command sudo /usr/local/bin/clear-pg-cache.sh ``` Connect to the database and run the same test: ```command sudo -u postgres psql aio_test ``` ```sql SET effective_io_concurrency = 32; ``` ```sql \timing on ``` ```sql SELECT COUNT(*), AVG(value) FROM io_benchmark WHERE value > 500; ``` Record this time and compare it with both your synchronous baseline (2913ms) and the worker method (2221ms). The `io_uring` method typically shows similar or slightly better performance than worker, though the difference may be small on local SSDs. The real advantage of io_uring is lower CPU overhead since it doesn't need separate worker processes. ## Making asynchronous I/O settings permanent To keep your asynchronous I/O settings permanently, you would save them in PostgreSQL’s main configuration file instead of changing them temporarily during a session. After finishing your tests, you would close `psql` with `\q` and find where PostgreSQL’s configuration file is stored. It’s usually something like: ```text /etc/postgresql/18/main/postgresql.conf ``` That’s the file where permanent settings go. Inside it, you could have lines like this: ```ini [label postgresql.conf] [highlight] effective_io_concurrency = 32 # your optimal value from testing [/highlight] maintenance_io_concurrency = 100 # higher for VACUUM, CREATE INDEX .... # Asynchronous I/O configuration io_method = 'worker' # or 'io_uring' if supported io_workers = 3 # number of I/O worker processes ``` The `maintenance_io_concurrency` line controls how many operations can run at once during maintenance tasks (like VACUUM or CREATE INDEX). It’s often set higher since those tasks usually happen when the system isn’t busy. After saving changes, PostgreSQL would need a restart for them to take effect. You could then check the settings to confirm they were applied. ## When asynchronous I/O doesn't help Understanding scenarios where async I/O provides minimal benefit prevents unrealistic expectations and guides configuration decisions. ### High buffer cache hit rates PostgreSQL's shared buffer cache keeps frequently accessed data in memory. When queries find required data already cached, no disk I/O occurs, leaving async I/O with nothing to optimize. Check your cache hit rate: ```sql SELECT sum(heap_blks_read) as disk_reads, sum(heap_blks_hit) as cache_hits, round(sum(heap_blks_hit)::numeric / nullif(sum(heap_blks_hit) + sum(heap_blks_read), 0) * 100, 2) as hit_ratio FROM pg_statio_user_tables; ``` ```text [output] disk_reads | cache_hits | hit_ratio ------------+------------+----------- 8234 | 12456789 | 99.93 ``` A hit ratio above 99% indicates most data access happens from memory. Async I/O won't significantly improve performance here because the workload doesn't generate enough disk I/O to benefit from parallelization. ### CPU-bound queries Complex analytical queries spending most execution time on sorting, aggregation, or joins won't improve from async I/O if the CPU represents the bottleneck rather than I/O. If the plan shows minimal buffer reads but high execution times in sort and aggregate nodes, the query is CPU-bound. Async I/O optimization won't help since the database isn't waiting for storage. ### Storage already saturated Async I/O can only improve performance up to the storage system's physical limits. If your storage is already at 100% utilization with high service times, enabling async I/O won't increase throughput—it will simply reach the limit faster. Monitor storage utilization: ```command iostat -x 2 ``` ```text [output] Device r/s w/s rkB/s wkB/s await aqu-sz %util sda 1234.5 89.2 123456.7 8912.3 8.45 12.45 100.0 ``` If `%util` consistently shows 100% with high `await` (service time), you've hit a hardware bottleneck that software optimizations can't overcome. ## Troubleshooting asynchronous I/O issues Despite careful configuration, you may encounter situations where async I/O doesn't work as expected. ### Verifying io_uring support If you configured `io_method = 'io_uring'` but PostgreSQL falls back to synchronous I/O, your system may lack necessary support. Check kernel version: ```command uname -r ``` The `io_uring` interface requires Linux kernel 5.1 or newer. Verify kernel support: ```command grep io_uring /proc/kallsyms | head -3 ``` If this returns no results, your kernel doesn't support `io_uring`. Either upgrade your kernel or use `io_method = 'worker'` instead. Verify PostgreSQL was compiled with liburing: ```command ldd /usr/lib/postgresql/18/bin/postgres | grep uring ``` ```text [output] liburing.so.2 => /lib/x86_64-linux-gnu/liburing.so.2 (0x00007f8b9c3d0000) ``` If this shows no liburing dependency, your binary wasn't compiled with `--with-liburing`. Use the worker method or rebuild PostgreSQL with liburing support. ### Memory pressure from high concurrency Async I/O consumes kernel memory for I/O buffers and tracking structures. Systems with limited RAM may experience memory pressure when running high `io_max_concurrency` values. Monitor kernel memory usage: ```command cat /proc/meminfo | grep -E 'MemTotal|MemAvailable' ``` ```text [output] MemTotal: 3785876 kB MemAvailable: 156789 kB ``` If available memory drops dangerously low after enabling async I/O, reduce concurrency settings or add system RAM. PostgreSQL shouldn't compromise overall system stability in pursuit of I/O performance. ### Resolving worker process limits When using `io_method = 'worker'`, PostgreSQL spawns dedicated I/O worker processes at startup. If the server fails to start or logs show worker initialization failures, you may have hit system process limits. Check the process limit: ```command ulimit -u ``` ```text [output] 4096 ``` PostgreSQL needs to spawn `io_workers` processes in addition to regular backend processes. Increase system limits in `/etc/security/limits.conf` if necessary: ```ini postgres soft nproc 8192 postgres hard nproc 8192 ``` ## Final thoughts PostgreSQL 18’s asynchronous I/O is a major step forward in how the database handles disk reads. By allowing multiple read operations to run at once, it helps you reduce wait times and improve performance, especially if you use cloud storage or work with higher-latency systems. You might not see large improvements if your system already uses fast SSDs, keeps most data in memory, or runs CPU-heavy queries. But if your database often reads from disk or handles large, read-intensive workloads, asynchronous I/O can give you noticeable performance gains with very little setup. PostgreSQL 18 gives you more control over how data is read, making it easier to tune performance for your specific workload. For more details, check the [official PostgreSQL 18 documentation](https://www.postgresql.org/docs/18/).