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's 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, backfill in batches, add a validated CHECK (col IS NOT NULL), then SET NOT NULL (which PostgreSQL 12+ does without re-scanning the table).

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+. 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's 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's no single default — the value must be computed per row, or comes from elsewhere — don't do it in one shot. Do it in four safe steps:

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 and validate a CHECK constraint — the NOT VALID step is quick, and VALIDATE uses a weaker SHARE UPDATE EXCLUSIVE lock that allows reads and writes:

ALTER TABLE orders ADD CONSTRAINT orders_region_not_null
  CHECK (region IS NOT NULL) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_region_not_null;

4. Set NOT NULL — on PostgreSQL 12+ this skips the table scan entirely, because it trusts the already-validated CHECK. Fast, but 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:

ALTER TABLE orders ALTER COLUMN region SET NOT NULL;
ALTER TABLE orders DROP CONSTRAINT orders_region_not_null;  -- 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's one very long transaction plus a huge dead-tuple burst that autovacuum can't 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's the direct translation of the request. It can't 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: nullable → batch backfill → validated CHECK → SET NOT NULL.
  • 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's safe, or the nullable-backfill-validate sequence where it isn't. 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, backfill in batches, add a validated CHECK (col IS NOT NULL), then SET NOT NULL — on PostgreSQL 12+ the validated check lets it skip the scan. It still takes a brief ACCESS EXCLUSIVE lock, so use a lock_timeout.

Why backfill in batches? Not for locking — an UPDATE takes only ROW EXCLUSIVE and doesn't block readers. A single table-wide UPDATE is one long transaction with a dead-tuple burst that autovacuum can't reclaim while it's 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.