Skip to content
notes.vinhnq.com

Search

SpringMid14 min read

Cleanup cron jobs: batched deletes, archiving and partitions

Review 10 cards
On this page

Cron jobs series: 1. Cron expressions and @Scheduled · 2. Deleting old data safely

The system uses soft deletes (it flips status to INACTIVE instead of removing the row), so the table grows forever, indexes grow, and queries and backups slow down. This post shows how to write a cron job that backs up and then hard deletes rows that have been INACTIVE for more than X months without hurting the production database. Scheduling (cron expressions, @Scheduled, ShedLock, idempotency) is covered in part 1.

1. Deleting old data safely

Key idea: delete in small batches, one short transaction each, archiving before deleting inside that same transaction. The job must be able to stop halfway, run again, and raise an alarm when it doesn’t run.

The job is only fast if the table already has status, a technical updated_at column and an index on them from the design stage. A soft delete is a state in the entity’s lifecycle; the set of technical columns every entity table should have is discussed in The data modeling process and table templates.

Don’t delete it all in one go

Key idea: one big DELETE is one big transaction, and the whole database pays for it.

-- Dangerous on a large table
DELETE FROM orders WHERE status = 'INACTIVE' AND updated_at < now() - interval '6 months';

How to spot such a “too large” transaction running in production (innodb_trx.trx_rows_modified, pg_stat_activity), and the other three kinds of harmful transaction (long-running, stalled, abandoned), are in Harmful transactions.

Delete in batches, one transaction per batch

Key idea: repeat “fetch N ids, archive, delete, commit, pause” until no matching rows are left.

flowchart TD
  S[Job starts, acquires the lock] --> Q[Fetch up to N matching ids]
  Q --> E{Any rows left?}
  E -- no --> D[Log, emit metrics, release the lock]
  E -- yes --> A[Copy them to the archive table]
  A --> X[Delete those N rows]
  X --> C[Commit the batch transaction]
  C --> P[Short pause to let the DB breathe]
  P --> Q

PostgreSQL can archive and delete in a single statement (one transaction) with a data-modifying CTE, i.e. a WITH clause that contains INSERT/DELETE:

WITH batch AS (
    SELECT id
    FROM products
    WHERE status = 'INACTIVE' AND updated_at < :cutoff
    ORDER BY id
    LIMIT :batchSize
    FOR UPDATE SKIP LOCKED
),
archived AS (
    INSERT INTO products_archive              -- same columns as products + archived_at at the end
    SELECT p.*, now()
    FROM products p
    JOIN batch b ON b.id = p.id
    RETURNING id
)
DELETE FROM products p
USING archived a
WHERE p.id = a.id;

MySQL supports DELETE ... ORDER BY id LIMIT 1000 directly on a single table. So you can do INSERT INTO ... SELECT ... LIMIT and then DELETE the same IDs in one transaction.

Four things to think about:

  • An index for the filter: (status, updated_at) or a partial index WHERE status = 'INACTIVE' (an index holding only the rows that match). Without one, every batch is a full scan.
  • Batch size: 500-5000 rows depending on row width; measure each batch and keep it under a few seconds.
  • Foreign keys: delete children first (order_items before orders), or only delete a parent when all its children qualify too. Be careful with ON DELETE CASCADE on large tables: one parent row can drag tens of thousands of children into the same transaction.
  • Off-peak hours and a total time budget: stop after X minutes and continue next time. Because you delete by condition, resuming anywhere is correct.

Back up before you delete

Key idea: a hard delete can’t be undone, so you need a copy first; an archive table in the same DB is the only option that is atomic with the delete.

Archives are data too: they need a retention period and must follow personal-data regulations. Personal data a user asked you to erase may have to be truly deleted rather than archived.

The large-scale alternative: partitioning

Key idea: if the table is partitioned by time, cleanup means dropping a whole partition, like throwing out an entire drawer instead of picking out sheets one by one.

DELETE vs DETACH PARTITION numbers and the cost of partitioning

Real numbers are in the partitioning section of How joins run and when to partition a table. On PostgreSQL, removing one month of data (about 65,000 rows):

Approach Time Side effects
DELETE 316 ms produces WAL and dead tuples
DETACH PARTITION 2 ms none

That post also shows what partitioning costs: primary keys and unique constraints must include the partition key, and a query that doesn’t filter on the partition key has to read every partition.

Make it observable

Key idea: a failing job at least leaves logs; a job that never ran has nothing to log, so alert on “time of the last success”.

  • Log the start, the end, the number of rows archived/deleted and the duration.
  • Metrics: job_last_success_timestamp, rows_deleted_total, duration.
  • An alert for “the job hasn’t succeeded in 25 hours” (a dead man’s switch: an alarm that fires when the “I’m alive” signal stops arriving).

Exercises

The task

The task (paraphrased): finish the pet project, and write a job that runs at 00:00:00 on June 1 every year to delete INACTIVE records from the system’s tables.

Solution

The solution uses @Scheduled, ShedLock (@SchedulerLock) and an injected Clock, all explained in part 1.

The Spring cron expression: 0 0 0 1 6 * (sec min hour day month weekday), with zone = "Asia/Ho_Chi_Minh". I checked it: the next runs are 2026-06-01, 2027-06-01, 2028-06-01…

The key choices:

  • lockAtMostFor (3h) is longer than max-duration (2h) so the lock can’t expire while the job is still running.
  • Delete by condition, in batches: safe to rerun any number of times (idempotent), fine to stop halfway.
  • Archive and delete in the same transaction: no row is ever deleted without being backed up first.
Solution: full code (config, PurgeTarget, service, job) and suggested tests

Configuration, so the schedule can change without a rebuild:

jobs:
  purge-inactive:
    cron: "0 0 0 1 6 *"
    zone: "Asia/Ho_Chi_Minh"
    retention: P6M          # only delete rows that have been INACTIVE for over 6 months
    batch-size: 1000
    max-duration: PT2H
@ConfigurationProperties("jobs.purge-inactive")
public record PurgeProperties(String cron, String zone, Period retention,
                              int batchSize, Duration maxDuration) {}

Each table is described by a PurgeTarget; list order is deletion order (children before parents):

public record PurgeTarget(String table, String archiveTable) {}

@Configuration
@EnableConfigurationProperties(PurgeProperties.class)
class PurgeTargets {
    @Bean
    List<PurgeTarget> purgeTargets() {
        return List.of(
                new PurgeTarget("order_items", "order_items_archive"),
                new PurgeTarget("orders", "orders_archive"),
                new PurgeTarget("products", "products_archive"),
                new PurgeTarget("categories", "categories_archive"));
    }
}

The service does the real work, one transaction per batch:

@Service
@Slf4j
public class InactivePurgeService {

    private final JdbcTemplate jdbc;
    private final TransactionTemplate tx;
    private final List<PurgeTarget> targets;
    private final PurgeProperties props;
    private final Clock clock;

    public InactivePurgeService(JdbcTemplate jdbc, PlatformTransactionManager txManager,
                                List<PurgeTarget> targets, PurgeProperties props, Clock clock) {
        this.jdbc = jdbc;
        this.tx = new TransactionTemplate(txManager);
        this.targets = targets;
        this.props = props;
        this.clock = clock;
    }

    public void purgeAll() {
        Instant deadline = clock.instant().plus(props.maxDuration());
        OffsetDateTime cutoff = OffsetDateTime.now(clock).minus(props.retention());

        for (PurgeTarget target : targets) {
            long total = 0;
            int deleted;
            do {
                if (clock.instant().isAfter(deadline)) {
                    log.warn("Purge stopped at {} (time budget used up), {} rows so far", target.table(), total);
                    return;                                   // next run continues, since we delete by condition
                }
                deleted = tx.execute(status -> purgeBatch(target, cutoff));
                total += deleted;
            } while (deleted == props.batchSize());
            log.info("Purged {} rows from {}", total, target.table());
        }
    }

    private int purgeBatch(PurgeTarget t, OffsetDateTime cutoff) {
        // Table names come from internal config (never user input), so string formatting is fine here
        String sql = """
                WITH batch AS (
                    SELECT id FROM %1$s
                    WHERE status = 'INACTIVE' AND updated_at < ?
                    ORDER BY id
                    LIMIT ?
                    FOR UPDATE SKIP LOCKED
                ),
                archived AS (
                    INSERT INTO %2$s
                    SELECT src.*, now() FROM %1$s src JOIN batch b ON b.id = src.id
                    RETURNING id
                )
                DELETE FROM %1$s src USING archived a WHERE src.id = a.id
                """.formatted(t.table(), t.archiveTable());
        return jdbc.update(sql, cutoff, props.batchSize());
    }
}

The job only handles scheduling and the lock:

@Component
@Slf4j
class InactivePurgeJob {
    private final InactivePurgeService service;

    InactivePurgeJob(InactivePurgeService service) {
        this.service = service;
    }

    @Scheduled(cron = "${jobs.purge-inactive.cron}", zone = "${jobs.purge-inactive.zone}")
    @SchedulerLock(name = "purge-inactive", lockAtMostFor = "PT3H", lockAtLeastFor = "PT1M")
    void run() {
        long start = System.nanoTime();
        log.info("purge-inactive started");
        try {
            service.purgeAll();
            log.info("purge-inactive finished in {} ms", (System.nanoTime() - start) / 1_000_000);
        } catch (RuntimeException e) {
            log.error("purge-inactive failed", e);   // + metric/alert
            throw e;
        }
    }
}

Suggested tests:

  • Use Testcontainers PostgreSQL; insert ACTIVE, recent INACTIVE and old INACTIVE rows; inject a fixed Clock.
  • Run purgeAll() with batchSize = 2; assert only the old INACTIVE rows disappear from the main table and show up in the archive.
  • Run it again and assert nothing changes.

Remember

  • Explain why one DELETE of millions of rows hurts the whole database (locks, WAL/binlog, replicas, rollback).
  • Write the batched delete loop and name the four things to think about: index, batch size, foreign keys, time budget.
  • Know why archiving and deleting must share a transaction, and what to do when the backup goes to object storage.
  • Know when DROP PARTITION can replace batched deletes and when it can’t.
  • Explain a dead man’s switch and why a nightly job is safer than a once-a-year job.

Interview questions

Junior

What’s the difference between a soft delete and a hard delete? Why do you still need a hard-delete job?

A soft delete only marks the row; a hard delete removes it. With soft deletes alone the table grows forever, so a periodic job archives and then hard deletes rows past the retention period.

  • Soft delete (status = 'INACTIVE' or deleted_at): the data stays in the table, so you can restore it, keep history, and foreign keys don’t break.
  • The cost of never cleaning up: bigger indexes, slower queries and backups, and possibly breaking personal-data regulations.

Mid

Why should archiving and deleting happen in the same transaction?

So you never end up half done: either both the archive and the delete happen, or neither does.

  • Archive first, delete in a separate transaction: a job that dies in between leaves rows in both tables (and a rerun archives them twice).
  • Delete first and the archive fails: the data is gone.
  • One transaction: for example a single statement with a data-modifying CTE in PostgreSQL.
  • A backup to object storage can’t be atomic: confirm the file is written and the row counts match before deleting.
How does FOR UPDATE SKIP LOCKED help a batched delete job?

FOR UPDATE locks the selected rows so nobody changes them between archiving and deleting; SKIP LOCKED skips rows another transaction has locked instead of waiting for them.

  • The job never gets stuck behind a user’s transaction.
  • If two runs ever overlap, each one takes a different set of rows instead of fighting over the same ones.
  • Available in PostgreSQL 9.5+ and MySQL 8.0+.
How do you pick the batch size for a delete job?

Start around 500–5,000 rows depending on row width and the number of indexes, then measure and tune.

  • Goal: each batch commits in well under a few seconds, holds locks briefly, and leaves replication lag and WAL/binlog volume flat.
  • Too small: the total runtime balloons from per-transaction overhead. Too large: you’re back to the problems of one giant DELETE.
  • Add a short pause between batches and a cap on the total runtime.

Senior

How would you delete 50 million old rows from a live production table?

Not with a single DELETE: delete in small batches, each in a short transaction, archiving in the same transaction before deleting.

  • Index the filter.
  • Pause between batches and watch replication lag.
  • Run off-peak with a time budget.
  • Long term, partition by time so cleanup becomes DROP PARTITION.
How do you find out that a cron job has silently stopped running?

Use a dead man’s switch: alert when the last successful run is too old. Error logs and error alerts aren’t enough, because a job that doesn’t run produces no errors.

  • Every successful run records a job_last_success_timestamp metric (or pings a heartbeat service).
  • Alert when that value is older than the schedule plus a margin (25 hours for a daily job).
  • Add metrics for rows processed and run time so you also notice a job that runs but does nothing.

Useful phrases for interviews

  • “I’d never delete millions of rows in one statement; I’d archive and delete in small batches, each in its own transaction.”
  • “We alert when the job hasn’t reported success in a while, because a job that never runs doesn’t log anything.”
  • “A soft delete keeps the row for a while; a scheduled purge hard deletes it once the retention period is over.”
  • “The archive insert and the delete run in one statement, so a row can never disappear without being backed up.”
  • “Each batch locks its rows with FOR UPDATE SKIP LOCKED, so it never waits on rows someone else is working on.”
  • “I tune the batch size so each batch commits in well under a second and replication lag stays flat.”
  • “Because the delete is driven by a condition, the job can stop anywhere and pick up where it left off on the next run.”
  • “For append-only data like logs and events, I’d partition by month and drop old partitions instead of deleting rows.”

Notes based on the Fundamentals of Backend course (Ronin Engineer), rewritten in my own words.