Skip to main content

How to add a NOT NULL column safely in PostgreSQL

Quick answer: If you can give the column a constant default, just ADD COLUMN ... NOT NULL DEFAULT <const>. It is metadata-only and instant since PostgreSQL 11. If you need a backfilled value with no fixed default, do it in steps: add the column nullable and backfill in batches, then, on PostgreSQL 18+.ADD CONSTRAINT ... NOT NULL <col> NOT VALID (instant) and VALIDATE CONSTRAINT (scans under SHARE UPDATE EXCLUSIVE, so reads and writes continue). On PostgreSQL 17 and earlier you need the longer sequence: a validated CHECK (col IS NOT NULL), then SET NOT NULL (which PostgreSQL 12+ does without re-scanning the table), then drop the check.

Version-sensitive

The fast paths here depend on your major version: constant-default ADD COLUMN is metadata-only from PostgreSQL 11, and the no-scan SET NOT NULL is PostgreSQL 12+. From PostgreSQL 18, not-null constraints are stored in pg_constraint like any other constraint, so you can ADD CONSTRAINT ... NOT NULL <col> NOT VALID and VALIDATE CONSTRAINT later, which removes the ACCESS EXCLUSIVE scan from the picture entirely. Confirm against the version you run. This aligns with which ALTER TABLE statements lock a table.

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 for the instant it updates the catalog.

(On PostgreSQL 10 and earlier, this same statement rewrote the entire table under that lock, the classic migration-that-took-the-site-down.)

The careful case: NOT NULL with a backfill​

When there is no single default, the value must be computed per row, or comes from elsewhere, do not do it in one shot. Do it in steps: add the column, backfill it, and then attach the constraint. On PostgreSQL 18+ attaching the constraint is two commands and never scans under an exclusive lock; on 17 and earlier it is 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 no single statement runs as one long transaction and floods the table with dead tuples. (An UPDATE takes only a ROW EXCLUSIVE lock, it never blocks readers, so the hazard here is 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 done

Batching keeps each transaction short and lets autovacuum keep up (a single table-wide UPDATE is a bloat hazard).

3. Add the NOT NULL constraint NOT VALID, on PostgreSQL 18+, not-null constraints live in pg_constraint like any other constraint, so you can add one unvalidated. No placeholder CHECK is needed:

ALTER TABLE orders ADD CONSTRAINT orders_region_not_null
  NOT NULL region NOT VALID;

This takes an ACCESS EXCLUSIVE lock only for the instant it updates the catalog. There is no scan. The column is enforced against new inserts and updates immediately (pg_attribute.attnotnull flips to true right away); only pre-existing rows go unchecked, and the constraint is marked pg_constraint.convalidated = false until you validate it.

4. Validate it. This is the step that scans, and it runs under SHARE UPDATE EXCLUSIVE, which allows reads and writes for the whole scan:

ALTER TABLE orders VALIDATE CONSTRAINT orders_region_not_null;

That is it. 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 you need 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, which on PostgreSQL 12+ skips the table scan because it trusts the validated CHECK, but is not lock-free: it still takes an ACCESS EXCLUSIVE lock, so run it behind a lock_timeout and retry rather than letting it queue behind a long query and stall all traffic. Then drop the now-redundant check:

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 redundant

On PostgreSQL 11 and earlier, SET NOT NULL still does a full-table verification scan under ACCESS EXCLUSIVE, so guard it with a lock_timeout and run it in a low-traffic window.

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 is only for constant defaults.
  • One giant backfill UPDATE. Not a locking problem (UPDATE is only ROW EXCLUSIVE). It is one very long transaction plus a huge dead-tuple burst that autovacuum cannot clean until it commits. Batch it.

Why does aI-generated migration code get this wrong?​

Your AI coding agent (Claude Code, Cursor) writes the obvious statement.ADD COLUMN ... NOT NULL with a per-row backfill in a single UPDATE, or a volatile default, because it is the direct translation of the request. It cannot see that orders has 200 million rows, or which PostgreSQL version you run (which decides whether the fast paths even apply). Whether a migration is instant or an outage is a function of data volume and version, not the SQL text.

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 EXCLUSIVE step with a short lock_timeout.

How DBGorilla helps​

DBGorilla connects read-only and gives your AI coding agent (Claude Code, Cursor) the context a safe migration needs, the table's row count and size, and the server version that determines which fast path applies, so it can recommend the constant-default one-liner where it is safe, or the nullable-backfill-validate sequence where it is not. It surfaces and explains; it does not run your migrations. Get started free →

FAQ​

Is adding a NOT NULL column with a default slow? Not since PostgreSQL 11, a constant default is metadata-only, no rewrite even with NOT NULL. On 10 and earlier it rewrote the table.

How do I add a NOT NULL column without a fixed default? Add it nullable and backfill in batches. On PostgreSQL 18+ that is followed by just two commands: ADD CONSTRAINT ... NOT NULL <col> NOT VALID, an instant catalog change that immediately enforces the constraint on new inserts and updates, then VALIDATE CONSTRAINT, which scans the pre-existing rows under SHARE UPDATE EXCLUSIVE so reads and writes both continue. No SET NOT NULL, and nothing to drop afterwards. On PostgreSQL 17 and earlier, add a validated CHECK (col IS NOT NULL), then SET NOT NULL, on 12+ the validated check lets it skip the scan, but it still takes a brief ACCESS EXCLUSIVE lock, so use a lock_timeout, then drop the check.

Why backfill in batches? Not for locking, an UPDATE takes only ROW EXCLUSIVE and does not block readers. A single table-wide UPDATE is one long transaction with a dead-tuple burst that autovacuum cannot reclaim while it is open, so the table bloats. Batches keep transactions short and let vacuum keep up.

Does a volatile default rewrite the table? Yes, the metadata-only path is only for constant defaults. A volatile default like random() rewrites every row under ACCESS EXCLUSIVE.