Cleanup at scale

A nightly job deletes expired links. One morning you check and not a single row was deleted, and the same last night. Why?

Before this: The vanity alias race

In 30 seconds

The problem: The nightly cleanup deletes nothing, night after night, and the table keeps growing.
Why it happens: One DELETE is one transaction. It runs past the statement timeout, so Postgres cancels it and undoes every row it had deleted. Tomorrow's run is longer still.
The fix: Delete in short batches over the primary key, each its own tiny transaction, so progress sticks.
In an interview: Never delete millions of rows in one statement. Delete in short batches over the primary key, and at real scale partition by time so cleanup is DROP PARTITION.

Why it matters

Links expire, so old rows have to go. The obvious job is one line of SQL, and it works on staging with 2,000 rows.

In our run, scaled down to a laptop, 300,000 expired links sat next to 30,000 live ones. The timeout was set between the time one 2,000-row batch takes and the time the one-shot DELETE takes. The one-shot hit it and rolled back: all 300,000 expired links were still there. Production is the same story with a 30-second timeout and billions of rows.

It did not block redirects: reads in Postgres never wait on a DELETE, and 50 visitors were redirected fine during the run. The damage is quieter. The job never finishes, and while it runs it locks every row it touches, writes a mountain of log that makes replicas fall behind, and holds back vacuum.

Words you need

Transaction: A group of changes the database treats as one: all of them are saved, or none of them.
Statement timeout: A limit on how long one query may run. Past it, the database cancels the query and undoes what it did.
Batching: Collecting many small changes and writing them once. 10,000 clicks become one UPDATE.
Partition: A big table split into pieces, for example one per month. Dropping an old piece is instant, with no row-by-row delete.

Picture it

300,000 links have expired. The cleanup starts at midnight.

One DELETE for everything

DELETE starts
Deletes rows for a while
Hits the statement timeout
Postgres rolls everything back

0 rows deleted. Tomorrow there are more rows, so it fails again.

Short batches

Delete ids 1 to 2,000
Commit
Delete ids 2,001 to 4,000
Commit
And so on

Each batch takes milliseconds. If the job dies at batch 80, batches 1 to 79 are already done.

The fixes people try

One DELETE for everything
Fails
DELETE FROM links WHERE expires_at < now()
One transaction. It runs past the statement timeout or the next deploy, and Postgres undoes every row.
A bigger timeout
Half a fix
SET statement_timeout = 0
Lets it finish, once. Meanwhile every deleted row stays locked until the end, the log balloons, replicas fall behind and vacuum is held back. Tomorrow's table is bigger.
Batches with LIMIT
Half a fix
WHERE id IN (SELECT id … WHERE expires_at < now() LIMIT 2000)
Progress sticks, but every batch rescans the dead index entries the earlier batches left, so it slows down as it goes. Fine for small tables.
Check expiry when a link is opened
Half a fix
if (link.expires_at < now()) return 404
Expired links stop working at once, but their rows are still there and storage keeps growing. Pair it with a cleanup, not instead of one.
Primary-key range batches
Works
DELETE … WHERE id >= $1 AND id < $2 AND expires_at < now()
Each batch is a short transaction, and walking the key never revisits a range. If the job is killed, it can resume.
Partition by month, drop the old one
Works
DROP TABLE links_2026_09
The real answer at billions of rows. Dropping a whole month is instant, with no row-by-row delete and no bloat.

The fix and its cost

Walk the primary key in ranges of a couple of thousand ids and delete the expired rows in each range. Every range is its own transaction: a few milliseconds, then commit. In production add a short sleep between batches so replicas and vacuum can keep up.

Why ranges and not LIMIT? A LIMIT subquery has to skip the dead index entries earlier batches left behind, so each batch is slower than the last, and in our prototype it crept toward the timeout. Walking the primary key never revisits a range.

What it costs

Cleanup now takes longer on the clock, and it is a loop with a position to resume from instead of one statement. At billions of rows even batches are slow, so partition the table by month and drop old partitions instead.

Interviewers also listen for: lazy expiry

Check expires_at when a link is opened and return 404, so an expired link stops working at once. The cleanup job then only reclaims space, which means it is allowed to be slow.

Check yourself

1The DELETE is correct SQL. Why did it delete nothing?Show answerHide
It is one transaction. It ran past the statement timeout, so Postgres cancelled it and rolled back every row, not just the unfinished part.
2Does it block redirects while it runs?Show answerHide
No. Reads in Postgres do not wait on a DELETE. The cost is load, replicas falling behind, held-back vacuum, and a job that never catches up.
3Why walk the primary key and not use LIMIT?Show answerHide
A LIMIT subquery rescans the dead index entries the previous batches left, so it gets slower as it goes. Key ranges never revisit a range.

The code

GitHub

cleanup.bug.ts

import type { Pool } from "pg";

// Nightly job: one statement for every expired link.
export async function cleanup(db: Pool) {
  // One transaction. If it runs past the statement timeout,
  // Postgres cancels it and undoes every row it deleted.
  const res = await db.query("DELETE FROM links WHERE expires_at < now()");
  return `DELETED ${res.rowCount}`;
}

Example code. The tested version, with a proof test against real Postgres, lands in the repo.

Star the repo for the runnable version

Each trap in this world ships with the bug, the fix and a proof test that fires concurrent requests at a real database.

Star the repo See the folder

Remember

One statement is all or nothing. Delete in short batches, and say partitions when the table is huge.

Back to the world overview