Five metrics precede almost every PostgreSQL outage: connection saturation, transaction ID age, replication lag, disk headroom, and the age of the longest-running transaction. A dashboard with forty panels is a research tool. A dashboard with those five is an early warning system.
Monitoring guides for Postgres tend to be exhaustive, which is a different thing from useful. They list every statistic the database exposes, grouped by category, with no indication of which ones actually go wrong. In practice a small number of failures account for most unplanned downtime, and each has a metric that moves before the failure arrives.
Table of contents
- Connection saturation
- Transaction ID wraparound age
- Replication lag
- Disk headroom, including WAL
- The longest-running transaction
- The metrics worth watching but not paging on
- How this fits the rest of the stack
- FAQ
Connection saturation
Postgres allocates a process per connection. When max_connections is reached, new connections are refused, and the application layer usually responds by retrying, which makes it worse.
The failure is abrupt. You are at 60 percent of the limit and everything is fine, then a deploy doubles the number of application instances, or a background job opens its own pool, and you are refusing connections. There is no gradual degradation to notice.
SELECT count(*) AS total,
count(*) FILTER (WHERE state = 'active') AS active,
count(*) FILTER (WHERE state = 'idle') AS idle,
count(*) FILTER (WHERE state = 'idle in transaction') AS idle_in_txn
FROM pg_stat_activity;
SHOW max_connections;Alert at 80 percent of max_connections. That gives room to act.
Watch idle in transaction separately, because it is the worse number. A connection sitting idle inside an open transaction holds locks and prevents vacuum from cleaning up rows, so it causes bloat as well as consuming a slot. A non-zero count that persists indicates application code that opens a transaction and then does something slow — an HTTP call, usually — before committing.
The structural fix is a connection pooler in front of the database, so hundreds of application connections share a small number of real ones. Application-side pools help but do not solve it once you have several instances, because each has its own pool and they do not coordinate.
Transaction ID wraparound age
The most serious failure Postgres has, and the least known, because it is rare and catastrophic rather than common and annoying.
Postgres identifies transactions with a 32-bit counter. To keep old rows visible, vacuum periodically freezes them. If vacuum falls far enough behind that the counter is at risk of wrapping, the database refuses all writes and requires a single-user-mode recovery. That is an extended outage.
SELECT datname,
age(datfrozenxid) AS xid_age,
ROUND(100 * age(datfrozenxid) / 2000000000.0, 1) AS pct_to_wraparound
FROM pg_database
ORDER BY xid_age DESC;Normal operation keeps this well under 200 million. Alert at 500 million, treat a billion as urgent, and understand that the database stops writing somewhere around two billion.
It only reaches those numbers when autovacuum is being prevented from working, and the usual reasons are a long-running transaction holding an old snapshot, a replication slot that a consumer stopped reading, or autovacuum being throttled too aggressively on a very high-write table.
This metric is genuinely worth alerting on even though it will probably never fire, because when it does fire the consequence is a database that will not accept writes and a recovery measured in hours.
Replication lag
If you have a replica — for reads, for failover, or both — lag is how far behind it is. It matters in two ways with different urgency.
For a read replica serving queries, lag is a correctness problem: a user writes something and immediately reads it back from a replica that has not received it yet, and it appears to have vanished.
For a standby held for failover, lag is your data-loss window. Failing over to a replica thirty seconds behind means losing thirty seconds of committed transactions.
-- On the primary
SELECT client_addr, state, sent_lsn, replay_lsn,
pg_wal_lsn_diff(sent_lsn, replay_lsn) AS replay_lag_bytes
FROM pg_stat_replication;
-- On the replica
SELECT now() - pg_last_xact_replay_timestamp() AS replication_delay;Alert on both a byte threshold and a time threshold, because they catch different failures — a large byte lag with a small time lag is a burst of writes, while a growing time lag with no traffic means replay has stalled.
Related and worth its own alert: an inactive replication slot. A slot whose consumer has gone away causes the primary to retain WAL indefinitely, which fills the disk and blocks vacuum. It is a quiet cause of two other failures on this list.
Disk headroom, including WAL
A full disk on a database server is an immediate outage, and unlike an application server it is not gracefully recoverable — Postgres shuts down rather than corrupting data, and restarting requires space it does not have.
Monitor free space as a percentage and as an absolute figure, and monitor the rate of change. A disk at 70 percent is unremarkable; a disk at 70 percent that was at 40 percent this morning is an incident in progress.
Watch the WAL directory separately from the data directory, because they fill for different reasons. WAL grows when archiving fails, when a replication slot is stalled, or when checkpoints are not keeping up — none of which is visible from overall data growth.
SELECT pg_size_pretty(pg_database_size(current_database())) AS db_size;
SELECT slot_name, active,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots;That second query is the one that catches the WAL problem before the disk does. A slot retaining tens of gigabytes with active set to false is a stalled consumer, and the fix is to restart the consumer or drop the slot.
Also watch inodes on filesystems holding many small files, and remember that a temporary space spike from a large sort or an index rebuild can fill a disk that looked comfortable.
The longest-running transaction
One number that surfaces several distinct problems at once, which is why it earns a place over more specific metrics.
SELECT pid, usename, state,
now() - xact_start AS txn_age,
now() - query_start AS query_age,
wait_event_type, wait_event,
left(query, 120) AS query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY xact_start
LIMIT 10;A transaction open for hours is doing at least one of the following: holding locks that block other work, preventing vacuum from removing dead rows anywhere in the database, contributing to transaction ID age, and holding a connection slot.
Alert on any transaction older than a few minutes on an OLTP system. Genuine analytical queries can legitimately run long, so exclude them by user or by database rather than raising the threshold for everything.
The common causes are an application that opens a transaction before making a network call, a migration running against a large table, an analyst’s session left open in a client, or a deadlock that resolved on one side but left the other waiting.
Alongside this, watch dead tuples per table, because that is where the consequence shows up:
SELECT relname, n_live_tup, n_dead_tup,
ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;The metrics worth watching but not paging on
A second tier: useful for diagnosis, not worth waking anyone for.
- Cache hit ratio. Above 95 percent is healthy, above 99 percent is good. A drop indicates the working set has outgrown shared_buffers. Useful as a trend and a poor alert, because it moves for benign reasons.
- Slow queries via pg_stat_statements. This is the single best diagnostic extension available and should be enabled everywhere. Review it weekly rather than alerting on it.
- Deadlocks. A steady low rate is normal in a busy system. A sudden rise indicates a new code path taking locks in a different order.
- Index usage. Unused indexes cost write performance and disk; identify them periodically rather than continuously.
- Checkpoint frequency. Frequent checkpoints triggered by WAL volume rather than time suggest max_wal_size is too small.
- Temp file creation. Indicates sorts and hashes spilling to disk, which points at work_mem being too low for the queries actually running.
The distinction is worth being deliberate about. An alert that fires for something you will not act on immediately trains people to ignore alerts, which is worse than not having it.
Enable pg_stat_statements if nothing else on this list. It records normalised query statistics — total time, call count, mean time, rows — and answers the question of what is actually slow better than any amount of dashboard interpretation.
How this fits the rest of the stack
Five metrics with thresholds and an action for each beats forty panels nobody reads. Connections, transaction ID age, replication lag, disk headroom, and the oldest transaction cover most of what actually takes a Postgres instance down, and each of them moves before the failure rather than during it. Managed Postgres on RunxBuild handles backups, connection limits, and private networking so a smaller number of these are yours to watch, and the RunxBuild hosting calculator puts the database alongside the service and storage it serves.
Useful related references:
- PostgreSQL 18: How to Upgrade Without Downtime
- SQLite vs PostgreSQL: Which One Your Project Actually Needs
- PostgreSQL Show Tables: \dt, pg_catalog, and information_schema
- Databases on RunxBuild
FAQ
What are the most important PostgreSQL metrics to monitor?
Connection saturation against max_connections, transaction ID age for wraparound risk, replication lag if you run a replica, disk headroom including the WAL directory separately, and the age of the longest-running transaction. Those five precede most unplanned outages. Everything else is diagnostic rather than predictive.
What is a good cache hit ratio for PostgreSQL?
Above 95 percent is healthy and above 99 percent is good for an OLTP workload. It is a better trend than an alert, because it drops for benign reasons such as a large one-off analytical query. A sustained decline usually means the working set has grown beyond shared_buffers.
What is transaction ID wraparound and should I worry?
Postgres uses a 32-bit transaction counter, and if vacuum falls far enough behind that it risks wrapping, the database refuses writes until recovered in single-user mode. It is rare and severe. Alert when age(datfrozenxid) passes 500 million. It only gets there when something is blocking autovacuum, typically a long transaction or a stalled replication slot.
How do I find slow queries in PostgreSQL?
Enable the pg_stat_statements extension. It records normalised statistics per query — call count, total time, mean time, rows — which identifies both the slowest queries and the ones that are individually fast but run so often they dominate. It is the highest-value thing you can enable on a Postgres instance.
Why is idle in transaction a problem?
A connection idle inside an open transaction holds its locks and prevents vacuum from cleaning up dead rows anywhere in the database, so it causes table bloat and contributes to transaction ID age as well as occupying a connection slot. A persistent count usually means application code opens a transaction and then makes a slow network call before committing.