delay.run

Posted on Nov 5

Our nightly cleanup job deleted the wrong things. Here's how I'd build it today

#database #cron #backend #postgres

Every app eventually gets one: the cleanup job. Delete expired sessions. Purge soft-deleted rows after 30 days. Remove unconfirmed sign-ups. Trim old logs.

It's usually written in an afternoon and scheduled at 3am:

-- cleanup.sql, run nightly by cron
DELETE FROM sessions WHERE created_at < now() - interval '30 days';
DELETE FROM users WHERE confirmed = false AND created_at < now() - interval '7 days';
DELETE FROM events WHERE created_at < now() - interval '90 days';

It's the most boring code in the system, and it's the one scheduled job where a bug can't be fixed with a retry. A failed email job sends late. A failed report job reruns. A cleanup job that deletes the wrong thing is just... done.

Here's what I'd do differently, built from the classic ways these jobs go wrong.

1. Never write a bare DELETE in a scheduled job

The users line above looks fine until someone adds an "import users from CSV" feature that creates accounts with confirmed = false by default, for users who log in via a different flow. A week later, the cleanup job quietly removes every imported customer.

Today I'd select first, check, then delete by ID:

def cleanup_unconfirmed_users(db, dry_run=False, max_rows=500):
    rows = db.fetch_all("""
        SELECT id, email, created_at FROM users
        WHERE confirmed = false
          AND source = 'self_signup'          -- be explicit about WHICH users
          AND created_at < now() - interval '7 days'
        LIMIT %s
    """, [max_rows + 1])

    if len(rows) > max_rows:
        raise RuntimeError(
            f"Refusing to delete: more than {max_rows} candidates. "
            "Investigate before raising the limit."
        )

    ids = [r.id for r in rows]
    log.info("cleanup_unconfirmed_users candidates=%d dry_run=%s", len(ids), dry_run)
    if dry_run or not ids:
        return len(ids)

    db.execute("DELETE FROM users WHERE id = ANY(%s)", [ids])
    return len(ids)

Two changes that matter:

An explicit positive condition (source = 'self_signup') instead of "everything that isn't confirmed". Delete what you meant, not "whatever matches".
A circuit breaker. If the job suddenly wants to delete 10x more than usual, something changed. Stop and alert instead.

2. Soft delete first, hard delete later

The safest cleanup is two-stage:

-- Stage 1 (nightly): mark
UPDATE users SET pending_purge_at = now()
WHERE id = ANY(%s) AND pending_purge_at IS NULL;

-- Stage 2 (nightly, separate job): purge what has been marked for 7+ days
DELETE FROM users
WHERE pending_purge_at < now() - interval '7 days';

Now a bad rule gives you a week to notice and run UPDATE users SET pending_purge_at = NULL WHERE .... Stage 2 can also refuse to run if stage 1 marked an unusual number of rows.

3. Delete in batches, not one giant statement

DELETE FROM events WHERE created_at < now() - interval '90 days';

On a big table, this can hold locks for a long time, bloat the database, fill up replication lag and make the 3am job collide with the 6am traffic. Batch it:

while True:
    n = db.execute("""
        DELETE FROM events
        WHERE id IN (
            SELECT id FROM events
            WHERE created_at < now() - interval '90 days'
            ORDER BY id
            LIMIT 5000
        )
    """)
    if n == 0:
        break
    time.sleep(0.2)   # let replicas and other queries breathe

For very large, time-based tables, partitioning by month and dropping old partitions is usually much better than deleting rows at all.

4. Pin "now" at the start of the run

If the job runs for 40 minutes and every statement calls now() separately (or every batch computes its own cutoff), different parts of the job use different cutoffs. Compute the cutoff once and pass it everywhere:

cutoff = job_scheduled_time - timedelta(days=90)   # not "now" at each step

Using the scheduled time rather than the start time also makes reruns deterministic: if the job is retried two hours later, it deletes the same set it would have deleted originally.

5. Log what you deleted, not just that you deleted

"cleanup ok" in a log is useless during an incident. I want:

cleanup_unconfirmed_users cutoff=2026-10-27T03:00Z candidates=37 deleted=37 dry_run=false
cleanup_events cutoff=2026-08-05T03:00Z batches=12 deleted=58211

And for anything user-facing, I'd write the deleted IDs (not the data) to an audit table. When support asks "what happened to this account?", you have an answer.

6. Treat retention rules as product decisions, not code details

"Delete unconfirmed users after 7 days" is a business rule. So is "keep invoices for 7 years", which may be a legal requirement. The person writing the cron job shouldn't be the only person who knows these numbers.

Today I'd keep retention rules in one reviewed config file:

retention:
  sessions:          { keep_days: 30 }
  unconfirmed_users: { keep_days: 7, source: self_signup }
  events:            { keep_days: 90 }
  invoices:          { keep_days: 2555, note: "legal requirement, do not change without review" }

7. Make sure the job actually runs

The opposite failure is just as common: the cleanup job silently stopped months ago. The database grows, privacy promises ("we delete X after 30 days") quietly become false, and nobody notices until disk alerts fire.

Whatever runs it (cron, a cloud scheduler, or a hosted scheduler like delay.run), add a check that it ran and that it found a plausible number of rows. Zero deletions for a month is as suspicious as ten times the normal amount.

The checklist version

Explicit positive conditions, never "everything except".
A maximum-rows circuit breaker.
Soft delete, then hard delete after a grace period.
Batches, or partition drops for big tables.
One cutoff per run, based on the scheduled time.
Logs with counts and cutoffs, audit IDs for user data.
Retention rules in reviewed config.
Alerts for "didn't run" and "unusual count".

Top comments (1)

Lycore Development • Nov 5

The circuit breaker is the part I'd copy first. One refinement: since the SELECT and the DELETE by ID are separate statements, a row could change between them, so repeating the key predicates in the DELETE (or using DELETE ... RETURNING and logging what came back) keeps the audit trail honest. How do you pick max_rows, a fixed number or a multiple of the trailing average?

DEV Community