PostgreSQL exposes a lot of information about itself through statistics views (pg_stat_*). A handful of queries tell you whether the database is healthy, which queries are slow, whether vacuum keeps up, and whether replicas are lagging. Here are the checks worth knowing, and how to turn them into dashboards and alerts.

Run these in psql as a superuser or a role with pg_monitor. See psql commands.

1. Connections: who’s connected and what are they doing?

SELECT state, count(*) FROM pg_stat_activity GROUP BY state;
  • active — running a query.
  • idle — connected, doing nothing (normal with connection pools).
  • idle in transaction — a transaction left open. These hold locks and block vacuum, so investigate any that last long.

Long-running queries and transactions:

SELECT pid, usename, state, now() - xact_start AS xact_age, now() - query_start AS query_age, left(query, 80)
FROM pg_stat_activity
WHERE state <> 'idle' AND xact_start IS NOT NULL
ORDER BY xact_start
LIMIT 10;

Compare total connections with max_connections. If you’re close, add a pooler. See PgBouncer.

2. Locks and blocking

SELECT blocked.pid AS blocked_pid, left(blocked.query, 60) AS blocked_query,
       blocking.pid AS blocking_pid, left(blocking.query, 60) AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking ON blocking.pid = ANY(pg_blocking_pids(blocked.pid));

Terminate a stuck session if needed: SELECT pg_terminate_backend(<pid>);.

3. Slow queries: pg_stat_statements

This is the most valuable extension for performance work. It aggregates statistics for every normalised query. Enable it:

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all

Restart PostgreSQL, then in each database:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Top queries by total time:

SELECT calls, round(total_exec_time) AS total_ms, round(mean_exec_time, 1) AS mean_ms,
       rows, left(query, 100) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Optimise the top entries with EXPLAIN (ANALYZE, BUFFERS) and indexes. See database indexing basics. Reset statistics after changes: SELECT pg_stat_statements_reset();.

Also log individual slow statements:

log_min_duration_statement = 500ms

4. Cache hit ratio

SELECT round(100.0 * sum(blks_hit) / nullif(sum(blks_hit) + sum(blks_read), 0), 2) AS cache_hit_pct
FROM pg_stat_database;

On OLTP workloads, values above ~99% are typical. Lower values mean the working set doesn’t fit in shared_buffers plus the OS cache, so consider more RAM or tuning. See PostgreSQL performance tuning.

5. Vacuum and bloat

Dead tuples pile up when autovacuum can’t keep 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, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

High dead percentages, or very old last_autovacuum timestamps on busy tables, mean tuning is needed. Watch transaction ID wraparound too:

SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY 2 DESC;

Values approaching 200 million (the default autovacuum_freeze_max_age) mean autovacuum needs attention, well before the hard limit. See VACUUM and autovacuum.

6. Replication lag

On the primary:

SELECT client_addr, state, write_lag, flush_lag, replay_lag,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS bytes_behind
FROM pg_stat_replication;

On a replica:

SELECT now() - pg_last_xact_replay_timestamp() AS replay_delay;

Check replication slots. An inactive slot retains WAL forever, and can fill the disk:

SELECT slot_name, active, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained
FROM pg_replication_slots;

See PostgreSQL replication.

7. Size and disk usage

SELECT pg_size_pretty(pg_database_size(current_database()));
SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) AS total
FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC LIMIT 10;

Also watch the filesystem (df -h), including the WAL directory. A full disk stops PostgreSQL. See disk full troubleshooting.

8. Unused indexes

SELECT relname, indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes WHERE idx_scan = 0 ORDER BY pg_relation_size(indexrelid) DESC;

Indexes never used since the statistics reset cost writes and space. Check replicas too before dropping them.

Dashboards and alerts

  • postgres_exporter + Prometheus + Grafana: standard dashboards for connections, transactions, cache hit ratio, replication lag and locks. See Prometheus and Grafana.
  • Netdata auto-detects PostgreSQL and charts it with no setup. See Netdata.
  • pgwatch, pganalyze: dedicated PostgreSQL monitoring.

Alert on: the database being down, connections above 80% of max, replication lag above your tolerance, inactive replication slots, transaction ID age, disk usage above 80%, and long-running transactions.

Quick reference

Check View / query
Sessions pg_stat_activity
Blocking pg_blocking_pids()
Slow queries pg_stat_statements
Cache hit pg_stat_database (blks_hit / blks_read)
Dead tuples pg_stat_user_tables
Wraparound age(datfrozenxid)
Replication pg_stat_replication, pg_replication_slots
Sizes pg_total_relation_size()

Related: pgBackRest backups and PostgreSQL remote connections.

  • PostgreSQL
  • Databases
  • Monitoring