Diagnostic SQL Queries for Silent PostgreSQL Degradation

Short summary

A practical guide to detecting silent PostgreSQL degradation—bloat, unused indexes, open transactions—that standard monitoring dashboards miss. It provides copy-paste SQL queries against pg_stat_user_tables and pg_stat_statements to find dead-tuple ratios, total-time-consuming queries, and excessive sequential scans. Each section pairs diagnosis with remediation advice, including autovacuum tuning, pg_repack for lock-safe compaction, and EXPLAIN ANALYZE interpretation tips.

• •Dead-tuple ratio above 20% on large tables signals autovacuum is falling behind
• •Sort pg_stat_statements by total_exec_time, not mean, to find queries draining cumulative server time
• •High seq_tup_read on a large table with low idx_scan points to a missing index

Generated with AI, which can make mistakes.