The Essential Guide to PostgreSQL Performance Monitoring

Why Monitoring Matters

PostgreSQL is powerful, but without proper monitoring, performance issues can go unnoticed until they cause downtime. Effective monitoring is the foundation of reliable database operations.

Key Metrics to Track

Connection Metrics

• Active connections — how many connections are currently in use
• Idle connections — connections that are open but not executing queries
• Connection utilization — percentage of max_connections in use

A sudden spike in connections often indicates an application issue or connection pool misconfiguration.

Query Performance

• Query execution time — average, p95, and p99 latency
• Queries per second — throughput over time
• Slow queries — queries exceeding your defined threshold
• Lock waits — queries blocked waiting for locks

Resource Utilization

• CPU usage — both user and system time
• Memory — shared buffers hit ratio, work_mem usage
• Disk I/O — read/write throughput and IOPS
• Cache hit ratio — percentage of reads served from shared buffers (aim for >99%)

Replication Metrics

If using replication:

• Replication lag — how far behind replicas are
• WAL generation rate — write-ahead log volume
• Replication slot status — ensure slots aren't holding back WAL cleanup

Setting Up Alerts

Not every metric needs an alert. Focus on:

1. Critical: Connection exhaustion (>90%), replication lag (>30s), disk space (<10%)
2. Warning: Cache hit ratio (<99%), slow query count increase, CPU sustained >80%
3. Informational: Daily performance summaries, weekly trend reports

Automated Monitoring with DBA SaaS

Setting up comprehensive monitoring manually requires significant effort. DBA SaaS automates the entire process — deploy our agent, and you'll have full visibility into all these metrics within minutes, plus AI-powered insights that tell you exactly what to optimize.