How to Add a NOT NULL Column With No Downtime in PostgreSQL

Why the naive migration causes downtime

On PostgreSQL 10 and earlier, adding a column with a DEFAULT rewrites every row to store the new value, holding an AccessExclusiveLock for the entire duration. A 100,000-row table may lock for several seconds; a 100M-row table can lock for minutes - every write blocks until it finishes.

PostgreSQL 11+ made ADD COLUMN ... DEFAULT ... metadata-only for non-volatile defaults, so the default is recorded in the catalog rather than written to each row. But if you immediately add NOT NULL, Postgres must still scan every row to validate the constraint, which brings the lock back.

The safe pattern splits the work into three short, independently committable steps.

Step A - add the column as NULLABLE with a default

ALTER TABLE orders ADD COLUMN customer_segment TEXT DEFAULT 'standard';

On Postgres 11+ this is a pure catalog update - no row rewrite, no long lock. New rows get the default automatically. Existing rows are still NULL.

Step B - backfill existing rows in batches

Loop until no NULLs remain, committing each batch independently so no single transaction holds a long lock:

import psycopg2

conn = psycopg2.connect("postgres://postgres@localhost:5432/app")
conn.autocommit = True   # each batch is its own transaction
BATCH_SIZE = 5000

while True:
    with conn.cursor() as cur:
        cur.execute(
            """
            WITH batch AS (
              SELECT id FROM orders
              WHERE customer_segment IS NULL
              LIMIT %s
              FOR UPDATE SKIP LOCKED
            )
            UPDATE orders SET customer_segment = 'standard'
            FROM batch WHERE orders.id = batch.id
            """,
            (BATCH_SIZE,),
        )
        if cur.rowcount == 0:
            break
    print(f"backfilled {BATCH_SIZE} rows...")

Postgres does not support LIMIT directly in UPDATE, so the CTE pattern is the standard approach. FOR UPDATE SKIP LOCKED avoids blocking concurrent writers.

Step C - enforce NOT NULL and drop the default

Once every row has a value, the constraint check is a fast metadata scan, not a full rewrite:

ALTER TABLE orders ALTER COLUMN customer_segment SET NOT NULL;
ALTER TABLE orders ALTER COLUMN customer_segment DROP DEFAULT;

This does require a brief AccessExclusiveLock, but it is a constraint validation plus metadata change - not a row rewrite - so it completes in milliseconds.

Idempotency

Make each step safe to re-run by checking state before acting:

• Step A: query information_schema.columns for the column name before adding it.
• Step B: the loop terminates naturally when no NULLs remain - re-running is a no-op.
• Step C: check is_nullable = 'NO' before issuing the SET NOT NULL.

Idempotency means the script can be interrupted and restarted at any point without corrupting the table.