Monitoring is needed for db-A cluster. We should create a map of important points to monitor, by category.

Metrics example

Memory

• Buffer hit ratio
• Shared buffer reads
• Shared buffer hits

• Cache misses: blocks read/s vs blocks hit/s

• temp_bytes from pg_stat_database
• temp_files from pg_stat_database

• Alert when blocks read/s suddenly increase, document workload changed or we need more RAM
• Alert when we've large temp files, document it indicates sorts or hash joins spilling to disk, inefficient work_mem or poor execution plans

SELECT
    blks_hit,
    blks_read,
    round(100.0 * blks_hit / NULLIF(blks_hit + blks_read,0),2) AS hit_ratio
FROM pg_stat_database;

Connections

• numbackends
• max_connections
• numbackends / max_connections

• active sessions
• idle sessions
• idle in transaction (important in PostgreSQL)

• Alert if any transaction is > a few minutes, document it prevent vacuum cleanup, increase table bloat, replication lag and WAL retention

Query performance

• pg_stat_statements - execution count
• pg_stat_statements - average duration
• pg_stat_statements - total duration
• pg_stat_statements - stddev
• pg_stat_statements - rows returned

• Track P95 / P99 for the pg_stat_statements values

• Top - Total execution time
• Top - Mean execution time
• Top - Temporary file usage
• Top - I/O time

Transactions

• xact_commit
• xact_rollback

• Compute transactions / s
• Compute rollback %

• Alert on sudden rollback increases, document that usually indicates application issues

WAL

WAL is a very important point to monitor for PostgreSQL.

• WAL generated/sec
• Archive lag
• Archive failures
• Pending WAL
• WAL directory size

• Alert on WAL generation sudden increase, document that usually indicates bulk updates, maintenance, vacuum or application issues

• Alert at critical level on failed_count or any archive delay

Vacuum

• Autovacuum activity
• Vacuum duration
• Vacuum frequency
• Number of workers

• Do a real effort to collect n_dead_tup per table
• Alert when n_dead_tup grows, document that usually indicates vacuum falling behind, long transactions or insufficient autovacuum

• Alert on age(relfrozenxid) as tables approaching wraparound deserve immediate attention to prevent transaction ID wraparound outages

Table bloat

• Estimated table bloat
• Index bloat
• Dead tuple %

• Document on Agora applications with especially large tables must graph this on their own dashboard too

Locks

• Blocked sessions
• Blocking sessions
• Deadlocks
• Lock waits

• Try to combine pg_stat_activity and pg_locks for data about locks

Replication

We don't currently have replication enabled but we want to build HA so we'll enable it soon.

• Replication lag
• Replay lag
• Write lag
• Flush lag
• Apply lag
• Streaming state
• Replication slots

• Monitor pg_replication_slots
• Alert when there is any inactive replication slots, document that means those slots retain WAL forever, with risk to fill all disk space.

Checkpoints

• Timed checkpoints
• Requested checkpoints
• Checkpoint duration
• Checkpoint write time
• Checkpoint sync time

• Alert on too many requested checkpoints, document that usually indicates max_wal_size is too small

Background writer

• buffers_checkpoint
• buffers_clean
• buffers_backend
• buffers_backend_fsync

• Alert when buffers_backend is above a specified threshold still to detrmine ("large"), document that means backends are doing their own writes instead of the background writer.

I/O

Only for PostgreSQL 16+ or 17+ for every variable.

• Read latency
• Write latency
• Read throughput
• Write throughput
• IO by backend type
• Checkpoint I/O
• Vacuum I/O

Temporary files

• temp_files
• temp_bytes

• Alert on large temporary files, document that usually indicates missing indexes, insufficient work_mem or inefficient plans

Index usage

• Sequential scans
• Index scans
• Index hit ratio
• Unused indexes
• Duplicate indexes

• Graph seq_scan / idx_scan

Statistics health

• Monitor AutoAnalyze activity
• Monitor manual ANALYZE
• Tables with stale statistics

Database size

• Database size
• Largest tables
• Largest indexes

• Find a way to compute growth/day
• Find a way to compute growth/month

Capacity

• Disk usage
• WAL growth
• Table growth
• Index growth
• TOAST growth

Availability

• Server up/down
• Restart count
• Recovery mode
• Crash recovery
• Uptime