Adding a NOT NULL Column to a Large PostgreSQL Table: Constant Default, Backfill or NOT VALID

Disclosure: I build Schemity, a desktop ERD tool - this post is from our blog and uses it for the examples.

TL;DR: On PostgreSQL 11 and later, ADD COLUMN ... NOT NULL DEFAULT 'new' is a catalogue change that took 10 ms on 5,000,000 rows, but a volatile default such as gen_random_uuid() rewrote the whole table in 10.4 seconds. When the value has to be computed per row, add the column as nullable, backfill it in batches, and prove it NOT NULL with a constraint added NOT VALID and validated separately, so the full scan never holds a lock that blocks reads. Set lock_timeout on every one of these statements, because even the 10 ms version waits behind any open transaction that has touched the table and stalls every query that arrives after it.

How long it takes to add a NOT NULL column to a large PostgreSQL table depends almost entirely on the default. With a constant, the statement changes the catalogue and returns in milliseconds, whatever the table's size. With a value that has to be computed for each row, it rewrites the table while nothing else can read it, and the safe version is three statements instead of one.

The same ALTER TABLE looks equally harmless in a migration file either way. Every statement and timing below was run on PostgreSQL 18.3 in a throwaway container, against an orders table of 5,000,000 rows and 356 MB.

How to add a NOT NULL column to a large table in Postgres

Pick the migration by what the new column's value is for the rows that already exist:

The first two rows are fast because of a PostgreSQL 11 change, which the release notes describe as allowing ALTER TABLE "to add a column with a non-null default without doing a table rewrite". The ALTER TABLE documentation gives the rule: a non-volatile default "is evaluated at the time of the statement and the result stored in the table's metadata", while a volatile one "will cause the entire table and its indexes to be rewritten". The table's file on disk kept its pg_relation_filenode through the constant default and got a new one through gen_random_uuid(), growing from 356 MB to 473 MB.

now() is stable rather than volatile, so it takes the fast path too, which also means every existing order gets the same imported_at: the time the migration's transaction started. That is correct for an import timestamp and wrong for anything that was meant to record when each row happened.

On a table that has rows, a column with no default and NOT NULL fails on the first one, with ERROR: column "channel" of relation "orders" contains null values. That is the safe failure. The rewrite is the dangerous one, because it succeeds.

What to do when every row needs its own value

Split it into steps, each of which is either instant or does its slow work without blocking reads. Say each order takes its region from its customer. Adding the column as nullable is a catalogue change (0.7 ms):

ALTER TABLE orders ADD COLUMN region text;

Backfill in batches rather than one statement. A single UPDATE joining all 5,000,000 orders to a 100,000-row customers table took 19.6 seconds and kept a row lock on every order it had touched until it committed, so any other transaction updating one of those orders waited for the whole backfill. Batches of 50,000 took about 220 ms each, which keeps each lock short:

UPDATE orders o SET region = c.region FROM customers c
WHERE c.id = o.customer_id AND o.id BETWEEN 1 AND 50000 AND o.region IS NULL;
-- repeat for the next range, committing between batches

Then make the column NOT NULL. The obvious statement is the one to avoid on a big, busy table: ALTER TABLE orders ALTER COLUMN region SET NOT NULL holds ACCESS EXCLUSIVE while it scans every row for a NULL. On a freshly vacuumed table it took 367 to 380 ms over three runs. Straight after the one-statement backfill, when the scan also had to step over 5,000,000 dead row versions and set their hint bits, it took 1.9 seconds, and for all of that time a primary key lookup on orders could not run.

Does SET NOT NULL lock the table, and how do you avoid the scan?

It does, and the way around it is to prove the column has no NULL before SET NOT NULL runs. Since PostgreSQL 12, SET NOT NULL skips its scan when a validated check constraint already proves the same thing:

ALTER TABLE orders ADD CONSTRAINT orders_region_nn_check
  CHECK (region IS NOT NULL) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_region_nn_check;
ALTER TABLE orders ALTER COLUMN region SET NOT NULL;
ALTER TABLE orders DROP CONSTRAINT orders_region_nn_check;

Adding the constraint NOT VALID checks only new and updated rows, so it is instant. VALIDATE CONSTRAINT does the full scan, 534 ms here, but under SHARE UPDATE EXCLUSIVE, a lock that lets reads and writes carry on. The SET NOT NULL afterwards took 3.6 ms, and with client_min_messages at debug1 PostgreSQL says why: existing constraints on column "orders.region" are sufficient to prove that it does not contain nulls. The check constraint has done its job and can be dropped. Give it a name other than orders_region_not_null: on PostgreSQL 18 that is the name SET NOT NULL wants for its own constraint, and a clash leaves you with orders_region_not_null1.

PostgreSQL 18 removes the detour. It stores NOT NULL as a named constraint in pg_constraint, and its release notes add the ability "to set the NOT VALID attribute of NOT NULL constraints":

ALTER TABLE orders ADD CONSTRAINT orders_region_not_null NOT NULL region NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_region_not_null;

The first statement took 0.75 ms and already rejects inserts and updates that leave region NULL. The second scanned for 478 ms under SHARE UPDATE EXCLUSIVE. If a NULL slipped through the backfill, validation fails with the same contains null values error and nothing changes. On PostgreSQL 17 the first statement is a syntax error, so use the check-constraint version there.

Why a 10 ms ALTER TABLE can still stall every query

Every ALTER TABLE here except VALIDATE CONSTRAINT takes ACCESS EXCLUSIVE, at least for an instant, and it has to wait for that lock like anything else. While it waits, it sits in the lock queue, and every query that arrives after it waits behind it, reads included. The constant-default ADD COLUMN that took 9.6 ms on its own showed this on a 100,000-row copy of the table: one session held a transaction open on orders for 8 seconds, the ALTER TABLE took 6,986 ms because it was waiting for that transaction, and a SELECT total FROM orders WHERE id = 1 issued a second later took 5,995 ms. pg_blocking_pids() showed the chain: the SELECT blocked by the ALTER TABLE, and the ALTER TABLE blocked by the open transaction.

An application's pool drains the same way, behind a long report query or a forgotten BEGIN; SELECT ... FROM orders in someone's psql session. The fix is to let the migration give up rather than hold the queue, which the default does not do, since lock_timeout defaults to zero: "A value of zero (the default) disables the timeout."

BEGIN;
SET LOCAL lock_timeout = '2s';
ALTER TABLE orders ADD COLUMN channel text NOT NULL DEFAULT 'web';
COMMIT;

With the same open transaction in front of it, this failed after 2 seconds with ERROR: canceling statement due to lock timeout, and the queue behind it cleared. SET LOCAL ends with the transaction, so the setting does not stay behind on a pooled connection. A timeout aborts the whole transaction, so retry it from BEGIN a few times from the deploy script and the migration lands in a quiet moment. No static check can see which transaction will be open when the migration runs, so this one is yours to write in every migration that touches a hot table.

How Schemity shows what the migration will cost

Schemity is database design software that reads your live database, shows the impact of every schema change before it runs, and keeps the diagram as a file in Git. Adding a column to orders on a connected diagram is how you see the impact of every change before it becomes a migration: press F7 and impact analysis reads the pending schema migration against the database's own catalogue.

A new column drawn NOT NULL with no default is reported as "Adds orders.channel NOT NULL without a default, fails on ~5M existing rows". Preview changes shows it next to the table and the statement that would fail:

Making an existing column NOT NULL gives two findings, "Sets orders.region NOT NULL, fails if any of ~5M rows is NULL" and "Checking region for NULL holds back reads and writes to orders while it reads ~5M rows, 356 MB on disk", which is the lock described above:

In the Impact drawer, Count exactly runs a read-only count of the NULL values before you decide. The row counts are catalogue estimates, so no finding scans the table. Tables under 10,000 rows are left out of the lock findings, since that lock is over before anyone waits on it. In the current release every SET NOT NULL is reported as a scan, including one that a validated check constraint has already proven, so read that finding against the steps above.

When the migration comes from Rails, Django, Prisma or an AI agent instead, press Shift+F7 and paste the file into the SQL migration drawer: the same findings are reported for its statements, BEGIN, COMMIT and a SET LOCAL lock_timeout line are skipped as transaction control and a setting rather than schema changes, and nothing in the file is executed. A file that adds channel and sets it NOT NULL with no backfill in between is caught before it runs:

The migration dialog names the host, database, schema and environment it will run against, and on a connection tagged Production, Apply stays disabled until you type the database name, so the 10-second version cannot land on the wrong server by a misclick. The option is per connection, described in connections. The same findings are repeated above the SQL:

Which migration to write

• The value is the same for every existing row: add the column NOT NULL with a constant default, in one statement.
• The value differs per row: nullable column, batched backfill, then NOT NULL ... NOT VALID and VALIDATE CONSTRAINT on PostgreSQL 18, or the check-constraint version on 12 to 17.
• A volatile default on a big table: only when a rewrite under ACCESS EXCLUSIVE is acceptable, which on 5,000,000 rows was 10.4 seconds of nobody reading orders.
• Every one of them: SET LOCAL lock_timeout first, and retry the transaction on failure.

An AI agent or an ORM will write whichever version is shortest, and for a per-row value the shortest is the one that rewrites. The case for reviewing agent-written migrations covers the other lines that look this harmless, such as a rename generated as a drop and an add, and schema linting vs migration linting explains which of these a migration linter catches from the file alone. For another default that decides whether a table is rewritten, see the stored column measurements in generated column vs trigger.