Product wants email addresses to be unique in a 100-million-row users table that currently contains duplicates, and there is no maintenance window. Lay out the rollout you would run, including how you decide when it is safe to enforce.

## Why this is harder than adding a CHECK For CHECK and foreign-key constraints you can add the rule unvalidated, so enforcement starts immediately and verification happens later under a weak lock. **Uniqueness has no such mode.** A unique constraint is implemented by a unique index, and there is no unique index without building it — the build *is* the verification. So the staging mechanism has to be the index build itself, which is why the whole plan is shaped differently. ## Step 1 — decide what "unique" means This is where most of the risk lives, and it is not a database question: - **Case and normalisation.** Is `[email protected]` the same as `[email protected]`? Almost always yes for login, which means the constraint should be on a normalised expression or a stored normalised column — and that changes the duplicate count, often dramatically. - **Scope.** Are soft-deleted or deactivated users included? If a deleted user's address should be reusable, the rule is a *partial* uniqueness over active rows, not a global one. - **Missing values.** Whether an email is optional at all, and what should happen to rows without one. - **Merge semantics.** Two accounts with the same address own orders, sessions, subscriptions, audit history. Deciding which survives and what happens to the other's data is a product and possibly a legal question, and it is the long pole of the schedule. Get these wrong and you will build the index twice. ## Step 2 — enforce in the application first Deploy the check in code before touching the schema: registration and profile-update paths reject an address that already exists. This is not the durable guarantee — races can still slip a pair through — but it reduces the arrival rate of new duplicates to near zero, which is what makes the cleanup converge and the index build succeed. Let it soak long enough to see the duplicate-creation rate flatten in the data. ## Step 3 — measure, then dedupe in batches Count first: how many duplicate groups, how large, how old. The distribution decides the strategy — a few thousand old groups is a scripted merge; a few million spread across active users is a product programme. Dedupe in small transactions, keyed so the work is resumable. Keep the resolution rule explicit and reversible: record the merged-away identifiers somewhere durable so a bad merge can be reasoned about later. Rerun the duplicate-count query until it reaches zero and *stays* zero across a few hours of live traffic — that stability, not the single zero reading, is your signal that step 2 is actually holding. ## Step 4 — build the unique index without blocking ```sql CREATE UNIQUE INDEX CONCURRENTLY users_email_uniq ON users (lower(email)); ``` What to know about a concurrent build: - It makes **two passes** over the table and waits for existing transactions between them, so it takes considerably longer than a plain build — plan for hours on 100 million rows. - It does **not** block reads or writes, which is the point. - It cannot run inside a transaction block, and many migration frameworks wrap statements in transactions by default — a classic trip hazard. - If it encounters a duplicate, it fails and leaves an **invalid** index behind that still costs write overhead. You must `DROP INDEX CONCURRENTLY` it and start over. This is precisely why steps 2 and 3 come first: a failed build on a huge table is hours of wasted time. - On SQL Server the analogue is `CREATE UNIQUE INDEX ... WITH (ONLINE = ON)`. ## Step 5 — attach the index as a constraint ```sql ALTER TABLE users ADD CONSTRAINT users_email_key UNIQUE USING INDEX users_email_uniq; ``` This adopts the already-built index, so it takes a brief strong lock and performs **no rescan**. Run it with a short `lock_timeout` and retry. (Whether you need the named constraint at all is a separate judgement — a unique index already enforces uniqueness; the constraint form is what other constraints and some tooling can reference. Note also that an expression index such as `lower(email)` cannot be adopted as a table constraint, so if you normalise via an expression you keep the unique index rather than converting it — another reason step 1 must be settled early.) ## Step 6 — verify, monitor, and know the rollback - Confirm the index reports as valid in the catalog; a silently invalid index enforces nothing on reads and slows writes. - Watch application error rates: writes that used to succeed now fail with duplicate-key errors, and every caller must handle that. Ideally the application already returns a clean "email already registered" message from step 2, and the constraint error is a last-resort path. - The rollback is cheap and should be written down before you start: drop the constraint or index concurrently. Nothing in this plan is irreversible except the merges, which is another reason the merge step deserves the most scrutiny. ## The judgement being tested The mechanical steps are learnable. What distinguishes a strong answer is sequencing — enforce before you clean, clean before you build — and recognising that the semantic decisions in step 1 and the merge policy in step 3 carry more risk than any DDL statement in the plan.