Add a NOT NULL column with backfill to a seeded 5-million-row Postgres table. AI drafts the migration, the rollback, and the checks — and the draft contains two hazards that would take a production system down. Your review-gate checklist must catch both before anything runs; nothing executes until the checklist is signed.
Deepak runs change management for a Bengaluru SaaS company's largest tenant database. The feature team needs orders.channel — mandatory, defaulted for history, live by Thursday — and they have helpfully attached "the migration ChatGPT wrote, it worked on staging." Staging has 40,000 rows. Production has 5 million and a streaming replica. This lab is the gate that stands between those two facts: you will run the AI draft through a hazard checklist, catch what it gets wrong, rewrite it as a batched online migration with a rollback script and row-count proofs, and only then let it touch the table.
~90 minutes hands-on · Core tier (maps to module M2 · Schema, Change & Migration with Safety Gates).
Local Postgres 15+ (the Lab 01 container works), fresh database migrate:
docker exec -it tuning-lab psql -U postgres -c "create database migrate"
docker exec -it tuning-lab psql -U postgres -d migrate
Seed 5M rows — takes a minute or two:
create table orders (
order_id bigint generated always as identity primary key,
customer_id bigint not null,
amount numeric(12,2) not null,
placed_at timestamptz not null,
source_ref text
);
insert into orders (customer_id, amount, placed_at, source_ref)
select 1 + g % 200000,
round((random()*20000)::numeric, 2),
now() - (g % 1095) * interval '1 day',
case when g % 4 = 0 then 'WEB' when g % 4 = 1 then 'APP'
when g % 4 = 2 then 'PARTNER' else null end
from generate_series(1, 5000000) g;
analyze orders;
select count(*), count(source_ref) from orders; -- 5,000,000 / 3,750,000
The business rule: channel derives from source_ref (WEB/APP/PARTNER map straight across; NULL becomes 'LEGACY'). The feature team's AI draft, saved as draft-migration.sql — read it, do not run it:
-- draft-migration.sql (AI-generated; DO NOT RUN)
begin;
alter table orders add column channel text;
update orders
set channel = coalesce(source_ref, 'LEGACY'); -- hazard lives here
alter table orders
add constraint orders_channel_chk
check (channel in ('WEB','APP','PARTNER','LEGACY')); -- and here
alter table orders alter column channel set not null;
commit;
Write the gate checklist before reading the draft critically. Ten yes/no questions every DDL change must answer, in gate-checklist.md. Minimum set: Does any single statement update more than 100k rows? Does any statement take an ACCESS EXCLUSIVE lock for longer than milliseconds? Is every constraint added NOT VALID first and validated separately? Is there a tested rollback script? Are pre/post row-count checks defined? Is there a lock_timeout so the migration queues politely instead of blocking everything behind it? What happens on the replica? Can the migration be re-run safely if it dies halfway (idempotence)? Is the table's write traffic accounted for during backfill? Who signs?
Run the draft through the checklist on paper. Walk each question against draft-migration.sql and record pass/fail. Expected findings — the two seeded hazards: (a) the single-statement UPDATE rewrites all 5M rows in one transaction — hours of row locks on a live table, a WAL flood, table bloat near 2x, and a long-running transaction that stalls vacuum estate-wide; (b) the CHECK constraint and SET NOT NULL are added validating immediately — each takes a full-table scan under ACCESS EXCLUSIVE, blocking all writes for the scan duration, and the WAL burst plus lock storm is exactly what makes streaming replicas fall behind and read traffic time out. A third, subtler miss: everything is in one begin/commit, so the locks compound. Write the findings as annotations on the draft.
Get AI to fix its own draft — with the checklist as the prompt. Paste the draft plus your checklist into the assistant: "Rewrite this migration to pass every checklist item: batched backfill, NOT VALID constraint pattern, lock timeouts, idempotent re-run, separate transactions per phase." The point of this step: the same model that produced the hazard produces the safe version when the constraint is in the prompt — the checklist is the skill, the model is interchangeable.
Author the final forward migration. Verify the AI's revision line-by-line against the checklist and correct it where it drifts; the shape you should land on:
-- phase 1: add nullable column (instant, metadata-only)
set lock_timeout = '2s';
alter table orders add column if not exists channel text;
-- phase 2: batched backfill (run repeatedly until 0 rows updated)
with batch as (
select order_id from orders
where channel is null limit 50000 for update skip locked)
update orders o set channel = coalesce(o.source_ref, 'LEGACY')
from batch where o.order_id = batch.order_id;
-- phase 3: constraint as NOT VALID (instant), then validate (lighter lock)
alter table orders add constraint orders_channel_chk
check (channel is not null and
channel in ('WEB','APP','PARTNER','LEGACY')) not valid;
alter table orders validate constraint orders_channel_chk;
Note the pattern in phase 3: a validated CHECK (channel is not null ...) lets a later SET NOT NULL (Postgres 12+) skip its full-table scan, or stand in for it entirely. Wrap phase 2 in a loop script (backfill.sh) that runs the batch until UPDATE 0 and logs progress every batch.
Author the rollback script and the checks. rollback.sql: drop the constraint, drop the column — and a comment stating the point of no return (once the application writes channel, rollback becomes a data-loss decision, not a script). checks.sql: pre-count total rows; post-checks — count(*) unchanged, count(*) where channel is null = 0, per-value distribution matching the source_ref distribution (3 × 1,250,000 + 1,250,000 LEGACY).
Sign the checklist, then execute. Fill every checklist answer for the final package, date it, sign it (your name — the ritual matters), and only then run: phase 1, bash backfill.sh (expected: 100 batches of 50k, visible progress), phases 3. Run checks.sql — expected: 5,000,000 / 0 / 1,250,000 per channel.
Prove the migration stayed online. While backfill.sh runs, hold a second session open running one write per second: insert into orders (customer_id, amount, placed_at, source_ref) values (1, 10, now(), 'WEB'); in a loop. Expected: zero blocked inserts during backfill (thanks to batching and skip locked), a sub-second stall at most during validate constraint. Record the observation in the checklist's evidence column.
Test the rollback. On a scratch copy (create table orders_rb as select * from orders limit 100000 plus replayed forward phases), run rollback.sql and confirm the table returns to its prior shape. A rollback script that has never been executed is a rumor, not a control.
gate-checklist.md exists with ≥10 questions, answered twice: failing for the draft, passing and signed for the final package.backfill.sh is restartable: killing it mid-run and re-running converges to UPDATE 0 with no duplicates or misses.checks.sql output shows 5,000,000 rows, zero NULL channels, and the expected 1.25M-per-value split.Self-grading rubric (pass = 8/10): checklist quality — covers locks, batching, NOT VALID, rollback, idempotence, replica (0–3); both hazards caught with consequences named (0–3); final package correct and executed with proofs (0–2); online-ness demonstrated with the concurrent writer (0–1); rollback actually tested (0–1).
The AI wrote a migration that works — on staging, once, with no traffic. The checklist is what encodes the difference between staging and production: batches, NOT VALID, lock timeouts, a tested rollback, and a signature. Gate the draft, and the same assistant that wrote the hazard writes the safe version in one more prompt.