How to add a NOT NULL column safely in PostgreSQL
Quick answer: If you can give the column a constant default, use
ADD COLUMN ... NOT NULL DEFAULT <const>. This is metadata-only and instant since PostgreSQL 11. If you need a backfilled value with no fixed default, add the column nullable, backfill it in batches, then, on PostgreSQL 18+, runADD CONSTRAINT ... NOT NULL <col> NOT VALID(instant) andVALIDATE CONSTRAINT(scans underSHARE UPDATE EXCLUSIVE, so reads and writes continue). On PostgreSQL 17 and earlier, use the longer sequence: a validatedCHECK (col IS NOT NULL), thenSET NOT NULL(which PostgreSQL 12+ does without re-scanning the table), then drop the check.
The easy case: a constant default
Section titled “The easy case: a constant default”If every existing row can share the same value, one statement does it:
ALTER TABLE orders ADD COLUMN status text NOT NULL DEFAULT 'active';Since PostgreSQL 11, this is metadata-only: the default is stored in the
catalog and applied to existing rows logically, so there is no table rewrite even
though the column is NOT NULL. It takes an ACCESS EXCLUSIVE lock only while
updating the catalog.
(On PostgreSQL 10 and earlier, this statement rewrote the entire table under that lock.)
The careful case: NOT NULL with a backfill
Section titled “The careful case: NOT NULL with a backfill”When there is no single default because the value must be computed per row or comes
from elsewhere, do not do it in one statement. Add the column, backfill it, and
then attach the constraint. On PostgreSQL 18+, attaching the constraint uses
two commands and does not scan under an exclusive lock. On 17 and earlier, use
a longer sequence built around a placeholder CHECK.
1. Add the column nullable (instant):
ALTER TABLE orders ADD COLUMN region text;2. Backfill in batches so that no single statement runs as one long
transaction and creates a large number of dead tuples. (An UPDATE takes only a
ROW EXCLUSIVE lock, which does not block readers. The risks here are transaction
length and bloat, not locking.)
UPDATE orders SET region = compute_region(id)WHERE region IS NULL AND id BETWEEN 1 AND 10000;-- repeat in ranges until doneBatching keeps each transaction short and gives autovacuum time to keep up. A
single table-wide UPDATE creates a
bloat
hazard.
3. Add the NOT NULL constraint NOT VALID. On PostgreSQL 18+, not-null
constraints live in pg_constraint like other constraints, so you can add one
without validating existing rows. No placeholder CHECK is needed:
{/* sql-check: pg 18+ */}
ALTER TABLE orders ADD CONSTRAINT orders_region_not_null NOT NULL region NOT VALID;This takes an ACCESS EXCLUSIVE lock only while updating the catalog. There is no
scan. The column is enforced against new inserts and updates immediately
(pg_attribute.attnotnull flips to true right away). Existing rows remain
unchecked, and the constraint is marked
pg_constraint.convalidated = false until you validate it.
4. Validate it. This step scans the table and runs under
SHARE UPDATE EXCLUSIVE, which allows reads and writes during the scan:
{/* sql-check: pg 18+ */}
ALTER TABLE orders VALIDATE CONSTRAINT orders_region_not_null;There is no SET NOT NULL step and nothing to drop afterwards.
On PostgreSQL 17 and earlier, not-null is only a column attribute, so use the
older four-step sequence: add a placeholder CHECK (region IS NOT NULL) NOT VALID, VALIDATE it under SHARE UPDATE EXCLUSIVE, then SET NOT NULL. On
PostgreSQL 12+, that last step skips the table scan because it trusts the
validated CHECK, but it still takes an ACCESS EXCLUSIVE lock. Run it behind a
lock_timeout and retry rather than letting it queue behind a long query and
stall traffic. Then drop the redundant check:
{/* sql-check: isolated */}
ALTER TABLE orders ADD CONSTRAINT orders_region_check CHECK (region IS NOT NULL) NOT VALID;ALTER TABLE orders VALIDATE CONSTRAINT orders_region_check;ALTER TABLE orders ALTER COLUMN region SET NOT NULL;ALTER TABLE orders DROP CONSTRAINT orders_region_check; -- now redundantOn PostgreSQL 11 and earlier, SET NOT NULL still performs a full-table
verification scan under ACCESS EXCLUSIVE, so guard it with a lock_timeout and
run it during a low-traffic window.
What to avoid
Section titled “What to avoid”- A volatile default.
ADD COLUMN ... DEFAULT random()(or any volatile function) must differ per row, so it rewrites the whole table. The metadata-only path applies only to constant defaults. - One giant backfill UPDATE. The problem is not locking (
UPDATEis onlyROW EXCLUSIVE) but one very long transaction and a large burst of dead tuples that autovacuum cannot clean until the transaction commits. Batch the update.
Why does AI-generated migration code get this wrong?
Section titled “Why does AI-generated migration code get this wrong?”Your AI coding agent writes the obvious statement: ADD COLUMN ... NOT NULL with
a per-row backfill in a single UPDATE, or a volatile default, because that is
the direct translation of the request. It cannot see that orders has 200
million rows or which PostgreSQL version you run. That version determines whether
the fast paths apply. Whether a migration is instant or causes an outage depends
on data volume and version, not only on the SQL text.
How do I keep migrations safe?
Section titled “How do I keep migrations safe?”- Prefer the constant-default one-liner when you can (PG 11+).
- Otherwise on PG 18+: nullable → batch backfill →
NOT NULL ... NOT VALID→VALIDATE CONSTRAINT. - On PG 17 and earlier: nullable → batch backfill → validated CHECK →
SET NOT NULL→ drop the check. - Guard every
ACCESS EXCLUSIVEstep with a shortlock_timeout.
Is adding a NOT NULL column with a default slow in PostgreSQL?
Not since PostgreSQL 11. Adding a column with a constant (non-volatile) default is metadata-only, the default is recorded in the catalog and applied to existing rows logically, so there is no table rewrite even with NOT NULL. On PostgreSQL 10 and earlier, that same statement rewrote the whole table.
How do I add a NOT NULL column without a fixed default?
Add the column nullable and backfill the values in batches. On PostgreSQL 18+ you then need only two more commands: ALTER TABLE ... ADD CONSTRAINT ... NOT NULL col NOT VALID, which is an instant catalog change that immediately enforces the constraint on new inserts and updates, followed by ALTER TABLE ... VALIDATE CONSTRAINT, which scans the pre-existing rows under SHARE UPDATE EXCLUSIVE and so allows both reads and writes. There is no SET NOT NULL step and nothing to drop afterwards. On PostgreSQL 17 and earlier, add a CHECK (col IS NOT NULL) constraint NOT VALID, VALIDATE it, then run SET NOT NULL, on 12+ that skips the full-table scan because it trusts the validated CHECK, but it still takes a brief ACCESS EXCLUSIVE lock, so run it with a lock_timeout and retry, then drop the now-redundant CHECK.
Why backfill in batches instead of one UPDATE?
Not because of a table lock, an UPDATE takes only ROW EXCLUSIVE, which does not block reads. The problem is that a single UPDATE of every row runs as one long transaction and creates a huge burst of dead tuples; the open transaction holds back the xmin horizon so autovacuum cannot clean them up until it commits, causing bloat. Batching keeps each transaction short and lets vacuum keep pace.
Does adding a column with a volatile default rewrite the table?
Yes. The metadata-only optimization only applies to constant, non-volatile defaults. A volatile default such as random() must produce a different value per row, so PostgreSQL rewrites every row under an ACCESS EXCLUSIVE lock.