Run a Bulk Delete with a Checkpoint Table and Dry Run

Stage the doomed rows into a checkpoint table, review the dry-run count, delete in bounded batches, and keep an instant restore path.

~45 mindatabasepostgresoperations

Task

Delete millions of rows safely by staging them first, verifying the selection, batching the delete, and retaining a one-statement restore.

When to use this

Retention policy requires deleting 30 million old sessions. A wrong WHERE clause yesterday cost another team a restore-from-backup weekend.

Prerequisites

A writable scratch schema for the checkpoint table
Enough disk to hold the doomed rows temporarily
A quiet window for the initial staging scan

Steps

1. Stage the exact selection into a checkpoint table.

CREATE TABLE purge_sessions_2026_10 AS
SELECT id FROM sessions
WHERE last_seen_at < now() - interval '18 months';
CREATE UNIQUE INDEX ON purge_sessions_2026_10 (id);

Expected output: The checkpoint table holds exactly the ids under review, and staging it is read-only with respect to production data.

2. Review the dry run before anything is deleted.

SELECT count(*) FROM purge_sessions_2026_10; SELECT min(last_seen_at), max(last_seen_at) FROM sessions WHERE id IN (SELECT id FROM purge_sessions_2026_10);

Expected output: A count of 30 million with a max last_seen_at safely in the past confirms the selection matches the retention policy.

3. Delete in bounded batches driven by the checkpoint.

while :; do n=$(psql -Atc "WITH batch AS (SELECT id FROM purge_sessions_2026_10 LIMIT 50000) DELETE FROM sessions WHERE id IN (SELECT id FROM batch) RETURNING id"); [ "$n" = "0" ] && break; psql -c "DELETE FROM purge_sessions_2026_10 WHERE id IN (SELECT id FROM batch)" 2>/dev/null || break; done

Expected output: Each iteration deletes fifty thousand rows, removes them from the checkpoint, and leaves the remaining checkpoint rows as live progress state.

4. Keep the instant restore path until sign-off.

psql -c "SELECT count(*) FROM purge_sessions_2026_10;"

Expected output: Zero means finished; the sessions just deleted are exactly the ids removed from the checkpoint, so mistakes restore with one INSERT SELECT from a backup of it.

5. Archive the checkpoint, then drop it.

pg_dump -t purge_sessions_2026_10 -f purge_2026_10.sql && psql -c "DROP TABLE purge_sessions_2026_10;"

Expected output: The id list is archived as the audit record of what was deleted and when, then the scratch table is dropped.

Troubleshooting

Problem

New rows match the predicate during the run

Solution

The checkpoint froze the selection, which is the point; the next scheduled run picks them up, so the delete never chases a moving target.

Problem

Batches slow to a crawl

Solution

Foreign keys recheck the parent delete per row; drop or defer the constraint during the purge window and rebuild it after, or batch by child table first.

Problem

Checkpoints accumulate as scratch debt

Solution

Each run must archive and drop its table; add the cleanup to the same runbook step as the archive so scratch schema stays clean.

Limitations

The checkpoint adds one full scan and temporary storage; for tiny purges a transaction is simpler.
The restore path restores rows, not dependent side effects such as emitted events; coordinate with downstream consumers.

Delete like you might be wrong

Every bulk delete disaster has the same shape: a WHERE clause that was one operator off, executed in one transaction, with the only recovery being a backup restore from hours earlier. The checkpoint pattern removes the courage requirement. Stage the doomed ids into a scratch table first, review the selection as data, delete in batches driven by that table, and keep the id list until the change is signed off. The restore path is then one INSERT SELECT, not one weekend.

-- Stage first, delete later
CREATE TABLE purge_sessions_2026_10 AS
SELECT id FROM sessions
WHERE last_seen_at < now() - interval '18 months';

-- The batch loop consumes the checkpoint
WITH batch AS (SELECT id FROM purge_sessions_2026_10 LIMIT 50000)
DELETE FROM sessions WHERE id IN (SELECT id FROM batch);

The checkpoint is progress and audit at once

Driving the delete from the staged id table gives you three properties for free: the selection is frozen and reviewable, remaining rows are a live progress meter, and the archived list answers exactly what was deleted, months later, when an auditor or a regression asks. A WHERE clause gives you none of that.

Bounded batches protect everyone else

Fifty-thousand-row deletes keep lock windows short, WAL bursty but manageable, and replication lag under control, while foreign keys stay valid at every commit boundary. The loop also survives interruption: rerun it and it continues from whatever the checkpoint still contains.