I/O statistics and in-flight async I/O
pg_io_statsIdentify which PostgreSQL subsystem performs I/O and through which path, using cumulative per-backend-type stats and in-flight async I/O handles to pinpoint stalled or heavy I/O on PostgreSQL 16+.
Instructions
I/O observability: cumulative per-backend-type I/O from pg_stat_io (PostgreSQL 16+), plus in-flight asynchronous I/O handles from pg_aios (PostgreSQL 18+). This is the layer underneath pg_top_queries and pg_health -- it says WHICH subsystem is doing the I/O (client backends vs autovacuum vs checkpointer vs walwriter) and through which path, which a per-query or per-table view cannot.
io: one row per (
backend_type,io_object,io_context) combination. Countersreads/writes/extends/writebacks/hits/evictions/reuses/fsyncsare bigints returned as decimal strings;read_time_ms/write_time_ms/writeback_time_ms/extend_time_ms/fsync_time_msare float8 milliseconds. A timing of 0 next to a non-zero op count meanstrack_io_timingis off, NOT that the I/O was free -- turn it on to get real numbers. A NULL counter means the operation is not possible for that combination, which is different from 0.io[].read_bytes / write_bytes / extend_bytes: a normalized byte figure that means the same thing on every supported server. On PG16-17 it is computed as
op_bytes * <op count>; on PG18op_byteswas removed and the server reports bytes directly. The top-levelbyte_accountingfield says which source produced the numbers.io[].stats_reset: these are CUMULATIVE counters, so a row is only interpretable next to its reset point. Reported per row because that is how the view reports it;
pg_stat_reset_shared('io')resets them together in practice, but this tool does not assert that.Rows whose counters are all zero are omitted by default (
pg_stat_iois mostly zeros on a quiet system, and the noise buries the handful of rows that matter). PassincludeZeroRows: truefor the full matrix.in_flight + io_method: PostgreSQL 18+ ONLY, and both keys are ABSENT on older servers rather than empty/null -- an empty
in_flightarray would read as 'nothing is stalled' when the truth is 'this server cannot tell you'.in_flightis live, currently-outstanding async I/O (pid,io_id,op,state,off,length,target_desc), which is what you want while a stall is happening rather than after it.io_method(worker/io_uring/sync) explains whatin_flightcan contain: withio_method = syncthere is no asynchronous submission, so the array is legitimately empty no matter how much I/O is running. Requires PostgreSQL 16+. Sub-queries that fail (pg_stat_ioandpg_aiosare permission-gated on some managed providers) append to_warningsand set their field to null; the rest of the response still returns.
Input Schema
| Name | Required | Description | Default |
|---|---|---|---|
| limit | No | Max rows per section (default 200). pg_stat_io has well under 200 combinations, so this effectively bounds the in-flight list on a busy PG18 server. | |
| includeZeroRows | No | If true, return every (backend_type, io_object, io_context) row including the ones with no recorded activity. Default false -- the view is mostly zeros on a quiet system. |
Output Schema
| Name | Required | Description | Default |
|---|---|---|---|
| io | Yes | Null (not []) when the fetch was refused -- [] is a real answer on a freshly reset cluster. | |
| _warnings | No | ||
| in_flight | No | PostgreSQL 18+ only. Currently-outstanding async I/O. Null (not []) when the fetch was refused -- reading a denial as 'nothing outstanding' would point the investigation the wrong way. | |
| io_method | No | PostgreSQL 18+ only. worker | io_uring | sync. With `sync` there is no async submission, so `in_flight` is legitimately empty however much I/O is running. | |
| byte_accounting | Yes | Which source produced read_bytes / write_bytes / extend_bytes: native columns, or op_bytes * ops. | |
| include_zero_rows | Yes | Echoed because it changes what an empty `io` means: no recorded I/O, vs the view returned nothing. | |
| server_version_num | Yes | Echoed so a caller can tell WHY the PG18-only keys are absent without a second round-trip. |