Resuming an Interrupted Backfill from a Checkpoint

The backfill that populates orders.region was 61% through 400 million rows when the worker pod was evicted during a node upgrade. It restarted from the beginning, because the only record of progress was a loop variable in memory. The first 244 million rows were skipped quickly thanks to a WHERE region IS NULL filter — but “quickly” still meant scanning an index over hundreds of millions of rows batch by batch, and the second run took almost as long as the first. On large tables, a backfill will be interrupted: by deploys, node rotation, failovers, throttling pauses, a lag alarm someone acted on. Designing it to resume exactly where it stopped turns every interruption into a non-event. This guide adds durable checkpoints to a keyset backfill, shows how to restart safely, and how to prove completeness at the end. It belongs to Backfill Optimization.

Symptom / Error Signatures

A backfill without durable progress shows these patterns:

• After a restart, the job logs start from the beginning (processing ids 1–5000) even though earlier runs processed much of the table.
• Each restart is slower than it should be, because the “skip already-done rows” filter still has to scan past them.
• Nobody can answer “how far along is it?” without counting rows, which on a large table is itself expensive.
• Two workers accidentally run at once after a restart and contend on the same rows, producing lock waits or deadlocks (40P01).

Root Cause Analysis

A keyset backfill walks the primary key in order: each batch processes id > last_id up to a batch size and records the largest id it touched. That last_id is a perfect checkpoint — it fully describes progress — but only if it is stored somewhere that survives the process. Storing it in memory or in a log line makes it disappear with the pod.

Where to keep the checkpoint matters. The best place is the database itself, updated in the same transaction as the batch. Then the checkpoint and the data change commit or roll back together: if the process dies after updating rows but before committing, both are rolled back and the batch simply reruns; if it dies after commit, the checkpoint already reflects the batch. A checkpoint kept elsewhere (Redis, a file) can disagree with the data after a crash, which is safe only if the batch itself is idempotent.

Immediate Mitigation

If a checkpoint-less backfill has just been interrupted:

1. Recover the approximate position from the data. For a backfill that fills NULLs, the lowest id still NULL is a safe restart point; use an index on the target column or a partial index if one exists.

-- PostgreSQL · read-only · fastest with a partial index WHERE region IS NULL
SELECT min(id) FROM orders WHERE region IS NULL;

2. Create a checkpoint table and seed it with that position so the next run resumes there.

-- PostgreSQL · migration role · one row per backfill job
CREATE TABLE IF NOT EXISTS backfill_state (
  job        text PRIMARY KEY,
  last_id    bigint NOT NULL,
  updated_at timestamptz NOT NULL DEFAULT now(),
  done       boolean NOT NULL DEFAULT false
);
INSERT INTO backfill_state (job, last_id) VALUES ('orders_region', 243998000)
ON CONFLICT (job) DO UPDATE SET last_id = EXCLUDED.last_id, updated_at = now();
-- ROLLBACK PATH: DELETE FROM backfill_state WHERE job = 'orders_region';

3. Prevent concurrent workers. Take an advisory lock at start so a second worker exits immediately instead of competing.

-- PostgreSQL · first statement of each worker session · returns false if another worker holds it
SELECT pg_try_advisory_lock(hashtext('backfill:orders_region'));

Permanent Fix / Long-Term Pattern

Write every long backfill as a loop of self-contained transactions that read the checkpoint, process one keyset batch, advance the checkpoint, and commit. The pattern below is idempotent (it only touches rows still needing work), resumable (the checkpoint is exact) and exclusive (the advisory lock).

-- PostgreSQL 11+ · run by the worker in a loop; each CALL commits one batch
-- WARNING: keep batches small enough to finish well under statement_timeout and lock budgets.
CREATE OR REPLACE PROCEDURE backfill_orders_region_batch(batch_size int DEFAULT 5000)
LANGUAGE plpgsql AS $$
DECLARE
  start_id bigint;
  end_id   bigint;
BEGIN
  SELECT last_id INTO start_id FROM backfill_state WHERE job = 'orders_region' FOR UPDATE;
  SELECT max(id) INTO end_id FROM (
    SELECT id FROM orders WHERE id > start_id ORDER BY id LIMIT batch_size) b;
  IF end_id IS NULL THEN
    UPDATE backfill_state SET done = true, updated_at = now() WHERE job = 'orders_region';
    RETURN;
  END IF;
  UPDATE orders SET region = CASE ship_country WHEN 'DE' THEN 'eu' WHEN 'US' THEN 'na' ELSE 'other' END
  WHERE id > start_id AND id <= end_id AND region IS NULL;
  UPDATE backfill_state SET last_id = end_id, updated_at = now() WHERE job = 'orders_region';
END $$;
-- ROLLBACK PATH: DROP PROCEDURE backfill_orders_region_batch; the column stays nullable.

The worker calls the procedure repeatedly, sleeping between calls and pausing when replica lag is high, as in tuning backfill batch size against replication lag. Progress is now a single row: last_id against max(id) gives the percentage and, with updated_at, the rate. Alert when updated_at stops advancing while done is false. For batch shape and pagination, see cursor-based vs keyset pagination for large backfills.

When the job reports done, verify completeness without a full rescan of the whole table in one query — count remaining NULLs in id ranges, or rely on a partial index WHERE region IS NULL that should now be empty. Then continue the expand-and-contract sequence, for example by tightening constraints as in adding NOT NULL via a CHECK constraint in Postgres.

Verification Checklist

• The checkpoint is stored in the database and updated in the same transaction as each batch.
• An advisory lock prevents two workers from running the same job.
• Killing the worker mid-run in staging and restarting it resumes from the last committed batch.
• Batches are idempotent, so reprocessing a batch after a crash changes nothing.
• An alert fires when the checkpoint’s updated_at stalls while the job is not done.

Frequently Asked Questions

Why store the checkpoint in the same transaction as the batch? So the two cannot disagree. If the process dies before commit, both the row changes and the checkpoint roll back and the batch reruns; after commit, both are durable. A checkpoint stored elsewhere can be one batch ahead or behind the data.

Do I still need idempotent batches if the checkpoint is exact? It is good practice. Idempotency protects against manual reruns, overlapping ranges after a configuration change, and checkpoints reset by an operator. It usually costs only a filter such as AND region IS NULL.

What if rows are inserted behind the checkpoint during the backfill? New rows get higher ids than the checkpoint in a sequence-keyed table, so the backfill reaches them. Rows written by application code should already populate the new column; the backfill only needs to cover historical rows.

Can the checkpoint approach work with UUID keys? Yes, as long as you walk the key in a consistent order with a supporting index. Random UUIDs make batches scatter across the table, so time-ordered UUIDs (such as UUIDv7) or a separate sequential column give better locality.