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

Summary

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

deepseek-chat·prompt v2·output: en·10567ms·1739↑ / 1480↓ tok·$0.00210

Root cause hypotheses

• highNew cron job in payment-svc v2.41 runs a slow query every 30s, consuming connections and blocking others.

  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) running every 30s; active_connections at max 500.

• mediumConnection pool leak in payment-svc due to unclosed connections from the new code.

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

• lowExternal traffic spike causing increased connection demand.

  Evidence: No traffic spike observed; error rate correlates with deploy time, not traffic patterns.

Investigation checklist

1. Check if the slow query is from the new cron job by examining the application logs for the cron execution.

   kubectl logs -n prod -l app=payment-svc --since=30m | grep -E 'settlement|cron|ledger_entries' | head -20

   Copy

   Expected: Lines showing cron job execution and the slow query being logged.

2. Identify the source of connections consuming the pool.

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

   Copy

   Expected: Multiple connections running the slow SELECT * FROM ledger_entries query, all from payment-svc pods.

3. Check if the slow query can be terminated to free connections.

   SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE query LIKE '%ledger_entries%' AND state = 'active';

   Copy

   Expected: Terminates the blocking queries; connections freed.

4. Verify the deploy manifest for the cron job configuration.

   kubectl get deployment payment-svc -n prod -o yaml | grep -A 10 'cron'

   Copy

   Expected: Cron schedule and command for the settlement batch.

Mitigation plan

• Immediately terminate the slow queries to free connections and restore service.

  Risk: Terminating queries may leave ledger_entries in an inconsistent state; the batch may need to be re-run.

  Rollback: If service does not recover, restart payment-svc pods to reset connection pools.

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

  Risk: Rollback may cause brief downtime during deployment; ensure no data loss from the batch.

  Rollback: Re-deploy v2.41 if rollback causes issues, but first disable the cron via feature flag.

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

  Risk: Index creation locks the table briefly; may cause short latency spike.

  Rollback: Drop the index if it causes performance degradation.

Customer impact

Customers are experiencing failed checkouts and payment processing errors. Approximately 12% of requests are failing with HTTP 500. Affected users see 'payment failed' messages. Estimated impact: thousands of users over the last 15 minutes.

Postmortem draft

Follow-ups

• P0Add index on ledger_entries.status column— Database team
• P1Set up alert on Postgres active_connections > 80% max— Platform team
• P1Add slow query monitoring and alert for queries >1s— Observability team
• P2Review deploy process to require approval for new cron jobs— Service owner
• P1Implement feature flag for settlement batch to disable without rollback— Payment-svc team
• P2Update runbook for connection pool exhaustion— On-call SRE