Learning center

Make PostgreSQL SET NOT NULL skip the table scan

Making a column required with ALTER COLUMN ... SET NOT NULL reads the whole table while it holds a lock that blocks reads and writes. On PostgreSQL 12 and later it skips that read when a validated CHECK (column IS NOT NULL) constraint already proves the column has no nulls. Validate that constraint first, under a weaker lock, and the SET NOT NULL itself becomes a catalog change.

Watch PostgreSQL scan the table#

A payments table needs currency to be required. Two million rows hold a value, and one row written by an old import does not. We ran this on PostgreSQL 16.15 with client_min_messages set to debug1, which makes PostgreSQL say when it verifies a table.

CREATE TABLE payments (id integer PRIMARY KEY, currency text);
INSERT INTO payments SELECT g, 'EUR' FROM generate_series(1, 2000000) g;
UPDATE payments SET currency = NULL WHERE id = 1999999;

SET client_min_messages = debug1;
\timing on
ALTER TABLE payments ALTER COLUMN currency SET NOT NULL;
DEBUG:  verifying table "payments"
ERROR:  column "currency" of relation "payments" contains null values
Time: 104.780 ms

After fixing the row, the same statement succeeds after another full scan:

UPDATE payments SET currency = 'EUR' WHERE id = 1999999;
ALTER TABLE payments ALTER COLUMN currency SET NOT NULL;
DEBUG:  verifying table "payments"
ALTER TABLE
Time: 74.766 ms

Both attempts held an ACCESS EXCLUSIVE lock for the length of the scan. At two million narrow rows that is about a tenth of a second. The time grows with the table, and every query on it waits.

Prove it with a constraint first#

Remove the NOT NULL again to try the other route. Add a CHECK constraint as NOT VALID, which is a quick catalog change, then validate it:

ALTER TABLE payments ALTER COLUMN currency DROP NOT NULL;

ALTER TABLE payments
    ADD CONSTRAINT payments_currency_not_null
    CHECK (currency IS NOT NULL) NOT VALID;            -- 0.7 ms

ALTER TABLE payments
    VALIDATE CONSTRAINT payments_currency_not_null;    -- 73.9 ms, scans

ALTER TABLE payments ALTER COLUMN currency SET NOT NULL;
DEBUG:  existing constraints on column "payments.currency" are sufficient to prove that it does not contain nulls
ALTER TABLE
Time: 0.496 ms

Run each statement in its own transaction, as psql does in its default autocommit mode. A lock lasts until its transaction ends, so if a migration tool wraps all three in one transaction, the ACCESS EXCLUSIVE lock from ADD CONSTRAINT covers the whole validation scan. Put the ADD CONSTRAINT and the VALIDATE in separate migrations.

The validation still scans the table, but it takes a SHARE UPDATE EXCLUSIVE lock, which lets reads and writes continue. The ALTER TABLE documentation describes this shortcut for SET NOT NULL. Once the column is NOT NULL, the check constraint is redundant, and dropping it is another catalog change:

ALTER TABLE payments DROP CONSTRAINT payments_currency_not_null;

Order the releases#

The NOT VALID constraint is enforced on new inserts and updates from the moment it is added. Every writer, including the previous application release and any import job, must already supply a value. Ship that application change first, fill the missing values, then run the three statements. What NOT VALID means for PostgreSQL CHECK constraints shows how a NOT VALID constraint also rejects updates to other columns of a row that violates it.

PostgreSQL 18 lets ALTER TABLE add a NOT NULL constraint as NOT VALID directly, according to its release notes. On earlier versions the CHECK route above is the way to avoid the long lock.

What to rehearse#

Rehearse on a copy with the production row count, with client_min_messages = debug1 in the migration session. The rehearsal passes when the SET NOT NULL prints the "sufficient to prove" message and not "verifying table". If you see "verifying table", the constraint is missing, not validated, or written in a form that does not exclude nulls. CHECK (currency IS NOT NULL AND length(currency) = 3) worked as proof in our run. CHECK (currency <> '') did not, because a check passes when its expression is null.

Find the other changes that need a rehearsal in which PostgreSQL migration needs a rehearsal.

One month of free code review.

New organizations get one calendar month of hosted code review for private and public GitHub repositories. No card or cloud connection required. Eligibility and fair-use terms.

Start free with GitHub

Related reading