Database SLOs

database_slos:
  postgresql:
    availability: 99.95%    # ~22 min downtime per month
    latency_p99: 50ms       # 99% of queries under 50ms
    latency_p999: 200ms     # 99.9% of queries under 200ms
    replication_lag: 5s      # replicas within 5 seconds of primary
    connection_saturation: 80%  # never exceed 80% of max_connections
    error_rate: 0.1%         # fewer than 0.1% of queries fail

  redis:
    availability: 99.99%
    latency_p99: 5ms
    hit_rate: 99%            # cache hit rate
    memory_usage: 80%        # never exceed 80% of maxmemory

-- PostgreSQL: query latency via pg_stat_statements
SELECT
    LEFT(query, 60) AS query,
    calls,
    ROUND(total_exec_time::numeric, 1) AS total_ms,
    ROUND(mean_exec_time::numeric, 1) AS avg_ms,
    ROUND(stddev_exec_time::numeric, 1) AS stddev_ms
FROM pg_stat_statements
WHERE calls > 1000
ORDER BY mean_exec_time DESC
LIMIT 10;

-- Redis latency monitoring
redis-cli --latency-history -i 5

-- Check slow queries log
SELECT * FROM pg_stat_statements
WHERE mean_exec_time > 100  -- queries averaging over 100ms
ORDER BY mean_exec_time DESC;

-- Track downtime incidents
CREATE TABLE downtime_incidents (
    id SERIAL PRIMARY KEY,
    started_at TIMESTAMP NOT NULL,
    ended_at TIMESTAMP,
    duration_seconds INTEGER,
    cause TEXT,
    impact TEXT
);

-- Calculate monthly availability
SELECT
    date_trunc('month', started_at) AS month,
    SUM(duration_seconds) AS total_downtime_seconds,
    ROUND((1 - SUM(duration_seconds)::numeric /
        EXTRACT(EPOCH FROM (date_trunc('month', started_at) +
            INTERVAL '1 month' - date_trunc('month', started_at)))
    ) * 100, 4) AS availability_pct
FROM downtime_incidents
WHERE started_at > NOW() - INTERVAL '12 months'
GROUP BY 1
ORDER BY 1 DESC;

-- Current connection usage
SELECT
    count(*) AS current_connections,
    current_setting('max_connections')::int AS max_connections,
    ROUND(count(*)::numeric / current_setting('max_connections')::int * 100, 1) AS usage_pct
FROM pg_stat_activity
WHERE backend_type = 'client backend';

-- Connection count over time (requires pg_stat_statements or logging)
SELECT
    date_trunc('hour', query_start) AS hour,
    COUNT(DISTINCT pid) AS unique_connections
FROM pg_stat_activity
WHERE backend_type = 'client backend'
  AND query_start > NOW() - INTERVAL '24 hours'
GROUP BY 1
ORDER BY 1;

-- Primary: replication lag per replica
SELECT
    client_addr,
    state,
    replay_lag,
    pg_wal_lsn_diff(sent_lsn, replay_lsn) AS lag_bytes,
    pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn)) AS lag_pretty
FROM pg_stat_replication;

-- Alert if lag exceeds threshold
-- Application: check replication health
SELECT
    CASE
        WHEN replay_lag > INTERVAL '5 minutes' THEN 'CRITICAL'
        WHEN replay_lag > INTERVAL '1 minute' THEN 'WARNING'
        ELSE 'HEALTHY'
    END AS status,
    replay_lag
FROM pg_stat_replication;

error_budget_policy:
  - name: "Healthy"
    condition: "budget_remaining > 50%"
    action: "Normal development velocity"
    deployment_frequency: "Normal"

  - name: "Caution"
    condition: "budget_remaining 20-50%"
    action: "Review deployment practices"
    deployment_frequency: "Reduced"

  - name: "At Risk"
    condition: "budget_remaining 5-20%"
    action: "Freeze non-critical changes"
    deployment_frequency: "Emergency only"

  - name: "Exhausted"
    condition: "budget_remaining < 5%"
    action: "Full freeze — focus on reliability"
    deployment_frequency: "Reliability fixes only"