Postmortem: the day Postgres took down production

TL;DR: A missing index on a foreign key column, combined with a cron job running during peak hours, caused a 47-minute outage on a billing API.

Timeline (all times UTC)

• 14:00 — Cron job starts: cleanup_old_invoices.sh
• 14:02 — Customer reports slow checkout
• 14:05 — PagerDuty alert: API latency p99 > 5s
• 14:08 — On-call engineer (me) opens laptop
• 14:14 — Identify: Postgres CPU 100%, hundreds of DELETE queries waiting
• 14:22 — Kill the cron job manually → queries drain in ~30s
• 14:25 — Service recovering
• 14:47 — Full recovery, replication caught up

Total impact: 47 minutes of degraded service, ~120 failed transactions.

Root cause

The cleanup script ran DELETE FROM invoices WHERE created_at < NOW() - INTERVAL '90 days'.

Two things went wrong:

1. created_at had no index — full table scan for every DELETE batch
2. Foreign key from invoice_items.invoice_id — Postgres had to verify no referencing rows on every DELETE, also a full scan

So each DELETE became:

SCAN invoices (~3M rows)        — no index
  → for each deleted row:
    SCAN invoice_items (~50M rows) — no index on FK

With 10,000 rows to delete, that’s ~3×10⁹ row checks. At ~2 minutes per batch, the cron job would have run for 2+ hours, locking the whole table.

Fix

1. Add the missing index:

   CREATE INDEX CONCURRENTLY idx_invoices_created_at ON invoices (created_at);
   CREATE INDEX CONCURRENTLY idx_invoice_items_invoice_id ON invoice_items (invoice_id);
   

2. Move the cron job to off-peak (3 AM, not 2 PM)
3. Add statement timeout: SET statement_timeout = '5min' in postgresql.conf
4. Add a pre-check: if a long-running query already exists, skip the cleanup

Lessons

• Always index foreign keys. Postgres doesn’t do this for you.
• Cron jobs don’t belong at peak hours. Unless they require it.
• Statement timeouts save you. Without it, a bad query will run forever.
• Run a load test. This bug existed for 6 months — only showed up when the table grew past a threshold.
• Document your incident. So the next person doesn’t relearn it.

Action items

• Add missing indexes
• Move cron to off-peak
• Add statement_timeout
• Run EXPLAIN on all DELETE/UPDATE queries >10K rows
• Quarterly: review slow query log