PostgreSQL 18 Asynchronous I/O (AIO): Why Cold Cache Sequential Scans Are 3× Faster and Benchmark Results

When tuning database performance, there always comes a moment where you hit an I/O bottleneck. You've bumped up shared_buffers, tweaked effective_io_concurrency, switched to SSDs — and yet full scans on large tables are still slow. Honestly, I've been there myself, at that wall, thinking "maybe that's just how PostgreSQL works."

PostgreSQL 18 (released in 2025) tears down that wall in a fairly fundamental way. By officially introducing an Asynchronous I/O (AIO) subsystem, it enables noticeable performance gains in I/O-bound environments simply by upgrading. In this post, we'll walk through how AIO works internally, what actual measurements look like, and in what situations you can expect meaningful results.

The hands-on examples in this post are aimed at backend developers who operate or have operated PostgreSQL directly. We assume you have superuser privileges to edit postgresql.conf directly or use ALTER SYSTEM.

Core Concepts

How PostgreSQL Handled I/O Before

Through PG17, PostgreSQL processed disk I/O synchronously. When a backend process sent a request to the OS to read a block, that process would sit idle and wait until the data arrived — doing nothing in the meantime.

Synchronous I/O: A mode where the process that issued the read request blocks and waits until the response arrives. CPU resources are wasted, and I/O bandwidth is not fully utilized.

On low-latency storage like local NVMe SSDs, this isn't a big problem. But on network block storage like AWS EBS, where each request can take several milliseconds, those wait times accumulate until most of a query's execution time is just "waiting." For operations like sequential scans that read millions of blocks, this becomes a serious issue.

A natural question arises: "Can't we just prefetch multiple blocks ahead of time?" The effective_io_concurrency and prefetch-related settings in PG17 were exactly that attempt — but they were merely hints to the OS, and the underlying structure remained synchronous.

PG18 AIO's Three Modes

PG18 lets you choose an I/O processing method with a single io_method parameter.

The key point is that worker mode is now the default. The moment you upgrade to PG18, asynchronous I/O is enabled without touching any settings.

It's worth understanding how worker mode works internally. Instead of the backend process reading from disk directly, it places I/O requests into the queue of a dedicated background worker process. While the worker handles the actual reads, the backend can keep queuing up requests for the next blocks. When a result is ready, the worker signals the backend, which then fetches the data. The structure shifts from "wait for one at a time" to "fire off multiple requests simultaneously and process whichever is ready first."

io_uring goes one step further. Introduced in Linux kernel 5.1, io_uring places a shared ring buffer between userspace and the kernel, dramatically reducing the number of syscalls. It can also eliminate context-switching overhead between worker processes, making it theoretically more efficient. However, the Linux-only constraint means it's not available on macOS or Windows, or in some cloud-managed services (like RDS).

io_uring: A high-performance asynchronous I/O interface introduced in Linux kernel 5.1 (2019). It minimizes syscall count via a shared ring buffer between userspace and the kernel. Accessible via the liburing library.

In summary: for cloud or cross-platform environments, start with worker mode; for Linux on-premises or self-managed EC2, consider testing io_uring.

What AIO Currently Covers

There are currently three types of operations that benefit from PG18 AIO:

• Sequential Scan: Full scans of large tables
• Bitmap Heap Scan: Random heap block access following index conditions
• VACUUM: Maintenance operations that traverse an entire table

Conversely, write I/O and WAL operations are still synchronous in PG18. Index Scan and Index-Only Scan are also outside the current AIO scope. This means the impact is limited for write-heavy OLTP workloads. The community is actively discussing these areas for future improvement.

Key Parameters

-- Check PG17 → PG18 default value changes
SHOW effective_io_concurrency;  -- PG17: 1, PG18: 16
 
-- Check current io_method
SHOW io_method;  -- default: worker
 
-- Query in-progress asynchronous I/O requests (new in PG18)
SELECT * FROM pg_aios;

The jump in effective_io_concurrency from 1 to 16 is also worth noting. Previously, even if you knew "raising this value enables prefetching," you had to set it manually. Now the default behavior itself has changed.

Practical Application

Example 1: Measuring Large Table Scans in a Cold Cache Environment

This is the scenario where you'll see the most dramatic effect. It's a sequential scan on a large table with the OS page cache cleared — in production, this is similar to when a nightly batch job runs for the first time, or immediately after a DB server restart.

There's one important thing to know before changing settings. io_method is a postmaster context parameter. It requires modifying postgresql.conf or running ALTER SYSTEM, followed by a PostgreSQL restart — not a session-level SET. pg_reload_conf() alone won't apply it.

-- Step 1: Change io_method (restart required)
ALTER SYSTEM SET io_method = 'io_uring';   -- Linux 5.1+ environments
-- ALTER SYSTEM SET io_method = 'worker';  -- Cross-platform or RDS
 
-- Confirm after PostgreSQL restart
-- (On direct Linux install: systemctl restart postgresql)
SHOW io_method;
 
-- Step 2: Adjust concurrent I/O count (reload without restart)
ALTER SYSTEM SET effective_io_concurrency = 200;  -- For NVMe SSD
-- ALTER SYSTEM SET effective_io_concurrency = 64; -- For cloud EBS
ALTER SYSTEM SET io_max_concurrency = 32;
SELECT pg_reload_conf();
 
-- Step 3: Create a cold cache state
-- Clear OS cache (requires root; use only in test environments):
-- sudo sh -c 'echo 3 > /proc/sys/vm/drop_caches'
--
-- Check if pg_prewarm extension is installed:
SELECT * FROM pg_extension WHERE extname = 'pg_prewarm';
 
-- Step 4: Check execution plan and timing
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT COUNT(*) FROM large_table;

Cache clear warning: echo 3 > /proc/sys/vm/drop_caches clears all OS page cache. It requires root privileges, and running it on a live system affects not just PostgreSQL but the performance of other processes as well. Use only in dedicated test environments.

Here is a summary of measured results. Baseline: 200 million row table, SELECT COUNT(*) after clearing OS cache.

There was one result that surprised me. In some high-bandwidth sequential scan cases, worker has been reported to match or even outperform io_uring. While io_uring is theoretically more efficient, actual results can vary by workload characteristics. This is exactly why directly comparing the two modes yourself matters.

That said, in a warm cache state, the difference between the three modes is negligible. If your environment has a shared_buffers hit rate of 99% or higher, changing these settings won't produce noticeable results.

Example 2: Real-World sync vs. worker Measurements on AWS RDS

Cloud environments tend to show a more pronounced effect from asynchronous I/O. Network block storage like EBS has higher per-request latency than local NVMe, which means the core value of AIO — "while waiting, fire off the next request" — is far better utilized.

In Classmethod's real-world RDS PostgreSQL 18 measurements, worker mode recorded approximately 26% better sequential scan throughput compared to sync. As a single number it may not sound dramatic, but for jobs like nightly reports or data migrations that involve repeated large-scale scans, the cumulative effect is significant.

-- Check available modes in RDS environment
-- (RDS does not support io_uring; only worker is available)
SELECT name, setting, context
FROM pg_settings
WHERE name IN ('io_method', 'effective_io_concurrency', 'io_max_concurrency');
 
-- Monitor AIO activity
SELECT pid, mode, offset, nbytes, already_done
FROM pg_aios;

On RDS, io_method and effective_io_concurrency can be configured via Parameter Groups. Whether changes take effect immediately varies by parameter type, so check the "Requires Restart" flag in the console first.

Example 3: Optimizing Nightly Batch Analytical Queries

Nightly batch jobs are the best fit for AIO, for several reasons. Batch start time is almost always cold cache; multiple queries run sequentially, each performing sequential scans, so async prefetch effects compound continuously. And since there's virtually no write activity, the limitation of writes not being covered by AIO doesn't apply.

-- Check current I/O settings before batch starts
SELECT name, setting, unit, context
FROM pg_settings
WHERE name IN (
  'io_method',
  'effective_io_concurrency',
  'io_max_concurrency'
);
 
-- Example large aggregation query
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT
  date_trunc('day', created_at) AS day,
  COUNT(*)                       AS order_count,
  SUM(total_amount)              AS revenue
FROM orders
WHERE created_at >= '2024-01-01'
GROUP BY 1
ORDER BY 1;
 
-- Check AIO activity during query execution (from a separate session)
SELECT pid, mode, nbytes, already_done
FROM pg_aios;

In EXPLAIN (ANALYZE, BUFFERS) output, the X in Buffers: shared read=X won't change before and after applying AIO. AIO changes not how much you read but how fast you read it — so the difference shows up in Execution Time.

Pros and Cons Analysis

Advantages

Tail latency (P99 latency): The 99th percentile response time across all requests. Even if the average is fast, the slowest 1% of requests can significantly impact user experience, making it important to measure separately.

Disadvantages and Caveats

In practice, the most common pitfalls are the first and fourth. Disappointment from setting high expectations in a hot cache environment, and discovering too late that changing io_method requires a restart — these happen quite often.

Most Common Mistakes in Practice

1. Setting unrealistic expectations in hot cache environments: If shared_buffers is sufficiently large and the same queries run repeatedly in an OLTP service, changing AIO settings will produce almost no noticeable difference. Check the heap_blks_hit / heap_blks_read ratio in pg_stat_user_tables first to gauge whether AIO will actually help.

2. Assuming io_uring is always the better choice: There are cases where worker mode has matched or outperformed io_uring in high-bandwidth sequential scans. It's better to compare the two modes directly for your specific workload.

3. Setting effective_io_concurrency too high: Practically recommended ranges are 64–128 for cloud EBS and around 200 for local NVMe. Increasing it indefinitely doesn't produce proportional gains — tune it alongside io_max_concurrency to match your workload characteristics.

Closing Thoughts

PostgreSQL 18's asynchronous I/O is one of those rare free performance wins that works without any configuration changes — just upgrade — and delivers immediate results especially for workloads with heavy cold cache reads, such as cloud environments or nightly batch jobs.

Three steps you can take right now:

1. Measure cache hit rate and I/O wait time: Check the heap_blks_hit / heap_blks_read ratio in pg_stat_user_tables, and look at the proportion of wait_event_type = 'IO' in pg_stat_activity. This will help you determine whether your environment is one where AIO will have an impact.

2. Run mode comparisons in staging: Change io_method from worker → io_uring (each change requires a restart) and compare execution times for the same queries. Measure with EXPLAIN (ANALYZE, BUFFERS) in a cold state after clearing the OS page cache, and verify that async I/O requests are actually being generated by checking the pg_aios view.

3. Apply to production and monitor: Once validated, set io_method, effective_io_concurrency, and io_max_concurrency in postgresql.conf. If you're using a connection pooler like PgBouncer, check pool size and configuration compatibility at the same time.

References

• PostgreSQL 18 Released! | PostgreSQL Official
• Waiting for Postgres 18: Accelerating Disk Reads with Async I/O | pganalyze
• PostgreSQL 18: Better I/O performance with AIO | CYBERTEC
• PostgreSQL 18 and beyond: From AIO to Direct IO? | CYBERTEC
• PostgreSQL 18 Asynchronous I/O: A Complete Guide | Better Stack
• I tested PostgreSQL 18's async I/O performance on RDS | Classmethod DevelopersIO
• Async I/O in Postgres 18: 3× Faster Seq Scans with io_uring | Medium
• Exploring why PostgreSQL 18 put async I/O in your database | Aiven
• PostgreSQL 18 Async I/O in Production: Real-World Benchmarks | PostgresSQL HTX
• Benchmarking Postgres 17 vs 18 | PlanetScale
• Tuning PostgreSQL 18's AIO for Performance | ChatDBA
• PostgreSQL 18 Asynchronous I/O | Neon Documentation
• Get Excited About Postgres 18 | Crunchy Data Blog