Part 197: Database Health Check Runbook: Bloat, Unused Indexes & Connections

The production diagnostic queries every database engineer runs during health audits.

1. Finding Unused Indexes (Wasted Disk & Write Overhead)

-- Indexes with 0 scans wasting write I/O:
SELECT 
    schemaname || '.' || relname AS table_name,
    indexrelname AS index_name,
    idx_scan AS number_of_scans,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0 
  AND indexrelname NOT LIKE '%_pkey' -- Don't drop primary keys!
ORDER BY pg_relation_size(indexrelid) DESC;

2. Finding Table Bloat & Dead Tuples

SELECT 
    relname AS table_name,
    n_live_tup AS live_rows,
    n_dead_tup AS dead_rows,
    ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
    last_vacuum,
    last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY dead_pct DESC;

3. Investigating Connection Count & Active States

SELECT 
    state,
    COUNT(*) AS connection_count
FROM pg_stat_activity
GROUP BY state;

Key Takeaways

• Run this diagnostic script weekly to catch indexing debt and vacuum drift before incidents occur.

In Part 198, we explore Production Query Triage: Slow Query Hunting & Terminating Rogue Queries!