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

Summary

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

deepseek-chat·prompt v2·output: en·12715ms·1739↑ / 1860↓ tok·$0.00252

Root cause hypotheses

• highNew cron job in payment-svc v2.41 runs an unindexed query (SELECT * FROM ledger_entries WHERE status='pending') every 30s, consuming DB connections and causing connection pool exhaustion.

  Evidence: Deploy at 13:50 UTC added cron job; slow query log shows the query taking ~2.4s; active_connections at max (500); app logs show 'too many clients already'.

• mediumConnection pool leak in payment-svc due to a bug in v2.41, causing connections not to be released.

  Evidence: Active connections at max; no other changes; but CPU is normal, suggesting connections are idle or waiting.

• lowExternal traffic spike overwhelming payment-svc, causing cascading connection exhaustion.

  Evidence: No traffic spike reported; error rate correlates with deploy time; latency increase is consistent with DB bottleneck.

Investigation checklist

1. Check the cron job configuration in the new deployment to confirm the query and schedule.

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

   Copy

   Expected: Should show schedule */30 * * * * and a command containing 'SELECT * FROM ledger_entries WHERE status=pending'

2. Verify the query plan and index status on ledger_entries.

   EXPLAIN ANALYZE SELECT * FROM ledger_entries WHERE status='pending';

   Copy

   Expected: Should show Seq Scan on ledger_entries (cost=... rows=... actual time=... loops=1) and no index usage.

3. Check current active connections and their states on PostgreSQL.

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

   Copy

   Expected: Multiple connections showing the slow query or waiting state.

4. Check if the cron job is still running and consuming connections.

   kubectl get pods -n prod -l app=payment-svc --field-selector=status.phase=Running | grep -i cron

   Copy

   Expected: Pods named payment-svc-cron-* should be running.

5. Review payment-svc logs for connection pool errors.

   kubectl logs -n prod -l app=payment-svc --since=15m | grep -iE 'connection pool|too many clients|connection refused' | tail -50

   Copy

   Expected: Repeated 'FATAL: sorry, too many clients already' and 'connection refused' errors.

Mitigation plan

• Immediately scale down the cron job to stop the unindexed query: kubectl scale cronjob -n prod payment-svc-cron --replicas=0

  Risk: Stops the nightly settlement batch; no revenue impact as it's non-critical. No destructive ops.

  Rollback: Scale back up: kubectl scale cronjob -n prod payment-svc-cron --replicas=1

• Kill the slow queries currently running to free up connections: SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE query LIKE '%ledger_entries%' AND state = 'active';

  Risk: Terminates active queries; may cause incomplete batch processing but safe as batch can be re-run. No data loss.

  Rollback: No rollback needed; queries will be re-issued by cron if re-enabled.

• Increase max_connections temporarily to allow recovery: ALTER SYSTEM SET max_connections = 800; SELECT pg_reload_conf();

  Risk: May increase memory pressure on DB; monitor memory usage. Safe if memory headroom exists.

  Rollback: Set back to 500: ALTER SYSTEM SET max_connections = 500; SELECT pg_reload_conf();

Customer impact

Customers are experiencing failed checkouts and errors when trying to complete payments. Approximately 12% of payment attempts are failing with HTTP 500 errors. The issue started around 14:02 UTC and is ongoing. No ETA yet.

Postmortem draft

Follow-ups

• P0Add index on ledger_entries.status to prevent full table scans.— Database team
• P1Add monitoring alert for DB connection pool utilization > 80%.— Platform team
• P1Add slow query monitoring and alerting on PostgreSQL.— Platform team
• P1Implement pre-deployment query performance review for cron jobs.— Service owner
• P2Add integration test that runs the cron job query against a staging database with representative data.— QA team
• P2Review deployment process to require performance testing for new cron jobs.— Service owner