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
Card required. Cancel before day 7 and you are not charged.
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.