Zero-downtime migrations

The goal: change schema while keeping the app serving traffic, with old and new code running simultaneously during the deploy window.

The expand/contract pattern

Every breaking schema change splits into three phases:

1. Expand — add the new shape; both old and new app versions can read/write.
2. Migrate — move data; deploy new app code that uses the new shape.
3. Contract — once all old code is gone, drop the old shape.

You never deploy a migration and a code change in the same release.

Renaming a column

Naive: ALTER TABLE users RENAME COLUMN email TO email_address; — instantly breaks every running app instance still selecting email.

Zero-downtime:

In practice steps 2 + 4 may merge if your deploy is fast and traffic tolerates a short window. Steps 1 and 6 are migrations; the rest are code releases.

Adding a NOT NULL column

Direct ALTER TABLE users ADD COLUMN tier text NOT NULL; requires every existing row to have a value — Postgres has to rewrite the whole table holding an ACCESS EXCLUSIVE lock. Death.

Zero-downtime:

-- 1. Add nullable, with default
ALTER TABLE users ADD COLUMN tier text DEFAULT 'free';
-- (PG 11+: this is metadata-only; older PG rewrites the table)

-- 2. Backfill in batches (in app code or DBA script)
UPDATE users SET tier = 'free' WHERE tier IS NULL AND id BETWEEN 1 AND 1000;
-- ...repeat in batches of 1k–10k...

-- 3. Add NOT NULL constraint (cheap once no NULLs remain — PG 12+)
ALTER TABLE users ADD CONSTRAINT users_tier_not_null
    CHECK (tier IS NOT NULL) NOT VALID;
ALTER TABLE users VALIDATE CONSTRAINT users_tier_not_null;
-- Or directly: ALTER TABLE users ALTER COLUMN tier SET NOT NULL; (PG 12+ uses constraint as proof)

NOT VALID lets you add the constraint without scanning the table. VALIDATE later only takes a SHARE UPDATE EXCLUSIVE lock (allows reads/writes).

Adding an index

CREATE INDEX idx_users_email ON users (email);                  -- locks writes!
CREATE INDEX CONCURRENTLY idx_users_email ON users (email);     -- doesn't

CONCURRENTLY:

• Doesn’t block writes.
• Takes ~2x longer.
• Can leave behind invalid indexes if it fails midway — check pg_index.indisvalid, drop and retry.
• Not allowed inside a transaction.

In Alembic:

def upgrade():
    op.execute("COMMIT")  # exit Alembic's transaction
    op.create_index("idx_users_email", "users", ["email"], postgresql_concurrently=True)

Dropping a column

ALTER TABLE ... DROP COLUMN is fast (metadata change), but only safe after no code references the column. Same expand/contract: deploy code that doesn’t read the column, then drop.

Batched backfills

Updating millions of rows in one statement holds a transaction open and grows WAL:

# Bad: single UPDATE on 10M rows
session.execute(text("UPDATE events SET tier = 'free' WHERE tier IS NULL"))

# Good: batched
while True:
    rows = session.execute(text("""
        UPDATE events SET tier = 'free'
        WHERE id IN (
            SELECT id FROM events WHERE tier IS NULL LIMIT 1000
        )
    """)).rowcount
    session.commit()
    if rows == 0:
        break
    time.sleep(0.05)  # avoid replication lag

Sleep between batches to give replication and vacuum time to catch up.

Locking — what’s safe

Always check the lock level. Rule of thumb:

• Safe (no ACCESS EXCLUSIVE): adding columns with no default (PG 11+ with constant default), CREATE INDEX CONCURRENTLY, ALTER TABLE ... VALIDATE CONSTRAINT.
• Dangerous: any ALTER TABLE that rewrites (changing column type, adding default to NOT NULL on old PG), creating a non-concurrent index on a busy table, VACUUM FULL.

Always set a lock_timeout before running risky DDL:

SET lock_timeout = '5s';
-- migration here — fails fast if it can't grab the lock

Long autovacuum runs and idle-in-transaction sessions block ALTER TABLE indefinitely. Kill them with pg_terminate_backend(pid) or wait.

Tools

pg-osc and gh-ost create a shadow table, copy data, sync changes via triggers/binlog, then atomically swap. Use for changes that would otherwise lock for hours.

Interview angle

• Q: “How do you rename a column without downtime?” — expand/contract: add new, dual-write, switch reads, stop writing old, drop.
• Q: “How do you add a NOT NULL column safely?” — nullable first, backfill in batches, then add NOT NULL constraint via NOT VALID + VALIDATE.
• Follow-up: “What’s CREATE INDEX CONCURRENTLY and what’s the catch?” — non-blocking, ~2x slower, can leave invalid indexes on failure.
• Follow-up: “Why batch backfills?” — avoid long transactions, replication lag, WAL growth, lock contention.

See 08_transactions_isolation.md for the locking model.