Measure Database Replication and CDC Lag

SELECT
  consumer_name,
  source_type,
  region,
  COUNT(*) AS samples,
  AVG(lag_seconds) AS average_lag_seconds,
  MAX(lag_seconds) AS maximum_lag_seconds,
  MAX(replay_bytes_behind) AS maximum_bytes_behind,
  SUM(CASE WHEN lag_seconds > 30 THEN 1 ELSE 0 END) AS stale_samples,
  100.0 * SUM(CASE WHEN lag_seconds > 30 THEN 1 ELSE 0 END)
    / NULLIF(COUNT(*), 0) AS stale_sample_rate_pct
FROM database_replication_samples
WHERE timestamp_utc >= now() - INTERVAL '24 hours'
  AND environment = 'production'
GROUP BY consumer_name, source_type, region
HAVING COUNT(*) >= 10
ORDER BY maximum_lag_seconds DESC;