Database Observability: Metrics, Logs and Traces

-- connections by state
SELECT state, count(*) FROM pg_stat_activity GROUP BY state;

-- long transactions (block vacuum, hold locks)
SELECT pid, now() - xact_start AS age, state, left(query, 60)
FROM pg_stat_activity WHERE xact_start < now() - interval '5 minutes' ORDER BY age DESC;

-- lock waits right now
SELECT a.pid, a.wait_event_type, a.wait_event, pg_blocking_pids(a.pid), left(a.query, 60)
FROM pg_stat_activity a WHERE a.wait_event_type = 'Lock';

-- per-database health
SELECT datname, xact_commit, xact_rollback, deadlocks, temp_bytes,
       round(100.0 * blks_hit / nullif(blks_hit + blks_read, 0), 2) AS hit_pct
FROM pg_stat_database WHERE datname NOT LIKE 'template%';

groups:
- name: postgres
  rules:
  - alert: PostgresReplicationLagHigh
    expr: pg_replication_lag_seconds > 30
    for: 2m
  - alert: PostgresDiskSpaceLow
    expr: node_filesystem_avail_bytes{mountpoint="/var/lib/postgresql"} / node_filesystem_size_bytes < 0.15
    for: 5m
  - alert: PostgresConnectionsNearMax
    expr: sum(pg_stat_activity_count) / pg_settings_max_connections > 0.8
    for: 5m
  - alert: PostgresXidAgeHigh
    expr: max(pg_database_age) > 1000000000        # well before wraparound trouble
  - alert: PostgresLongTransaction
    expr: pg_stat_activity_max_tx_duration > 600