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

Summary

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

deepseek-chat·prompt v2·output: en·11153ms

Root cause hypotheses

• highNew cron job in payment-svc v2.41 runs a slow query (SELECT * FROM ledger_entries WHERE status='pending') every 30s, consuming connections and blocking other queries.

  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 a bug in v2.41, causing connections not to be released.

  Evidence: Application logs show 'too many clients already' but no direct evidence of leak; connection count is exactly max_connections.

• lowExternal traffic spike causing legitimate connections to exceed max_connections.

  Evidence: No traffic spike reported; error rate correlates with deploy time, not traffic pattern.

Investigation checklist

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

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

   Copy

   Expected: A cron job with schedule */30 * * * * and a command that runs the slow query.

2. Identify the exact query and its execution plan.

   SELECT query, calls, total_exec_time, rows, mean_exec_time FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

   Copy

   Expected: The slow query with high total_exec_time and mean_exec_time > 2s.

3. Check if the query has an index on status.

   \d+ ledger_entries

   Copy

   Expected: No index on status column.

4. Check current active connections and their states.

   SELECT state, count(*) FROM pg_stat_activity GROUP BY state;

   Copy

   Expected: Many connections in 'active' state running the slow query.

5. Check if the cron job is still running and its resource usage.

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

   Copy

   Expected: A pod with name containing 'cron' or 'settlement'.

Mitigation plan

• Immediately scale down the cron job by suspending it: kubectl patch cronjob -n prod <cronjob-name> -p '{"spec":{"suspend":true}}'

  Risk: Nightly settlement batch will not run; financial reconciliation may be delayed. No data loss.

  Rollback: Resume the cron job: kubectl patch cronjob -n prod <cronjob-name> -p '{"spec":{"suspend":false}}'

• Increase max_connections temporarily to 1000 to allow existing queries to complete and restore service.

  Risk: May increase DB memory pressure; monitor CPU and memory. Safe if DB has headroom.

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

• Kill the slow queries to free connections immediately.

  Risk: May cause partial data loss for the batch job; safe as it's a SELECT.

  Rollback: No rollback needed; queries can be re-run.

Customer impact

Customers are experiencing failed checkouts and payment processing errors. Approximately 12% of requests are failing with HTTP 500. Affected users see an error message and cannot complete purchases. Estimated impact: all users attempting checkout during the incident window.

Postmortem draft

Follow-ups

• P0Add index on ledger_entries.status to prevent full table scans.— Database team
• P0Review and optimize the settlement batch query to use index or reduce frequency.— Payment service team
• P1Add pre-deploy performance regression tests for new queries (e.g., EXPLAIN ANALYZE in CI).— Platform team
• P1Set up alerting on connection pool usage (e.g., >80% max_connections).— Observability team
• P2Document runbook for Postgres connection exhaustion incident.— SRE team