Backfill
A one-time job that fills a newly added column, or repairs bad values, across rows that already existed, in safe batches.
See it
What it is
A backfill is the one-time job that fills in data for rows that already existed. New column, new denormalized counter, a bug that wrote garbage for six weeks: the schema change is instant, but the million rows behind it need a job. Backfills are data migrations, not schema migrations, and treating them as the same thing is how deploys hang.
The safe shape is four steps. Add the column nullable with no default, so the DDL is instant. Ship code that writes the new field for every new and updated row (dual write), so the past stops growing while you work. Backfill the old rows in batches, walking by primary key or cursor rather than a giant UPDATE. Only then add the NOT NULL constraint or make reads depend on it.
Gotchas: one UPDATE across ten million rows takes a single enormous transaction, holds locks, balloons the write-ahead log, spikes replication lag on every read replica, and leaves vacuum cleaning up for hours. Batch it (1k to 10k rows), sleep briefly between batches, and checkpoint progress so a crash resumes instead of restarting. Make the job idempotent and filter on 'WHERE new_column IS NULL' so re-running is free. And run it against a copy first: a backfill with a wrong WHERE clause is just a slow data loss incident.
Ask AI for it
Write a resumable, idempotent backfill job for <column> on <table>. Process in batches of 5000 rows ordered by primary key, using keyset pagination (WHERE id > last_processed_id) rather than OFFSET, and filter to rows still needing work (WHERE <column> IS NULL). Commit each batch in its own transaction, persist the last processed id so a restart resumes where it stopped, sleep 100ms between batches to keep replication lag down, and log progress plus an ETA. Add a --dry-run flag that reports affected row counts without writing. Assume code is already dual-writing the column for new rows.