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 ratioThe share of reads served from memory rather than disk; usually above 99% on a busy OLTP database.: how often reads are served from memory rather than disk.
• Vacuum and bloatGrowth of tables and indexes from dead row versions that vacuum has not cleaned up, slowing queries.: dead rows waiting to be cleaned, and when autovacuumPostgreSQL's background process that cleans up dead row versions so their space can be reused. 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 runbookA short document for an alert explaining what it means, how to check it and what to do.: 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.