Summary

Severity reasoning: User-facing outage: customers cannot complete checkouts (failed transactions), revenue impact, and high error rate. Scope is all users hitting payment-svc. Reversibility: rolling back the deploy and killing the cron job can restore service, but data integrity of pending entries must be verified.

deepseek-chat·prompt v1·output: en·11636ms·1358↑ / 1651↓ tok·$0.00218

Root cause hypotheses

• highNew cron job in v2.41 runs a full table scan on ledger_entries every 30s, consuming all DB connections and causing connection exhaustion.

  Evidence: Deploy at 13:50 UTC added 'nightly settlement batch (cron: */30 * * * *)'. Slow query log shows SELECT * FROM ledger_entries WHERE status='pending' (no index, 2.4s per execution). Active connections at 500/500.

• lowA sudden traffic spike from external clients overwhelmed the DB connection pool.

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

• mediumA deadlock or long-running transaction held connections, causing new queries to queue and timeout.

  Evidence: No deadlock logs mentioned; waiting_queries=87 suggests queuing, but root cause is connection exhaustion from slow query.

Investigation checklist

1. Confirm the cron job process is running and consuming connections.

   kubectl exec -n production deploy/payment-svc -- ps aux | grep -i cron

   Copy

   Expected: A process matching the cron schedule (e.g., 'settlement' or 'batch') should be visible.

2. Check the number of active connections per user/application in PostgreSQL.

   psql -h postgres-primary -c "SELECT usename, count(*) FROM pg_stat_activity GROUP BY usename ORDER BY count DESC;"

   Copy

   Expected: payment-svc user likely has many connections (near 500).

3. Identify the slow query and its execution plan.

   psql -h postgres-primary -c "EXPLAIN ANALYZE SELECT * FROM ledger_entries WHERE status='pending';"

   Copy

   Expected: Seq Scan on ledger_entries (cost=... rows=... actual time=...). No index used.

4. Check if the cron job is the only source of the slow query.

   grep -r 'ledger_entries' /etc/cron.d/ /var/spool/cron/ 2>/dev/null || kubectl exec -n production deploy/payment-svc -- cat /proc/1/cmdline | tr '\0' ' '

   Copy

   Expected: Cron entry or command line referencing settlement batch.

5. Verify the deploy manifest for v2.41 to confirm the cron addition.

   kubectl get deploy payment-svc -n production -o yaml | grep -A5 'cron'

   Copy

   Expected: A cron schedule definition (e.g., '*/30 * * * *') in the container args or config.

Mitigation plan

• Immediately kill the cron job process to free connections. Use SIGTERM first, then SIGKILL if needed.

  Risk: The cron job may be in the middle of processing; pending entries could be left in an inconsistent state. However, this is a read-only query (SELECT), so no data corruption risk.

  Rollback: If killing the process does not free connections, restart the payment-svc pod: kubectl rollout restart deploy/payment-svc -n production

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

  Risk: Index creation on a 12M row table may cause brief lock contention. Safer to create concurrently: CREATE INDEX CONCURRENTLY idx_ledger_entries_status ON ledger_entries(status);

  Rollback: DROP INDEX IF EXISTS idx_ledger_entries_status;

• Reduce max_connections temporarily to prevent overload, or increase connection pool limits in payment-svc.

  Risk: Reducing max_connections may cause legitimate requests to fail if pool is too small. Increase gradually after index is created.

  Rollback: Set max_connections back to original value (500) in postgresql.conf and reload.

Customer impact

Customers are unable to complete checkouts, receiving HTTP 500 errors. Approximately 12% of all payment requests 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
• P1Implement alert on DB connection utilization >80%.— platform team
• P1Add pre-deploy performance testing for new cron jobs (e.g., run EXPLAIN ANALYZE on queries).— service owner
• P2Review deploy process to require approval for changes that add background jobs.— platform team
• P2Update runbook for payment-svc with steps to handle connection exhaustion.— on-call SRE