CHAOS EXPERIMENT - DATABASE FAILOVER RESILIENCE

EXPERIMENT SCOPE: Production staging environment (identical to prod)
RISK LEVEL: Medium (staging only, no customer data)
APPROVAL: Security team signed off 2026-07-30
EXPERIMENT OWNER: @marcus (infrastructure)

════════════════════════════════════════════════════════════
HYPOTHESIS
════════════════════════════════════════════════════════════

"The API gracefully handles primary database failover to read replica
within 3 seconds, with <1% request loss and automatic retry queue."

Success criteria:
  ✓ Failover detection: < 3s
  ✓ Request loss: < 1% (target: 0%, acceptable: <1%)
  ✓ Error transparency: Users see "temporarily unavailable" not 500
  ✓ Automatic recovery: No manual intervention required

════════════════════════════════════════════════════════════
CURRENT STATE ANALYSIS
════════════════════════════════════════════════════════════

Database configuration:
  Primary: postgres-prod-01 (8GB RAM, 100GB storage)
  Replica: postgres-replica-01 (standby, streaming replication)
  Load balancer: HAProxy monitoring connection pool
  Failover mechanism: Manual via DNS change (CURRENT = slow)
  Recovery RTO: 15-20 minutes (manual)

API configuration:
  Connection pool: 50 connections
  Query timeout: 5 seconds
  Retry logic: 1 retry on timeout (current, needs upgrade)
  Circuit breaker: None (would prevent cascading)

Current weakness identified:
  Failover is MANUAL → downtime measured in minutes
  Goal: Make it automatic with <3s detection

════════════════════════════════════════════════════════════
CHAOS TEST PLAN (5 phases)
════════════════════════════════════════════════════════════

PHASE 1: BASELINE MEASUREMENT (10 min)
  ────────────────────────────────
  Load: 50 concurrent users, 5 req/sec
  Metrics collected:
    • Latency (p50, p95, p99)
    • Success rate (% requests answered)
    • DB connection pool utilization
    • Error rate by type
  
  Baseline results:
    Latency p50: 45ms | p95: 120ms | p99: 280ms
    Success rate: 99.8%
    DB connections: 35/50 (70% utilization)
    Errors: 0.2% (timeout, typically)

PHASE 2: SIMULATE PRIMARY DATABASE FAILURE (3 min)
  ────────────────────────────────
  Chaos method: Network partition (iptables block, 0 latency)
  Target: postgres-prod-01 (primary)
  Action: Block port 5432 → simulate "database gone"
  Expected behavior: Failover triggers, requests route to replica
  
  What happens:
    t=0s: iptables blocks postgres-prod-01 port
    t=0-1s: API detects connection failures (50 connections timeout out)
    t=1-2s: HAProxy marks primary as DOWN
    t=2s: Replica promoted (read-only mode, OK for reads)
    t=2-3s: Requests route to replica, resume executing
  
  OBSERVED METRICS (from chaos run):
    Detection time: 2.8 seconds (✓ meets <3s target)
    Requests failed during failover: 12/300 = 4% (✗ exceeds <1% target)
    Request retry success: 11/12 (92% recovery)
    Latency spike: p95 jumps from 120ms → 850ms, recovers in 4s
    Error messages: 500 Internal Server Error (✗ not transparent)

PHASE 3: MEASURE IMPACT (2 min)
  ────────────────────────────────
  Continue load (50 users, 5 req/sec) while primary is down
  
  Observations:
    • Replica handles 80% of traffic (it's read-only)
    • Writes fail immediately with "replica is read-only" error
    • Write queue builds up, customers see "Saving..."
    • After 60 seconds, queue has 145 pending writes
  
  Critical issue identified:
    Write operations cannot complete on read-only replica
    → No failover strategy for write workload
    → This is a BLOCKER for automatic failover

PHASE 4: RESTORE PRIMARY & MEASURE RECOVERY (5 min)
  ────────────────────────────────
  Chaos action: Remove iptables block, restore primary connection
  
  Recovery behavior:
    t=0s: iptables rule removed
    t=1s: HAProxy detects primary is back online
    t=1-2s: 145 queued writes processed (5s backlog)
    t=2-3s: Replica writes revert to primary (good)
    t=3-5s: All requests complete, latency normalizes
  
  Recovery metrics:
    Time to resume full traffic: 3 seconds (✓ good)
    Data loss: 0 (writes were queued, not lost) (✓ good)
    Consistency issues: 0 detected (✓ good)
    Customer-facing error resolution time: 3s (acceptable)

PHASE 5: CONTROLLED SHUTDOWN & ANALYSIS (5 min)
  ────────────────────────────────
  Stop chaos scenario, restore baseline, collect full logs

════════════════════════════════════════════════════════════
KEY FINDINGS
════════════════════════════════════════════════════════════

HYPOTHESIS ASSESSMENT: PARTIALLY FAILED ✗
  ✓ Failover detection: 2.8s (meets <3s)
  ✗ Request loss: 4% (exceeds <1% target, gap: 3%)
  ✗ Error transparency: Users see 500, not "temporarily unavailable"
  ? Automatic recovery: Worked, but required manual intervention to promote replica

BLOCKING ISSUES FOUND:

1. WRITE TRAFFIC CANNOT FAILOVER
   Severity: CRITICAL
   Issue: Replica is read-only, writes must go to primary
   During failover, write requests fail immediately
   No queue-and-retry strategy exists
   
   Solution required: Implement async write queue
     • Queue writes during failover (max queue size: 1000)
     • Replay queue when primary comes back
     • Customer sees "We're saving, please wait" state
   
   Estimated fix time: 8 hours (implementation + testing)
   Dependency: Need Kafka or similar for write queue

2. MANUAL REPLICA PROMOTION
   Severity: HIGH
   Issue: Replica doesn't auto-promote to primary
   Currently requires:
     a) Detect primary is down (done)
     b) Promote replica to primary (manual via script)
     c) Pause writes to prevent split-brain (manual)
   
   Solution: Use Patroni or similar automatic failover tool
   Estimated cost: $200/month (managed service)
   Estimated fix time: 2 days (setup + testing)

3. ERROR MESSAGING
   Severity: MEDIUM
   Issue: API returns generic 500, not customer-friendly message
   Solution: Intercept DB errors, return specific message
     "Our database is temporarily unavailable. Please try again in a few seconds."
   Estimated fix time: 2 hours

════════════════════════════════════════════════════════════
RECOMMENDATIONS (priority order)
════════════════════════════════════════════════════════════

IMMEDIATE (this sprint):
  1. Implement async write queue (Kafka) → reduces write failures 95%
  2. Add transparent error messaging → improves UX
  3. Set up automated failover (Patroni) → removes manual step

NEXT SPRINT:
  4. Add circuit breaker pattern → prevent cascading failures
  5. Increase connection pool from 50 → 100 → handle spike
  6. Add health check endpoint for monitoring

LONG-TERM (Q4):
  7. Multi-region failover (DR site in different availability zone)
  8. Read replicas in 3 geographic regions (for data locality)

════════════════════════════════════════════════════════════
REPEAT TEST SCHEDULE
════════════════════════════════════════════════════════════

Monthly failover test: Last Friday of each month, 2am UTC
Quarterly write queue stress test: After Kafka implementation
Post-deployment verification: After each fix is deployed

════════════════════════════════════════════════════════════
LOGS & ARTIFACTS
════════════════════════════════════════════════════════════

Full chaos log: /var/log/chaos-experiments/db-failover-2026-07-31.log
Metrics snapshot: Prometheus data points exported (24h retention)
Replay video: Screen recording of load test during chaos (5min)
Error stack traces: 12 unique errors captured and categorized

Share with team: Post-experiment report link (expires in 30 days)