Database problems are usually slow-developing — the system gets gradually worse over weeks until it tips over and a query that used to be 10ms is 30s. Catching this requires monitoring. The right metrics turn slow degradation into a Tuesday-morning "we should look at that" instead of a 3am page.
This page is the working set of metrics for Postgres specifically; principles transfer.
Five categories. Get these and you catch most database problems.
| Category | Top metrics |
|---|---|
| Connections | Active + idle counts, max-connection limit utilisation, pool wait time |
| Queries | p95/p99 latency, slow-query rate, top queries by total time |
| Locks | Lock wait time, deadlock count, longest-held locks |
| I/O & cache | Cache hit ratio, dirty page rate, disk I/O wait |
| Replication & WAL | Replication lag, WAL volume, archive failures |
Each of these has a sane Postgres view to read from. Most observability stacks have prebuilt exporters (postgres_exporter for Prometheus); use them.
Connection pressure is the single most common Postgres issue. Postgres uses one process per connection; max_connections defaults to 100, can go higher but doesn't scale linearly.
Track:
pg_stat_activity — current connections by state (active, idle, idle in transaction).idle in transaction count — these are bugs in your application (transactions started but not committed). High counts indicate connection leaks.Alert:
max_connections for 5+ minutes.idle in transaction count > 5 for any sustained period.The almost-universal fix for connection pressure is PgBouncer in transaction mode in front of the database. PgBouncer multiplexes thousands of client connections to a pool of hundreds (or fewer) backend connections. Mandatory at any meaningful scale.
pg_stat_statements is the most useful Postgres extension. It records normalised query stats — total_exec_time, calls, mean_exec_time, rows — for every query the database has seen.
Top-by-total-time view:
SELECT query, calls, total_exec_time, mean_exec_time, rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;
This tells you what to optimise. The query at the top of "total time" is where the database is actually spending its life — even if it's individually fast, high call counts add up.
Alert:
Set log_min_duration_statement = 500 (or wherever your latency threshold is). Slow queries land in the Postgres log. Tools like pgBadger summarise.
Use it as the complement to pg_stat_statements — the latter aggregates, the former gives you full bound-parameter examples to reproduce.
pg_locks joined with pg_stat_activity shows currently-held locks and waiting queries.
Key metrics:
pg_stat_database.deadlocks) — should be near zero. Non-zero means application code with conflicting transaction ordering.Most problems show up here:
ACCESS EXCLUSIVE waiting on a long-running query, blocking everything.SELECT FOR UPDATE holding rows; another transaction hangs.Postgres uses shared buffers (configurable) plus the OS page cache.
pg_stat_database.blks_hit / (blks_hit + blks_read) — buffer cache hit ratio. > 99% is healthy; < 95% means working set doesn't fit and you're going to disk constantly.pg_statio_user_tables — per-table I/O stats. Identifies which tables are I/O-heavy.iostat, node-exporter. High await or util indicates I/O bottleneck.Solutions:
For setups with replicas:
pg_stat_replication.replay_lag on the primary, or pg_last_xact_replay_timestamp() on the replica.Alert:
Postgres MVCC creates dead tuples; autovacuum cleans them up. When autovacuum can't keep up, tables bloat.
Track:
pg_stat_user_tables.n_dead_tup / n_live_tup).Tooling: pgstattuple extension shows true bloat. pg_repack rebuilds bloated tables online.
A high-write table with > 50% dead-tuple ratio means autovacuum is losing. Tune autovacuum_vacuum_scale_factor lower for that table; check that long-running transactions aren't blocking vacuum.
The dashboard a DBA actually looks at:
If you're staring at this for the first time and one of the panels is missing, that's where the next outage is hiding.
pg_stat_statements — non-negotiable. Enable in postgresql.conf.postgres_exporter + Prometheus + Grafana — open-source stack. Many grafana dashboards exist; start with the official Postgres one.pgBadger — log analysis, slow-query reports.pgstattuple — bloat measurement, when needed.auto_explain — automatically logs query plans for slow queries; invaluable for "why is this slow."For a team setting up Postgres monitoring from scratch:
- Enable pg_stat_statements
- Enable auto_explain (log_min_duration = 1000, log_analyze = on)
- Run pgBouncer in transaction mode in front of Postgres
- Run postgres_exporter; ship to Prometheus
- Build grafana dashboard with the six panels above
- Set log_min_duration_statement = 500 to catch slow queries
- Configure pgBadger to run nightly on logs
- Set up alerts on the metrics above
A day's work; permanent operational visibility.
Real examples this monitoring stack catches:
idle in transaction → the new feature has a missing commit(). Fix.pg_stat_statements shows it changed plan → auto_explain shows missing index → add index.Without monitoring, each of these is a frantic investigation. With it, a click and a fix.