You need to make an existing, heavily-used column NOT NULL on a table with hundreds of millions of rows, some of which currently hold NULL. How do you get there without a long outage?
answer
- naive SET NOT NULL = strong lock + full scan
- lock queue: your DDL blocks everything behind it
- batch backfill by PK range, pause for replication
- CHECK ... IS NOT NULL as NOT VALID → VALIDATE → SET NOT NULL
- short lock_timeout + retry, always
basics
~20 sBackfill the NULLs in batches first, stop new NULLs from arriving (application change or a CHECK ... IS NOT NULL added as NOT VALID), validate that check with a non-blocking scan, then promote to NOT NULL — engines can use the proven check to skip the full re-scan under the blocking lock.
solid answer
~60 sA naive `ALTER TABLE ... SET NOT NULL` takes a strong table lock and scans every row to prove no NULL exists, so on a huge table it blocks reads and writes for the length of the scan. Sequence it instead: 1. **Stop the bleeding.** Make the application always write a value, and give the column a DEFAULT so omitted-column inserts stop producing NULLs. 2. **Backfill in batches** — bounded UPDATE chunks by primary key, committing each, with pauses so replication lag and vacuum/undo pressure stay controlled. 3. **Add a CHECK (col IS NOT NULL) as NOT VALID** (Postgres) or the engine's equivalent. This is a metadata-only change that enforces all new writes immediately. 4. **VALIDATE** the constraint. The validation scan takes only a weak lock and runs concurrently with traffic. 5. **SET NOT NULL.** With a validated proof present, Postgres 12+ skips the re-scan, so the strong lock is held only briefly. Drop the redundant CHECK afterwards. Always take the DDL lock with a short lock timeout and retry, so you never queue behind a long transaction and block the table.
go deeper
Know that the change requires proving no NULLs exist, so it scans and locks the table, and that existing NULLs must be backfilled first.
Lay out the ordered steps — stop new NULLs, batched backfill, then the constraint — and explain why a strong table lock on a big table is an outage.
Give the NOT VALID → VALIDATE → SET NOT NULL sequence with its version caveat, lock_timeout and retry hygiene, and batch-size control tied to replication lag.
Frame the migration as a data-semantics decision plus a risk-managed rollout: what NULL meant, whether the constraint is worth it, and the fallback (online schema change tool or new column) when the engine cannot do it in place.
## Why the naive statement hurts Making a column NOT NULL is a promise the engine must verify: it has to prove no existing row holds NULL. The straightforward `ALTER TABLE t ALTER COLUMN c SET NOT NULL` acquires the strongest table lock and performs a full scan while holding it. Two separate problems follow. First, the duration — a full scan of a large table is minutes to hours, and reads and writes queue behind it. Second, and nastier, is the *lock queue*: a pending strong lock request blocks every later request, even ordinary SELECTs. If a long-running transaction is holding a weak lock on the table, your ALTER waits behind it and everything else waits behind your ALTER. The table appears dead long before the scan even starts. ## The staged approach **1. Stop new NULLs.** Nothing else matters if NULLs keep arriving. Update the write paths to always supply a value, and add a DEFAULT to the column so inserts that omit it get a real value. Note the limit of DEFAULT: it does not stop a caller that explicitly writes NULL, which is why the application change or a constraint is still needed. **2. Backfill in bounded batches.** Update NULL rows in chunks driven by primary-key ranges, one transaction per chunk, with a short pause between chunks. Batching keeps each transaction's lock footprint and undo/WAL volume small, avoids a single giant transaction that stalls vacuum or fills undo, and lets replication catch up. Track progress by key range rather than repeatedly scanning for remaining NULLs, and index or partition-scan to find them if the column has no useful index. **3. Add the check without blocking.** In Postgres, `ALTER TABLE t ADD CONSTRAINT c_nn CHECK (col IS NOT NULL) NOT VALID` is metadata-only: it takes a brief strong lock, enforces every subsequent write, and asserts nothing about existing rows. Other engines have analogues — Oracle's `ENABLE NOVALIDATE`, SQL Server's `WITH NOCHECK`. From this point the table can only get cleaner. **4. Validate.** `ALTER TABLE t VALIDATE CONSTRAINT c_nn` scans the table under a lock weak enough to allow reads and writes. If it fails, some rows were missed; fix and re-run. Validation is restartable in the practical sense that a failure leaves the NOT VALID constraint in place, still enforcing new writes. **5. Promote to NOT NULL.** Since Postgres 12, `SET NOT NULL` recognises a validated `CHECK (col IS NOT NULL)` as proof and skips the scan, so the strong lock is momentary. Then drop the now-redundant CHECK. On engines without that optimisation, you either accept a scan during a maintenance window or keep the validated check as the practical equivalent of NOT NULL. ## Lock hygiene around the DDL Every strong-lock step should run with a short `lock_timeout` (a second or two) and an automatic retry loop. That converts "we blocked the whole table for ten minutes" into "the DDL failed and we tried again". Also confirm no long-running transactions or idle-in-transaction sessions hold the table, since they are what your DDL will queue behind. ## Alternatives Where the engine cannot do the staged promotion — notably MySQL, where changing a column to NOT NULL may require a table copy depending on version and algorithm, and where existing NULLs are silently coerced under non-strict SQL modes — the usual routes are an online schema-change tool (shadow table plus trigger-maintained copy plus atomic rename) or a rolling migration through a new column. In all cases decide beforehand what the NULLs *mean*: backfilling with a sentinel value quietly converts "unknown" into a real value, and that is a data-modelling decision, not a migration detail. Sometimes the right answer is that the column should stay nullable and the application should handle absence.
- Why does an ALTER on a busy table sometimes freeze even ordinary SELECTs that ought to be compatible with each other?The strong lock request the ALTER makes queues behind whatever weak lock is currently held, and lock requests are granted in order. Every request arriving after the ALTER queues behind it, including SELECTs that would otherwise be compatible with the current holder. The result is a full stall until the blocking transaction ends. A short lock_timeout with retries prevents the pile-up.
- What should the backfill write into the NULL rows?That is a modelling decision, not a mechanical one. If NULL meant 'not yet set' and a sensible default exists, backfill it. If NULL meant 'genuinely unknown', writing a sentinel destroys that information and you should question whether the column should be NOT NULL at all. Whatever you choose, apply the same rule in the application so new rows agree with backfilled ones.
- How do you keep the batched backfill from hurting the system?Bound each batch by primary-key range, commit per batch, and sleep between batches so replicas can catch up and cleanup processes can reclaim old row versions. Monitor replication lag and undo/WAL growth and slow down when they rise. Avoid one giant UPDATE, which holds locks for a long time, generates enormous WAL/undo, and blocks vacuum from advancing.
saying these in an interview costs you the question
- Running a bare SET NOT NULL on a huge table and expecting it to be quick
- Assuming a lock only affects writers, so reads keep flowing
- Believing adding a DEFAULT alone eliminates future NULLs (explicit NULLs still pass through it)
- Backfilling with one enormous UPDATE statement
- Treating the choice of backfill value as trivial rather than a data-meaning decision