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

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.

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'

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;

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';

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';

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;

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

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);

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;

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;

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;

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';

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.