Add a NOT NULL column to a huge table safely
Instead of a blocking SET NOT NULL that scans the whole table under lock, add a CHECK (col IS NOT NULL) NOT VALID, VALIDATE it without a heavy lock, then flip the column. The constraint goes convalidated f then t.
Problem
What you're actually looking at
The symptom as it shows up on a real server.
Adding NOT NULL to a large existing column forces a full-table scan while holding a strong lock, which can freeze the table. The safe pattern splits the work: add the constraint as NOT VALID (instant, no scan under strong lock), validate it separately (scans without blocking writes), then set the column NOT NULL cheaply.
Meridian needs service_tier NOT NULL on a huge waybills_big table without a maintenance outage. The NOT VALID / VALIDATE split lets the enforcement land without a long exclusive lock.
Simple terms
Telling PostgreSQL a column must never be null normally forces it to scan the whole table to prove no existing row breaks the rule, all while holding a strong lock that can freeze the table. The safe trick is to split the job: first add a CHECK (col IS NOT NULL) marked NOT VALID, which starts enforcing on new rows instantly without the scan; then VALIDATE it in a separate step that scans without blocking writes; then flip the column to NOT NULL cheaply. Same result, no outage.
Full runbook for this incident
- The full identify checklist, the exact signals that tell you it's this incident
- Every diagnostic query; lab output is attached only to the steps we actually captured
- The resolution path and the pitfalls that make it worse
- Mitigation steps to stop it recurring, plus a verify-you're-done query
More in this category
Other Schema migrations runbooks
Neighbouring incidents that share the same diagnostic surface.
Connected
How this connects to the rest of the library
A live view of this page's real cross-references, what explains it, what fixes it, what to tune, and where to go next. Every link is an authored relationship, not a guess.
Need the full procedure?
Pro runbooks finish the incident path
Free runbooks teach the shape. Pro opens the full step transcript, edge cases, and prevention depth.