# PHASE 2 of 4 — BACKFILL (S3.15.b)

> 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.

```sql
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_activity` for 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.

```sql
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_tup` exceeds `<<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>> = NULL` to "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, duration
- `quarantine.csv` — every row that would not convert, with its reason
- `progress.txt` — the §6 query, sampled
- `resume.txt` — the record of any interruption and the cursor it resumed from

## Completion criteria

1. `remaining` in §6 is **0**, computed from the immutable predicate in §3 and
   not from the batch counter — a counter counts what was attempted.
2. 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.
3. `checkpoint.jsonl` covers 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.
- [ ] `expand` completed 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.

```sql
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.

```sql
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.

```sql
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.

```sql
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.

```sql
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.

```sql
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.
