New monitoring metric in PostgreSQL 16: pg_stat_io

[jchancojr]

Add view for pg_stat_io

pg_stat_io: Rows of the view show IO operations for a particular backend type, IO target object, IO context combination (e.g. a client backend's operations on permanent relations in shared buffers) and each column in the view is the total number of IO Operations done (e.g. writes). So a cell in the view would be, for example, the number of blocks of relation data written from shared buffers by client backends since the last stats reset.

https://www.postgresql.org/message-id/20200124195226.lth52iydq2n2uilq@alap3.anarazel.de https://pganalyze.com/blog/pg-stat-io

[hunleyd]

two good examples of what to report on in https://postgrespro.com/blog/pgsql/5970086 as well

[keithf4]

So the PG17 changes required gathering some BGW stats from pg_stat_io since they were removed from the pg_stat_bgwriter catalog.

https://github.com/CrunchyData/pgmonitor/pull/429

I'm pulling that out via a stat that specific to the backend_type = backend writer. That seems like a good way to split these IO metrics up for better usability. Might also be able to break it down further by context as a label too

postgres=# select backend_type from pg_stat_io group by 1;
    backend_type     
---------------------
 autovacuum launcher
 autovacuum worker
 background worker
 background writer
 checkpointer
 client backend
 standalone backend
 startup
 walsender

[keithf4]

Convert the reads/writes blocks columns to bytes. Just add it as an additional column/metric so people can parse blocks too if they really want to. Example output

   backend_type    |  object  |  context  | read_bytes | read_time | write_bytes | write_time |  hits  | evictions | reuses | fsyncs | fsync_time
-------------------+----------+-----------+------------+-----------+-------------+------------+--------+-----------+--------+--------+------------
 client backend    | relation | bulkread  | 2211438592 |    327.42 |           0 |       0.00 |      1 |         0 | 269919 |        |
 client backend    | relation | bulkwrite |          0 |      0.00 |  4447838208 |    1057.40 | 536489 |         0 | 542976 |        |
 client backend    | relation | normal    |      73728 |      0.59 |           0 |       0.00 | 546301 |         0 |        |      0 |       0.00
 client backend    | relation | vacuum    | 4692975616 |    690.50 |  4448051200 |    1249.69 |   2151 |         0 | 572847 |        |
 background worker | relation | bulkread  | 2236350464 |    327.67 |           0 |       0.00 |   2080 |         0 | 272960 |        |
 background worker | relation | normal    |          0 |      0.00 |           0 |       0.00 |    144 |         0 |        |      0 |       0.00
 checkpointer      | relation | normal    |            |           |    35332096 |      15.58 |        |           |        |     92 |    3779.12```