Purging Old Rows at Scale: MySQL DELETE … LIMIT vs PostgreSQL Batch Loops

The events table kept ninety days of history by policy, which meant deleting about 41 million rows a month, 1.4 million a day. The first version of the retention job was the obvious one: a nightly DELETE FROM events WHERE occurred_at < NOW() – INTERVAL 90 DAY. On the MySQL 8.0 system it ran for fifty minutes, held row locks across a third of the table’s hot range, and stacked up so many waiting transactions that the connection pool exhausted and the API started timing out — the purge took the site down, not the traffic. After the PostgreSQL 16 migration, the ported job ran the same single statement in nine minutes, which felt like a victory until the autovacuum storm arrived an hour later: 41 million dead tuples to clean, table bloat doubling the on-disk size, and read replicas lagging behind on WAL replay while the cleanup churned.

Both incidents had the same root: a retention delete is not a query, it is a maintenance operation, and both engines punish doing it in one statement — just in different ways, on different clocks. MySQL’s pain is immediate, in lock scope and undo growth; PostgreSQL’s pain is deferred, in dead tuples and vacuum debt. This article is the field guide to the batched purge: the syntax each engine gives you, the failure modes we measured, and the partitioning exit that eventually retired the whole problem.

Why is one big DELETE the wrong shape on both engines?

On InnoDB, a DELETE does not remove anything; it marks records deleted and writes undo records so other transactions can still see the old versions, and the purge thread cleans up later. A 41-million-row delete therefore builds a massive undo segment inside a single transaction, grows the history list — the metric the history list versus vacuum notes explain in detail — and delays purge for every other long transaction on the system. It also holds next-key locks on everything it touches under the default isolation level, which is how our purge blocked unrelated inserts into recent ranges. And with row-based replication, the delete replays on every replica as 41 million individual row events, so a fifty-minute primary statement becomes a fifty-plus-minute replication lag window.

On PostgreSQL, the single DELETE is gentler up front — readers are never blocked by it, because MVCC keeps old row versions visible — but every deleted row becomes a dead tuple that autovacuum must later remove, and until it does, the table and its indexes carry the dead weight. Our nine-minute statement produced a table that was physically twice its live size, index scans that read past millions of dead tuples, and an autovacuum worker that ran flat out for hours while generating enough WAL to push the replicas into lag. Neither engine failed; both just charged the cost to a different account at a different hour.

How does the MySQL batched purge work, and where does it still bite?

MySQL is the friendlier engine for this job because DELETE accepts ORDER BY and LIMIT, so the standard pattern deletes in primary-key order, a bounded slice at a time, looping until the affected-row count drops below the batch size:

-- MySQL 8.0: run in a loop until ROW_COUNT() returns less than 5000
DELETE FROM events
WHERE occurred_at < NOW() - INTERVAL 90 DAY
ORDER BY id
LIMIT 5000;

-- between iterations: COMMIT, sleep ~200ms, then the next batch

The ORDER BY id matters twice: it makes each batch touch a contiguous key range, which keeps locks tight and lets the next batch resume from a warm buffer pool position, and it makes the batch boundary deterministic so a killed job can resume safely. We settled on 5,000-row batches after measuring lock-wait time per batch — at 20,000 rows a batch held its locks long enough to collide with the write path during peak; at 5,000 it never showed up in the slow log. The remaining sharp edges: with row-based replication, even batched deletes produce a steady stream of row events, so watch replica lag rather than primary runtime; the throttle sleep belongs between transactions, not inside one; and the job must record its progress, because the day it dies mid-run you want to know which id range is already clean.

What is the PostgreSQL batch pattern without LIMIT on DELETE?

PostgreSQL’s DELETE takes no LIMIT clause, so the batch boundary has to move into a subquery that selects the keys to delete. The shape that worked for us selects primary keys with a LIMIT, then deletes by key:

-- PostgreSQL 16: keys first, delete by primary key, loop until 0 rows
DELETE FROM events
WHERE id IN (
  SELECT id FROM events
  WHERE occurred_at < now() - interval '90 days'
  ORDER BY id
  LIMIT 5000
);

-- for very hot tables, FOR UPDATE SKIP LOCKED in the subquery
-- lets several purge workers share the range without colliding

Two details from the incident report. First, the subquery must use the primary key, not ctid tricks copied from old blog posts — ctid is a physical address that a concurrent vacuum can invalidate, and we lost an afternoon to a purge that skipped rows after an autovacuum ran between batches. Second, the sleep between batches is doing different work than on MySQL: it is not about locks, it is about giving autovacuum room to keep up, so we tuned autovacuum_vacuum_scale_factor down to 0.02 on this table and watched dead-tuple counts instead of lock waits. If several workers purge in parallel, the SKIP LOCKED pattern from the job-queue comparison applies verbatim — and on hot tables we ended up running exactly that, four workers, each claiming disjoint key ranges.

When does partitioning retire the whole problem?

When the retention boundary is a time column — which it almost always is — the eventual answer on both engines is to stop deleting rows and start dropping partitions. A monthly or weekly range-partitioned table turns the 41-million-row purge into ALTER TABLE … DROP PARTITION on MySQL or DROP TABLE / DETACH PARTITION on PostgreSQL: metadata operations that complete in milliseconds, release the disk immediately, produce no undo, no dead tuples, no replication flood. We resisted partitioning for two years because the migration looked scarier than the cron job, and in hindsight that arithmetic was backwards — the purge job paged someone three times, the partition migration paged nobody. The engine-by-engine mechanics, including DETACH PARTITION CONCURRENTLY and MySQL’s partition-pruning quirks, are covered in the partitioning comparison. Until the partition cutover ships, though, the batched loop above is the honest way to run retention, and it is what we ran in production for those two years.

Where MonPG fits when the purge is running

A batched purge has a healthy signature and an unhealthy one, and telling them apart is a monitoring problem. Healthy: short statements, flat lock-wait time, dead tuples draining between batches. Unhealthy: batch duration creeping up as the index degrades, dead tuples accumulating faster than vacuum removes them, replica lag climbing during the job window. MonPG’s PostgreSQL monitoring tracks per-statement runtime, table-level dead tuple ratios, and vacuum activity together, so a purge that starts losing the race against its own debris shows up as a trend before it shows up as a page.

MonPG monitors PostgreSQL today; MySQL support is on the roadmap. When it lands, the MySQL side of this article is what it will watch: history-list length and replication lag during purge windows. Until then, the MySQL monitoring page tracks that work. The portable lesson is one line: retention deletes are maintenance, not queries — batch them, throttle them, and measure the cleanup, not just the delete.

Related documentation

• MySQL: DELETE
• PostgreSQL: DELETE