Does SET NOT NULL lock the table in PostgreSQL?

Short answer

ALTER TABLE orders ALTER COLUMN customer_id SET NOT NULL takes an ACCESS EXCLUSIVE lock and then reads the whole table to check that no row has a NULL there. It doesn’t rewrite anything, but every query on the table waits until the scan finishes: about 0.1 s for our 3 million rows, and proportionally longer on a big table.

PostgreSQL skips the scan when a valid CHECK (customer_id IS NOT NULL) constraint already exists. You can add and validate that constraint without blocking reads or writes, so the safe recipe is:

1. ADD CONSTRAINT … CHECK (customer_id IS NOT NULL) NOT VALID (instant)
2. VALIDATE CONSTRAINT … (scans, but reads and writes carry on)
3. SET NOT NULL (instant now: no scan)
4. DROP CONSTRAINT … (instant)

On PostgreSQL 18 you can do it in two steps with a NOT NULL … NOT VALID constraint instead.

What it locks

SHARE UPDATE EXCLUSIVE conflicts only with other schema changes, VACUUM and ANALYZE, and CREATE INDEX CONCURRENTLY on the same table. Ordinary SELECT, INSERT, UPDATE and DELETE don’t wait for it.

A NOT VALID check doesn’t help SET NOT NULL: with one in place, it still scanned.

Does it rewrite the table?

No. The data file stays the same; SET NOT NULL only reads. If it finds a NULL, it stops with:

ERROR:  column "a" of relation "t" contains null values

VALIDATE CONSTRAINT fails the same way (check constraint "…" is violated by some row) and leaves the constraint NOT VALID, so fix the rows and validate again.

Run it safely

1. Fill in the NULLs first, in batches. One UPDATE of millions of rows holds row locks for its whole run and leaves a lot for vacuum; batches of a few thousand don’t:

UPDATE orders SET customer_id = 0
WHERE id BETWEEN 1 AND 10000 AND customer_id IS NULL;
-- repeat for the next range until no rows are left

Make sure new rows can’t be NULL either (application code or a column default) before you go on.

2. Add the constraint without checking old rows, then validate it. Set lock_timeout for the step that takes ACCESS EXCLUSIVE, even though it’s instant, so it can’t queue behind a long query:

SET lock_timeout = '2s';
ALTER TABLE orders ADD CONSTRAINT orders_customer_id_not_null
  CHECK (customer_id IS NOT NULL) NOT VALID;

ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_id_not_null;

From the first statement on, new and updated rows are checked. The second reads the whole table but lets other sessions read and write.

3. Set NOT NULL and drop the helper constraint:

BEGIN;
SET LOCAL lock_timeout = '2s';
ALTER TABLE orders ALTER COLUMN customer_id SET NOT NULL;   -- no scan
ALTER TABLE orders DROP CONSTRAINT orders_customer_id_not_null;
COMMIT;

If any step fails with ERROR: canceling statement due to lock timeout, nothing has changed; run that step again after a pause. See lock timeout.

On PostgreSQL 18: NOT NULL constraints are now real constraints with names, and can be added NOT VALID:

SET lock_timeout = '2s';
ALTER TABLE orders ADD CONSTRAINT orders_customer_id_not_null
  NOT NULL customer_id NOT VALID;                                   -- instant
ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_id_not_null;  -- scans; reads and writes run

After the VALIDATE, the column is NOT NULL and there’s nothing to drop. While the constraint is still NOT VALID, new rows are already checked and \d already shows the column as not null. Don’t run SET NOT NULL in between: with an unvalidated NOT NULL constraint, it validates it by scanning under ACCESS EXCLUSIVE.

How we checked

On PostgreSQL 14.24, 15.19, 16.14, 17.11 and 18.6 (Docker on a Mac), each step in a rolled-back transaction on a 3-million-row table:

CREATE TABLE seo_mig.big (id bigint, n int, s text);
INSERT INTO seo_mig.big SELECT g, g % 1000, md5(g::text) FROM generate_series(1, 3000000) g;

BEGIN;
SELECT seq_scan FROM pg_stat_xact_user_tables WHERE relid = 'seo_mig.big'::regclass;  -- before
ALTER TABLE seo_mig.big ALTER COLUMN n SET NOT NULL;
SELECT relation::regclass, mode FROM pg_locks
WHERE pid = pg_backend_pid()
  AND relation IN (SELECT oid FROM pg_class WHERE relnamespace = 'seo_mig'::regnamespace);
SELECT seq_scan FROM pg_stat_xact_user_tables WHERE relid = 'seo_mig.big'::regclass;  -- after
ROLLBACK;

pg_stat_xact_user_tables.seq_scan counts the table scans made by the current transaction, so a difference of 1 means SET NOT NULL read the table. Times are the median of three runs.

With a second session holding each step open on a 1,000-row table, a third session’s SELECT and INSERT (with lock_timeout = '1s') waited during SET NOT NULL and ran during VALIDATE CONSTRAINT, on all five versions. On 14–17, ADD CONSTRAINT … NOT NULL … NOT VALID is a syntax error. On 18.6 it took AccessExclusiveLock with no scan; its VALIDATE took ShareUpdateExclusiveLock with one scan, and afterwards convalidated was true. While the constraint was still NOT VALID, an insert of NULL already failed with null value in column "n" of relation "big" violates not-null constraint.

In Inlet

The structure editor shows the DDL for a nullability change before it runs and warns when a change rewrites or scans the table. While a scan runs, the Activity monitor shows which sessions are queued behind it, and lets you cancel it.