How do you write a DELETE that purges 50 million old rows in bounded chunks?

## Why the statement needs a bound at all `DELETE FROM events WHERE created_at < DATE '2024-01-01'` is a correct, set-based statement, and on a 50-million-row purge it is also a single indivisible unit of work: one statement, one transaction, all fifty million rows, all the locks and all the log the engine needs to be able to undo it. Chunking replaces it with a few thousand small statements, each of which completes quickly and commits on its own. The question an interviewer is really asking is: *how do you express "at most N rows" in a DELETE?* ## Standard SQL has no DELETE ... LIMIT This surprises people, because `SELECT` has `FETCH FIRST n ROWS ONLY` (spelled `LIMIT n` in several engines) and `DELETE` looks like it should inherit it. It does not: the standard's `DELETE` is `DELETE FROM <table> [WHERE <predicate>]`, full stop. Engines add their own spellings — MySQL supports `DELETE ... ORDER BY ... LIMIT n`, SQL Server supports `DELETE TOP (n) FROM ...` — but PostgreSQL has neither, and none of them is portable. So the bound must live in the `WHERE` clause. ## Shape 1: the key-window delete Drive the chunks off a monotonic key and let the caller advance the window: ```sql DELETE FROM events WHERE created_at < DATE '2024-01-01' AND id >= :lo AND id < :lo + 5000; ``` Each execution touches at most the ids in that window. The caller starts at the table's minimum id and advances `:lo` by the window size, stopping when it passes the maximum id at the time the purge started. The great property here is that the work **moves forward**: chunk k never re-examines the ground chunk k−1 already cleared, so the purge does not get slower as it proceeds. The cost is that windows are id ranges, not row counts — a sparse range deletes few rows, a dense one deletes the full 5,000. ## Shape 2: the key-subquery delete When there is no usable monotonic key, or you want an exact row cap per statement, select the keys first and delete by them: ```sql DELETE FROM events WHERE id IN (SELECT id FROM events WHERE created_at < DATE '2024-01-01' ORDER BY id FETCH FIRST 5000 ROWS ONLY); ``` The inner `SELECT` is where the standard's row-limiting clause is legal, so this is the portable way to cap a `DELETE` at exactly n rows. Two caveats. First, some engines refuse a subquery that reads the same table the statement modifies; the usual workaround is to wrap it in a derived table, `... WHERE id IN (SELECT id FROM (SELECT id FROM events WHERE ... ORDER BY id FETCH FIRST 5000 ROWS ONLY) AS batch)`. Second, if the inner query has to scan past rows it already deleted to find the next 5,000, the purge degrades over time; keeping the `ORDER BY` on the same key the predicate ranges over, or switching to shape 1, avoids that. ## Terminating the loop The loop condition is **the affected-row count of the last statement**, which every client API exposes. Repeat while it is greater than zero (for shape 2), or while the window has not passed the maximum key (for shape 1). What you must *not* do is compute `ceil(row_count / 5000)` up front and run that many iterations: rows keep being inserted and deleted while the purge runs, so the number is stale before the first chunk finishes, and the job either stops early leaving rows behind or wastes passes on nothing. SQL itself has no loop construct here — the loop lives in the client, a job scheduler, or a procedural extension, and only the statement is the language's concern. ## Restartability Because each chunk commits on its own, a purge that dies halfway has simply done less work, and re-running it is safe: the predicate `created_at < :cutoff` still selects exactly the rows that remain to be deleted. That idempotence is a property of writing the chunk predicate over the *data*, not over a position — which is also why a chunk driven by "skip the first N rows" is a bad idea: rows disappearing underneath a positional cursor causes it to skip rows entirely. ## When chunking is the wrong tool If you are deleting the overwhelming majority of a table, copying the survivors out and swapping is often dramatically cheaper than deleting row by row: `INSERT INTO events_new SELECT ... FROM events WHERE created_at >= :cutoff`, then rename. That is a schema-level operation with its own coordination cost, but for a 95%-purge it is worth raising. ## What a strong answer contains Name the missing `LIMIT` on `DELETE`, show one of the two bounded shapes with real syntax, state that the loop terminates on affected rows, note that the chunk predicate should be one an index can serve so each chunk stays fast, and mention the copy-and-swap alternative for near-total purges.