Monitoring

Why it matters

Databases rarely fail without warning. Disks fill a little every day, a new feature adds a query that runs a million times, a forgotten session holds a transaction open and cleanup stops. Monitoring turns those slow trends into alerts before users notice. It also gives you the evidence for every tuning decision: you fix what the numbers say, not what feels slow.

What to watch

A short list covers most incidents:

• Availability: can a test client connect and run SELECT 1?
• Connections: active vs idle vs idle in transaction, compared with max_connections.
• Query performance: total and mean time per query pattern, slow-query log entries.
• Cache hit ratio: how often reads are served from memory rather than disk.
• Vacuum and bloat: dead rows waiting to be cleaned, and when autovacuum last ran.
• Replication: lag per replica, and replication slots that retain WAL.
• Resources: disk space (data and WAL), CPU, memory, I/O latency.
• Errors: deadlocks, failed logins, out-of-disk, checkpoint warnings in the server log.

PostgreSQL's built-in views

Who is connected and what are they doing?

SELECT pid, usename, state, wait_event_type,
       now() - xact_start AS xact_age, left(query, 60) AS query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY xact_age DESC NULLS LAST;

A session that is idle in transaction for many minutes is holding locks and blocking vacuum.

Which queries cost the most? The pg_stat_statements extension groups queries by shape (constants replaced with $1) and records calls and timings. It must be loaded at server start (shared_preload_libraries = 'pg_stat_statements', then a restart) and enabled per database with 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, 70) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

(Column names are for PostgreSQL 13 and later.) Sort by total time to find the best tuning targets; SELECT pg_stat_statements_reset(); starts a fresh measurement window.

Is the cache working?

SELECT datname,
       round(100.0 * blks_hit / nullif(blks_hit + blks_read, 0), 2) AS cache_hit_pct
FROM pg_stat_database
WHERE datname = current_database();

On a busy OLTP database this is usually above 99%. A falling value suggests the working set no longer fits in memory.

Dead tuples and bloat. Because of MVCC, updates and deletes leave old row versions behind. Autovacuum cleans them up so the space can be reused; if it falls behind, tables and indexes grow ("bloat") and queries slow down.

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;

Many dead tuples and an old last_autovacuum point to a vacuum problem, often caused by long-running transactions. pg_size_pretty(pg_total_relation_size('table')) shows how big a table and its indexes are. Plain VACUUM frees space for reuse without blocking; VACUUM FULL rewrites the table and locks it, so it is a planned maintenance action, not a routine one.

Unused indexes: pg_stat_user_indexes with idx_scan = 0 over a representative period.

Turning numbers into alerts

Collect metrics continuously with a tool rather than running queries by hand: Prometheus with postgres_exporter and Grafana dashboards, a cloud provider's monitoring (CloudWatch, Azure Monitor), or a commercial tool. Then alert on symptoms with sensible thresholds:

• disk above 80% (and the growth rate),
• connections above 80% of max_connections,
• replication lag above your RPO,
• any transaction open longer than, say, 15 minutes,
• the error rate or deadlock count jumping.

Every alert should have a short runbook: what it means, how to check, what to do. Alerts that fire constantly and are ignored are worse than none.

Common mistakes

• Monitoring the server (CPU) but not the database (locks, vacuum, slow queries).
• Running pg_stat_statements once during an incident with no baseline to compare.
• Fixing bloat with VACUUM FULL at peak time and locking a busy table.
• Alerting on everything, so real alerts drown.

Quick recap

• Watch availability, connections, query time, cache hit ratio, vacuum, replication, disk and errors.
• pg_stat_activity, pg_stat_statements, pg_stat_database and pg_stat_user_tables answer most questions.
• Collect continuously, alert on a few meaningful thresholds, and write a runbook for each.

Try it

In the Do tab, enable pg_stat_statements on your practice server, generate load with pgbench, and find the most expensive queries and the table with the most dead rows.