Database SRE: Applying Reliability Engineering Principles Specifically to Your Database Tier

# 1-hour and 6-hour burn rate for query latency SLO (<100ms)
(
  sum(rate(database_query_duration_seconds_bucket{le="0.1"}[1h]))
  /
  sum(rate(database_query_duration_seconds_count[1h]))
) < 0.999
and
(
  sum(rate(database_query_duration_seconds_bucket{le="0.1"}[6h]))
  /
  sum(rate(database_query_duration_seconds_count[6h]))
) < 0.999

# Inspect blocked lock chains without table acquisition
SELECT blocked.pid AS blocked_pid,
       blocking.pid AS blocking_pid,
       act.usename AS blocked_user,
       now() - act.query_start AS duration,
       act.query AS blocked_statement
FROM pg_catalog.pg_locks blocked
JOIN pg_catalog.pg_stat_activity act ON act.pid = blocked.pid
JOIN pg_catalog.pg_locks blocking 
    ON blocking.locktype = blocked.locktype
    AND blocking.database IS NOT DISTINCT FROM blocked.database
    AND blocking.relation IS NOT DISTINCT FROM blocked.relation
    AND blocking.pid != blocked.pid
WHERE NOT blocked.granted;