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

> 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.

Source: https://schemity.com/blog/postgres-add-not-null-column-large-table/

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:

| Existing rows get | Migration | Time on 5M rows | Lock held |
| --- | --- | --- | --- |
| The same constant | `ADD COLUMN status text NOT NULL DEFAULT 'new'` | 9.6 ms | `ACCESS EXCLUSIVE`, for the catalogue change only |
| The time of the migration | `ADD COLUMN imported_at timestamptz NOT NULL DEFAULT now()` | 1.8 ms | `ACCESS EXCLUSIVE`, catalogue only |
| A new value per row | `ADD COLUMN public_id uuid NOT NULL DEFAULT gen_random_uuid()` | 10.4 s | `ACCESS EXCLUSIVE` for the whole rewrite |
| Nothing | `ADD COLUMN channel text NOT NULL` | Fails at once | `ACCESS EXCLUSIVE` until the error |
| A value you compute | Nullable column, batched backfill, then prove `NOT NULL` | Seconds of scanning, none of it blocking reads | See below |

The first two rows are fast because of a PostgreSQL 11 change, which the [release notes](https://www.postgresql.org/docs/release/11.0/) describe as allowing `ALTER TABLE` "to add a column with a non-null default without doing a table rewrite". The [`ALTER TABLE` documentation](https://www.postgresql.org/docs/18/sql-altertable.html) 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):

```sql
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:

```sql
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:

```sql
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":

```sql
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.

| Approach | Blocking lock held during the scan | Works on |
| --- | --- | --- |
| `SET NOT NULL` | `ACCESS EXCLUSIVE`, reads and writes wait | Every version |
| `CHECK (... IS NOT NULL) NOT VALID`, validate, `SET NOT NULL` | None, the scan runs under `SHARE UPDATE EXCLUSIVE` | PostgreSQL 12 and later |
| `NOT NULL ... NOT VALID`, validate | None, the scan runs under `SHARE UPDATE EXCLUSIVE` | PostgreSQL 18 and later |

## 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`](https://www.postgresql.org/docs/18/runtime-config-client.html) defaults to zero: "A value of zero (the default) disables the timeout."

```sql
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](https://schemity.com/schema-migration-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:

![Schemity's change preview for adding channel TEXT to orders with no default and no NULL marker: the channel row highlighted green on the orders table, the planned migration ALTER TABLE "orders" ADD COLUMN "channel" TEXT NOT NULL, one planned change reading Column orders.channel added, two lint findings, and one impact finding reading Adds orders.channel NOT NULL without a default, fails on ~5M existing rows](https://schemity.com/images/blog/postgres-add-not-null-column-large-table/impact-add-not-null-no-default.webp)

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:

![Schemity's change preview for making orders.region NOT NULL: the region row tinted blue as altered, the planned migration ALTER TABLE "orders" ALTER COLUMN "region" SET NOT NULL, one planned change reading Column orders.region altered, one lint finding, and two impact findings reading 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](https://schemity.com/images/blog/postgres-add-not-null-column-large-table/impact-set-not-null.webp)

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:

![Schemity's change preview for a pasted migration file: the planned migration lists BEGIN, SET LOCAL lock_timeout = '2s', ALTER TABLE orders ADD COLUMN channel text, ALTER TABLE orders ALTER COLUMN channel SET NOT NULL and COMMIT; the channel row is highlighted green on the orders table, with two impact findings reading Sets orders.channel NOT NULL, fails if any of ~5M rows is NULL and Checking channel for NULL holds back reads and writes to orders while it reads ~5M rows, 356 MB on disk](https://schemity.com/images/blog/postgres-add-not-null-column-large-table/sql-migration-file.webp)

The [migration dialog](https://schemity.com/doc/migration-sql-diff/) 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](https://schemity.com/doc/connections-overview/). The same findings are repeated above the SQL:

![Schemity's Migration confirm dialog targeting 127.0.0.1 : pg_empty (public) marked PRODUCTION in red: under Can fail on existing data, Sets orders.region NOT NULL, fails if any of ~5M rows is NULL; under Holds back other statements, Checking region for NULL holds back reads and writes to orders while it reads ~5M rows, 356 MB on disk; the SQL statement ALTER TABLE "orders" ALTER COLUMN "region" SET NOT NULL; a note that migrations run in a transaction and roll back on error; an empty field asking to type the database name pg_empty; and Apply SQL statement disabled](https://schemity.com/images/blog/postgres-add-not-null-column-large-table/migration-dialog-production.webp)

## 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](https://schemity.com/blog/should-ai-agents-write-database-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](https://schemity.com/blog/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](https://schemity.com/blog/postgres-generated-column-vs-trigger/).

## Frequently asked questions

### Does adding a column with a default rewrite the table in PostgreSQL?

Not since PostgreSQL 11, as long as the default is not volatile. A constant or a stable function such as `now()` is evaluated once and stored in the catalogue, which took about 10 ms on a 5,000,000-row table. A volatile default such as `gen_random_uuid()` or `clock_timestamp()` is computed for every row, and that rewrote the same table in 10.4 seconds under an `ACCESS EXCLUSIVE` lock.

### Does `SET NOT NULL` lock the table in Postgres?

Yes. `ALTER COLUMN ... SET NOT NULL` takes an `ACCESS EXCLUSIVE` lock and scans every row to prove there is no `NULL`, so reads and writes wait for the whole scan. PostgreSQL 12 and later skip the scan when a validated `CHECK (col IS NOT NULL)` constraint already proves it, and PostgreSQL 18 lets you add the `NOT NULL` constraint itself as `NOT VALID` and validate it under a weaker lock.

### Why did a fast `ALTER TABLE` take down my application?

Because the `ALTER TABLE` queued for its lock behind a long-running transaction, and every query that arrived after it queued behind the `ALTER TABLE`. In a test, a 10 ms statement waited 7 seconds and a primary key lookup waited 6 seconds behind it. Setting `lock_timeout` makes the migration give up and retry instead of holding the queue.