Deleting a large number of rows (say, a million) in a single query feels efficient. It is not. It can crush your database.
When we run a large DELETE in one go, the database has to hold locks on every row it touches for the entire duration of the operation. If it takes 30 seconds to run, those locks are held for 30 seconds. Every other query that needs those rows is stalled, waiting.
On busy systems (high query or update load), this has a ripple effect. Reads pile up, writes get blocked, and your connection pool starts getting exhausted. This cleanup job will likely become a production incident :)
There’s also the transaction log (WAL) to think about. A massive DELETE generates a huge amount of log data in one shot, which can spike disk I/O and slow down replication. Your replicas can fall behind, sometimes significantly.
The fix is pretty simple - batch your deletes.
DELETE … WHERE … LIMIT 1000, then sleep for a small interval, then repeat. It’s slower in wall-clock time, but the locks are short-lived, the log writes are spread out, and your database stays responsive throughout.
Fun fact: Databases cannot protect you from yourself :) This is one of those things you learn once, usually the hard way. Don’t ask me how I did :)