PostgreSQL Monitoring: The Complete 2026 Guide

Short answer: PostgreSQL monitoring stands on five pillars: connections (pool exhaustion is the #1 outage), query performance (pg_stat_statements — enable it today), vacuum health (bloat and transaction ID wraparound are the silent killers), replication (lag and slot retention), and locks (blocked query trees). Instrument with postgres_exporter + Prometheus or a platform agent, alert on connection saturation, oldest idle-in-transaction, and replication slot lag, and you will catch every classic Postgres incident before your users do. PostgreSQL 16/17 assumed.

Enable pg_stat_statements first (it's your query X-ray)

-- postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
-- then:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Top queries by total time (the only ranking that matters)
SELECT left(query, 80) AS query, calls,
       round(total_exec_time::numeric, 1) AS total_ms,
       round(mean_exec_time::numeric, 1) AS avg_ms,
       rows
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

Sort by total time, not worst single call — a 5 ms query called a million times costs more than a 10 s report run twice. Reset stats weekly (pg_stat_statements_reset()) so rankings reflect current reality, and watch dealloc — if the stats file evicts entries, you're losing your slowest queries exactly when you need them.

The metrics that matter, by pillar

Connections — Postgres forks a process per connection; hundreds of connections = memory churn and scheduler pain:

Related guideMySQL Monitoring: The Complete 2026 Guide→

Throughput & latency: xact_commit/xact_rollback rates (TPS), blks_hit/(blks_hit+blks_read) cache hit ratio (> 99% for OLTP; sliding values mean the working set outgrew shared_buffers/RAM).

Vacuum & bloat (the Postgres-specific pillar):

Replication:

SELECT client_addr, state, replay_lag FROM pg_stat_replication;
SELECT slot_name, active, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots;

Alert on replay lag > 30 s and on inactive slots retaining WAL — a forgotten replication slot fills the WAL disk and takes the primary down. This one has ended more weekends than any other Postgres bug.

Locks:

SELECT blocked.pid, blocked.query, blocking.pid AS blocker, blocking.query AS blocker_query
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE cardinality(pg_blocking_pids(blocked.pid)) > 0;

Collection: postgres_exporter → Prometheus

# docker run quay.io/prometheuscommunity/postgres-exporter
# DATA_SOURCE_NAME="postgresql://monitor:pass@db:5432/postgres?sslmode=disable"
scrape_configs:
  - job_name: postgres
    static_configs: [ { targets: ['postgres-exporter:9187'] } ]

The monitoring user needs pg_monitor (PG 10+): GRANT pg_monitor TO monitor; — no superuser required. For per-database breakdowns, label by datname and aggregate in PromQL. Platform alternative: Guance's DataKit collects Postgres metrics, pg_stat_statements digests, and logs in one input, correlating slow queries with the application traces that caused them.

The 5 classic PostgreSQL incidents (and their signatures)

1. Connection storm. A deploy multiplies app replicas; each opens its pool; max_connections is hit; even psql can't connect. Prevention: PgBouncer in transaction-pooling mode; alert at 85% capacity.
2. Idle-in-transaction wedge. A forgotten BEGIN holds locks and blocks vacuum; dead tuples pile up; the table bloats; latency creeps for days. Alert on oldest idle-in-transaction > 10 min.
3. Autovacuum starvation. Default settings can't keep up with high-churn tables; dead tuple ratio climbs; index scans slow. Signature: n_dead_tup growing on hot tables — tune per-table autovacuum scale factors.
4. Replication slot WAL flood. An inactive slot retains WAL; pg_wal fills the disk; the primary halts. Alert on retained WAL per slot > 10 GB.
5. Wraparound approach. age(datfrozenxid) climbs past 1.5 billion on a database where vacuum was disabled. This is the one Postgres failure that forces downtime — alert at 1.5B, page at 1.8B.

Related guideMySQL Integration→

FAQ

Q: What are the most important PostgreSQL metrics?
Connection usage vs max_connections, cache hit ratio (blks_hit ratio > 99%), TPS and rollback rate, dead tuple ratio per table (vacuum health), age(datfrozenxid) (wraparound distance), replication replay lag, and replication slot WAL retention. Plus disk free on the data and WAL directories.

Q: How do I find slow queries in PostgreSQL?
Enable pg_stat_statements (add to shared_preload_libraries, restart, CREATE EXTENSION), then rank by total_exec_time — that surfaces the queries costing the most aggregate time. For live detail, pg_stat_activity shows running queries; EXPLAIN (ANALYZE, BUFFERS) explains individual plans.

Q: How much connection pooling do I need?
Almost always: yes, use a pooler. Postgres spawns a process per connection and degrades past a few hundred active connections. PgBouncer in transaction-pooling mode is the standard; size pools so total possible connections across all app replicas stay under ~80% of max_connections.

Q: What is transaction ID wraparound and why monitor it?
Postgres uses 32-bit transaction IDs (~2 billion) that recycle; vacuum must freeze old rows before the counter laps them. If age(datfrozenxid) approaches 2 billion, Postgres shuts down to protect data and recovery is painful. Monitor the age of every database and alert at 1.5 billion — it only becomes a crisis if ignored for months.

Q: How do I monitor PostgreSQL on RDS/Cloud SQL?
Managed Postgres hides the OS but exposes the database: enable Enhanced Monitoring/Cloud SQL metrics for host-level data, pull pg_stat_statements via a SQL-polling collector (postgres_exporter custom queries, or a platform's DBM feature), and export logs to your log pipeline. Our AWS RDS monitoring guide covers the CloudWatch specifics.

---

Sources: PostgreSQL 16/17 documentation (pg_stat_statements, pg_stat_replication, autovacuum), postgres_exporter project docs. Verified 2026-08-07. Published by Guance — the PostgreSQL integration collects metrics, statements, and logs via DataKit.