Why one big update is the wrong shape
The obvious backfill is a single statement that updates every row. On a large table that statement runs for a long time in one transaction, holds row locks as it goes, writes a flood of changes at once and leaves a great many dead row versions behind. In PostgreSQL, updates create new row versions and the old ones are removed later by vacuum, so a huge update leaves cleanup with a backlog. If the statement fails near the end, everything is rolled back and you start again.
Batching changes the shape: update a bounded range of keys, commit, pause, repeat. Each batch is short, so locks are short, replication keeps up and progress survives an interruption. The cost is that you now need a way to know which rows are done.
- Choose batches by primary-key range so each batch uses an index.
- Make batch size and the pause between batches settings, not constants.
Make it stoppable and resumable
A backfill should be safe to stop at any moment and run again. The simplest way is to make each batch idempotent: it only touches rows where the new column is still empty, or where it differs from what the rule gives. Then a restart repeats at most one batch and does no harm, and running the whole thing twice changes nothing the second time.
Record progress somewhere simple, such as the last key processed, and log counts per batch. During a rehearsal, stop the backfill halfway, check the counts, resume and confirm that no row was processed twice and none was skipped. That test is cheap and catches the commonest mistakes in range handling.
- Test the boundary rows of each range, including the first and last key.
- Keep the rule that fills the column in one place so the check can reuse it.
Prove every row, then constrain
After the backfill, compare, do not assume. Count rows, count rows where the column is filled, and count rows where the value differs from the rule applied afresh. The last number must be zero. Only then add a NOT NULL or foreign-key constraint, and on PostgreSQL use the gentler order the manual describes: add the constraint NOT VALID so it is checked for new writes without scanning the table, then validate it later under a weaker lock.
Writes that arrive during the backfill need a plan too. If the application can insert rows without the new column, either set the value on write before the backfill starts or run the backfill again until a final pass finds nothing to change.
- Keep the comparison query; it is the evidence in the handover.
- Decide who writes the column while the backfill runs.
Write the rollback first and run it
The rollback belongs in the plan from the start. Django's documentation says a data-migration step with no reverse callable raises an exception when migrating backwards, and Rails raises an error for an irreversible migration, so a framework will not let you pretend. Write the undo alongside the change: drop constraints and indexes added, then the column, in reverse order, noting that anything the application has already written to the column is lost.
Then run it on the rehearsal copy and compare structure and row counts with the starting state. The add-column outcome is accepted only after that run, and its runbook orders the application revert before the column drop. Nothing is applied to production by us; you run the steps after a restore point you have tested, because on MySQL in particular a half-applied schema change cannot be rolled back by the database.
- Say in the runbook what data would be lost by rolling back after go-live.
- Keep the restore point tested and recent before starting.
Sources and limits
- Django 5.2: Migrations Checked 2026-10-11.
- Data migrations are best written as separate migrations, using historical models, and a RunPython operation without a reverse callable raises an exception when migrating backwards.
- PostgreSQL and SQLite run migrations in a single transaction by default, while MySQL has no transactional schema changes so a failed migration must be unpicked by hand.
- Rails 8.1 guide: Active Record Migrations Checked 2026-10-11.
- A migration can be reversed with db:rollback when it is reversible, and an irreversible one raises ActiveRecord::IrreversibleMigration.
- Editing a migration that has already run in production is discouraged; write a new migration instead.
- PostgreSQL 18: ALTER TABLE Checked 2026-10-11.
- NOT VALID constraints and VALIDATE CONSTRAINT let a constraint be added without a full scan under a strong lock and checked later with a weaker one.
- PostgreSQL 18: Routine vacuuming Checked 2026-10-11.
- UPDATE and DELETE leave dead row versions that VACUUM later removes, so a large update creates dead rows that cleanup must catch up on.