Adding NOT NULL on a Big Table Without Downtime

The Dangerous ALTER TABLE Everyone Runs First

You’ve got a 200-million-row table in Postgres. Your app has been happily inserting rows for three years without a flag column because nobody thought they’d need it. Now they need it. Classic.

So you open a psql session at 2 PM on a Tuesday — maybe you even test it on staging first, good for you — and you type the obvious thing:

ALTER TABLE big ADD COLUMN flag boolean NOT NULL DEFAULT false;

On a small table: done in milliseconds. On a 200M-row table with a non-constant default in an older Postgres version: congratulations, you just kicked off a full table rewrite. AccessExclusiveLock. Nothing reads, nothing writes. Your monitoring starts going red. Slack starts pinging.

Here’s the thing: Postgres 11 made a massive improvement here. For constant defaults (like DEFAULT false or DEFAULT 0), Postgres 11+ stores the default in the catalog and doesn’t rewrite existing rows at all — the column is added in microseconds. So if you’re on PG 11+, that specific example above is actually fine.

But.

The moment you need a non-constant default — a function call like DEFAULT now(), a sequence, anything dynamic — PG 17 still rewrites the table. And the moment you’re doing any other schema change (adding an index, adding a FK, validating a constraint), you’re back in lock territory. So the safe, zero-downtime playbook is worth knowing cold.

The Safe Pattern (Do This Instead)

This is the multi-step approach that keeps your table online throughout. It looks like more work because it is more work — but your users won’t notice a thing, and your 2 AM self will thank you.

Step 1: Add the Column as Nullable (Instant)

ALTER TABLE big ADD COLUMN flag boolean;

No default, no NOT NULL. Postgres just updates the catalog. Done in milliseconds regardless of table size. All existing rows now have flag = NULL, which is fine for now.

Step 2: Set the Default for New Rows (Instant)

ALTER TABLE big ALTER COLUMN flag SET DEFAULT false;

Now any new INSERT that doesn’t specify flag gets false automatically. Existing rows are still NULL. You haven’t touched a single data page yet.

Step 3: Backfill in Batches (Slow, But Safe)

This is where you earn your stripes. Don’t do UPDATE big SET flag = false — that locks the whole table and also creates one giant transaction that autovacuum hates with a passion.

Instead, chunk it:

-- Run this in a loop; adjust batch size to taste
UPDATE big
SET flag = false
WHERE id BETWEEN :start AND :end
  AND flag IS NULL;

Here’s a bash script that does the full loop with progress, replication lag awareness, and throttling:

#!/usr/bin/env bash
# backfill_flag.sh — batch-backfill big.flag with safety rails

set -euo pipefail

DB_URL="${DATABASE_URL:-postgres://localhost/mydb}"
BATCH_SIZE=10000
SLEEP_MS=50          # milliseconds between batches
LAG_THRESHOLD=10     # pause if replica lag > 10 seconds
LOG_FILE="/tmp/backfill_flag_$(date +%Y%m%d_%H%M%S).log"

log() { echo "[$(date -u +%H:%M:%SZ)] $*" | tee -a "$LOG_FILE"; }

get_min_max() {
  psql "$DB_URL" -At -c "SELECT MIN(id), MAX(id) FROM big WHERE flag IS NULL;"
}

check_lag() {
  psql "$DB_URL" -At -c "
    SELECT COALESCE(
      MAX(EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp()))),
      0
    )::int;"
}

log "Starting backfill. Batch size: $BATCH_SIZE, sleep: ${SLEEP_MS}ms"
log "Full log: $LOG_FILE"

read -r MIN_ID MAX_ID <<< "$(get_min_max | tr '|' ' ')"

if [[ -z "$MIN_ID" || "$MIN_ID" == "" ]]; then
  log "No rows to backfill. Done."
  exit 0
fi

log "ID range: $MIN_ID → $MAX_ID"

START=$MIN_ID
TOTAL_UPDATED=0
BATCH_COUNT=0

while [[ $START -le $MAX_ID ]]; do
  END=$(( START + BATCH_SIZE - 1 ))

  # Throttle on replication lag
  LAG=$(check_lag)
  if [[ $LAG -gt $LAG_THRESHOLD ]]; then
    log "Replica lag ${LAG}s > ${LAG_THRESHOLD}s threshold — pausing 5s"
    sleep 5
    continue
  fi

  ROWS=$(psql "$DB_URL" -At -c "
      WITH updated AS (
        UPDATE big
        SET flag = false
        WHERE id BETWEEN $START AND $END
          AND flag IS NULL
        RETURNING 1
      ) SELECT COUNT(*) FROM updated;")

  TOTAL_UPDATED=$(( TOTAL_UPDATED + ROWS ))
  BATCH_COUNT=$(( BATCH_COUNT + 1 ))
  PCT=$(( (START - MIN_ID) * 100 / (MAX_ID - MIN_ID + 1) ))

  log "Batch $BATCH_COUNT | IDs $START–$END | rows: $ROWS | total: $TOTAL_UPDATED | ~${PCT}% done"

  START=$(( END + 1 ))
  sleep "$(echo "scale=3; $SLEEP_MS / 1000" | bc)"
done

log "Backfill complete. $TOTAL_UPDATED rows updated in $BATCH_COUNT batches."

Realistic numbers: a 200M-row table with 10K batches and 50ms sleep will chew through this in roughly 6 hours. Your users won’t notice. Autovacuum will happily process dead tuples between batches. You can watch progress from the log and kill it if something looks wrong.

Step 4: Add a NOT VALID Check Constraint (Instant)

ALTER TABLE big
  ADD CONSTRAINT flag_not_null
  CHECK (flag IS NOT NULL)
  NOT VALID;

The NOT VALID clause is the magic here. Postgres trusts that future rows will satisfy the constraint, but it doesn’t scan existing rows. This takes an AccessExclusiveLock for just long enough to update the catalog — effectively instant.

Step 5: Validate the Constraint (Slow, But Only ACCESS SHARE)

ALTER TABLE big VALIDATE CONSTRAINT flag_not_null;

Now Postgres does the full table scan to verify every existing row satisfies the constraint. This takes time proportional to table size — but it only needs an ACCESS SHARE lock, the same lock a normal SELECT takes. Reads and writes continue uninterrupted the whole time.

If your backfill finished cleanly, this will pass without drama.

Step 6: Promote to Real NOT NULL (Instant on PG 12+)

ALTER TABLE big ALTER COLUMN flag SET NOT NULL;

On Postgres 12 and later, if there’s a validated CHECK constraint proving the column is never NULL, this ALTER is smart enough to skip the table scan entirely and just updates the catalog. Instant. AccessExclusiveLock held for microseconds.

Step 7: Drop the CHECK Constraint (Optional)

ALTER TABLE big DROP CONSTRAINT flag_not_null;

The NOT NULL constraint is now enforced natively. The CHECK constraint is redundant. Drop it or keep it — your call. Most people drop it to keep the schema clean.

Never Wait Forever: lock_timeout and statement_timeout

Every schema-change DDL should be wrapped in timeouts. If something already holds an AccessExclusiveLock on your table, your ALTER TABLE will queue behind it — and while it queues, every other query on that table queues behind the DDL. One slow lock holder turns into a pile-up fast.

-- Set these before every DDL statement in migrations
SET lock_timeout = '5s';
SET statement_timeout = '60s';

ALTER TABLE big ALTER COLUMN flag SET NOT NULL;

If the lock can’t be acquired in 5 seconds, the statement fails cleanly with an error instead of hanging. Build a retry loop around it:

#!/usr/bin/env bash
# retry DDL with lock_timeout

DB_URL="${DATABASE_URL:-postgres://localhost/mydb}"
MAX_ATTEMPTS=10
RETRY_SLEEP=10

for attempt in $(seq 1 $MAX_ATTEMPTS); do
  echo "Attempt $attempt/$MAX_ATTEMPTS..."

  if psql "$DB_URL" -c "
    SET lock_timeout = '5s';
    SET statement_timeout = '60s';
    ALTER TABLE big ALTER COLUMN flag SET NOT NULL;
  " 2>&1; then
    echo "Success on attempt $attempt"
    exit 0
  fi

  echo "Failed — waiting ${RETRY_SLEEP}s before retry"
  sleep $RETRY_SLEEP
done

echo "Migration failed after $MAX_ATTEMPTS attempts"
exit 1

Other Schema Changes Worth Doing Safely

Adding an Index

Never CREATE INDEX on a large table without CONCURRENTLY. The plain version takes a write lock for the entire build.

-- Bad: locks writes for the duration
CREATE INDEX idx_big_flag ON big(flag);

-- Good: reads and writes continue throughout
CREATE INDEX CONCURRENTLY idx_big_flag ON big(flag);

Downside: CONCURRENTLY can’t run inside a transaction block, and it takes roughly twice as long. Worth it every time on production data.

Adding a Foreign Key

Same NOT VALID + VALIDATE trick:

-- Step 1: add the FK without validating existing rows (instant)
ALTER TABLE big
  ADD CONSTRAINT big_user_id_fk
  FOREIGN KEY (user_id) REFERENCES users(id)
  NOT VALID;

-- Step 2: validate existing rows (ACCESS SHARE lock only)
ALTER TABLE big VALIDATE CONSTRAINT big_user_id_fk;

Dropping a Column

The ALTER TABLE big DROP COLUMN flag is instant — it just marks the column as dropped in the catalog. But here’s what trips people up: Postgres does not immediately reclaim the space. The column data is still physically on disk until you run VACUUM FULL or CLUSTER (both of which rewrite the table and lock it). For most tables this is fine — just know that DROP COLUMN doesn’t free disk space right away.

Renaming a Column

ALTER TABLE big RENAME COLUMN flag TO is_active;

Instant. But your app needs to handle both names until the rename is live in production. The classic approach: deploy app code that reads both column names (or use a view), rename the column, then clean up the compatibility layer in the next deploy.

Tooling That Handles This for You

If you’d rather not orchestrate all this manually, there are solid tools in this space:

pg-osc (PostgreSQL Online Schema Change) — Shopify’s tool for zero-downtime Postgres changes. Creates a shadow table, streams changes via triggers, swaps at the end. Heavy but battle-tested at scale.

pg-osc --dbname mydb --table big --statement "ADD COLUMN flag boolean NOT NULL DEFAULT false"

Bytebase — GUI-based schema change management with a migration review workflow. Good if you’re running multiple Postgres instances and want change history, approvals, and rollback.

strong_migrations (Rails gem) — If you’re in a Rails shop, this gem checks your migrations against a list of known dangerous patterns and refuses to run them without the safe alternatives. It’s opinionated in the best way.

Reality Check: What This Actually Looks Like

Here’s the full sequence for a 200M-row table, with realistic timing:

Six hours of backfill sounds painful until you compare it to 30 minutes of table-lock hell while your users time out and your on-call rotation has a terrible afternoon.

The VALIDATE CONSTRAINT phase is the one that genuinely takes wall-clock time, but it’s doing a sequential scan under a lock that lets reads and writes through. Your app stays up. Your users don’t know anything happened. That’s the goal.

The Bottom Line

“Just run ALTER TABLE” works on developer laptops and staging environments with toy datasets. On production tables that have been collecting rows for years, it’s a gamble you don’t want to take.

The multi-step pattern — nullable column, SET DEFAULT, batch backfill, NOT VALID constraint, VALIDATE, SET NOT NULL — is more steps but each step is safe. The worst any individual step does is hold an AccessExclusiveLock for single-digit milliseconds.

Wrap every DDL in lock_timeout = '5s' and a retry loop, run your backfill script with lag monitoring, and you’ll get through schema migrations on massive tables without anyone noticing. Which is exactly how it should feel: boring.

If you’re doing this regularly and want guardrails built in, add strong_migrations to your app or make pg-osc part of your migration pipeline. The investment pays off the first time someone on your team goes to “just add a column real quick” and the tool stops them.

Your table will still be there when you’re done. So will your users.

Related Reading

• ClickHouse vs DuckDB vs StarRocks: Light OLAP
• Dragonfly vs Redis: Single-Binary Performance
• DuckDB for Local Analytics: Pandas on Steroids
• KeyDB: A Redis Fork With Multi-Threading
• NocoDB DB Management System