Check database health (seq_scan, dead tuples, lock waits, buffer hit ratio)

The 30 second answer

I check the PostgreSQL statistics views for tables with many sequential scans, a lot of dead tuples, lock waits, and a low buffer cache hit ratio. Each points to a different fix: a missing index, vacuum not keeping up, contention between transactions, or not enough memory. A high sequential scan count on a small table is normal, so I always read it together with table size.

pg_stat_user_tables has seq_scan, idx_scan, n_dead_tup and last_autovacuum. pg_stat_database has blks_hit and blks_read for the hit ratio. Joining pg_locks with pg_stat_activity (wait_event_type = 'Lock') shows lock waits.

SELECT relname, seq_scan, idx_scan, n_dead_tup FROM pg_stat_user_tables ORDER BY seq_scan DESC;
SELECT datname, blks_hit::float / nullif(blks_hit + blks_read, 0) AS hit_ratio FROM pg_stat_database;

Note: a high seq_scan count is not always bad. For small tables a sequential scan is the cheapest plan (see Using EXPLAIN).

Sources (2)

PostgreSQL 17: The Cumulative Statistics System
pg_locks