New monitoring metric in PostgreSQL 16: pg_stat_io #324

Activity

1. jchancojr

   added

   UNCONFIRMEDInitial state. Need's triagedInitial state. Need's triaged

   on Mar 21, 2023
2. hunleyd commented on Jun 4, 2023

   

   hunleyd

   on Jun 4, 2023

   Contributor

   More actions

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

   added this to the 5.1 milestone on May 15, 2024
4. keithf4

   added

   FEATURENew feature or requestNew feature or request

   and removed

   UNCONFIRMEDInitial state. Need's triagedInitial state. Need's triaged

   on Sep 13, 2024
5. keithf4 commented on Sep 13, 2024

   

   keithf4

   on Sep 13, 2024

   Contributor

   More actions

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

   #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
   
6. keithf4 commented on Sep 13, 2024

   

   keithf4

   on Sep 13, 2024

   Contributor

   More actions

   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```
   
7. keithf4

   modified the milestones: 5.1, Future, Next, 5.2 on Sep 16, 2024
8. keithf4

   modified the milestones: 5.2, 5.3 on Feb 28, 2025
9. keithf4

   modified the milestones: 5.3, 5.4 on Jun 30, 2025