GuidesPostgreSQL monitoring

PostgreSQL monitoring: the metrics, queries and alerts that matter

Updated 17 October 2026 · 9 minute read

PostgreSQL monitoring comes down to a short list of questions. Can clients connect? Is work finishing, or is it waiting on locks? Is the cache doing its job? Are replicas keeping up? Is vacuum keeping tables healthy? PostgreSQL answers all of them through built-in statistics views, so you can start with nothing more than psql and a read-only role.

This guide lists the metrics to watch, the SQL to read them, a least-privilege monitoring role and alert thresholds that work as a starting point. It applies to PostgreSQL 13 and later; most of it also works on older versions.

The metrics to watch

Most counters in the statistics views are cumulative since the last statistics reset. To get a rate, sample them at intervals and take the difference. A monitoring tool does this for you.

Create a monitoring role first

Do not monitor with a superuser. Since PostgreSQL 10, the built-in pg_monitor role grants read access to all the statistics views, including other sessions' query text, without write access to any data:

CREATE ROLE monitoring WITH LOGIN PASSWORD 'use-a-long-random-password' CONNECTION LIMIT 3;
GRANT pg_monitor TO monitoring;

The connection limit stops a misconfigured collector from using up your connection slots. In pg_hba.conf, allow this role only from the hosts that collect metrics, and require TLS and scram-sha-256 authentication.

Connections: pg_stat_activity

pg_stat_activity has one row per server process. Count client connections by state and compare them with the limit:

SELECT state, count(*)
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY state;

SHOW max_connections;

Watch the idle in transaction state closely. Such sessions hold locks and snapshots while doing nothing, usually because the application forgot to commit. Remember that superuser_reserved_connections (3 by default) are not available to ordinary users.

Long-running queries and transactions

SELECT pid, usename, state,
       now() - xact_start  AS transaction_age,
       now() - query_start AS query_age,
       wait_event_type, wait_event,
       left(query, 80)     AS query
FROM pg_stat_activity
WHERE backend_type = 'client backend' AND state <> 'idle'
ORDER BY xact_start NULLS LAST
LIMIT 10;

The oldest transaction matters even if it is idle: as long as it is open, vacuum cannot clean up rows deleted after it started. Setting idle_in_transaction_session_timeout is a good safety net.

Throughput and cache: pg_stat_database

SELECT datname, numbackends, xact_commit, xact_rollback, deadlocks,
       round(100.0 * blks_hit / nullif(blks_hit + blks_read, 0), 2) AS cache_hit_pct,
       temp_bytes
FROM pg_stat_database
WHERE datname IS NOT NULL;

For a transactional workload whose working set fits in memory, the cache hit ratio usually stays above 99%. A drop from your normal level means the working set has outgrown shared_buffers or a new query is scanning large tables. Note that blks_read counts reads from the operating system, which may still come from its page cache. Rising temp_bytes means sorts and hashes spill to disk; look at work_mem and the queries involved.

Locks and waits: pg_locks

To see who is blocked and by whom, use pg_blocking_pids():

SELECT pid, pg_blocking_pids(pid) AS blocked_by,
       now() - query_start AS waiting_for, left(query, 80) AS query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

For a summary of ungranted locks by type:

SELECT locktype, mode, count(*)
FROM pg_locks
WHERE NOT granted
GROUP BY locktype, mode;

Short lock waits are normal. Sessions blocked for more than a minute, or a chain of sessions behind one blocker, are not. Setting log_lock_waits = on logs any wait longer than deadlock_timeout (1 second by default), which helps after the fact.

Replication: pg_stat_replication

On the primary, each connected standby has a row:

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

Alert when a standby you expect is missing from this view, not only when its lag grows. On a standby, SELECT now() - pg_last_xact_replay_timestamp(); gives the time since the last replayed transaction. It also grows when the primary is simply idle, so compare it with byte lag before you page anyone. If you use replication slots, check pg_replication_slots too: an inactive slot keeps WAL on the primary and can fill its disk.

Size, bloat and autovacuum

SELECT datname, pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
ORDER BY pg_database_size(datname) DESC;

SELECT relname, n_live_tup, n_dead_tup, last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

A table whose dead rows keep climbing while last_autovacuum stays old is a vacuum problem. The usual causes are a long-open transaction, an abandoned replication slot, or autovacuum settings too gentle for a busy table.

Also watch transaction ID age. PostgreSQL must freeze old rows before about 2.1 billion transactions pass, or it stops accepting writes to protect data:

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

Slow queries: pg_stat_statements

pg_stat_statements records execution statistics for every normalised statement. It must be loaded at server start, so it needs a restart:

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'

-- after the restart, in each database where you want to query it
CREATE EXTENSION pg_stat_statements;

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

Sort by total time to find what costs the server most, and by mean time to find what users feel most. On PostgreSQL 12 and older the columns are total_time and mean_time.

Alert thresholds to start with

Tune these against your own baseline after a week or two. Alert on sustained conditions, not single samples.

A note on MySQL and MariaDB

The same questions apply, with different sources. SHOW GLOBAL STATUS gives counters such as Threads_connected, Threads_running, Innodb_row_lock_waits and Slow_queries; compare connections with SHOW VARIABLES LIKE 'max_connections'. For top statements, query performance_schema.events_statements_summary_by_digest ordered by SUM_TIMER_WAIT. Replication lag is Seconds_Behind_Source in SHOW REPLICA STATUS (Seconds_Behind_Master on MariaDB and older MySQL). A monitoring user typically needs only PROCESS, REPLICATION CLIENT and SELECT on performance_schema.

Monitor PostgreSQL with Televane

Running these queries by hand works during an incident, but you need history and alerts as well. Televane database monitoring collects them for PostgreSQL, MySQL and MariaDB through the Televane agent, every minute. It shows:

• Connections by state, against the maximum
• Lock waits, deadlocks and the longest-running query
• Commits, rollbacks, rows read and written per second
• Cache hit ratio, database size and replication lag
• The 25 statements with the highest total time, normalised and with values masked

It uses a least-privilege, read-only monitoring account like the one above. The password is typed on your server during setup and kept in a protected file; it is never sent to Televane. If your applications send OpenTelemetry traces, database calls appear in APM and the slowest statements link to their traces, so you can see which request a slow query slowed down.

For alerting, Televane alerts include ready-made database templates, are evaluated every 15 seconds and notify email, Slack, Microsoft Teams, PagerDuty or a webhook. Pricing is based on the data you send, with no per-host fees, and the Free plan includes 50 GB a month (see pricing). To try it, create an account and follow getting started to install the agent.

Frequently asked questions