cd ..

$ cat ~/field-notes/resumable-batch-jobs-checkpoints.md

Resumable Batch Jobs: Finish the Backfill Without Starting Over

A backfill updates nine million of ten million records before its worker restarts. The next run begins at row one because progress lived in a variable.

The immediate cost is wasted work. The more serious question is whether repeating that work changes the result.

A resumable job needs an input boundary, a durable checkpoint, and a definition of safe repetition.

Choose a Stable Traversal

For a table with an immutable numeric primary key, a simple traversal is:

SELECT id, source_value
FROM records
WHERE id > :last_id
  AND id <= :upper_id
ORDER BY id
LIMIT 500;

Unlike an offset, the cursor identifies a position in the key space. Deleting an earlier row does not shift that position. A unique ordering also avoids ambiguous batch boundaries; PostgreSQL explains why limited queries need predictable ordering.

Use an index that supports the range. Keep the cursor in its native representation: large database integers may not fit exactly in a JavaScript number.

Do not move the cursor column during the job. If you must sort by a non-unique timestamp, include an immutable unique tie-breaker and store both values in the checkpoint.

A Maximum ID Is Not a Snapshot

Capturing the largest visible ID gives the job a finite upper range. It does not freeze the table. Another transaction may have allocated a lower ID earlier and commit only after the job has already passed that position.

Rows can also become eligible after you pass them. A filter such as WHERE needs_backfill = true does not make that transition disappear.

Choose a policy explicitly:

  • materialize the intended input IDs under a suitable snapshot
  • briefly coordinate writers while establishing the input boundary
  • use a version marker and reconciliation passes to find unfinished work

For an online derived-field backfill, a practical design may deploy writers that maintain the new field first, then process old versions and reconcile remaining rows. That connects naturally to database migration rollout.

Commit the Checkpoint with the Work

Assume a single worker, or serialize ownership by locking a job row inside each batch transaction. A local-only batch can then follow this protocol:

begin transaction
  lock the job's checkpoint row
  load the next bounded page using its stored cursor
  transform and update the selected records
  set checkpoint.last_id to the largest selected ID
  increment committed progress counters
commit

Use SELECT ... FOR UPDATE on the job row to serialize competing workers. PostgreSQL's row locking documentation describes the transaction lifetime of that lock.

If the process crashes before commit, both updates and progress roll back. If it crashes after commit, the next worker starts after the completed batch.

Do not skip a failed row and advance past it silently. Roll back the batch, or persist that row in a durable exception set with a documented repair path. Completion must account for those exceptions.

Protect Concurrent Application Writes

A cursor says which rows you visited; it says nothing about whether the input changed while you worked.

For a local transformation, calculate from the current row inside the update when possible. Otherwise use an input version in a conditional update, and queue conflicts for another pass. Avoid overwriting a newer application value using an older copy read at the start of a long batch.

Keep batches short enough that transaction locks and retries have a bounded cost. Batch size is an operating parameter: reduce it when database latency or replication lag rises.

External Effects Need Durable Intent

Sending an email and then saving a checkpoint leaves an ambiguous failure window. A restart can send the same email again. Saving the checkpoint first can lose the email instead.

Record the intended effect in an outbox within the batch transaction. Use a stable identity such as job ID, record ID, and operation version. The downstream consumer still needs deduplication or provider idempotency; an outbox does not guarantee exactly-once external effects.

Avoid holding a database transaction open while waiting on thousands of remote calls.

Make Completion Reviewable

Track the input definition, operation version, cursor, upper boundary, committed count, exception count, and last successful batch time. Derive throughput from committed work.

A job is finished when its defined input is accounted for and reconciliation agrees, not merely when one range query returns no rows. Practice stopping after a batch, restarting, and replaying a delivery before running the full backfill. The restart path is part of the job's normal design.