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

Summary

Severity reasoning: User-facing outage with error rate >1% for >5 minutes (12% for ~10 minutes) and revenue path broken (failed checkouts).

deepseek-chat·prompt v2·output: en·8977ms·1739↑ / 1399↓ tok·$0.00201

Root cause hypotheses

• highNew cron job in payment-svc v2.41 runs SELECT * FROM ledger_entries WHERE status='pending' every 30s, causing full table scan and blocking connections.

  Evidence: Deploy at 13:50 UTC added cron job; slow query log shows this query running every 30s with ~2.4s execution; active_connections at max (500).

• mediumConnection pool leak in payment-svc due to bug in v2.41, not releasing connections after query.

  Evidence: Application logs show 'too many clients already' but no direct evidence of leak; could be secondary effect.

• lowPostgres primary overloaded by other services due to shared database.

  Evidence: No evidence of other services; CPU is low (35%), only waiting queries are from the slow query.

Investigation checklist

1. Check the cron job definition in the new deployment.

   kubectl get cronjob -n prod -l app=payment-svc -o yaml | grep -A10 'schedule:'

   Copy

   Expected: Should show schedule */30 * * * * and command containing 'ledger_entries'.

2. Confirm the slow query is the one from the cron job.

   kubectl logs -n prod -l app=payment-svc --since=15m | grep -E 'SELECT.*ledger_entries.*status' | head -5

   Copy

   Expected: Log lines showing the query execution.

3. Check current active connections and waiting queries on Postgres.

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

   Copy

   Expected: Active connections near 500, waiting queries >0.

4. Verify no other recent deployments or changes.

   kubectl rollout history deployment/payment-svc -n prod

   Copy

   Expected: Only revision 2.41 at 13:50 UTC.

Mitigation plan

• Rollback payment-svc to previous version (v2.40) to remove the cron job.

  Risk: Rollback may cause brief downtime; ensure no data loss from in-flight transactions.

  Rollback: Re-deploy v2.41 by running: kubectl rollout undo deployment/payment-svc -n prod --to-revision=1

• Kill the offending cron job if rollback is not immediate.

  Risk: Cron job may be recreated by Kubernetes; temporary fix.

  Rollback: Re-apply the cron job manifest if needed.

• Add an index on ledger_entries.status to prevent full table scan.

  Risk: Index creation may lock table briefly; test on replica first.

  Rollback: DROP INDEX IF EXISTS idx_ledger_entries_status;

Customer impact

Customers are experiencing failed checkouts and payment processing errors. Approximately 12% of checkout attempts are failing with HTTP 500 errors. Affected users see an error page or timeout. No ETA yet.

Postmortem draft

Follow-ups

• P0Add index on ledger_entries.status column— Database team
• P1Review and restrict cron job deployments to require approval— Platform team
• P1Add alert on active connections approaching max_connections— Observability team
• P2Update runbook for payment-svc with connection pool exhaustion steps— Service owner
• P1Conduct postmortem and document timeline— On-call SRE