The app slows while a cleanup job inches along

On-call incidentEasy15 min

Your task

It is 14:50. Your app runs on one Postgres 16 primary. At 13:30 the growth team started a one-off job to delete 2 million fake accounts that a bot created last month. The job runs 8 workers. Each deletes one account at a time with DELETE FROM accounts WHERE id = $1. This alert fires:

ApiLatencyP95: p95 above 800 ms for 15 minutes (current 2.2 s)

What you see:

  • p95, the time 95% of requests finish within, has risen on every endpoint since 13:30. Database CPU is 92%, up from 30%.
  • In pg_stat_statements, the job's delete has a mean of 2.1 s. The job has deleted about 18,000 accounts so far.
  • In pg_stat_user_tables, seq_scan for sessions rises by about 4 a second. Before 13:30 it barely moved.
  • sessions has 30 million rows. \d sessions lists indexes on id and token, and the constraint FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE CASCADE.

What do you do right now, and how do you get the job finished this week without slowing the app?

It covers

  • Foreign keys
  • Missing index
  • Bulk delete