PostgreSQL 19 asynchronous I/O - worker autoscaling and read-ahead

Asynchronous I/O arrived in PostgreSQL 18: instead of a backend issuing one read and blocking on it, scans submit a stream of prefetch requests that the kernel or a pool of I/O workers services in parallel. 18 shipped it with a fixed pool - io_workers workers, no more, no fewer, whatever the load. 19 makes the pool elastic, improves how large requests are scheduled, and adds the EXPLAIN output that lets you see the machinery.

The autoscaling worker pool

io_method still defaults to worker, but the fixed io_workers count is gone. Four new GUCs govern the pool:

The shape of the trade-off is the classic autoscaler: io_min_workers guarantees a warm pool so the first prefetch burst does not wait on process launch; io_max_workers caps how much process and memory overhead a busy cluster spends on I/O; io_worker_launch_interval stops a spiky workload from thrashing the process table. On a host that shared its io_workers between queries and vacuum, the ceiling now decides how hard vacuum’s streaming reads can push in parallel.

The release notes also list improved read-ahead scheduling for large requests - the scheduler that decides how far ahead of the scan prefetch runs. The beta documentation does not expose a knob for it; it is a correctness-of-throughput change you observe through EXPLAIN (ANALYZE, IO) rather than tune.

Seeing it: the EXPLAIN IO option

EXPLAIN gains an IO option that reports, per scan node that provides the information: the prefetch queue’s average and maximum distance and its capacity, plus issued I/O requests - total count, average request size, number of I/O waits, and the average number of concurrent requests.

EXPLAIN (ANALYZE, IO, BUFFERS) SELECT count(*) FROM events WHERE ts > now() - interval '7 days';

Read it with BUFFERS: read blocks tell you what the scan asked for, the IO block tells you how far the prefetcher ran ahead of the consumption point and how often the scan had to wait. auto_explain gains a matching auto_explain.log_io option that adds the same reporting to logged plans, and auto_explain.log_extension_options lets modules such as pg_plan_advice attach their own EXPLAIN options to auto_explain output.

What changed, and in which release

COPY FROM parsing belongs on this page rather than the SQL one because the speedup is in the input path: text and CSV input now use SIMD CPU instructions, which matters most on the bulk loads the I/O subsystem also serves.

Where it breaks

• Assuming io_workers still exists - the GUC is replaced by the min/max pair; a tuned postgresql.conf line for it is an unknown-setting error on 19, not a silent miss.
• Judging the pool by process count - idle workers retire after io_worker_idle_timeout, so a quiet ps listing is expected, not a sign the subsystem is off.
• Reading IO without ANALYZE - the option reports issued requests, which exist only when the plan actually runs.
• Beta caveat - GUC names and defaults are from the beta documentation.

What this is not for

• Diagnosing non-I/O waits - IO reports prefetch and request behaviour; lock waits, client waits and CPU time need pg_stat_activity and pg_stat_io, covered in monitoring.
• Replacing pg_stat_io capacity planning - the per-backend EXPLAIN view is a microscope for one query; the cumulative view is the long-term picture.