How to monitor PostgreSQL connections, cache hit ratio and replication lag (and get paged before it hurts)
The four PostgreSQL numbers that predict an outage, the SQL to read them, sensible thresholds, and how to alert on them without installing an exporter.
Most PostgreSQL outages announce themselves an hour early. Connections creep towards max_connections, the buffer cache starts missing, a replica falls behind. Nobody looks, because the dashboard that shows it lives in a tool nobody opens. This guide covers the four numbers worth alerting on, the queries behind them, and thresholds that page rarely but early.
1. Connections against max_connections
The single most common self-inflicted PostgreSQL outage is FATAL: sorry, too many clients already. It happens when an app pool grows, a migration script leaks connections, or someone runs pgbouncer in the wrong mode.
SELECT count(*) AS used,
current_setting('max_connections')::int AS max,
round(100.0 * count(*) / current_setting('max_connections')::int, 1) AS pct
FROM pg_stat_activity;
Alert at 90 % of the maximum. Warn at 75 % if you want a heads-up. Note that max_connections includes superuser_reserved_connections (3 by default), so ordinary roles hit the wall a few connections earlier than the number suggests. Look at the state column too: dozens of idle in transaction sessions mean an application is holding transactions open, which pins the xmin horizon so vacuum cannot reclaim dead tuples and, with a long enough hold, blocks DDL behind an ACCESS EXCLUSIVE lock queue.
2. Cache hit ratio
Postgres serves reads from shared_buffers when it can and from the OS page cache or disk when it cannot. A hit ratio that drops below 90 % on an OLTP database usually means the working set outgrew memory, a new query is scanning a big table, or somebody shrank the instance.
SELECT round(100.0 * sum(blks_hit) / nullif(sum(blks_hit) + sum(blks_read), 0), 2) AS cache_hit_pct
FROM pg_stat_database
WHERE datname = current_database();
These counters are cumulative since the last pg_stat_reset(), so a monitor has to diff consecutive readings and compute the ratio on the deltas. Alert when the rolling ratio stays under 90 % for several minutes rather than on a single reading. One caveat: blks_read counts reads from the OS page cache as misses, because Postgres cannot see the kernel cache. On a host with lots of free memory a "miss" may still be served from RAM, so pair this number with the host's disk read throughput before concluding the working set outgrew memory.
3. Replication lag
On a streaming replica:
SELECT CASE WHEN pg_last_wal_receive_lsn() = pg_last_wal_replay_lsn() THEN 0
ELSE extract(epoch FROM now() - pg_last_xact_replay_timestamp()) END AS lag_seconds;
The CASE matters: when the replica has replayed everything it received, pg_last_xact_replay_timestamp() stops advancing on an idle primary, and the naive now() - replay_timestamp reports growing lag on a system that is perfectly in sync. On the primary, pg_stat_replication shows every replica's write_lag, flush_lag and replay_lag as intervals measured from WAL send time, which is the more precise view. Alert at 30 seconds for read replicas that serve traffic, longer for backup replicas. Lag that grows steadily is a replica that cannot keep up (usually single-threaded replay on a primary with many parallel writers); lag that spikes and recovers is usually a long transaction, a bulk load or a vacuum on the primary.
4. Active and waiting queries
SELECT count(*) FILTER (WHERE state = 'active') AS active,
count(*) FILTER (WHERE wait_event_type = 'Lock') AS waiting_on_locks,
count(*) FILTER (WHERE state = 'active' AND now() - query_start > interval '5 seconds') AS slow
FROM pg_stat_activity
WHERE backend_type = 'client backend';
A rising waiting_on_locks count is the earliest signal of a lock pile-up behind a migration. Alert when it exceeds a handful for more than a minute. wait_event_type and backend_type exist from PostgreSQL 10; on older servers drop the backend_type filter. pg_blocking_pids(pid) tells you which session is at the head of the queue, which is usually an ALTER TABLE waiting for a long-running SELECT.
Running these checks without an exporter
You can wrap the queries in a cron job that posts to a webhook, or run postgres_exporter and Prometheus. Both work; both are one more thing to maintain.
If a server already runs the Vigil agent, add a database entry to /etc/vigil-agent.json and restart it. The agent calls psql locally with the password passed through the environment, never on the command line, and reports the numbers above every interval:
{
"databases": [
{"system": "postgresql", "name": "main",
"dsn": "postgresql://vigil_ro:PASSWORD@127.0.0.1:5432/app"}
]
}
Create a read-only role for it first. pg_monitor (PostgreSQL 10+) grants exactly the statistics views the queries above need, including the unredacted query column in pg_stat_activity, without any table access:
CREATE ROLE vigil_ro LOGIN PASSWORD 'PASSWORD';
GRANT pg_monitor TO vigil_ro;
vigil-server-agent --db-test prints what the collector sees so you can check the DSN before restarting. In the server's Databases tab you get connections, cache hit, transactions per second, replication lag, active and waiting queries, and the top statements when pg_stat_statements is installed. Rules for connections percentage, cache hit ratio and replication lag open incidents automatically, with the thresholds above as defaults.
Thresholds in one table
| Metric | Warn | Page |
|---|---|---|
| Connections used | 75 % | 90 % |
| Cache hit ratio (rolling) | under 95 % | under 90 % |
| Replication lag | 10 s | 30 s |
| Sessions waiting on locks | 3 | 10 for 1 minute |
| Idle in transaction | any older than 5 min | any older than 15 min |
Tune them to your workload after a week of data, and make sure the alert goes to a channel someone reads at night.