The app slows while a cleanup job inches along
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_scanforsessionsrises by about 4 a second. Before 13:30 it barely moved. sessionshas 30 million rows.\d sessionslists indexes onidandtoken, and the constraintFOREIGN 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