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

Summary

Severity reasoning: User-facing outage: customers cannot complete checkouts (revenue impact). 12% error rate and high latency affect a large subset of users. Reversible by disabling the cron job or adding an index, but immediate mitigation required.

deepseek-chat·prompt v1·output: en·9568ms·1358↑ / 1428↓ tok·$0.00194

Root cause hypotheses

• highNew cron job in payment-svc v2.41 runs a full table scan on ledger_entries every 30s, holding connections open and exhausting the pool.

  Evidence: Deploy at 13:50 UTC matches symptom onset. Slow query log shows SELECT * FROM ledger_entries WHERE status='pending' (no index, 2.4s per execution) running every 30s. active_connections at max (500).

• mediumConnection pool leak in payment-svc v2.41 causes connections not to be returned to pool after query execution.

  Evidence: Connection count hit max_connections rapidly; application logs show 'too many clients already'. However, the cron job pattern is more consistent with the periodic query.

• lowExternal DDoS or traffic spike overwhelming payment-svc and DB connections.

  Evidence: No evidence of traffic spike in provided context; error rate correlates with deploy time, not gradual increase.

Investigation checklist

1. Check if the new cron job is running and consuming connections.

   kubectl exec -n prod deploy/payment-svc -- ps aux | grep settlement

   Copy

   Expected: A process running the settlement batch (e.g., 'settlement-cron') should appear.

2. Confirm the slow query and its frequency.

   kubectl exec -n prod svc/postgres-primary -- psql -c "SELECT query, calls, total_time, rows FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5;"

   Copy

   Expected: The query 'SELECT * FROM ledger_entries WHERE status='pending'' should be near the top with high total_time.

3. Check current active connections and which application is using them.

   kubectl exec -n prod svc/postgres-primary -- psql -c "SELECT state, count(*) FROM pg_stat_activity GROUP BY state;"

   Copy

   Expected: Many connections in 'active' state, possibly all from payment-svc.

4. Verify the deploy rollout status.

   kubectl rollout status -n prod deploy/payment-svc

   Copy

   Expected: Rollout complete at 13:50 UTC.

5. Check if there is a feature flag to disable the cron job.

   kubectl exec -n prod deploy/payment-svc -- env | grep SETTLEMENT

   Copy

   Expected: An environment variable like SETTLEMENT_CRON_ENABLED=true might exist.

Mitigation plan

• Immediately disable the settlement cron job by scaling down the cron workload or toggling a feature flag.

  Risk: Settlement batch will not run, potentially delaying financial reconciliation. No data loss.

  Rollback: Re-enable the cron job after adding an index on ledger_entries.status.

• Kill existing slow queries to free up connections.

  Risk: May cause incomplete transactions; ensure no data corruption by checking for open transactions first.

  Rollback: No rollback needed; queries will be retried.

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

  Risk: Index creation may lock the table briefly; schedule during low traffic or use CONCURRENTLY.

  Rollback: DROP INDEX IF EXISTS idx_ledger_entries_status;

Customer impact

Customers are experiencing failed checkouts with HTTP 500 errors. Approximately 12% of checkout attempts are failing. No ETA yet; mitigation in progress.

Postmortem draft

Follow-ups

• P0Add index on ledger_entries.status to prevent full table scans.— service owner
• P1Set up alert on active_connections approaching max_connections.— platform team
• P1Add load testing for new cron jobs before production deploy.— service owner
• P2Review deploy process to require performance review for cron changes.— platform team