Add a NOT NULL Column to a Big Table Without Downtime

You need a new column that cannot be empty, on a table that is big and in use. ALTER TABLE ... ADD COLUMN ... NOT NULL looks like one statement, and what it costs depends on the default value. In the worst case, PostgreSQL rewrites the whole table while holding a lock that blocks every reader and writer. This page shows how to tell the cheap case from the expensive one, and gives a safe sequence for the case where each row needs its own value. To prove that a table was not rewritten, the page compares the table's file number before and after each step. If the number is the same, the table's file was not rewritten.

Step 1: a table with 20,000 rows

CREATE TABLE big_orders AS
SELECT g AS order_id,
       (g % 8) + 1 AS customer_id,
       ((g % 97) + 0.5)::numeric(8,2) AS total
FROM generate_series(1, 20000) AS g;
ALTER TABLE big_orders ADD PRIMARY KEY (order_id);

SELECT 20000
ALTER TABLE

Step 2: a constant default is instant

Since PostgreSQL 11, a constant default is stored in the catalog and not written into each row. \gset saves the file number in a psql variable, and the next query compares it with the current one.

SELECT pg_relation_filenode('big_orders') AS before_node \gset
ALTER TABLE big_orders ADD COLUMN channel text NOT NULL DEFAULT 'web';
SELECT pg_relation_filenode('big_orders') = :before_node AS same_file;

ALTER TABLE
 same_file
-----------
 t
(1 row)

same_file is true: the 20,000 rows were not touched, and the lock was held for a moment. On a table with 200 million rows, it takes the same time.

A default that is calculated for each row, such as random(), is a different case. PostgreSQL cannot store one value for it, and it rewrites the table.

SELECT pg_relation_filenode('big_orders') AS before_node \gset
ALTER TABLE big_orders ADD COLUMN token float8 NOT NULL DEFAULT random();
SELECT pg_relation_filenode('big_orders') <> :before_node AS was_rewritten;
ALTER TABLE big_orders DROP COLUMN token;

ALTER TABLE
 was_rewritten
---------------
 t
(1 row)

ALTER TABLE

was_rewritten is true. During that rewrite the table is locked against everything, including SELECT. On a big table that can mean minutes of downtime.

Step 3: each row needs its own value

Suppose the new region column depends on the customer. There is no constant default, so use this sequence. First, add the column as nullable. It is instant, because a column without a default needs no rewrite.

SELECT pg_relation_filenode('big_orders') AS before_node \gset
ALTER TABLE big_orders ADD COLUMN region text;
SELECT pg_relation_filenode('big_orders') = :before_node AS same_file;

ALTER TABLE
 same_file
-----------
 t
(1 row)

Second, deploy the application so that every new row gets a region. Third, fill in the old rows in batches. Each batch is its own transaction, so locks are held for a moment and the table can be vacuumed as you go. The DO block commits after each batch, and the region IS NULL condition makes it safe to stop and run it again.

DO $$
DECLARE lo int := 1; step int := 5000; n int;
BEGIN
  WHILE lo <= 20000 LOOP
    UPDATE big_orders
    SET region = CASE WHEN customer_id % 2 = 0 THEN 'north' ELSE 'south' END
    WHERE order_id >= lo AND order_id < lo + step AND region IS NULL;
    GET DIAGNOSTICS n = ROW_COUNT;
    COMMIT;
    RAISE NOTICE 'batch from %: % rows', lo, n;
    lo := lo + step;
  END LOOP;
END $$;
SELECT count(*) FILTER (WHERE region IS NULL) AS missing, count(*) AS total
FROM big_orders;

NOTICE:  batch from 1: 5000 rows
NOTICE:  batch from 5001: 5000 rows
NOTICE:  batch from 10001: 5000 rows
NOTICE:  batch from 15001: 5000 rows
DO
 missing | total
---------+-------
       0 | 20000
(1 row)

Use a batch size that your server handles in a second or two. On a table with millions of rows, run the batches from a script or a job, with a short pause between them, and watch replication lag.

Step 4: add the rule without scanning the table

A plain SET NOT NULL scans the table while it holds a lock that blocks writers. A constraint marked NOT VALID applies to new and changed rows at once, and skips the existing rows, so it is instant. \set VERBOSITY terse keeps the error to its first line.

\set VERBOSITY terse
ALTER TABLE big_orders ADD CONSTRAINT region_not_null NOT NULL region NOT VALID;
INSERT INTO big_orders (order_id, customer_id, total) VALUES (20001, 1, 5);
INSERT INTO big_orders (order_id, customer_id, total, region) VALUES (20001, 1, 5, 'north');
SELECT conname, convalidated FROM pg_constraint
WHERE conrelid = 'big_orders'::regclass AND conname = 'region_not_null';

ALTER TABLE
ERROR:  null value in column "region" of relation "big_orders" violates not-null constraint
INSERT 0 1
     conname     | convalidated
-----------------+--------------
 region_not_null | f
(1 row)

New rows without a region are already refused. convalidated is false, because nobody has checked the old rows yet.

Step 5: validate without blocking writes

ALTER TABLE big_orders VALIDATE CONSTRAINT region_not_null;
SELECT conname, convalidated FROM pg_constraint
WHERE conrelid = 'big_orders'::regclass AND conname = 'region_not_null';
SELECT attname, attnotnull FROM pg_attribute
WHERE attrelid = 'big_orders'::regclass AND attname = 'region';

ALTER TABLE
     conname     | convalidated
-----------------+--------------
 region_not_null | t
(1 row)

 attname | attnotnull
---------+------------
 region  | t
(1 row)

VALIDATE CONSTRAINT reads the table, but it takes a lock that lets reads and writes continue. It fails if any old row is still NULL, and then you run the backfill again. When it passes, the column is NOT NULL for good.

On a server older than PostgreSQL 18, the same idea works with a check constraint: ADD CONSTRAINT ... CHECK (region IS NOT NULL) NOT VALID, then VALIDATE CONSTRAINT, and then SET NOT NULL, which skips its own scan because the validated check already proves it.

Related course: SQL Advanced.