Skip to content

PostgreSQL 16: More I/O statistics

PostgreSQL is already tracking various kinds of statistics which can help in identifying bottlenecks. Some of these statistics are available in the the pg_statio_* catalog views:

postgres=# select viewname from pg_views where viewname like '%statio%';
         viewname         
--------------------------
 pg_statio_all_sequences
 pg_statio_all_tables
 pg_statio_sys_tables
 pg_statio_user_tables
 pg_statio_all_indexes
 pg_statio_sys_indexes
 pg_statio_user_indexes
 pg_statio_sys_sequences
 pg_statio_user_sequences
(9 rows)

Other views providing I/O related statistics are:

That’s what you have up to PostgreSQL 15. The next major version, which will be PostgreSQL 16, will come with more detailed I/O statistics. The new catalog view which provides this, is called pg_stat_io:

postgres=# select version();
                                               version                                                
------------------------------------------------------------------------------------------------------
 PostgreSQL 16devel on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
(1 row)

postgres=# \d pg_stat_io
                       View "pg_catalog.pg_stat_io"
    Column    |           Type           | Collation | Nullable | Default 
--------------+--------------------------+-----------+----------+---------
 backend_type | text                     |           |          | 
 io_object    | text                     |           |          | 
 io_context   | text                     |           |          | 
 reads        | bigint                   |           |          | 
 writes       | bigint                   |           |          | 
 extends      | bigint                   |           |          | 
 op_bytes     | bigint                   |           |          | 
 evictions    | bigint                   |           |          | 
 reuses       | bigint                   |           |          | 
 fsyncs       | bigint                   |           |          | 
 stats_reset  | timestamp with time zone |           |          | 

postgres=# 

As my instance was newly created the numbers in there are not very huge:

postgres=# select * from pg_stat_io;
    backend_type     |   io_object   | io_context | reads | writes | extends | op_bytes | evictions | reuses | fsyncs |          stats_reset          
---------------------+---------------+------------+-------+--------+---------+----------+-----------+--------+--------+-------------------------------
 autovacuum launcher | relation      | bulkread   |     0 |      0 |         |     8192 |         0 |      0 |        | 2023-02-13 16:00:04.736157+01
 autovacuum launcher | relation      | normal     |     2 |      0 |         |     8192 |         0 |        |      0 | 2023-02-13 16:00:04.736157+01
 autovacuum worker   | relation      | bulkread   |     0 |      0 |         |     8192 |         0 |      0 |        | 2023-02-13 16:00:04.736157+01
 autovacuum worker   | relation      | normal     |   324 |      0 |      11 |     8192 |         0 |        |      0 | 2023-02-13 16:00:04.736157+01
 autovacuum worker   | relation      | vacuum     |   144 |      0 |       0 |     8192 |         0 |    108 |        | 2023-02-13 16:00:04.736157+01
 client backend      | relation      | bulkread   |     0 |      0 |         |     8192 |         0 |      0 |        | 2023-02-13 16:00:04.736157+01
 client backend      | relation      | bulkwrite  |     0 |      0 |       0 |     8192 |         0 |      0 |        | 2023-02-13 16:00:04.736157+01
 client backend      | relation      | normal     |   455 |      0 |       1 |     8192 |         0 |        |      0 | 2023-02-13 16:00:04.736157+01
 client backend      | relation      | vacuum     |     0 |      0 |       0 |     8192 |         0 |      0 |        | 2023-02-13 16:00:04.736157+01
 client backend      | temp relation | normal     |     0 |      0 |       0 |     8192 |         0 |        |        | 2023-02-13 16:00:04.736157+01
 background worker   | relation      | bulkread   |     0 |      0 |         |     8192 |         0 |      0 |        | 2023-02-13 16:00:04.736157+01
 background worker   | relation      | bulkwrite  |     0 |      0 |       0 |     8192 |         0 |      0 |        | 2023-02-13 16:00:04.736157+01
 background worker   | relation      | normal     |     0 |      0 |       0 |     8192 |         0 |        |      0 | 2023-02-13 16:00:04.736157+01
 background worker   | relation      | vacuum     |     0 |      0 |       0 |     8192 |         0 |      0 |        | 2023-02-13 16:00:04.736157+01
 background worker   | temp relation | normal     |     0 |      0 |       0 |     8192 |         0 |        |        | 2023-02-13 16:00:04.736157+01
 background writer   | relation      | normal     |       |      0 |         |     8192 |           |        |      0 | 2023-02-13 16:00:04.736157+01
 checkpointer        | relation      | normal     |       |     67 |         |     8192 |           |        |     29 | 2023-02-13 16:00:04.736157+01
 standalone backend  | relation      | bulkread   |     0 |      0 |         |     8192 |         0 |      0 |        | 2023-02-13 16:00:04.736157+01
 standalone backend  | relation      | bulkwrite  |     0 |      0 |       8 |     8192 |         0 |      0 |        | 2023-02-13 16:00:04.736157+01
 standalone backend  | relation      | normal     |   689 |   1007 |     493 |     8192 |         0 |        |      0 | 2023-02-13 16:00:04.736157+01
 standalone backend  | relation      | vacuum     |    10 |      0 |       0 |     8192 |         0 |      0 |        | 2023-02-13 16:00:04.736157+01
 startup             | relation      | bulkread   |     0 |      0 |         |     8192 |         0 |      0 |        | 2023-02-13 16:00:04.736157+01
 startup             | relation      | bulkwrite  |     0 |      0 |       0 |     8192 |         0 |      0 |        | 2023-02-13 16:00:04.736157+01
 startup             | relation      | normal     |     0 |      0 |       0 |     8192 |         0 |        |      0 | 2023-02-13 16:00:04.736157+01
 startup             | relation      | vacuum     |     0 |      0 |       0 |     8192 |         0 |      0 |        | 2023-02-13 16:00:04.736157+01
 walsender           | relation      | bulkread   |     0 |      0 |         |     8192 |         0 |      0 |        | 2023-02-13 16:00:04.736157+01
 walsender           | relation      | bulkwrite  |     0 |      0 |       0 |     8192 |         0 |      0 |        | 2023-02-13 16:00:04.736157+01
 walsender           | relation      | normal     |     0 |      0 |       0 |     8192 |         0 |        |      0 | 2023-02-13 16:00:04.736157+01
 walsender           | relation      | vacuum     |     0 |      0 |       0 |     8192 |         0 |      0 |        | 2023-02-13 16:00:04.736157+01
 walsender           | temp relation | normal     |     0 |      0 |       0 |     8192 |         0 |        |        | 2023-02-13 16:00:04.736157+01

What you see is one row per backend type, context and object. Running a small pgbench load will increase those numbers. Doing a manual checkpoint right after also increases the fsync operations of the checkpointer:

Questions about your own setup? A dbi services consultant can look at your case.

Talk to an expert (opens in a new tab)
postgres=# \! pgbench -i -s 10 postgres
dropping old tables...
creating tables...
generating data (client-side)...
1000000 of 1000000 tuples (100%) done (elapsed 2.76 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done in 3.85 s (drop tables 0.00 s, create tables 0.03 s, client-side generate 2.83 s, vacuum 0.14 s, primary keys 0.84 s).
postgres=# select * from pg_stat_io;
    backend_type     |   io_object   | io_context | reads | writes | extends | op_bytes | evictions | reuses | fsyncs |          stats_reset          
---------------------+---------------+------------+-------+--------+---------+----------+-----------+--------+--------+-------------------------------
 autovacuum launcher | relation      | bulkread   |     0 |      0 |         |     8192 |         0 |      0 |        | 2023-02-13 16:00:04.736157+01
 autovacuum launcher | relation      | normal     |     2 |      0 |         |     8192 |         0 |        |      0 | 2023-02-13 16:00:04.736157+01
 autovacuum worker   | relation      | bulkread   |     0 |      0 |         |     8192 |         0 |      0 |        | 2023-02-13 16:00:04.736157+01
 autovacuum worker   | relation      | normal     |   324 |      0 |      11 |     8192 |         0 |        |      0 | 2023-02-13 16:00:04.736157+01
 autovacuum worker   | relation      | vacuum     |   144 |      0 |       0 |     8192 |         0 |    108 |        | 2023-02-13 16:00:04.736157+01
 client backend      | relation      | bulkread   |  7568 |      0 |         |     8192 |         0 |   7536 |        | 2023-02-13 16:00:04.736157+01
 client backend      | relation      | bulkwrite  |     0 |  14346 |   16394 |     8192 |         0 |  14346 |        | 2023-02-13 16:00:04.736157+01
 client backend      | relation      | normal     |   533 |      0 |       6 |     8192 |         0 |        |      0 | 2023-02-13 16:00:04.736157+01
 client backend      | relation      | vacuum     | 14346 |      0 |       0 |     8192 |         0 |  14314 |        | 2023-02-13 16:00:04.736157+01
 client backend      | temp relation | normal     |     0 |      0 |       0 |     8192 |         0 |        |        | 2023-02-13 16:00:04.736157+01
 background worker   | relation      | bulkread   |  6746 |      0 |         |     8192 |         0 |   6714 |        | 2023-02-13 16:00:04.736157+01
 background worker   | relation      | bulkwrite  |     0 |      0 |       0 |     8192 |         0 |      0 |        | 2023-02-13 16:00:04.736157+01
 background worker   | relation      | normal     |     5 |      0 |       0 |     8192 |         0 |        |      0 | 2023-02-13 16:00:04.736157+01
 background worker   | relation      | vacuum     |     0 |      0 |       0 |     8192 |         0 |      0 |        | 2023-02-13 16:00:04.736157+01
 background worker   | temp relation | normal     |     0 |      0 |       0 |     8192 |         0 |        |        | 2023-02-13 16:00:04.736157+01
 background writer   | relation      | normal     |       |      0 |         |     8192 |           |        |      0 | 2023-02-13 16:00:04.736157+01
 checkpointer        | relation      | normal     |       |     67 |         |     8192 |           |        |     29 | 2023-02-13 16:00:04.736157+01
 standalone backend  | relation      | bulkread   |     0 |      0 |         |     8192 |         0 |      0 |        | 2023-02-13 16:00:04.736157+01
 standalone backend  | relation      | bulkwrite  |     0 |      0 |       8 |     8192 |         0 |      0 |        | 2023-02-13 16:00:04.736157+01
 standalone backend  | relation      | normal     |   689 |   1007 |     493 |     8192 |         0 |        |      0 | 2023-02-13 16:00:04.736157+01
 standalone backend  | relation      | vacuum     |    10 |      0 |       0 |     8192 |         0 |      0 |        | 2023-02-13 16:00:04.736157+01
 startup             | relation      | bulkread   |     0 |      0 |         |     8192 |         0 |      0 |        | 2023-02-13 16:00:04.736157+01
 startup             | relation      | bulkwrite  |     0 |      0 |       0 |     8192 |         0 |      0 |        | 2023-02-13 16:00:04.736157+01
 startup             | relation      | normal     |     0 |      0 |       0 |     8192 |         0 |        |      0 | 2023-02-13 16:00:04.736157+01
 startup             | relation      | vacuum     |     0 |      0 |       0 |     8192 |         0 |      0 |        | 2023-02-13 16:00:04.736157+01
 walsender           | relation      | bulkread   |     0 |      0 |         |     8192 |         0 |      0 |        | 2023-02-13 16:00:04.736157+01
 walsender           | relation      | bulkwrite  |     0 |      0 |       0 |     8192 |         0 |      0 |        | 2023-02-13 16:00:04.736157+01
 walsender           | relation      | normal     |     0 |      0 |       0 |     8192 |         0 |        |      0 | 2023-02-13 16:00:04.736157+01
 walsender           | relation      | vacuum     |     0 |      0 |       0 |     8192 |         0 |      0 |        | 2023-02-13 16:00:04.736157+01
 walsender           | temp relation | normal     |     0 |      0 |       0 |     8192 |         0 |        |        | 2023-02-13 16:00:04.736157+01
(30 rows)

postgres=# checkpoint;
CHECKPOINT
postgres=# select * from pg_stat_io;
    backend_type     |   io_object   | io_context | reads | writes | extends | op_bytes | evictions | reuses | fsyncs |          stats_reset          
---------------------+---------------+------------+-------+--------+---------+----------+-----------+--------+--------+-------------------------------
 autovacuum launcher | relation      | bulkread   |     0 |      0 |         |     8192 |         0 |      0 |        | 2023-02-13 16:00:04.736157+01
 autovacuum launcher | relation      | normal     |     2 |      0 |         |     8192 |         0 |        |      0 | 2023-02-13 16:00:04.736157+01
 autovacuum worker   | relation      | bulkread   |     0 |      0 |         |     8192 |         0 |      0 |        | 2023-02-13 16:00:04.736157+01
 autovacuum worker   | relation      | normal     |   324 |      0 |      11 |     8192 |         0 |        |      0 | 2023-02-13 16:00:04.736157+01
 autovacuum worker   | relation      | vacuum     |   144 |      0 |       0 |     8192 |         0 |    108 |        | 2023-02-13 16:00:04.736157+01
 client backend      | relation      | bulkread   |  7568 |      0 |         |     8192 |         0 |   7536 |        | 2023-02-13 16:00:04.736157+01
 client backend      | relation      | bulkwrite  |     0 |  14346 |   16394 |     8192 |         0 |  14346 |        | 2023-02-13 16:00:04.736157+01
 client backend      | relation      | normal     |   533 |      0 |       6 |     8192 |         0 |        |      0 | 2023-02-13 16:00:04.736157+01
 client backend      | relation      | vacuum     | 14346 |      0 |       0 |     8192 |         0 |  14314 |        | 2023-02-13 16:00:04.736157+01
 client backend      | temp relation | normal     |     0 |      0 |       0 |     8192 |         0 |        |        | 2023-02-13 16:00:04.736157+01
 background worker   | relation      | bulkread   |  6746 |      0 |         |     8192 |         0 |   6714 |        | 2023-02-13 16:00:04.736157+01
 background worker   | relation      | bulkwrite  |     0 |      0 |       0 |     8192 |         0 |      0 |        | 2023-02-13 16:00:04.736157+01
 background worker   | relation      | normal     |     5 |      0 |       0 |     8192 |         0 |        |      0 | 2023-02-13 16:00:04.736157+01
 background worker   | relation      | vacuum     |     0 |      0 |       0 |     8192 |         0 |      0 |        | 2023-02-13 16:00:04.736157+01
 background worker   | temp relation | normal     |     0 |      0 |       0 |     8192 |         0 |        |        | 2023-02-13 16:00:04.736157+01
 background writer   | relation      | normal     |       |      0 |         |     8192 |           |        |      0 | 2023-02-13 16:00:04.736157+01
 checkpointer        | relation      | normal     |       |   2174 |         |     8192 |           |        |     70 | 2023-02-13 16:00:04.736157+01
 standalone backend  | relation      | bulkread   |     0 |      0 |         |     8192 |         0 |      0 |        | 2023-02-13 16:00:04.736157+01
 standalone backend  | relation      | bulkwrite  |     0 |      0 |       8 |     8192 |         0 |      0 |        | 2023-02-13 16:00:04.736157+01
 standalone backend  | relation      | normal     |   689 |   1007 |     493 |     8192 |         0 |        |      0 | 2023-02-13 16:00:04.736157+01
 standalone backend  | relation      | vacuum     |    10 |      0 |       0 |     8192 |         0 |      0 |        | 2023-02-13 16:00:04.736157+01
 startup             | relation      | bulkread   |     0 |      0 |         |     8192 |         0 |      0 |        | 2023-02-13 16:00:04.736157+01
 startup             | relation      | bulkwrite  |     0 |      0 |       0 |     8192 |         0 |      0 |        | 2023-02-13 16:00:04.736157+01
 startup             | relation      | normal     |     0 |      0 |       0 |     8192 |         0 |        |      0 | 2023-02-13 16:00:04.736157+01
 startup             | relation      | vacuum     |     0 |      0 |       0 |     8192 |         0 |      0 |        | 2023-02-13 16:00:04.736157+01
 walsender           | relation      | bulkread   |     0 |      0 |         |     8192 |         0 |      0 |        | 2023-02-13 16:00:04.736157+01
 walsender           | relation      | bulkwrite  |     0 |      0 |       0 |     8192 |         0 |      0 |        | 2023-02-13 16:00:04.736157+01
 walsender           | relation      | normal     |     0 |      0 |       0 |     8192 |         0 |        |      0 | 2023-02-13 16:00:04.736157+01
 walsender           | relation      | vacuum     |     0 |      0 |       0 |     8192 |         0 |      0 |        | 2023-02-13 16:00:04.736157+01
 walsender           | temp relation | normal     |     0 |      0 |       0 |     8192 |         0 |        |        | 2023-02-13 16:00:04.736157+01
(30 rows)

A nice addition for tracking I/O related statistics. Documentation for this catalog view is currently only available in the development branch of the documentation.

Let’s work together

Ready to operate at
the next level?

Tell us about your challenge. We’ll bring the depth, the expertise, and the Swiss precision.

Open source Team

Open source Team

Posts published under the Open source Team account are collective contributions by the dbi services open infrastructure consultants. The practice covers open-source databases and platforms, PostgreSQL first of all, along with MySQL, MariaDB, and Linux, from architecture, replication, and high availability to performance tuning and operations under service level agreements, and contributes actively to the PostgreSQL community.View Author posts

721 posts since September 2011

Most read in Database

The Database posts our readers open most: the all-time favourites, and what is trending right now.

Leave a Comment

Your email address will not be published. Required fields are marked *