データベース接続プールの飽和状態を測定する

SELECT
  service,
  pool_name,
  COUNT(*) AS samples,
  100.0 * AVG(active_connections)
    / NULLIF(MAX(max_connections), 0) AS average_utilization_pct,
  SUM(CASE WHEN wait_ms > 0 THEN 1 ELSE 0 END) AS waited_samples,
  100.0 * SUM(CASE WHEN wait_ms > 0 THEN 1 ELSE 0 END)
    / NULLIF(COUNT(*), 0) AS wait_rate_pct,
  AVG(wait_ms) AS average_wait_ms,
  SUM(CASE WHEN timed_out THEN 1 ELSE 0 END) AS timeouts
FROM database_pool_samples
WHERE timestamp_utc >= now() - INTERVAL '24 hours'
  AND environment = 'production'
GROUP BY service, pool_name
HAVING COUNT(*) >= 10
ORDER BY wait_rate_pct DESC, average_utilization_pct DESC;