Nov 11, 2025

Exploring why PostgreSQL 18 put asynchronous I/O in your database

Alexander Fridriksson
|RSS Feed
Alexander does product marketing for databases at Aiven. Throughout his career he's worked across marketing, sales, analytics, data engineering and more.

PostgreSQL®

For years, PostgreSQL relied on synchronous I/O, meaning that when the database needed data from disk, each read operation was a blocking system call. The database process would therefore pause and wait for the data retrieval before moving to the next task.

Synchronous I/O works well for local storage, but our database needs have changed drastically since then, resulting in this architecture creating significant bottlenecks when storage has higher latency. That is common with network-attached cloud storage, such as Amazon EBS.

Asynchronous I/O (AIO) fundamentally changes this by allowing Postgres to submit multiple read requests concurrently, enabling I/O and computation to overlap. This change allows the database to control read-ahead decisions based on query plans, resulting in more predictable and higher-throughput I/O behavior.

The three I/O methods in PostgreSQL 18

PostgreSQL 18 introduces the io_method configuration parameter to control how AIO works.

I/O Method	Use case	Explaination
`worker`	Asynchronous I/O (default)	A background worker processes I/O requests, allowing the main process to continue execution. This is the default setting in PostgreSQL 18.
`sync`	Synchronous I/O	If you want to keep the same behavior as PostgreSQL 17 with blocking reads, you can set it to `sync` instead.
`io_uring`	Specific to Linux	Creates a shared ring buffer that minimizes system call overhead. While this usually gives the best performance, it's important to keep in mind that you might be opening yourself up to security risks by using `io_uring`. To use it, you would also need your PostgreSQL to be built with the `--with-liburing` flag.

Configuring and testing asynchronous I/O

AIO benefits read-heavy workloads, such as sequential scans of tables exceeding available memory or large index range scans. However, write operations are currently only synchronous.

As always, you can create a free Aiven for PostgreSQL instance with the Aiven CLI to try things out for yourself.

avn service create -t pg cloud do-nyc --plan pg:free-1-1gb 'async-crab' -c 'pg_version=18'
Loading code...

Check the configuration

-- Checking your AIO settings and description
SELECT name, setting, short_desc
FROM pg_settings
WHERE name IN (
               'io_method',
               'io_workers',
               'io_max_concurrency',
               'effective_io_concurrency'
              )
ORDER BY name;
Loading code...

name	setting	short_desc
effective_io_concurrency	16	Number of simultaneous requests that can be handled efficiently by the disk subsystem.
io_max_concurrency	64	Max number of IOs that one process can execute simultaneously.
io_method	worker	Selects the method for executing asynchronous I/O.
io_workers	3	Number of IO worker processes, for `io_method=worker`.

As we can confirm from the query output, the io_method is set to the default worker method, so we don’t need to do anything to make it asynchronous. But if we wanted it to behave like PostgreSQL 17, we could change it to be synchronous.

-- Making it synchronous
ALTER SYSTEM SET io_method = 'sync';

-- You can always making asynchronous again
ALTER SYSTEM SET io_method = 'worker';
Loading code...

One thing to keep in mind is that after modifying the io_method, you might have to restart PostgreSQL if the result of the pending_restart is true.

-- Check if restart is required
SELECT name, setting, pending_restart
FROM pg_settings
WHERE name = 'io_method';
Loading code...

We also saw previously that the default effective_io_concurrency is 16, but we can increase or decrease it if needed.

-- Make it go burr with moar concurrency
ALTER SYSTEM SET effective_io_concurrency = 32;
Loading code...

Creating data for our test

With AIO you could expect to see a big difference when data is read directly from disk, but not necessarily when reading from the buffer cache, as the process might never be waiting for high-latency I/O.

Therefore, we need data that is larger than our shared_buffers for a proper test.

-- Create test table
CREATE TABLE async_crab_rave (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    question TEXT,
    num_crabs INTEGER,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Insert large amount of data
INSERT INTO async_crab_rave (question, num_crabs)
SELECT
    'How many crabs attended the async crab rave?',
    (random() * 1000000)::INTEGER
FROM generate_series(1, 999999);

-- Verify that the size is larger than shared_buffers
SHOW shared_buffers; -- Result: 128MB

SELECT pg_size_pretty(pg_total_relation_size('async_crab_rave'));
-- Result: 143 MB
Loading code...

The final thing we need to do for a proper test is to clear the stats and buffer cache for our async_crab_rave table. For convenience, we can use the pg_prewarm extension to help us with this.

CREATE EXTENSION IF NOT EXISTS pg_prewarm;

-- Stats and cache begone
SELECT pg_stat_reset(), 
pg_prewarm('async_crab_rave'::regclass, 'buffer', 'main', NULL, NULL);
Loading code...

Running the tests

For our tests, let’s compare the new asynchronous worker method against the old synchronous sync method.

-- How many times is it over 9000?
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT COUNT(*)
FROM async_crab_rave
WHERE num_crabs > 9000;
Loading code...

For our comparison, we’ll need to switch sync and disable effective_io_concurrency by setting it to 0.

ALTER SYSTEM SET io_method = 'sync';
ALTER SYSTEM SET effective_io_concurrency = 0;
Loading code...

You should expect a solid performance improvement, with the most significant improvements coming from high-latency and read-heavy workloads.

Monitoring asynchronous activity

PostgreSQL 18 introduces the pg_aios system view to provide insights into asynchronous I/O operations and requests currently in progress:

-- Show me the AIO
SELECT * FROM pg_aios;
Loading code...

You can also use pg_stat_activity to view information about background worker processes by searching for the io worker backend type.

SELECT *
FROM pg_stat_activity
WHERE backend_type = 'io worker';
Loading code...

Summary

PostgreSQL 18 introduced Asynchronous I/O (AIO) primarily to improve database performance for read-heavy workloads, particularly in environments with higher storage latency like cloud platforms. This feature allows the database to initiate multiple read operations concurrently without waiting for each one to complete individually, overcoming the bottlenecks of previous synchronous I/O models.

Postgres 18 comes with a lot more exciting features, and you can leverage these exciting new features and explore what else Postgres 18 has to offer with Aiven today.

Table of contents

The three I/O methods in PostgreSQL 18
Configuring and testing asynchronous I/O
Check the configuration
Creating data for our test
Running the tests
Monitoring asynchronous activity
Summary

Stay updated with Aiven

Subscribe for the latest news and insights on open source, Aiven offerings, and more.

Subscribe to RSS