Add a NOT NULL Column to a 50M-Row Prod Table
Scenario
Product needs a new column on the busiest table in your PostgreSQL database — roughly 50 million rows, sitting directly on the application's hot path with constant reads and writes, plus a couple of read replicas hanging off the primary. The requirement says the column must be NOT NULL with a sensible value for every existing row. The table cannot take meaningful downtime: if it locks up, the product locks up.
The Quick Fix on the Table
A teammate has the migration ready to go: one ALTER TABLE ... ADD COLUMN ... DEFAULT ... NOT NULL statement, run during business hours. One statement, one deploy, done by lunch. It works instantly on their laptop database, so how bad can it be?
The quick fix is on the table and the room is waiting for your call. Would you sign off on it? Take a position and justify it — out loud or on paper — before revealing the analysis.
Why the Quick Fix Fails
- DDL takes the strongest lock there is.
ALTER TABLEneeds anACCESS EXCLUSIVElock. For however long the statement runs, every read and write against the table blocks — on the hot path, that is an outage by another name. - The lock queue is the real killer. Even if the ALTER itself would be fast, it must first wait for every in-flight query to finish. While it waits, every new query queues behind it. One long-running report or analytics query in front of your ALTER turns "a few seconds" into minutes of a completely frozen table.
- Validation and rewrites scale with row count. Depending on the Postgres version and the exact form of the statement, adding the constraint or a non-static default can force a full scan — or a full table rewrite — of all 50 million rows while the exclusive lock is held. What was instant on a laptop with a thousand rows is a multi-minute lockout at production scale.
- Old application code becomes a landmine. The moment
NOT NULLis enforced, any code path still inserting rows without the column starts throwing errors. A single-step migration gives you no window in which old and new code can coexist — which is exactly the window a rolling deploy needs. - There is no graceful abort. Half-way through a locked, rewriting ALTER your only option is to cancel and roll the transaction back — extending the freeze — while replicas replay the same pain downstream, stalling reads that were supposed to be your safety valve.
The interviewer nods: “Fine, the quick fix is off the table. So what exactly would you do — step by step?” Sketch your plan before revealing the approach.
The Right Approach
Split the one-liner into stages that each hold only brief, cheap locks, with a verification gate between them. This is the expand–backfill–contract pattern applied to a constraint.
- Add the column nullable (and, on modern Postgres, with a static default).
ALTER TABLE orders ADD COLUMN region text DEFAULT 'eu';— on PostgreSQL 11+ a static default is a catalog-only change: no rewrite, lock held for milliseconds. Do not addNOT NULLyet. Set alock_timeout(e.g.SET lock_timeout = '3s') so if the ALTER cannot get its lock quickly it fails fast instead of freezing the table behind a lock queue — then retry. - Ship application code that always writes the column. Deploy the change so every new and updated row carries a real value. From this point the NULL population can only shrink.
- Backfill existing rows in small batches. Loop
UPDATE orders SET region = ... WHERE id BETWEEN ... AND ... AND region IS NULLin chunks of a few thousand rows, committing between batches, with pauses sized against replication lag and primary load. Never one 50M-row UPDATE — that bloats the table, blows up the WAL stream, and stalls the replicas. - Verify completeness before enforcing anything.
SELECT count(*) FROM orders WHERE region IS NULL;must be zero — and stay zero, which proves step 2 actually covered every write path. - Add the constraint as NOT VALID, then validate separately.
ALTER TABLE orders ADD CONSTRAINT region_not_null CHECK (region IS NOT NULL) NOT VALID;takes only a brief lock and enforces the rule for new writes. ThenALTER TABLE orders VALIDATE CONSTRAINT region_not_null;scans the 50M rows holding only a weak lock that does not block reads or writes. - Optionally convert to a real column-level NOT NULL. On PostgreSQL 12+, with the validated CHECK constraint in place,
ALTER TABLE orders ALTER COLUMN region SET NOT NULL;skips the full-table scan and completes quickly; afterwards the redundant CHECK constraint can be dropped. - Rehearse and monitor. Run the whole sequence against a production-sized copy (a snapshot restore) first, and watch lock waits, replication lag and p99 latency at every stage in production, with a documented stop/rollback point per stage.
Final pushback: “Your plan costs more time and money than the quick fix. Convince me.” How do you defend your position under pressure?
How to Defend It
- "The single ALTER doesn't just lock the table while it runs — it queues behind every running query, and everything else queues behind it. One slow report in front of us and the hot path is frozen for minutes."
- "It working instantly on a laptop is exactly the trap: the cost of this statement scales with row count and concurrency, and production has 50 million rows and constant traffic."
- "The staged plan touches the same 50 million rows, but always under locks measured in milliseconds — the expensive work happens in batches and in a validation scan that doesn't block anyone."
- "Each stage has a checkpoint and a way back. If the backfill misbehaves we pause it; if the one-liner misbehaves mid-lock, our only option is an outage either way — cancel or wait."
- "This costs us a few days of elapsed time instead of one deploy, but the engineering time is nearly the same — we're trading calendar patience for zero customer impact."