PostgreSQL Statistics Views

Monitoring and Operations / Guide 41 of 47 / Intermediate

• OSUbuntu 26.04 LTS
• PostgreSQL18.6-0ubuntu0.26.04.1
• Host2 core / 4 GB, 400,000-row test table
• TimeAbout 17 min

Since PostgreSQL 15 these counters live in shared memory rather than in a separate collector process, and survive a clean shutdown. stats_fetch_consistency controls how they behave inside a transaction.

This guide includes

Use this when you need to know how the database is actually being used. This matters because four views answer four different questions - and a reset does not clear everything you would expect it to.

• reading the whole-database counters from pg_stat_database
• comparing sequential against index scans, per table
• finding the indexes that are earning nothing
• splitting block-level I/O into heap and index reads
• watching a counter move, then resetting and finding what a reset does not touch

Before you start

• The appdb database with the events table and its indexes.

1. 1

   The whole-database counters

2. 2

   How each table is being read

3. 3

   Find the indexes earning nothing

4. 4

   Block-level I/O per table

5. 5

   Watch a counter move

6. 6

   Reset the counters, and find out what a reset does not touch

Official sources

• PostgreSQL 18 Documentation - The Statistics Collector
• PostgreSQL 18 Documentation - Viewing Locks
• PostgreSQL 18 Documentation - Explicit Locking
• PostgreSQL 18 Documentation - Error Reporting and Logging
• PostgreSQL 18 Documentation - Connections and Authentication
• PgBouncer Documentation