Alexander Fridriksson
|RSS FeedAlexander does product marketing for databases at Aiven. Throughout his career he's worked across marketing, sales, analytics, data engineering and more.
Subscribe for the latest news and insights on open source, Aiven offerings, and more.
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.
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. |
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.
Loading code...
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.
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.
Loading code...
We also saw previously that the default effective_io_concurrency is 16, but we can increase or decrease it if needed.
Loading code...
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.
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.
Loading code...
For our tests, let’s compare the new asynchronous worker method against the old synchronous sync method.
Loading code...
For our comparison, we’ll need to switch sync and disable effective_io_concurrency by setting it to 0.
Loading code...
You should expect a solid performance improvement, with the most significant improvements coming from high-latency and read-heavy workloads.
PostgreSQL 18 introduces the pg_aios system view to provide insights into asynchronous I/O operations and requests currently in progress:
Loading code...
You can also use pg_stat_activity to view information about background worker processes by searching for the io worker backend type.
Loading code...
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.
avn service create -t pg cloud do-nyc --plan pg:free-1-1gb 'async-crab' -c 'pg_version=18'-- 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;-- Making it synchronous
ALTER SYSTEM SET io_method = 'sync';
-- You can always making asynchronous again
ALTER SYSTEM SET io_method = 'worker';-- Check if restart is required
SELECT name, setting, pending_restart
FROM pg_settings
WHERE name = 'io_method';-- Make it go burr with moar concurrency
ALTER SYSTEM SET effective_io_concurrency = 32;-- 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 MBCREATE EXTENSION IF NOT EXISTS pg_prewarm;
-- Stats and cache begone
SELECT pg_stat_reset(),
pg_prewarm('async_crab_rave'::regclass, 'buffer', 'main', NULL, NULL);-- How many times is it over 9000?
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT COUNT(*)
FROM async_crab_rave
WHERE num_crabs > 9000;
ALTER SYSTEM SET io_method = 'sync';
ALTER SYSTEM SET effective_io_concurrency = 0;-- Show me the AIO
SELECT * FROM pg_aios;SELECT *
FROM pg_stat_activity
WHERE backend_type = 'io worker';