Production RDS Ran Out of Storage at 3 AM — Incident Response and RCA

# Check current storage allocation
aws rds describe-db-instances \
  --db-instance-identifier prod-postgres \
  --query 'DBInstances[0].{Storage:AllocatedStorage,MaxStorage:MaxAllocatedStorage,MultiAZ:MultiAZ}'

# Expand storage (takes 5-15 minutes, no downtime on Multi-AZ)
aws rds modify-db-instance \
  --db-instance-identifier prod-postgres \
  --allocated-storage 500 \     # Double from current 250GB
  --apply-immediately

# Monitor the modification progress
aws rds describe-db-instances \
  --db-instance-identifier prod-postgres \
  --query 'DBInstances[0].PendingModifiedValues'

# Wait for status to return to 'available'
aws rds wait db-instance-available \
  --db-instance-identifier prod-postgres

# Template for incident Slack message
echo "
[INC-2024-0115] RDS Storage Outage
Status: Mitigating
Impact: All write operations failing (orders, sessions)
Action: Expanding RDS storage from 250GB to 500GB (no downtime)
ETA: 15 minutes to full recovery
Owner: @on-call-engineer
"

-- Connect to PostgreSQL and check database sizes
SELECT 
  datname,
  pg_size_pretty(pg_database_size(datname)) as size
FROM pg_database
ORDER BY pg_database_size(datname) DESC;

-- Find the largest tables
SELECT
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) as total_size,
  pg_size_pretty(pg_relation_size(schemaname || '.' || tablename)) as table_size,
  pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename) 
    - pg_relation_size(schemaname || '.' || tablename)) as index_size
FROM pg_tables
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC
LIMIT 20;

-- Check if table has grown recently (look at row count vs size ratio)
SELECT
  relname as table_name,
  n_live_tup as live_rows,
  n_dead_tup as dead_rows,
  pg_size_pretty(pg_relation_size(oid)) as size,
  last_autovacuum,
  last_autoanalyze
FROM pg_stat_user_tables
ORDER BY pg_relation_size(oid) DESC
LIMIT 10;

-- Delete old log entries (use DELETE with LIMIT to avoid long lock times)
-- Run in batches to avoid blocking other queries
DO $$
DECLARE
  deleted_count INTEGER;
BEGIN
  LOOP
    DELETE FROM application_logs
    WHERE id IN (
      SELECT id FROM application_logs
      WHERE created_at < NOW() - INTERVAL '30 days'
      LIMIT 10000
    );
    
    GET DIAGNOSTICS deleted_count = ROW_COUNT;
    EXIT WHEN deleted_count = 0;
    
    PERFORM pg_sleep(0.1);  -- 100ms pause between batches
  END LOOP;
END $$;

-- After deleting, VACUUM to reclaim space
VACUUM ANALYZE application_logs;

# Enable RDS Storage Auto Scaling right now
aws rds modify-db-instance \
  --db-instance-identifier prod-postgres \
  --max-allocated-storage 1000 \   # Auto-scale up to 1TB max
  --apply-immediately

# Create proper CloudWatch alarm (70% used = 30% free)
aws cloudwatch put-metric-alarm \
  --alarm-name "RDS-prod-postgres-LowFreeStorage" \
  --alarm-description "RDS storage below 30% free" \
  --metric-name FreeStorageSpace \
  --namespace AWS/RDS \
  --dimensions Name=DBInstanceIdentifier,Value=prod-postgres \
  --statistic Average \
  --period 300 \
  --threshold 80000000000 \   # 80GB free = 30% of 250GB
  --comparison-operator LessThanThreshold \
  --alarm-actions arn:aws:sns:us-east-1:...:ops-critical \
  --ok-actions arn:aws:sns:us-east-1:...:ops-critical \
  --evaluation-periods 2