The Postgres migration that passed every test

Here is a migration that passes every performance test you would bother to write:

ALTER TABLE customers
  ADD CONSTRAINT customers_email_key UNIQUE (email);

No table rewrite. No type change. No backfill. Postgres builds the index, finds the first duplicate key, and aborts:

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

Runtime: single-digit milliseconds. It fails that fast on yours too. This migration is not slow. It is wrong, and the only way to find out which is to run it against data that behaves like your customers behave.

Why the test passed

The typical fixture set for a customers table looks like this:

INSERT INTO customers (first_name, last_name, email, country) VALUES
  ('Alice',  'Nguyen', 'alice.nguyen@example.com', 'US'),
  ('Bob',    'Okafor', 'bob.okafor@example.com',   'NG'),
  ('Carlos', 'Mendez', 'carlos.mendez@example.com','MX');

Every email is unique, because the person writing fixtures writes different names on purpose. Three rows, three distinct humans. The migration runs clean, CI goes green, and the deploy is scheduled.

The fixture did not test the assumption. It expressed it. Uniqueness was baked into the data by the same person who later relied on that uniqueness to hold, which is confirmation bias with a test harness attached. Nothing in that run could have failed, and that is exactly the property that makes it useless as evidence.

Why random data passes the same broken test

The obvious fix is to stop hand-writing rows and generate them instead. Generate email with a faker library and you get:

sarah.jones.4821@example.net
d.miranda-9c@mailinator.com
kiley_hartmann87@example.org

Those are realistic-looking, and they are also almost never equal to each other. A generator that invents an independent localpart per row is drawing from a large space with a uniform-ish hand. Generate 100,000 of them and the expected number of accidental collisions is close to zero: collisions between independent uniform draws are rare, which is the same arithmetic behind why hash collisions are a nuisance rather than a certainty.

So the naive upgrade reproduces the bug. The migration now passes against 100,000 rows of plausible email addresses, exactly as it passed against three rows of hand-written ones, and it still fails in production on the first deploy after.

Real names collide, and derived emails inherit it

Your customers' emails are not independent draws from a large space. In most products the signup flow builds them from the name the user typed, which is the one input in the whole row that follows a heavy-tailed frequency list.

example.com has a large namespace for the localpart. Real human first names do not. A population of 100,000 customers contains hundreds of Marias, thousands of Johns, and a long tail of names that appear exactly once. When the localpart is first.last, the collision rate is set by the name distribution, and name distributions are the opposite of uniform.

That is the whole mechanism. The duplicate emails in your customers table are not a bug in your data pipeline. They are a property of human naming, and they show up the moment the email column is derived from a name column instead of randomized independently of it.

Which means the test that finds this class of failure needs the derivation, not the volume.

Reproduce it in one command

Cookbook recipe 03 builds this scenario on purpose. The schema is two tables and nothing exotic:

CREATE TABLE customers (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  first_name text NOT NULL,
  last_name  text NOT NULL,
  email      text NOT NULL,
  country    text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);
 
CREATE TABLE orders (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint NOT NULL REFERENCES customers (id),
  total       numeric(10, 2) NOT NULL CHECK (total >= 0),
  placed_at   timestamptz NOT NULL DEFAULT now()
);

Names come from realistic name lists, so common names come out common. The one decision that makes the experiment work is telling Weavori to build email from the name columns the way a signup flow does, with a formula:

--formula gives a column a computed value instead of a random one, and the formula beats the default generator for that column. Without it, email gets its own unrelated generated address and the experiment shows nothing.

Then count the damage the fixture never had:

SELECT count(*) - count(DISTINCT email) AS duplicate_rows FROM customers;

 duplicate_rows
----------------
           4188

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

            email            |  n
-----------------------------+------
 maria.garcia@example.com    |  104
 john.smith@example.com      |   89
 james.brown@example.com     |   61
 maria.rodriguez@example.com |   58
 david.johnson@example.com   |   47

Now apply the migration:

psql -d target -f migration.sql

It fails, in milliseconds, with a real duplicate key, on a database you built in one command. The argument you thought you had already won is the one you are now having at 2 a.m. with a broken migration history and a deploy that needs unwinding.

The general case

UNIQUE is the clearest example because the failure is loud. The same debt is owed by every constraint that validates existing rows rather than only guarding future ones:

All six pass against fixtures. Several pass against uniform random data too, because random data is generated after the constraint was decided and therefore respects it by construction. That last point is worth sitting with: data you generate to match your rules cannot test your rules. It has to be shaped like the population your rules were written for.

What this does not do

Two smaller limits. The recipe assumes the email column is derived from names, which is a real pattern and not a universal one: if your signup flow generates random localparts, your duplicates come from a different source, and the same technique needs a formula that models that source. And this is PostgreSQL; the constraint semantics are Postgres semantics.

Summary

1. A fast, lock-free migration can still be wrong. Locks and rewrite time are one half of migration risk. Row content is the other, and the second half fails loudly rather than slowly.
2. Hand-written fixtures confirm your assumptions. The person writing them makes the data satisfy the constraint by hand.
3. Uniform random data confirms them too. Independent draws rarely collide, so a plausibility generator passes the same broken test at 100,000 rows.
4. Distributions create the collision. Human names are heavy-tailed, and an email derived from a name inherits that. Model the derivation, not just the volume.
5. Rehearse before you schedule. One command, one constraint, one count query, and the argument moves from opinion to a number.

Try it on a table you are about to add a constraint to:

Then add the --formula that mirrors how your app actually builds that column. weavori estimate shows the row counts and generator choices before anything is written.

• Cookbook recipe 03: migration testing
• Formulas and computed columns
• Postgres query plans without production data: the other half of migration risk, volume and selectivity
• How to generate realistic test data for PostgreSQL
• Postgres test data without seed scripts