Amazon RDSSRE & Consulting Services

# Measure average read/write disk latency and queue depth over the past 30 minutes
aws cloudwatch get-metric-data \
  --metric-data-queries '[
    {"Id":"r1","MetricStat":{"Metric":{"Namespace":"AWS/RDS","MetricName":"ReadLatency","Dimensions":[{"Name":"DBInstanceIdentifier","Value":"production-rds-pg"}]},"Period":60,"Stat":"Average"}},
    {"Id":"w1","MetricStat":{"Metric":{"Namespace":"AWS/RDS","MetricName":"WriteLatency","Dimensions":[{"Name":"DBInstanceIdentifier","Value":"production-rds-pg"}]},"Period":60,"Stat":"Average"}},
    {"Id":"q1","MetricStat":{"Metric":{"Namespace":"AWS/RDS","MetricName":"DiskQueueDepth","Dimensions":[{"Name":"DBInstanceIdentifier","Value":"production-rds-pg"}]},"Period":60,"Stat":"Average"}}
  ]' \
  --start-time $(date -u -v-30M +%Y-%m-%dT%H:%M:%SZ) \
  --end-time $(date -u +%Y-%m-%dT%H:%M:%SZ)

# Inspect oldest transaction age and frozen XID headroom to guard against wraparound
psql "host=production-rds-pg.c12345.us-east-1.rds.amazonaws.com dbname=app user=jusdb_monitor sslmode=require" -c "
SELECT datname, age(datfrozenxid) AS xid_age,
       2147483648 - age(datfrozenxid) AS remaining_txids,
       round((age(datfrozenxid)::numeric / 2147483648::numeric) * 100, 2) AS pct_towards_wraparound
FROM pg_database
ORDER BY xid_age DESC;
"