skip to content

On a busy PostgreSQL 18 accounts table, how do you add a NOT NULL signup_country column that needs a backfill without a long ACCESS EXCLUSIVE scan?

level: seniorimportance: should knowfreq 37%

answer

  1. constant default is already free
  2. add nullable, then backfill
  3. SET NOT NULL scans under the lock
  4. not-null constraint NOT VALID
  5. validate under SHARE UPDATE EXCLUSIVE

basics

~20 s

Add the column nullable and backfill in batches; then, in PostgreSQL 18, add the not-null constraint NOT VALID and VALIDATE it under SHARE UPDATE EXCLUSIVE. On 17, a validated CHECK (signup_country IS NOT NULL) lets SET NOT NULL skip its scan.

solid answer

~50 s

If a constant would do, `ADD COLUMN locale text NOT NULL DEFAULT 'en'` is already safe: the value is stored once in the catalog and there is no scan. A value that must be backfilled is the hard case, because `ALTER COLUMN signup_country SET NOT NULL` scans the whole table under `ACCESS EXCLUSIVE`. In PostgreSQL 18 the safe path is: add the column nullable; make the application write it; backfill in batches; `ADD CONSTRAINT accounts_signup_country_nn NOT NULL signup_country NOT VALID`, a brief `ACCESS EXCLUSIVE` with no scan; then `VALIDATE CONSTRAINT`, which scans under `SHARE UPDATE EXCLUSIVE` while traffic continues. Do not finish with `SET NOT NULL`: on a column with an invalid not-null constraint it validates it under `ACCESS EXCLUSIVE`. On 17, add `CHECK (signup_country IS NOT NULL) NOT VALID`, validate it, then `SET NOT NULL` skips the scan, and drop the check.

code

sql · 11 lines
sql
SET lock_timeout = '3s';
ALTER TABLE accounts ADD COLUMN signup_country text;

-- application now writes signup_country; backfill old rows in batches

ALTER TABLE accounts
  ADD CONSTRAINT accounts_signup_country_nn
  NOT NULL signup_country NOT VALID;          -- brief lock, no scan

ALTER TABLE accounts
  VALIDATE CONSTRAINT accounts_signup_country_nn;  -- scan, traffic continues

go deeper

for a junior

Recall that adding a NOT NULL column with a constant default is safe, and that making an existing column NOT NULL checks every row.

for a middle

Explain why SET NOT NULL is costly: a full scan under ACCESS EXCLUSIVE. Know that NOT VALID plus VALIDATE CONSTRAINT moves the scan to a weaker lock.

for a senior

Lay out the full release order: nullable column, application writes, batched backfill, NOT VALID constraint, VALIDATE, each strong-lock step with lock_timeout, plus the 17 CHECK route.

for a principal

Judge when a staged NOT NULL is worth its extra deploys versus a constant default, and make the recipe a reusable pattern for every team that touches the busiest tables.

## The release, and the trap in it The scenario: a zero-downtime release adds a mandatory column to a busy social network's `accounts` table. The obvious statement, `ALTER TABLE accounts ADD COLUMN signup_country text NOT NULL`, fails on a non-empty table because existing rows would be NULL. The next obvious sequence, add the column, backfill it, then `ALTER COLUMN signup_country SET NOT NULL`, works but has a hidden cost: **`SET NOT NULL` reads every row to prove there is no NULL, and it does so while holding `ACCESS EXCLUSIVE`**, which blocks even plain `SELECT`. On hundreds of millions of rows, that is minutes of outage. ## Case 1: a constant default is already safe If every existing account can take the same value, there is nothing to stage: - `ALTER TABLE accounts ADD COLUMN locale text NOT NULL DEFAULT 'en'` evaluates `'en'` once and stores it in `pg_attribute.attmissingval`. - Because the stored value is not NULL, PostgreSQL skips the NOT NULL check entirely: no scan, no rewrite. - The only risk is waiting for the brief `ACCESS EXCLUSIVE`, which a `lock_timeout` handles. A volatile default such as `gen_random_uuid()` is different: it rewrites the table. ## Case 2: a value that needs a backfill (PostgreSQL 18) PostgreSQL 18 stores `NOT NULL` constraints in `pg_constraint` and lets them be added `NOT VALID`, which gives a clean recipe: 1. `ALTER TABLE accounts ADD COLUMN signup_country text;` is catalog-only and needs a brief `ACCESS EXCLUSIVE`. 2. Deploy application code that writes `signup_country` on every insert and update. 3. Backfill existing rows in small batches, each in its own short transaction. 4. `ALTER TABLE accounts ADD CONSTRAINT accounts_signup_country_nn NOT NULL signup_country NOT VALID;` takes a brief `ACCESS EXCLUSIVE` but does not scan. From now on, inserts and updates that leave the column NULL fail. 5. `ALTER TABLE accounts VALIDATE CONSTRAINT accounts_signup_country_nn;` scans the table under `SHARE UPDATE EXCLUSIVE`, which lets reads and writes continue. Step 2 must come before step 4, because a `NOT VALID` constraint is still enforced on new rows; an old application version that omits the column would start failing. ## Why the last step is VALIDATE, not SET NOT NULL The 18 manual is explicit: if a column has an invalid not-null constraint, `SET NOT NULL` validates it. That validation happens inside `SET NOT NULL`, under `ACCESS EXCLUSIVE`, so it reintroduces the very scan the recipe avoids. `VALIDATE CONSTRAINT` does the same check under the weaker lock. ## The PostgreSQL 17 route: let a CHECK prove it Before 18, a not-null constraint could not be `NOT VALID`, but `SET NOT NULL` skips its scan when a **valid** `CHECK` constraint already proves that no NULL can exist: 1. `ADD CONSTRAINT accounts_signup_country_chk CHECK (signup_country IS NOT NULL) NOT VALID`: brief lock, no scan. 2. `VALIDATE CONSTRAINT accounts_signup_country_chk`: scan under `SHARE UPDATE EXCLUSIVE`. 3. `ALTER COLUMN signup_country SET NOT NULL`: brief `ACCESS EXCLUSIVE`, scan skipped. 4. `DROP CONSTRAINT accounts_signup_country_chk`: the check is now redundant. The recipe still works on 18; the native one is shorter. ## Locks at each step | Step | Lock on `accounts` | Scans the table? | |---|---|---| | `ADD COLUMN` (no default) | `ACCESS EXCLUSIVE`, brief | no | | `ADD CONSTRAINT ... NOT NULL ... NOT VALID` | `ACCESS EXCLUSIVE`, brief | no | | `VALIDATE CONSTRAINT` | `SHARE UPDATE EXCLUSIVE` | yes, traffic continues | | `SET NOT NULL` with an invalid not-null constraint | `ACCESS EXCLUSIVE` | yes, traffic blocked | | `SET NOT NULL` with a valid proving `CHECK` | `ACCESS EXCLUSIVE`, brief | no | ## Guard rails - Put `SET lock_timeout` on every `ACCESS EXCLUSIVE` step and retry on failure, because even a brief lock can queue the whole table if it has to wait. - Run `VALIDATE CONSTRAINT` when no manual `VACUUM` is running on `accounts`; both take `SHARE UPDATE EXCLUSIVE` and would wait for each other. - Before step 4, check that the backfill is complete with a query such as `SELECT 1 FROM accounts WHERE signup_country IS NULL LIMIT 1`. It reads without blocking anyone, and an empty result means validation should succeed. - Keep each backfill batch small and in its own transaction, so the row locks it takes are released quickly and a failure costs only that batch. - If validation fails, some row is still NULL: fix the backfill and run `VALIDATE CONSTRAINT` again. The constraint keeps protecting new rows meanwhile.

  • In PostgreSQL 18, VALIDATE CONSTRAINT on accounts_signup_country_nn fails; what state is the table in, and what do you do?
    Nothing is lost: the failed validation rolls back, the constraint stays in place as `NOT VALID`, and it keeps rejecting NULLs in new and updated rows. Some old rows were missed by the backfill. Find them with `WHERE signup_country IS NULL`, fill them, and run `VALIDATE CONSTRAINT` again.
  • Why must the application write signup_country before the NOT VALID constraint is added in PostgreSQL?
    Because `NOT VALID` only skips checking existing rows; the constraint is enforced on every insert and update from the moment it is committed. If an application version that leaves the column NULL is still serving, its writes start failing, so the code change has to be fully deployed first.

  • ALTER COLUMN SET NOT NULL only changes the catalog, so it is instant on any table size.
  • A NOT VALID not-null constraint is not enforced on new rows until it is validated.
  • After adding the constraint NOT VALID, finish with SET NOT NULL; it is the same as VALIDATE.
  • ADD COLUMN ... NOT NULL DEFAULT 'en' scans the whole table to check for NULLs.
  • An index on signup_country lets SET NOT NULL skip its scan.

Assumes PostgreSQL 18, where NOT NULL constraints live in pg_constraint and can be added NOT VALID; on 17 the CHECK-then-SET NOT NULL route is needed.