Zero-Downtime Migrations

The migration that takes your database down isn't the one that adds a table — it's ALTER TABLE big_table ADD COLUMN x NOT NULL DEFAULT 'y': a full table rewrite under an access-exclusive lock. Every zero-downtime migration strategy is a variation on "never hold a lock while doing heavy work."

What's cheap and what's expensive

• Cheap (metadata only): ADD COLUMN nullable or with a constant default (Postgres 11+ stores it in metadata), DROP COLUMN, RENAME, CREATE INDEX CONCURRENTLY.
• Expensive (rewrite or scan): ALTER COLUMN type changes, ADD COLUMN with a volatile default (now()), SET NOT NULL without a pre-validated check, VALIDATE CONSTRAINT on huge tables.

Expand-contract for risky changes

Never change a column in place when readers depend on the old shape:

1. Expand: add the new column (new_email), deploy code that writes to both and reads the old.
2. Backfill: copy old→new in batches (1k rows/transaction — don't hold a transaction open for an hour).
3. Flip: deploy code that reads the new column.
4. Contract: drop the old column after the old code is fully gone.

Painful? Yes. Cheaper than an outage? Always.

The lock discipline

• CREATE INDEX CONCURRENTLY — builds without blocking writes; can't run inside a transaction; monitor pg_stat_progress_create_index.
• ALTER TABLE ... ADD CONSTRAINT ... NOT VALID + VALIDATE CONSTRAINT — the two-step that skips the full scan under lock.
• lock_timeout — set it in migration scripts (SET lock_timeout = '2s'): a migration that waits 10 minutes for a lock queues every write behind it.
• Batch backfills with pg_sleep between chunks and LIMIT-based keyset iteration.

Field notes

• Test migrations on a production-sized copy — a 30-second staging migration can be a 30-minute prod one.
• pg_stat_activity during migrations: watch for idle in transaction sessions holding locks.
• Migrations should be one-way and idempotent where possible — a rolled-back deploy should leave a working database.