[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·10429ms·1739↑ / 1585↓ tok·$0.00221

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 long-running queries that exhaust DB 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) with 87 waiting queries.

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

  Evidence: Active connections at max; but no evidence of leaked connections in logs (no 'connection not closed' warnings).

• lowExternal traffic spike causing increased load on payment-svc and DB.

  Evidence: No traffic spike observed in metrics; latency and error rate correlate with deploy time, not traffic.

Investigation checklist

1. Check if the cron job is the source of the slow query by examining its SQL and execution plan.

   kubectl exec -n prod deploy/payment-svc -- cat /app/cron/settlement.sh | grep -i 'SELECT.*ledger_entries'

   Copy

   Expected: Should show the SELECT query with WHERE status='pending' and no index hint.

2. Confirm the slow query is consuming connections by checking pg_stat_activity for the query.

   psql -h postgres-primary -U postgres -c "SELECT pid, query_start, state, wait_event_type, query FROM pg_stat_activity WHERE query LIKE '%ledger_entries%' AND state != 'idle' ORDER BY query_start;"

   Copy

   Expected: Multiple rows with the same query, long query_start times, and state 'active'.

3. Check if the cron job is scheduled to run every 30 minutes (as per changelog) and if it's currently running.

   kubectl get cronjob -n prod -l app=payment-svc -o jsonpath='{.items[*].spec.schedule}'

   Copy

   Expected: Should show '*/30 * * * *' or similar.

4. Verify the DB connection pool settings in payment-svc config.

   kubectl exec -n prod deploy/payment-svc -- cat /app/config.yaml | grep -A5 'connection_pool'

   Copy

   Expected: Should show max pool size (likely 500) and timeout settings.

5. Check if there are any other long-running queries or locks.

   psql -h postgres-primary -U postgres -c "SELECT pid, now() - pg_stat_activity.query_start AS duration, query, state FROM pg_stat_activity WHERE state != 'idle' ORDER BY duration DESC LIMIT 10;"

   Copy

   Expected: The slow query should be at the top with duration >2s.

Mitigation plan

• Immediately disable the cron job by scaling the cronjob to 0 replicas or suspending it.

  Risk: Nightly settlement batch will not run; may cause delayed reconciliation but no data loss.

  Rollback: Re-enable cron job by scaling back to 1 replica or unsuspending.

• Kill the long-running queries to free up connections.

  Risk: May cause incomplete settlement processing; safe as queries are SELECT only.

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

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

  Risk: Index creation may lock table briefly; consider CONCURRENTLY to avoid downtime.

  Rollback: Drop the index if it causes issues.

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 message and cannot complete purchases. No ETA for full recovery yet.

Postmortem draft

Follow-ups

• P0Add index on ledger_entries.status to prevent full table scans.— Database team
• P1Review and test all cron jobs in staging before production deploy.— Service owner
• P1Add monitoring for long-running queries and connection pool saturation.— Platform team
• P2Implement pre-deploy SQL review for any new queries in deployments.— Service owner
• P2Update runbook for payment-svc to include steps for connection pool exhaustion.— On-call SRE