Chapter 31: Monitoring and Performance Tuning
Key Metrics to Monitor
| Metric | Healthy | Action if Bad |
|---|---|---|
| Buffer hit ratio | >99% | Increase shared_buffers / add RAM |
| Active connections | <80% of max | Use connection pooling (PgBouncer) |
| Replication lag | <1 second | Check network, replica load |
| Transaction rate | Stable | Investigate spikes |
| Slow queries | Few/none | Add indexes, optimize queries |
| Dead tuples | Low ratio | Tune autovacuum |
| Disk usage | <80% | Archive old data, add storage |
PostgreSQL Monitoring Queries
-- Active queries right now
SELECT pid, state, query, now() - query_start AS duration
FROM pg_stat_activity WHERE state = 'active';
-- Slow queries (enable pg_stat_statements extension)
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;
-- Table bloat (dead tuples needing vacuum)
SELECT relname, n_dead_tup, n_live_tup,
round(n_dead_tup::numeric/n_live_tup*100,2) AS dead_pct
FROM pg_stat_user_tables WHERE n_live_tup > 0
ORDER BY dead_pct DESC;
-- Index usage (unused indexes waste space and slow writes)
SELECT indexrelname, idx_scan
FROM pg_stat_user_indexes ORDER BY idx_scan ASC LIMIT 10;Tools
- pg_stat_statements: track query performance (built-in extension)
- pgBadger: log analyzer, generates HTML reports
- Prometheus + Grafana: real-time dashboards with postgres_exporter
- PgHero: web UI for quick health checks
Key Takeaways
- Monitor: hit ratio, connections, replication lag, slow queries, bloat
- pg_stat_statements is essential — enable it in every PostgreSQL instance
- Unused indexes waste space and slow writes — drop them
- Autovacuum keeps bloat under control — tune it, don't disable it