Monitoring PostgreSQL in Production

You can't operate a database you can't see. Every problem in this module, slow queries, replication lag, bloat, connection exhaustion, wraparound, announces itself in a metric well before it becomes an outage, but only if you're watching. Monitoring is how you catch the slow creep of a database heading for trouble and fix it during business hours instead of at 3am. This article covers the metrics that actually matter and where to find them, so you know what a healthy Postgres looks like and what the early warnings of an unhealthy one are.

This is part 38 of the PostgreSQL Masterclass, following autovacuum tuning.

The four categories worth watching

Postgres exposes its internals through statistics views, and good monitoring boils down to sampling the right ones over time. The metrics group into four areas.

1. Query performance

The queries are usually where problems start. pg_stat_statements, from the finding-slow-queries article, is the backbone here: track total execution time per query shape and watch for shapes whose time is climbing. Two live views tell you what's happening right now:

-- currently running queries, longest first
SELECT pid, now() - query_start AS duration, state, query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

Alert on long-running queries (something running for minutes that shouldn't be) and on idle in transaction sessions, which as we've seen block vacuum and hold locks. A rising count of either is an early warning.

2. Connections and pooling

Connection exhaustion takes an application down hard. Track how close you are to max_connections:

SELECT count(*) AS total,
       count(*) FILTER (WHERE state = 'active')             AS active,
       count(*) FILTER (WHERE state = 'idle in transaction') AS idle_in_txn
FROM pg_stat_activity;

If total connections trend toward the limit, you need a pooler (or a better-tuned one) before you hit the wall. A growing idle in transaction count points at application code holding transactions open, which is worth fixing at the source.

3. Replication and durability

If you run replicas, replication lag is a first-class metric, because reads served from a lagging replica return stale data. Measured from the primary:

SELECT client_addr,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS lag_bytes
FROM pg_stat_replication;

Alert when lag exceeds your read-your-own-writes tolerance. Also monitor that WAL archiving for backups is keeping up, since a stalled archive_command both breaks PITR and lets WAL pile up on disk until it fills.

4. Table and vacuum health

This is where slow decay hides. Track dead tuples and autovacuum activity per table (from the last article), cache hit ratio, and transaction ID age:

-- cache hit ratio: should be high (>0.99) for a well-sized database
SELECT sum(heap_blks_hit) / nullif(sum(heap_blks_hit + heap_blks_read), 0) AS cache_hit_ratio
FROM pg_statio_user_tables;

-- transaction ID age: alert well before wraparound
SELECT max(age(datfrozenxid)) FROM pg_database;

A cache hit ratio that drops means data no longer fits in memory and queries are hitting disk, a sign you need more RAM or less working set. A climbing transaction ID age means autovacuum is blocked. Both are the kind of gradual trend that monitoring catches and a spot-check misses.

Disk, CPU, and the host

Beyond Postgres's own views, the machine matters. Disk space is the one that causes the most abrupt outages: a full disk stops Postgres cold, and WAL, bloat, or an unarchived backup can fill it faster than you'd expect. Alert on disk usage with real headroom, not at 95%. Watch CPU, memory, and disk I/O saturation too, since a database that's fine until a traffic spike saturates its disk will surface as slow queries with no obvious query-level cause.

Tools that package this up

You don't query these views by hand in production. Monitoring tools collect, store, and alert on them:

• Prometheus with postgres_exporter plus Grafana dashboards is the common open-source stack, scraping the statistics views and graphing them over time.
• pgwatch2, PgHero, and pganalyze are Postgres-specific tools with ready-made dashboards and, in some cases, tuning recommendations. PgHero in particular is a quick way to surface slow queries, missing indexes, and bloat.
• Managed services provide built-in dashboards (CloudWatch for RDS, and similar) covering the host and database metrics.

Whatever you use, the goal is the same: the metrics above, sampled over time, with alerts on the thresholds that precede real problems.

Set alerts on leading indicators

The difference between monitoring that helps and monitoring that just records is alerting on leading indicators, the metrics that move before an outage, not after. The high-value alerts: replication lag past tolerance, connections nearing the limit, disk usage with headroom to react, transaction ID age climbing, long-running and idle-in-transaction sessions, and a dropping cache hit ratio. Each of these gives you hours or days of warning, which is exactly enough to fix the problem calmly.

Monitoring turns database operations from reactive firefighting into steady maintenance. Watch query performance, connections, replication, and table health through Postgres's statistics views, add host-level disk and CPU, package it with a tool that graphs and alerts, and set those alerts on the leading indicators. Do that and the failures this whole module described announce themselves early enough that you fix them before anyone notices.

Next, we pull the scaling thread together: the overall strategy for scaling PostgreSQL, from a bigger box to read replicas to sharding, and how to know which one you actually need.