WAL Activity Monitoring

Rank WAL-Hungry Queries

Monitor queries that generate the highest WAL volume to spot bulk writes, inefficient updates, or missing HOT optimisations.

SELECT queryid,
       LEFT(query, 120) AS query_preview,
       calls,
       wal_records,
       wal_fpi,
       wal_bytes,
       wal_bytes / NULLIF(calls, 0) AS wal_bytes_per_call
  FROM pg_stat_insights
 WHERE wal_bytes > 0
 ORDER BY wal_bytes DESC
 LIMIT 20;

Analyse WAL by Relation

Determine which tables or indexes contribute most to WAL traffic and evaluate whether autovacuum or fillfactor adjustments are needed.

SELECT relid::regclass AS relation,
       wal_bytes,
       wal_fpi,
       shared_blks_dirtied
  FROM pg_stat_insights_relation
 ORDER BY wal_bytes DESC
 LIMIT 15;

Trending & Alerting

Persist WAL history into a time-series table for Grafana or trigger alerts when daily volume exceeds expected baselines.

SELECT date_trunc('minute', collected_at) AS minute,
       SUM(wal_bytes) AS wal_bytes
  FROM pg_stat_insights_wal_history
 WHERE collected_at > now() - interval '24 hours'
 GROUP BY 1
 ORDER BY 1;

groups:
  - name: wal
    rules:
      - alert: WalGrowthSpike
        expr: increase(pg_stat_insights_wal_bytes[10m]) > 5e8
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "WAL throughput exceeded 500 MB in 10 minutes"

Remediation Checklist