How to Add a NOT NULL Column Without Locking Your Postgres Table

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.

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.

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.

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. Paste the migration below into Migration Checker first: it correctly tells these two cases apart, flagging SET NOT NULL on an existing column andADD COLUMN NOT NULL without a default as dangerous, while recognizing ADD COLUMN ... NOT NULL DEFAULT as safe.