Monitoring Basics

PostgreSQL monitoring answers one question: are users waiting on the database, and why? Watch connections, locks, replication lag, bloat, and disk before CPU graphs turn red. PostgreSQL 18.4 exposes rich stats in pg_stat_* views; pair them with host metrics and alert on symptoms users feel.

Recipe

-- Five-minute health snapshot
SELECT count(*) FILTER (WHERE state = 'active')   AS active,
       count(*) FILTER (WHERE wait_event_type = 'Lock') AS waiting_locks,
       count(*)                                        AS total
FROM pg_stat_activity
WHERE backend_type = 'client backend';
 
SELECT pg_size_pretty(pg_database_size(current_database())) AS db_size;
 
SELECT client_addr, state, wait_event_type, wait_event, query
FROM pg_stat_activity
WHERE wait_event IS NOT NULL
  AND pid <> pg_backend_pid()
LIMIT 10;

When to reach for this:

• On-call orientation for a new production cluster
• Designing first dashboards before postgres_exporter deploy
• Incident triage when "database is slow" tickets spike

Working Example

Daily ops dashboard queries: saturation, lag, and top wait events.

-- Connection saturation vs max_connections
SELECT
  (SELECT count(*) FROM pg_stat_activity WHERE backend_type = 'client backend') AS used,
  (SELECT setting::int FROM pg_settings WHERE name = 'max_connections') AS max_conn,
  round(100.0 * (SELECT count(*) FROM pg_stat_activity WHERE backend_type = 'client backend') /
    (SELECT setting::int FROM pg_settings WHERE name = 'max_connections'), 1) AS pct_used;
 
-- Replication lag (standby / subscriber)
SELECT application_name, state, sync_state,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_lag_bytes
FROM pg_stat_replication;
 
-- Database bloat signal (dead tuples)
SELECT relname, n_dead_tup, last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;
 
-- Disk risk: WAL and data directory (run on host too)
SELECT pg_size_pretty(sum(size)) AS wal_size
FROM pg_ls_waldir();

# Host metrics still mandatory
df -h /var/lib/postgresql
iostat -x 1 3

What this demonstrates:

• Connection count compared to max_connections hard ceiling
• Replication lag in bytes from pg_stat_replication
• Dead tuple growth as vacuum health signal
• WAL directory size as disk incident predictor

Deep Dive

What to Watch

Alert Philosophy

Alert on symptoms: connection exhaustion, lag above SLO, disk > 85%, lock waits > N seconds. Avoid paging on every counter spike.

Collection Stack

# Typical metrics path
# postgres_exporter -> Prometheus -> Grafana + Alertmanager

Gotchas

• Monitoring only CPU - Postgres is often I/O or lock bound. Fix: add iowait, disk latency, pg_stat_activity wait events.
• Ignoring PgBouncer pool - app sees pool saturation while Postgres is idle. Fix: export pool cl_active, cl_waiting.
• max_connections alert at 100% - new connections fail before alert fires if threshold is wrong. Fix: alert at 80% and track pool queue depth.
• Lag in seconds only on async replicas - bytes lag matters for bursty write workloads. Fix: chart pg_wal_lsn_diff and replay lag time together.
• No baseline - cannot tell normal from bad. Fix: record weekly p95 connections and top query times.
• Autovacuum not watched - bloat creeps for weeks. Fix: alert on tables with n_dead_tup high and last_autovacuum stale.

Alternatives

FAQs

Related

• pg_stat_activity & Locks - blocked sessions
• pg_stat_statements - query workload
• postgres_exporter & Grafana - metrics stack
• SLO Dashboards - user-facing budgets
• Monitoring Best Practices - alert checklist

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.