Postmortem: วันที่ Postgres ทำเว็บล่ม

สรุปสั้น: Index ที่หายไปใน foreign key column รวมกับ cron job ที่รัน ตอน peak hours ทำให้ billing API ล่ม 47 นาที

Timeline (เวลา UTC ทั้งหมด)

• 14:00 — Cron job เริ่ม: cleanup_old_invoices.sh
• 14:02 — ลูกค้าแจ้ง checkout ช้า
• 14:05 — PagerDuty alert: API latency p99 > 5s
• 14:08 — On-call engineer (ผม) เปิด laptop
• 14:14 — ระบุได้: Postgres CPU 100% มี DELETE queries รอหลายร้อยตัว
• 14:22 — Kill cron job แบบ manual → queries drain ใน ~30s
• 14:25 — Service เริ่มฟื้น
• 14:47 — ฟื้นเต็มที่ replication catch up แล้ว

ผลกระทบ: 47 นาทีที่ service ช้า ~120 transactions fail

ต้นเหตุ

Cleanup script รัน DELETE FROM invoices WHERE created_at < NOW() - INTERVAL '90 days'

มี 2 อย่างผิด:

1. created_at ไม่มี index — full table scan สำหรับทุก DELETE batch
2. Foreign key จาก invoice_items.invoice_id — Postgres ต้อง verify ว่าไม่มี referencing rows ในทุก DELETE ก็เป็น full scan

ทุก DELETE กลายเป็น:

SCAN invoices (~3M rows)        — ไม่มี index
  → สำหรับแต่ละ row ที่ลบ:
    SCAN invoice_items (~50M rows) — ไม่มี index บน FK

มี 10,000 rows ที่จะลบ นั่นคือ ~3×10⁹ row checks ที่ ~2 นาทีต่อ batch cron job จะรัน 2+ ชั่วโมง locking ทั้งตาราง

แก้ไข

1. เพิ่ม 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. ย้าย cron job ไป off-peak (3 AM ไม่ใช่ 2 PM)
3. เพิ่ม statement timeout: SET statement_timeout = '5min' ใน postgresql.conf
4. เพิ่ม pre-check: ถ้ามี long-running query อยู่แล้ว ข้าม cleanup

บทเรียน

• Index foreign keys เสมอ Postgres ไม่ทำให้คุณ
• Cron jobs ไม่ควรรันตอน peak เว้นแต่ต้องการจริง ๆ
• Statement timeouts ช่วยคุณ ถ้าไม่มี query แย่ ๆ จะรันไปตลอด
• Run load test bug นี้อยู่มา 6 เดือน — เพิ่งเห็นตอน table โตเกิน threshold
• Document incident ของคุณ เพื่อคนถัดไปไม่ต้องเรียนรู้ใหม่

Action items

• เพิ่ม indexes ที่หายไป
• ย้าย cron ไป off-peak
• เพิ่ม statement_timeout
• รัน EXPLAIN กับ DELETE/UPDATE queries ที่ >10K rows ทั้งหมด
• ทุกไตรมาส: ดู slow query log