[Eval][v3][en] Payment service connection pool exhaustion after batch job deploy

Summary

Severity reasoning: User-facing outage: error rate > 1% for >5 min (12% for ~12 min), revenue path broken (failed checkouts). Matches SEV1 criteria.

deepseek-chat·prompt v3·output: en·11063ms·1990↑ / 1631↓ tok·$0.00233

Root cause hypotheses

• highNew cron job in payment-svc v2.41 runs a full table scan on ledger_entries every 30s, consuming connections and blocking other queries.

  Evidence: Deploy at 13:50 UTC added cron '*/30 * * * *' for settlement batch. Slow query log shows SELECT * FROM ledger_entries WHERE status='pending' (no index, 2.4s, 12M rows). Active connections at 500/500.

• mediumConnection pool leak in payment-svc v2.41 causes connections not to be released after queries.

  Evidence: Active connections at max, but no direct evidence of leak. Could be secondary factor.

• lowPostgres primary is under-provisioned for current load, leading to connection queueing.

  Evidence: CPU at 35%, not high. max_connections=500 is a hard limit, not a performance issue.

• lowNetwork issue between payment-svc and Postgres causing connection timeouts.

  Evidence: No network errors in logs; 'connection refused' is from Postgres rejecting new connections due to pool exhaustion.

Investigation checklist

1. Check if the new cron job is running and its query plan.

   kubectl logs -n prod -l app=payment-svc --since=30m | grep -i 'settlement\|cron\|batch' | tail -20

   Copy

   Expected: Entries showing execution of settlement batch every 30s.

2. Confirm the slow query and its impact on connections.

   SELECT pid, query_start, state, wait_event_type, query FROM pg_stat_activity WHERE state != 'idle' AND query LIKE '%ledger_entries%' ORDER BY query_start LIMIT 20;

   Copy

   Expected: Multiple queries with state 'active' and long query_start times.

3. Check if the query is missing an index.

   \d+ ledger_entries

   Copy

   Expected: No index on 'status' column.

4. Verify connection pool exhaustion.

   SELECT count(*) FROM pg_stat_activity WHERE state != 'idle';

   Copy

   Expected: Count near 500.

5. Check if rolling back the deploy resolves the issue.

   kubectl rollout undo deployment/payment-svc -n prod

   Copy

   Expected: Deployment rolled back to previous version.

Mitigation plan

• Immediately roll back payment-svc to v2.40 to remove the cron job.

  Risk: Brief increase in errors during rollback; no data loss.

  Rollback: Re-apply v2.41 if needed after fix.

• If rollback is not possible, kill the offending cron job process and disable the cron schedule.

  Risk: May need to identify the specific process; could miss if multiple instances.

  Rollback: Re-enable cron after fix.

• Temporarily increase max_connections in Postgres to 1000 to relieve pressure.

  Risk: May cause memory pressure on DB; monitor CPU and memory.

  Rollback: Set back to 500 after mitigation.

• Add an index on ledger_entries.status to speed up the query.

  Risk: Index creation locks table briefly; could cause short blip.

  Rollback: Drop index if needed.

Customer impact

Customers are experiencing failed checkouts and payment processing errors. Approximately 12% of checkout attempts are failing with HTTP 500 errors. The issue started around 14:02 UTC and is ongoing.

Postmortem draft

Follow-ups

• P0Add index on ledger_entries.status to prevent full table scans.— database-administrator
• P1Implement pre-deployment performance testing for new cron jobs/queries.— platform-team
• P1Add monitoring and alerting for database connection pool usage.— observability-team
• P2Update runbook for connection pool exhaustion and slow query handling.— on-call SRE
• P2Review deployment process to include changelog review for performance impact.— platform-team