Test the migration before it tests production: rehearsing AI-generated database changes

The pre-merge checklist catches a bad migration's shape: no rollback, a table rewrite hiding in one step, a backfill welded to a schema change. What it cannot catch is behaviour. A migration can pass every review point and still take a nine-minute lock on your busiest table, because review happens on paper and locks happen on data.

Testing an AI-generated migration means rehearsing it. Run it, reverse it, and measure it against data that looks like yours, before production volunteers to go first. The agent wrote the migration in seconds. The rehearsal is what keeps that speed from becoming the incident.

Build a rehearsal database that can lie to you less

A rehearsal is only worth anything if the database can fail the way production fails. Three properties matter:

• Production-shaped data. An anonymized production dump, restored. If that is impossible, generated fixtures at real volume: row counts per table within an order of magnitude of production, and the same skew (one tenant with half the rows, timestamps clustered in the last month).
• The same engine. Same database major version, same extensions. Behaviour around locking, defaults, and type coercion shifts between versions just enough to hide the failures you are rehearsing for.
• The ugly rows. Nulls where the agent assumes none. Names at the column's length limit. Orphaned foreign keys from the bug you fixed in March. Agents test the happy path, because the happy path is what their training data shows. Production data is mostly edge cases that survived.

This is step 4 of the checklist, run it against realistic data, done properly. "Realistic" is a property you build, not a hope you have.

Up, down, up

The single most skipped test: run the migration forward, run the rollback, then run it forward again. Three runs, not one.

The down path is where agents bluff. Two patterns to watch for:

• The destructive stub. The auto-generated rollback drops the new column and calls it done. If the application wrote data to that column between deploy and rollback, the rollback deletes it. That is not a rollback; that is data loss with good posture.
• The second rewrite. The forward migration was carefully staged, and the rollback is a single statement that rewrites the table on the way back, taking the lock the forward path was designed to avoid.

Time the rollback while you are there. If the forward run takes twenty seconds and the rollback takes forty minutes, you do not have a rollback. You have a restore from backup with extra steps, and the time to learn that is during the rehearsal, while it is still a choice. Any step that is genuinely irreversible (dropping the old column in the contract phase, for example) gets rehearsed as what it is: a point of no return, scheduled deliberately, never discovered mid-incident.

Diff the data, not the schema

Schema diffs are the easy part, and tools do them well. Corruption is quieter. The migration succeeds, the deploy goes green, and three percent of the rows are wrong in a way nobody notices until the invoices go out.

Before and after the rehearsal run, capture:

• Row counts for every touched table.
• Null counts on every touched column.
• Aggregates that must not move: sums on money columns, counts of distinct keys, min and max on timestamps.
• Invariants as queries: every order still has a customer; every line item still sums to its order total; wherever the old and new columns coexist, they agree.

Take the worked example from the checklist, renaming users.name to users.full_name. After the backfill step, the rehearsal query is one line: count the rows where full_name is null and name is not. Anything above zero is a failed rehearsal, no matter how clean the deploy looked. Then sample twenty rows and read them. Some checks are still eyes.

Put it under load and watch the locks

A migration that is instant against a quiet staging copy can still stall production, because production is never quiet. So the rehearsal runs with a concurrent writer: a small script that inserts and updates rows in the table being altered, for the whole run.

• Watch the waits. On Postgres, pg_locks and pg_stat_activity show you who is waiting on whom. Set a low lock_timeout during rehearsal so contention fails loudly instead of queueing silently behind the migration.
• Measure the rewrite honestly. If a statement rewrites the table, duration scales with size. Ten seconds on a one-million-row rehearsal table is not ten seconds on a hundred million rows, and the rehearsal report should say the extrapolated number out loud.
• Check the concurrent index actually happened. A CREATE INDEX CONCURRENTLY that fails leaves an invalid index behind, quietly. The rehearsal verifies the index exists and is valid, not just that the command returned.

This is the same discipline as reviewing an AI-generated PR: the diff tells you what the author intended, and only running the thing tells you what it does.

Break the backfill on purpose

If the migration ships a backfill, kill it halfway through the rehearsal run. Then answer three questions. Does it resume where it stopped, restart safely from the top, or apply twice? Are the batches idempotent, keyed on ranges of the primary key rather than offsets, so a rerun skips finished work? Is progress visible, and is the rate limited enough that the backfill is not competing with the application for the database?

An agent's default backfill is a single UPDATE statement wearing a trench coat. On a large table that is a lock, a transaction log spike, and a very long minute. The rehearsal is where you find out which one you were sent.

The go or no-go gate

Five answers, written down before the deploy starts:

1. Rollback rehearsed, and its duration is known.
2. Data diffs clean: counts, nulls, aggregates, invariants.
3. Worst lock wait measured under load, not guessed.
4. Backfill killed and resumed successfully.
5. The rollback decision (run the down migration, or restore) is made now, not during the incident.

Agents are genuinely good at writing migrations: the boilerplate, the framework idioms, the syntax. The checklist keeps the judgment about shape. The rehearsal keeps the judgment about behaviour. Both stay yours, because production data is the one thing in the pipeline that cannot be regenerated.