Synthetic backfill log: new writes covered, batches, a resume, two reconciliations and a rollback run

The invented change

Everything below is invented for illustration. It is not a measurement of any real system and not a customer delivery. A table of 4,000,000 invented rows gains a nullable column holding a normalised copy of an email address. Two application write paths insert into the table (a signup form and an admin import), listed with the buyer at intake. The agreed limit for any lock wait during the rehearsal is 200 milliseconds. A synthetic write workload of 300 inserts a minute runs for 52 minutes, from step 2 to step 7.

If the matrix is wider than the box, scroll horizontally to read every column. Keyboard: focus the matrix and use Left/Right.

step 1  ADD COLUMN email_norm text (no default)         lock held: 4 ms
step 2  deploy: both write paths set email_norm on every insert
step 3  backfill ids 1 to 4,000,000 in batches of 5,000, pause 100 ms
step 4  ADD CONSTRAINT ... CHECK (email_norm IS NOT NULL) NOT VALID
        (applies to every new write from here; both paths set the column,
         so writes rejected: 0)
step 5  VALIDATE CONSTRAINT                             (weaker lock)
step 6  CREATE INDEX CONCURRENTLY ... (email_norm)
step 7  the write workload stops; the comparison is run again

Batches, a stop and a resume

The log records counts per batch. The backfill was deliberately stopped partway and resumed. It covers the rows that existed when it started; rows inserted since step 2 were filled by the write paths.

If the matrix is wider than the box, scroll horizontally to read every column. Keyboard: focus the matrix and use Left/Right.

batches 1-410   rows updated 2,050,000   max lock wait 38 ms
STOP (deliberate) after batch 410, last id 2,050,000
RESUME from last id
batches 411-800 rows updated 1,950,000   max lock wait 41 ms
total rows updated 4,000,000   rows processed twice 0   rows skipped 0

Reconciliation and rollback

The comparison query applies the rule afresh and counts disagreements; it must be zero both at the end of the backfill, while rows are still arriving, and after the write workload has stopped. The row total moves because rows keep arriving. Then the rollback is run and the starting structure compared.

If the matrix is wider than the box, scroll horizontally to read every column. Keyboard: focus the matrix and use Left/Right.

                                      end of backfill     after the workload stopped
                                      (minute 44)         (minute 52)
rows total                                4,013,200           4,015,600
rows inserted since step 2                   13,200              15,600   (300 a minute)
rows with email_norm filled               4,013,200           4,015,600
rows where email_norm differs
  from the rule applied afresh                    0                   0
largest lock wait in rehearsal            41 ms  (limit 200 ms)

ROLLBACK run on staging, workload stopped:
  1  drop index, drop constraint
  2  revert the write-path change (the application stops setting email_norm)
  3  drop column
  (order matters on a live system: the constraint would reject writes that
   omit the column, and the application must never write a column that is gone)
  structure listing identical to start: yes
  row count: 4,015,600 before the rollback, 4,015,600 after
  application tests before change / after rollback: pass / pass
  lost by design: the 4,015,600 values in email_norm; the runbook says to export them first if anyone needs them

Limits, and the priced enquiry

These numbers are invented and say nothing about any real table. A rehearsal under a synthetic workload does not promise zero impact in production. The fixed-scope add-column job produces a log of this shape for one new column on one table, rehearsed on a staging copy you prepare, starting from £695 as an untested proposal and paid after you sign off. It does not convert an existing column and switch the application to it, which is a separate job, and it does not run anything on production; you apply the runbook after a restore point you have tested.