Skip to content

How to add a NOT NULL column safely in PostgreSQL

Last updated

View as Markdown

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+, run 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, use 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.

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 done

Batching 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 redundant

On 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.

  • 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 (UPDATE is only ROW 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.

  • 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.
  • 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.