Table I/O

In addition to the operation statistic counts, there are also a set of counters in the database that concentrate on physical I/O in pg_statio_user_tables (with all/system variations, too). The first pair of fields monitor use of the shared_buffers structure that caches table data, which is called the heap in this context. When a read happens, the database distinguishes between whether that read could be satisfied using a block already in the database buffer cache (a heap block hit), or whether it required an OS read to satisfy:

    pgbench=# SELECT relname,cast(heap_blks_hit as numeric) / (heap_blks_hit + heap_blks_read) AS hit_pct,heap_blks_hit,heap_blks_read FROM pg_statio_user_tables WHERE (heap_blks_hit + heap_blks_read)>0 ORDER ...

Get PostgreSQL 10 High Performance now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.