Migration & constraint testing

The half of migration risk nobody rehearses

Most of the literature on safe PostgreSQL migrations is about time: which lock a statement takes, how long a table scan runs, whether you can split the work into NOT VALID and VALIDATE. That half is well documented, and it is real risk.

The other half is content, and it is the half that fails in the middle of a deploy window:

ALTER TABLE customers ALTER COLUMN email SET NOT NULL;

That statement takes an ACCESS EXCLUSIVE lock and scans the table to prove no row has a NULL. If it finds one, it aborts after the scan. So it is simultaneously a duration question and a correctness question, and only the first one has good tooling.

The correctness question is answerable in about a minute, if you have a table whose contents behave like production's. Most teams do not, because the only table that behaves like production is production, and you cannot point a test at it.

Why your current test data cannot answer it

Three common sources of migration test data all pass a migration that is doomed:

• Hand-written fixtures. The person writing three sample customers makes the emails different on purpose. The fixture does not test the assumption that email is unique; it expresses it.
• Uniform random rows. Independent draws from a large space almost never collide. A million random localparts produce the same green checkmark as three hand-typed ones.
• A staging database seeded last quarter. It has the volume but not the skew, and the constraint usually breaks in the long tail that staging lost.

The failure class is the same every time: data generated to satisfy your rules cannot test your rules. It has to be shaped like the population the rules were written for. The full argument, including why human names are heavy-tailed and what that does to a derived email column, is in the Postgres migration that passed every test.

Rehearse the migration

ERROR:  could not create unique index "customers_email_key"
DETAIL:  Key (email)=(john.smith@example.com) is not valid.

SELECT count(*) - count(DISTINCT email) AS duplicate_rows FROM customers;
 
SELECT email, count(*) AS n
FROM customers
GROUP BY email
HAVING count(*) > 1
ORDER BY n DESC
LIMIT 5;

Make the rehearsal honest

The rehearsal is only as good as the assumption you feed it. Each risky migration has a knob that shapes the property it tests:

Knob keys use the table.column form and can live in weavori.yaml, so the rehearsal is versioned with the migration instead of typed into a shell by whoever remembers it.

The one case generation will not cover is orphaned children. Weavori resolves foreign-key order and writes parents before children, so a freshly generated set is referentially intact by construction and can never hand you the dangling customer_id that a legacy DELETE left behind. Rehearse that migration either against real rows with weavori sync, or by introducing the damage deliberately after generating:

UPDATE orders SET customer_id = 999999999 WHERE id % 5000 = 0;

Two more properties that matter in a pipeline. --seed replays a failing run byte for byte, so a bad migration stays reproducible after you close the terminal. And DDL paste mode means you do not need a running database to start: weavori generate --input schema.sql --input-format ddl --rows 1000 builds rows from CREATE TABLE statements alone, which is useful the week before the database exists.

What a rehearsal does not tell you

Honest limits, because a rehearsal that oversells itself gets abandoned the first time it lies:

• Synthetic collisions are not your collisions. Generating 100,000 realistic customers proves that first.last@example.com is not unique at your scale. It does not identify the specific duplicate that breaks tonight's deploy. Production supplies the count of rows to fix; the rehearsal proves the class of failure is real and that your migration needs a backfill or de-duplication step. Run both.
• This is content, not duration. Rehearsing against synthetic rows tells you whether the migration is valid. It does not measure how long the scan holds a lock on your real table, which depends on your real size. For volume and query-plan fidelity, see Postgres query plans without production data.
• When you need the actual rows, copy them instead. Privacy-safe test data covers weavori sync, which moves real rows in FK order and can substitute recognized PII columns in the same pass.

Put the rehearsal in the pipeline

A migration rehearsal is most useful as a job that runs before the deploy, not a manual step someone skips. weavori estimate gives you a read-only pre-flight that reports table counts and estimated generation time without writing anything, and the CLI returns semantic exit codes so a failed rehearsal fails the job cleanly.

The CI/CD pipelines guide covers wiring that into GitHub Actions, GitLab, CircleCI, and Jenkins.

Get started