How to Run a Zero-Downtime Postgres Migration

Why the naive migration errors out

If you run:

ALTER TABLE users ADD COLUMN email_verified BOOLEAN NOT NULL;

against a table with existing rows, Postgres rejects it immediately:

ERROR: column "email_verified" of relation "users" contains null values

Every existing row would have NULL in the new column, which violates NOT NULL. Postgres refuses the statement rather than silently breaking your constraint.

Two safe approaches

Option 1: DEFAULT in one statement (Postgres 11+)

ALTER TABLE users
  ADD COLUMN email_verified BOOLEAN NOT NULL DEFAULT false;

On Postgres 11 and later this is a metadata-only change - Postgres records the default in the catalog and never rewrites existing rows. It is instant, even on a 100-million-row table. New rows get false; existing rows read false at query time. Use this when the default value is meaningful for the existing data.

Option 2: Three-step zero-downtime migration

When you cannot set a single permanent default (or need a different value per row), use the three-step pattern:

-- Step 1: add the column as nullable (no constraint, no table rewrite)
ALTER TABLE users ADD COLUMN email_verified BOOLEAN;

-- Step 2: backfill all existing rows
UPDATE users SET email_verified = false WHERE email_verified IS NULL;

-- Step 3: promote to NOT NULL now that no row is NULL
ALTER TABLE users ALTER COLUMN email_verified SET NOT NULL;

Each step acquires only a brief lock. Steps 1 and 3 are metadata operations. Step 2 (the UPDATE) takes a row-level lock per row, not a table lock, so reads keep working throughout.

What to watch on large tables

On tables with millions of rows, the UPDATE in step 2 can run for seconds or minutes. To avoid holding locks that long:

• Batch the backfill - chunk by primary key and sleep briefly between batches so the table stays available to writes:

DO $$
DECLARE
  batch_size INT := 10000;
  min_id BIGINT;
  max_id BIGINT;
BEGIN
  SELECT MIN(id), MAX(id) INTO min_id, max_id FROM users WHERE email_verified IS NULL;
  WHILE min_id <= max_id LOOP
    UPDATE users SET email_verified = false
      WHERE id >= min_id AND id < min_id + batch_size AND email_verified IS NULL;
    min_id := min_id + batch_size;
    PERFORM pg_sleep(0.05);
  END LOOP;
END $$;

• Set a lock_timeout - so the migration fails fast rather than queuing behind a long-running transaction:

SET lock_timeout = '2s';
ALTER TABLE users ALTER COLUMN email_verified SET NOT NULL;

• Use a tool for multi-TB tables - pg_repack, gh-ost (MySQL), or Postgres-native pg_osc automate the batching and lock management. The three-step pattern is the foundation those tools formalize.