Does ALTER COLUMN SET NOT NULL lock the table in Postgres?

Yes. ALTER COLUMN ... SET NOT NULL takes ACCESS EXCLUSIVE. Without an existing valid constraint that proves the column contains no nulls, PostgreSQL scans the table while holding that lock.

How do I add NOT NULL without downtime in Postgres?

Add a CHECK constraint with NOT VALID, validate it in a separate transaction, then run SET NOT NULL. On PostgreSQL 12 and later, the validated CHECK lets the final command skip the table scan, although it still takes ACCESS EXCLUSIVE briefly.

Does this SET NOT NULL recipe work on PostgreSQL 11?

The CHECK and validation steps work, but PostgreSQL 11 does not use the validated CHECK to skip the SET NOT NULL scan. The scan-skipping optimization was added in PostgreSQL 12.

September 20, 2026 · Flavius Munteanu

Postgres SET NOT NULL Without a Long Table Scan

ALTER COLUMN ... SET NOT NULL normally scans the table while holding ACCESS EXCLUSIVE. On PostgreSQL 12 and later, a validated CHECK constraint can prove the column is ready so the final metadata change stays brief.

By Flavius Munteanu, September 20, 2026

ALTER TABLE ... ALTER COLUMN ... SET NOT NULL takes an ACCESS EXCLUSIVE lock. If PostgreSQL has to scan a large table to prove that the column contains no nulls, that lock stays in place for the entire scan and blocks reads, writes, and other DDL.

On PostgreSQL 12 and later, there is a safer sequence: add a CHECK constraint as NOT VALID, validate it under a weaker lock, then run SET NOT NULL. The valid CHECK proves the column cannot contain nulls, so the final command skips the scan. The final command still requests ACCESS EXCLUSIVE, but only for a brief catalog change.

This is specifically about changing an existing nullable column. It is not the same problem as adding a new NOT NULL column, which has different data-population and default-value concerns.

Quick reference: the three-step sequence

Statement	Lock mode	Blocks reads	Blocks writes	Duration
`ADD CONSTRAINT ... CHECK ... NOT VALID`	ACCESS EXCLUSIVE	Yes	Yes	Brief, no table scan
`VALIDATE CONSTRAINT`	SHARE UPDATE EXCLUSIVE	No	No	Full table scan
`ALTER COLUMN ... SET NOT NULL` after validation	ACCESS EXCLUSIVE	Yes	Yes	Brief on PostgreSQL 12+
`DROP CONSTRAINT`	ACCESS EXCLUSIVE	Yes	Yes	Brief, metadata only

PostgreSQL documents that most ALTER TABLE forms use ACCESS EXCLUSIVE unless a weaker mode is named. It also documents SHARE UPDATE EXCLUSIVE for VALIDATE CONSTRAINT, which allows ordinary SELECT, INSERT, UPDATE, and DELETE statements to continue. See the official ALTER TABLE reference and the lock conflict table.

The migration that causes the long lock

Assume orders has 40 million rows. customer_id is already populated, and a review confirms there are no nulls. This migration still makes PostgreSQL prove that fact itself:

SET lock_timeout = '2s';
SET statement_timeout = '10min';

ALTER TABLE orders
  ALTER COLUMN customer_id SET NOT NULL;

Without a valid constraint that proves customer_id IS NOT NULL, PostgreSQL scans all 40 million rows. The current documentation says the table is ordinarily scanned during SET NOT NULL; it skips that scan only when a valid CHECK constraint proves no null can exist.

The risky part is not that the scan consumes I/O. The risky part is that ACCESS EXCLUSIVE is held while the scan runs. That mode conflicts with every table-level lock, including the ACCESS SHARE taken by an ordinary SELECT and the ROW EXCLUSIVE taken by INSERT, UPDATE, and DELETE.

If lock_timeout is missing, the migration can first wait behind an old transaction, then form a queue that places new application queries behind the waiting DDL. The migration does not need to acquire the lock before it starts hurting traffic. This is the lock queue failure mode that turns a catalog change into an outage.

Backfill nulls before adding the constraint

The safe sequence proves that the column is ready. It does not make existing nulls disappear. Check first:

SELECT count(*) AS null_rows
FROM orders
WHERE customer_id IS NULL;

If the result is nonzero, backfill outside the schema migration in small committed batches. One batch can look like this:

WITH batch AS (
  SELECT ctid
  FROM orders
  WHERE customer_id IS NULL
  ORDER BY id
  LIMIT 1000
  FOR UPDATE SKIP LOCKED
)
UPDATE orders AS o
SET customer_id = 1
FROM batch
WHERE o.ctid = batch.ctid;

Replace the placeholder value with the correct business value, commit, and repeat until the count reaches zero. A single update of every remaining row is not a safe substitute. It creates a large transaction, increases WAL, retains dead tuples longer, and makes rollback expensive.

Migration 1: add the proof without scanning old rows

Add a named CHECK constraint with NOT VALID:

SET lock_timeout = '2s';
SET statement_timeout = '30s';

ALTER TABLE orders
  ADD CONSTRAINT orders_customer_id_not_null
  CHECK (customer_id IS NOT NULL)
  NOT VALID;

NOT VALID skips the scan of existing rows, so the ACCESS EXCLUSIVE lock is held only for the catalog change. The constraint is immediately enforced for new and changed rows. Existing rows are the only part PostgreSQL has not yet verified.

Keep the timeout. A brief lock request can still wait behind a long-running transaction, and a waiting ACCESS EXCLUSIVE request can still create a queue.

Migration 2: validate under a weaker lock

Run validation in a separate migration and therefore a separate transaction:

SET lock_timeout = '2s';
SET statement_timeout = '30min';

ALTER TABLE orders
  VALIDATE CONSTRAINT orders_customer_id_not_null;

The scan now runs under SHARE UPDATE EXCLUSIVE. PostgreSQL’s lock matrix shows that this mode does not conflict with the locks used by normal reads and writes. It does conflict with operations such as VACUUM, ANALYZE, CREATE INDEX CONCURRENTLY, and another validation on the same table, so schedule it with awareness of other maintenance work.

Do not combine migration 1 and migration 2 inside one transaction. PostgreSQL normally holds locks until the transaction ends. If the validation follows the constraint add before commit, the stronger ACCESS EXCLUSIVE lock from the first statement remains held during the entire validation scan. The SQL looks like the safe pattern, but the lock window is the same shape you were trying to avoid.

You can confirm validation completed with:

SELECT conname, convalidated
FROM pg_constraint
WHERE conrelid = 'orders'::regclass
  AND conname = 'orders_customer_id_not_null';

Proceed only when convalidated is true.

Migration 3: set NOT NULL, then remove the helper

On PostgreSQL 12 and later, the valid CHECK lets SET NOT NULL skip the table scan:

SET lock_timeout = '2s';
SET statement_timeout = '30s';

ALTER TABLE orders
  ALTER COLUMN customer_id SET NOT NULL;

ALTER TABLE orders
  DROP CONSTRAINT orders_customer_id_not_null;

The PostgreSQL 12 release notes explicitly identify this optimization: SET NOT NULL can avoid the scan when column constraints already prove nulls are impossible. PostgreSQL 11 and earlier do not have that optimization. On those releases, validating the helper constraint is still useful proof, but the final SET NOT NULL scans again.

Do not drop the helper constraint in the same ALTER TABLE command that sets NOT NULL. The current documentation says the proof constraint must remain present while PostgreSQL decides whether the scan can be skipped. Separate statements, as shown above, keep that condition clear.

The final two statements both request ACCESS EXCLUSIVE, but they are catalog changes rather than full-table scans. With a short lock_timeout, they either acquire the lock quickly or fail quickly for a safe retry.

What pgfence reports

pgfence flags the direct one-statement migration and emits the same three-migration rewrite:

npx @flvmnt/pgfence explain "ALTER TABLE orders ALTER COLUMN customer_id SET NOT NULL"

Statement:
  ALTER TABLE orders ALTER COLUMN customer_id SET NOT NULL;

[MEDIUM] alter-column-set-not-null
  ALTER COLUMN "customer_id" SET NOT NULL: scans entire table under ACCESS EXCLUSIVE lock
  Lock: ACCESS EXCLUSIVE
  Blocks: reads, writes, other DDL

  Safe rewrite:
  Use CHECK constraint NOT VALID + VALIDATE to avoid full table lock
    -- Migration 1: add constraint without validating (brief ACCESS EXCLUSIVE lock)
    ALTER TABLE orders ADD CONSTRAINT chk_customer_id_nn CHECK (customer_id IS NOT NULL) NOT VALID;
    -- Migration 2: validate (SHARE UPDATE EXCLUSIVE, allows reads and writes)
    ALTER TABLE orders VALIDATE CONSTRAINT chk_customer_id_nn;
    -- Migration 3: the validated CHECK allows PostgreSQL to skip the full table scan
    ALTER TABLE orders ALTER COLUMN customer_id SET NOT NULL;
    ALTER TABLE orders DROP CONSTRAINT chk_customer_id_nn;

The analyzer catches the dangerous shape in the migration file. Production readiness still depends on the real data: backfill nulls, validate the helper constraint, and verify each step before continuing.

Review checklist

Confirm the change is ALTER COLUMN ... SET NOT NULL, not ADD COLUMN ... NOT NULL.
Count existing nulls and backfill them outside the schema migration.
Put ADD ... NOT VALID and VALIDATE CONSTRAINT in separate transactions.
Verify convalidated = true before the final step.
Use the scan-skipping recipe only on PostgreSQL 12 or later, and keep lock_timeout on every brief ACCESS EXCLUSIVE step.