Adding a NOT NULL Column in Postgres: When It Locks and When It Doesn't

If you're adding a brand-new column and can give it a constant default, you can stop reading and just run this:

ALTER TABLE users ADD COLUMN plan text NOT NULL DEFAULT 'free';

Since Postgres 11, this is a single fast statement — no table rewrite, and no scan to verify existing rows either. Postgres knows every existing row will read back as the default value, and a non-null default can't violate NOT NULL, so there's nothing to check. This works at any table size.

One caveat: it only holds for a non-volatile default. Per the Postgres docs, a volatile default such as clock_timestamp() (or a stored generated or identity column) still rewrites the entire table and its indexes, so a constant or otherwise non-volatile default is what makes this fast.

The actual risk: adding NOT NULL to a column that already has data

The dangerous case is different: a column that already exists in a populated table, where you now want to enforce NOT NULL retroactively — a field that's been optional since launch and now needs to be required, for example.

ALTER TABLE users ALTER COLUMN plan SET NOT NULL;

Run this directly and Postgres has to scan every existing row to prove none of them are null, holding an ACCESS EXCLUSIVE lock for the entire scan. On a small table that's instant and nobody notices. On a table with tens of millions of rows, that scan can run for minutes — and every read and write against the table queues up behind that lock until it finishes.

The safe pattern: validate without a hard lock

The trick is to split "add the constraint" from "prove it holds for existing data" into two separate steps, because Postgres gives you a much cheaper way to do the second one.

1. Backfill any remaining NULLs, in batches

UPDATE users SET plan = 'free'
WHERE plan IS NULL
  AND id BETWEEN 1 AND 50000;
-- repeat for the next id range, or loop this in a script

A single unbatched UPDATE over millions of rows is its own problem — it's one long transaction holding row locks and generating a large amount of WAL/vacuum work all at once. Batching it (by id range, or with a script that sleeps briefly between batches) keeps each transaction short.

2. Add the constraint as NOT VALID first

ALTER TABLE users
  ADD CONSTRAINT users_plan_not_null CHECK (plan IS NOT NULL) NOT VALID;

With NOT VALID, Postgres adds the constraint without scanning existing rows — it takes the usual ACCESS EXCLUSIVE lock, but only briefly, since there's no data to check. It applies to new/updated rows from this point forward.

That brief lock still has to wait for any transaction already touching the table, and by default Postgres waits indefinitely for a lock. Setting a lock_timeout first makes the statement give up instead of waiting, so you can retry at a quieter moment:

SET lock_timeout = '5s';

3. Validate it separately

ALTER TABLE users VALIDATE CONSTRAINT users_plan_not_null;

This is the step that actually scans the table to confirm every row satisfies the check — but per Postgres's own documentation it takes only a SHARE UPDATE EXCLUSIVE lock, which doesn't block normal reads or writes. Concurrent traffic keeps flowing while this runs.

4. Set NOT NULL for real

ALTER TABLE users ALTER COLUMN plan SET NOT NULL;
ALTER TABLE users DROP CONSTRAINT users_plan_not_null;

Since Postgres 12, if a validated CHECK constraint already guarantees the column can't be null, this step is metadata-only — Postgres trusts the existing constraint instead of re-scanning. The temporary check constraint can be dropped once the real NOT NULL is in place. Run the two statements separately, as above: the scan is only skipped if the CHECK constraint is not dropped in the same command.

On PostgreSQL 18 and later

PostgreSQL 18 removes the need for the temporary CHECK constraint. Its release notes list "Allow ALTER TABLE to set the NOT VALID attribute of NOT NULL constraints", which means the NOT NULL constraint itself can be added as NOT VALID (enforced for new writes, no scan of existing rows) and then validated separately with the same VALIDATE CONSTRAINT step. Check the exact syntax in the PostgreSQL 18 documentation for your minor version before relying on it. On PostgreSQL 12 to 17, the temporary constraint pattern above is still the way to do it.

Before you run any of this on production

Migrations like this are exactly the kind of thing worth double-checking before they hit a live database — a missing batch, a dropped default, or a step run out of order can turn a zero-downtime plan into a locked table. Run the migration through Migration Checker first. It tells these cases apart: it flags SET NOT NULL on an existing column (unless a validated CHECK (col IS NOT NULL) earlier in the same script lets PostgreSQL skip the scan) and ADD COLUMN NOT NULL without a default as dangerous, and reports ADD COLUMN ... NOT NULL DEFAULT with a constant default as metadata-only on PostgreSQL 11 and later. A column whose type is a domain with constraints is reported as a warning, because that can force a rewrite.