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:
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.
![Thumbnail [60x60]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/DWE_web-min-scaled.jpg)