Runs after the dual-write deploy, never before. A backfill without a live dual-write walks a snapshot of a table that is still taking writes: every row written behind the cursor stays unconverted, the run completes, and the counts at the end agree with the rows the backfill touched. The gap is invisible precisely because the verification was computed from the same set.
Copy this file, replace every <<placeholder>>, and keep the section headings —
the checker reads them.
This phase is a program, not a statement, so the template is Markdown with the SQL inside it. The parts that must not be improvised are the checkpoint table, the batch predicate, and the idempotency rule.
Owner#
Rota: <<https://runbook.internal/rota/YOUR-TEAM>>
Approvals#
- Schema owner:
<<rota-url>>accepted<<YYYY-MM-DD>> - Data platform:
<<rota-url>>accepted<<YYYY-MM-DD>> - Ticket:
<<TICKET-ID>>
Lock budget#
- Maximum lock hold: 1s — a backfill must never be the thing another statement waits behind.
- Maximum statement: 10s per batch.
- Between batches:
<<N>>ms pause, which is the rate limit.
SET lock_timeout = '1s';
SET statement_timeout = '10s';
A batch that hits lock_timeout is retried, not skipped. Skipping is how a
backfill reports completion over rows it never touched.
Observability#
Open before starting:
- Backfill progress:
SELECT * FROM <<checkpoint_table>>(§2) - Replication lag on every replica —
<<dashboard-url>> pg_stat_activityfor lock waits caused by this run- Dead tuple count on the table: a backfill that updates every row doubles the heap, and autovacuum has to keep up or the table's size becomes the incident.
SELECT n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables WHERE relname = '<<table>>';
Abort threshold#
Abort if:
- replication lag exceeds
<<N>>s and does not recover within<<N>>s, or n_dead_tupexceeds<<N>>×n_live_tup, or- the quarantine (§5) reaches
<<N>>rows, or - three consecutive batches hit
lock_timeout.
Aborting is safe at any point: the checkpoint records where it got to and the old column is untouched.
Incident and rollback#
Every state this phase can be interrupted in is a legal one: the new column is nullable, the old column is untouched, and the dual-write keeps both current for anything written from here on. So the rollback is not a data operation.
- Stop. Kill the runner. The checkpoint records where it got to.
- Undo it entirely, if the change is being abandoned:
expand's rollback applies —ALTER TABLE <<table>> DROP COLUMN IF EXISTS <<new_column>>;— but only after the dual-write binary is rolled back too. Dropping the column while a deployed process writes it fails that process's statements, which is a user-visible outage caused by a cleanup. - Do NOT
UPDATE <<table>> SET <<new_column>> = NULLto "reset". It rewrites every row a second time, doubles the dead tuples the abort threshold was already watching, and undoes the work of the dual-write for rows written since the run began.
The one state that needs a decision rather than a command is a non-empty quarantine: those rows are the reason phase 4 has a precondition, and abandoning the migration with rows still in there means deciding whether they mattered.
Evidence#
Write to evidence/migrations/<<TICKET-ID>>/backfill/:
checkpoint.jsonl— one line per batch: cursor, rows, durationquarantine.csv— every row that would not convert, with its reasonprogress.txt— the §6 query, sampledresume.txt— the record of any interruption and the cursor it resumed from
Completion criteria#
remainingin §6 is 0, computed from the immutable predicate in §3 and not from the batch counter — a counter counts what was attempted.- The quarantine is empty, or every row in it has a decision recorded against it. Phase 4 drops the old column, and these are the rows whose only copy is in it.
checkpoint.jsonlcovers the full key range with no gap.
1. Preconditions#
- The dual-write binary is deployed to 100% of the fleet. Not 99%: the one process still writing only the old column is producing rows this backfill has already walked past.
-
expandcompleted and its evidence is filed. - The new column is nullable, so a partially-converted table is legal.
2. The checkpoint table#
Separate from the migration runner's own bookkeeping, because a backfill resumes inside one migration rather than between migrations.
CREATE TABLE IF NOT EXISTS <<checkpoint_table>> (
job_id text PRIMARY KEY,
last_cursor text,
rows_written bigint NOT NULL DEFAULT 0,
rows_skipped bigint NOT NULL DEFAULT 0,
quarantined bigint NOT NULL DEFAULT 0,
started_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now(),
finished_at timestamptz
);
The checkpoint is written in the same transaction as the batch. Written after, a crash between the two re-runs a batch — survivable, since §4 makes the batch idempotent — but written before, a crash SKIPS one, and nothing later detects it.
3. The batch predicate#
Two rules, and both are about the cursor rather than about the data.
Immutable key. The cursor walks the primary key, or another column that
never changes. A cursor over updated_at re-walks every row the backfill itself
touches, and skips any row whose timestamp moves backwards past it.
Bounded. ORDER BY plus LIMIT, never an unbounded
UPDATE ... WHERE new_column IS NULL. Unbounded means one transaction over the
whole table: it blocks vacuum, cannot be resumed, and when it dies it has
converted an arbitrary prefix of an unknown set.
WITH batch AS (
SELECT <<pk>>
FROM <<table>>
WHERE <<new_column>> IS NULL
AND <<pk>> > $1 -- the checkpoint cursor
ORDER BY <<pk>>
LIMIT $2 -- the batch size
FOR UPDATE SKIP LOCKED -- see §4
)
UPDATE <<table>> t
SET <<new_column>> = <<expression>>
FROM batch
WHERE t.<<pk>> = batch.<<pk>>
AND t.<<new_column>> IS NULL -- see §4
RETURNING t.<<pk>>;
4. Idempotency#
Two clauses do this and they cover different races.
AND t.<<new_column>> IS NULL in the UPDATE makes a re-run of the same batch
a no-op: a row already converted — by an earlier attempt, or by the dual-write
between the SELECT and the UPDATE — is not written again. Without it, a
retry after a crash overwrites a value the application has since changed.
FOR UPDATE SKIP LOCKED lets two workers run without either waiting for the
other, and without both picking the same rows. SKIP LOCKED rather than
NOWAIT: a locked row is one somebody else is converting or a user is editing,
and both are reasons to come back to it rather than to fail the batch.
4b. The dual-write trigger, and the bug it always has#
If the dual write is a trigger rather than application code, write it against
TG_OP. A trigger that fills whichever column is NULL is correct on INSERT
and does nothing at all on UPDATE — because on an update both columns
already hold a value, so neither is NULL, so neither branch fires and the two
diverge from the first update onward.
Nothing detects that until the verify phase's write-parity check, and by then every row a user has edited since the dual write shipped is wrong in the new column.
CREATE FUNCTION <<dual_write_function>>() RETURNS trigger AS $$
BEGIN
IF TG_OP = 'INSERT' THEN
-- One side is absent and the other is the value.
IF NEW.<<new_column>> IS NULL THEN NEW.<<new_column>> := NEW.<<old_column>>; END IF;
IF NEW.<<old_column>> IS NULL THEN NEW.<<old_column>> := NEW.<<new_column>>; END IF;
ELSE
-- Both sides already hold a value. Propagate the one that CHANGED.
IF NEW.<<old_column>> IS DISTINCT FROM OLD.<<old_column>> THEN
NEW.<<new_column>> := NEW.<<old_column>>;
ELSIF NEW.<<new_column>> IS DISTINCT FROM OLD.<<new_column>> THEN
NEW.<<old_column>> := NEW.<<new_column>>;
END IF;
END IF;
RETURN NEW;
END $$ LANGUAGE plpgsql;
CREATE TRIGGER <<dual_write_trigger>>
BEFORE INSERT OR UPDATE ON <<table>>
FOR EACH ROW EXECUTE FUNCTION <<dual_write_function>>();
BEFORE, not AFTER: a BEFORE trigger modifies NEW and the row is written
once. An AFTER trigger has to issue a second UPDATE, which fires the trigger
again and doubles the write volume of a phase that is already the heaviest.
This is not a hypothetical. The first version of
scripts/v1-workbenches/migration-rehearsal.mjs had exactly the NULL-filling
form, and the rehearsal's own write-parity check is what caught it.
5. Quarantine#
A row that will not convert — an encoding the column accepts and the expression does not, a value out of the target type's range, a NULL in a source that was supposed to be populated — must not stop the run and must not be silently skipped. Both leave a state nobody can act on.
CREATE TABLE IF NOT EXISTS <<quarantine_table>> (
<<pk>> <<pk_type>> PRIMARY KEY,
reason text NOT NULL,
source_value text,
seen_at timestamptz NOT NULL DEFAULT now()
);
Catch the per-row failure, insert here, continue. source_value is the
original, which means this table inherits the classification of the column it
came from — if that column is protected, this one is too, and it is dropped in
phase 4 with the rest.
6. Progress#
Computed from the table, never from the counter. A counter reports what was attempted.
SELECT count(*) FILTER (WHERE <<new_column>> IS NOT NULL) AS converted,
count(*) FILTER (WHERE <<new_column>> IS NULL) AS remaining,
count(*) AS total
FROM <<table>>;
On a large table run this against a replica, or accept that the count itself is a sequential scan.
7. Resume#
Resuming is re-running the same loop with the stored cursor. Nothing special happens: §4 makes an overlapping batch a no-op, so an interrupted run and a fresh run differ only in where they start.
SELECT last_cursor, rows_written, quarantined
FROM <<checkpoint_table>> WHERE job_id = '<<job_id>>';
If last_cursor is NULL and rows_written is greater than zero, stop: the
checkpoint was not written in the batch transaction, which means the run cannot
say where it got to and starting again is the only correct action.