In PostgreSQL 18, what do io_method and io_workers control, and how do you choose between the worker, io_uring and sync methods?
answer
- new asynchronous I/O subsystem
- reads queued ahead of need
- restart for one, reload for the other
- a view of handles in flight
basics
~10 sPostgreSQL 18 adds asynchronous reads: io_method picks how they run (worker, the default; io_uring on Linux builds with liburing; sync), and io_workers sets how many I/O worker processes serve the worker method (default 3).
solid answer
~40 sPostgreSQL 18's asynchronous I/O lets a backend queue several reads ahead of need, which mainly helps sequential scans, bitmap heap scans and vacuum. `io_method` chooses the mechanism and needs a restart: `worker` (the default) hands reads to dedicated I/O worker processes and works on every platform; `io_uring` submits them through the Linux kernel interface and needs a build `--with-liburing`; `sync` performs the same reads synchronously, which is useful to rule AIO out when diagnosing. `io_workers` (default 3, reload) sizes the worker pool for the `worker` method. How many reads each session keeps in flight is steered by `effective_io_concurrency` and `maintenance_io_concurrency`, both raised to 16 in 18. `pg_aios` shows the handles in use right now.
code
sql · 4 linesSHOW io_method;
SHOW io_workers;
SELECT pid, state, operation, length, target_desc
FROM pg_aios;go deeper
Know that PostgreSQL 18 adds asynchronous reads, that io_method defaults to worker, and that pg_aios lists I/O in flight.
Explain the three methods, which settings need a restart or a reload, and why effective_io_concurrency now starts at 16.
Show you would keep the worker method, size io_workers from evidence, use sync to rule AIO out, and choose io_uring only with build and platform support.
Weigh adopting io_uring fleet-wide against build, platform and support constraints, and plan the restart-gated comparison as a measured rollout.
## What changed in 18 Before PostgreSQL 18, a backend that needed a data page it did not have in `shared_buffers` issued a read and waited for it; prefetching relied on advising the kernel ahead of time where that was supported. PostgreSQL 18 adds an **asynchronous I/O (AIO) subsystem**: a backend can queue several read requests and keep working while they complete. The release notes name **sequential scans, bitmap heap scans and vacuum** as the beneficiaries. It is not a general asynchronous write path: checkpoint and WAL writing are tuned with their own settings, covered with WAL and durability. ## The settings | setting | default | context | what it does | |---|---|---|---| | `io_method` | `worker` | restart | how asynchronous I/O is executed: `worker`, `io_uring` or `sync` | | `io_workers` | `3` (1 to 32) | reload | number of I/O worker processes; only used when `io_method = worker` | | `effective_io_concurrency` | `16` | user | how many I/Os a session tries to keep in flight; `0` disables issuing them | | `maintenance_io_concurrency` | `16` | user | the same for maintenance work done on behalf of many sessions | | `io_combine_limit` | 128kB | user | largest single combined read or write, clamped by `io_max_combine_limit` (restart) | `effective_io_concurrency` can also be overridden per tablespace with `ALTER TABLESPACE ... SET (effective_io_concurrency = ...)`, which is useful when one tablespace sits on higher-latency storage. Both concurrency defaults rose to 16 in 18 because the old ones undersold modern hardware. ## Choosing a method - **`worker`**: the default and the portable choice. Backends hand reads to a pool of `io worker` processes, which then appear as their own backend type in `pg_stat_io`. If the workers are constantly busy and reads queue behind them, raise `io_workers` and reload. - **`io_uring`**: available only on Linux and only when PostgreSQL was built with `--with-liburing` (or `-Dliburing` with Meson). Each backend submits its reads straight to the kernel, with no hand-off to another process. Choose it when the platform and build support it and measurements show the worker hand-off is the bottleneck. - **`sync`**: executes the asynchronous-eligible reads synchronously. It is the switch to flip when you suspect AIO itself, comparing behaviour with and without it, or when a platform issue makes the other methods unusable. Changing `io_method` takes a restart, so plan the comparison as a maintenance-window change, not a live experiment. ## Seeing it work `pg_aios` lists every AIO handle **currently in use**, one row per handle, with columns such as `pid`, `state` (`HANDED_OUT`, `DEFINED`, `STAGED`, `SUBMITTED`, `COMPLETED_IO`, ...), `operation` (`readv`, `writev`), `length`, `target_desc` and `result`. It is a point-in-time view, readable by superusers and members of `pg_read_all_stats`, mainly meant for developers but useful when tuning. Cumulative counts of reads by backend type and context live in `pg_stat_io` (part of the statistics views). ## Where AIO will not help Asynchronous reads only pay off when a query is waiting on storage. They do little when: - the data is already in `shared_buffers` or the kernel page cache, so no read is issued at all; - the query is CPU-bound, for example a large hash aggregation over cached data; - the bottleneck is commit latency, which depends on WAL flushing rather than reads; - the plan touches a handful of pages, as most single-row lookups do. That is why AIO is a storage-latency setting to evaluate with measurements (`EXPLAIN (ANALYZE)` timings and `pg_stat_io` read counts before and after), not a memory setting to raise by habit. It does not change how much memory `shared_buffers` or `work_mem` should get. ## Interview pitfalls 1. Calling `io_uring` the default: it is opt-in and build-dependent. 2. Expecting faster commits or checkpoints: 18's AIO is about reads. 3. Editing `io_method` and reloading: the old method stays until a restart. 4. Assuming the old concurrency defaults: in 18 both concurrency settings start at 16. On a logistics server whose month-end reports scan large `shipments` partitions, the realistic first steps are to leave `io_method = worker`, watch whether the I/O workers keep up, and adjust `io_workers` or the per-tablespace `effective_io_concurrency` before considering `io_uring`.
- Does PostgreSQL 18's io_method make checkpoints or WAL flushes asynchronous?No. The 18 release applies asynchronous I/O to reads, notably sequential scans, bitmap heap scans and vacuum; it is not a general asynchronous write path. Commit latency and checkpoint smoothing are still governed by the WAL and checkpoint settings, so switching `io_method` is not a fix for slow commits.
- How does effective_io_concurrency relate to asynchronous I/O in PostgreSQL 18?It sets how many I/O operations a single session tries to have in flight, default 16 in 18, with `0` disabling them and `maintenance_io_concurrency` playing the same role for maintenance work. It can be overridden per tablespace. AIO also lets values above zero work on platforms without `fadvise()` support, where prefetching was not available before.
- io_uring is the default io_method on Linux in PostgreSQL 18.
- Raising io_workers takes effect only after a full server restart.
- Asynchronous I/O in 18 makes WAL flushes and checkpoints asynchronous.
- pg_aios shows cumulative I/O totals per backend type since the last reset.
- Changing io_method with ALTER SYSTEM and pg_reload_conf() switches it immediately.
Assumes PostgreSQL 18; io_method, io_workers and pg_aios are new in 18, and effective_io_concurrency and maintenance_io_concurrency now default to 16.