You need to make an existing nullable column NOT NULL on a busy table of 200 million rows, with no maintenance window. Describe a rollout that avoids holding a long exclusive lock.

## Why the naive statement is dangerous `ALTER TABLE t ALTER COLUMN c SET NOT NULL` looks like a metadata change, and the flag itself is. But the server must prove no existing row holds NULL, so it scans the whole table — and because the operation takes `ACCESS EXCLUSIVE`, that scan happens with every reader and writer locked out. At 200 million rows, that is minutes of total unavailability for the table, plus the lock-queue amplification where every query arriving during the wait piles up behind the pending DDL. ## The staged rollout ### 1. Stop producing NULLs Nothing else matters if the application keeps writing NULLs, because your backfill will chase a moving target. Deploy the code change first: the column is always populated, with a sensible default where the value is genuinely unknown. This is a separate release from the schema change, and it should soak long enough that you trust it. ### 2. Backfill in batches ```sql UPDATE t SET c = <value> WHERE c IS NULL AND id BETWEEN ? AND ?; ``` Key the batches on an indexed column and keep each transaction small — thousands of rows, not millions. Reasons: short row-lock windows so concurrent writers are not blocked; bounded WAL generation so replicas do not fall behind; and the ability to pause or resume without losing work. A partial index on `WHERE c IS NULL` can make finding the remaining rows cheap and shrinks to nothing as you progress. ### 3. Add the CHECK constraint NOT VALID ```sql SET lock_timeout = '2s'; ALTER TABLE t ADD CONSTRAINT t_c_not_null CHECK (c IS NOT NULL) NOT VALID; ``` This is the pivot of the whole plan. It takes `ACCESS EXCLUSIVE` but only for the catalog write — no scan — so it completes in milliseconds. From that moment the database itself refuses NULLs in new and updated rows, so the invariant is enforced even if some code path you missed tries to write one. You can run it before or after the backfill; running it early makes the backfill converge, which is usually preferable once step 1 has soaked. ### 4. Validate ```sql ALTER TABLE t VALIDATE CONSTRAINT t_c_not_null; ``` The scan happens here, under `SHARE UPDATE EXCLUSIVE` — a lock that does not conflict with `SELECT`, `INSERT`, `UPDATE` or `DELETE`. The application keeps running. If any NULL rows remain the statement fails and the constraint stays `NOT VALID`, costing you nothing but the scan; finish the backfill and rerun. ### 5. Flip SET NOT NULL ```sql SET lock_timeout = '2s'; ALTER TABLE t ALTER COLUMN c SET NOT NULL; ``` This still needs `ACCESS EXCLUSIVE`, but from PostgreSQL 12 the server recognises a validated `CHECK (c IS NOT NULL)` as proof and skips the verification scan. The lock is therefore held for milliseconds rather than minutes. On PostgreSQL 11 and earlier, no such shortcut exists and this step scans — which is exactly why the version matters and why it is worth stating explicitly. ### 6. Clean up Drop the CHECK constraint once `NOT NULL` is in place; keeping both means every write evaluates the same predicate twice. ## The lock-timeout discipline Every step that takes `ACCESS EXCLUSIVE` — steps 3 and 5 — must run with a short `lock_timeout` and a retry loop. Without it, the DDL waits behind any long-running reader while every subsequent query queues behind the DDL, converting a millisecond operation into an outage. With it, the worst case is that the statement fails after two seconds and you try again in a minute. Before running, it is also worth checking for `idle in transaction` sessions, which hold locks indefinitely and are the usual culprit. ## Why not just add a DEFAULT? A `DEFAULT` supplies a value when the column is omitted; it does nothing about rows that explicitly write NULL, and nothing about existing rows. It is a useful companion to the rollout — it makes step 1 easier — but it is not a substitute for the constraint. (Distinct case worth not confusing: adding a *new* column with a default is metadata-only from PostgreSQL 11 onward and does not rewrite the table. That is about adding a column, not about constraining an existing one.) ## Rollback and verification Each step is independently reversible: drop the constraint, or `DROP NOT NULL`. Verify at the end by reading the catalog (`attnotnull` on the column) rather than trusting the migration log, and keep a monitoring query for remaining NULLs until the constraint is validated. The overall pattern — deploy code, backfill in batches, add an unvalidated constraint, validate under a weak lock, flip the cheap metadata bit — generalises to almost every online schema change you will do.