Somewhere in your production database is a migration that ran fine in staging and then took down the app for six minutes. Usually it's an ALTER TABLE that looked innocent — adding a NOT NULL column, renaming something, adding an index — and turned into a full table lock on a table with 40 million rows. The deploy pipeline didn't fail. The database just stopped taking writes while everyone stared at a spinning dashboard.
Zero-downtime database migrations are not a checkbox in your migration tool. They're a discipline: you stop thinking of a schema change as one atomic step and start thinking of it as a sequence of backward-compatible steps, each safe to ship on its own. The name for that discipline is the expand-contract pattern, and if you're running any service that can't afford a maintenance window, it should be the default way your team changes schema — not the thing you reach for only on the scary migrations.
The naive approach — write the migration, run it in the same deploy as the code that depends on it — works fine on a database with a few thousand rows and no live traffic. It falls apart for three reasons at scale:
ADD COLUMN ... NOT NULL DEFAULT in older Postgres versions rewrites the entire table. Adding a foreign key validates every existing row under a lock. On a large table, that's not milliseconds — it's minutes.The fix isn't a smarter migration tool. It's separating "the database supports both old and new shapes" from "the application only uses the new shape" into two distinct, independently deployable steps.
The pattern has three phases:
Each phase is its own migration, its own deploy, and — critically — its own decision point where you can pause, verify, or abort without an outage.
Renaming users.email to users.email_address is the textbook case for why you can't do this in one step. Here's the expand-contract version using a tool like golang-migrate, Flyway, or Rails migrations — the SQL is the same regardless of tool.
Expand (migration 1):
ALTER TABLE users ADD COLUMN email_address TEXT;
-- Backfill in batches, not one giant UPDATE
UPDATE users SET email_address = email
WHERE id BETWEEN 1 AND 10000 AND email_address IS NULL;
-- repeat in batches until done, or use a background job
At this point, email and email_address both exist and are kept in sync. To handle rows written by old code during the rollout, add a trigger or, more simply, dual-write from the application:
def create_user(email):
db.execute(
"INSERT INTO users (email, email_address) VALUES (%s, %s)",
(email, email),
)
Migrate (deploy, then migration 2): Ship application code that reads from email_address instead of email. Because the expand step guarantees email_address is populated for every row — old and new — this deploy is safe to roll out gradually. If it goes wrong, you roll back the app deploy, not the database.
Contract (migration 3, days or weeks later):
ALTER TABLE users DROP COLUMN email;
Only run this after you've confirmed — via query logs, APM, or a grep across the codebase — that nothing still references the old column. This is the step teams skip, and skipped contracts are how you end up with a database full of zombie columns nobody's brave enough to remove.
This is the single most common way teams accidentally lock a production table. Don't do this:
ALTER TABLE orders ADD COLUMN status TEXT NOT NULL DEFAULT 'pending';
On Postgres 11+ this specific case is actually safe because a constant default doesn't require a full rewrite — but the moment your default is an expression, or you're on an older engine, or you're adding a NOT NULL to an existing nullable column, you're back to a full table scan under lock. The expand-contract version sidesteps engine version trivia entirely:
-- Expand: nullable, no default constraint yet
ALTER TABLE orders ADD COLUMN status TEXT;
-- Backfill in batches
UPDATE orders SET status = 'pending' WHERE status IS NULL AND id BETWEEN 1 AND 50000;
-- Once fully backfilled and app writes it on every insert:
ALTER TABLE orders ALTER COLUMN status SET NOT NULL;
Enforce the NOT NULL constraint only after every row has a value and every code path writes one. Validating a NOT NULL constraint on Postgres does still require a scan, but with NOT VALID / VALIDATE CONSTRAINT for check constraints (and native support for this pattern with foreign keys) you can split even that into a non-blocking step:
ALTER TABLE orders ADD CONSTRAINT status_not_null CHECK (status IS NOT NULL) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT status_not_null; -- scans without blocking writes
If you're introducing this pattern to a team that's used to single-step migrations, here's the sequence that works:
CREATE INDEX CONCURRENTLY and nullable columns. Destructive or renaming changes always need expand-contract.UPDATE across 40 million rows holds locks and bloats your WAL. Batch in chunks of 1,000–50,000 rows with a short sleep between batches, or push the backfill into a background job with retry logic.Doing it all in one deploy anyway, because "it's a small migration." Table size isn't the only risk — lock duration depends on your database's specific mechanics per operation. ADD COLUMN behavior differs across Postgres versions and wildly between Postgres and MySQL. Don't estimate risk by table size alone; know what your specific migration does under your specific engine version.
Forgetting the dual-write step. If you backfill once and don't dual-write during the migrate phase, any row created or updated between your backfill and your cutover deploy is missing data in the new column. This is the single most common bug in expand-contract migrations — a silent partial backfill that only surfaces later as null-pointer-shaped bugs in production.
Skipping the contract phase indefinitely. Every unfinished contract is a small tax: extra storage, extra confusion for new engineers, extra surface area for someone to write new code against the wrong column. Track expand migrations in the same place you track tech debt, and set a real deadline for contracting them.
Running backfills without rate limiting. A backfill that saturates your primary's I/O will show up as latency spikes for every other query hitting that table. Batch it, and if you have read replicas, consider running the backfill against off-peak traffic windows or throttling based on replication lag.
Testing migrations only against a small local database. A migration that runs in 40ms locally can take 20 minutes against production data volume, and lock behavior under concurrent traffic doesn't show up in a single-connection local test at all. Run migration timing tests against a production-sized snapshot before you ship anything destructive.
Treating this as a database-only concern. Expand-contract only works if application code is written to tolerate both schema states during the migrate phase. That's a code review discipline, not just a DBA discipline — reviewers need to know a migration is in flight and check that new code doesn't assume the contract phase has already happened.
None of this is exotic. Every mature engineering org running a database at scale ends up converging on some version of expand-contract, usually after their first bad lock-related outage. The teams that do it well just formalize it earlier — as a written playbook, a migration template, and a CI check that flags destructive schema changes so they get routed through the multi-step process instead of shipped in one commit.
If your team is still debating whether a migration needs a maintenance window, or you've had a deploy take down writes because of a lock nobody anticipated, that's usually a sign the process needs codifying, not just the next migration needing more care. We help engineering teams build exactly this kind of migration discipline into their CI/CD pipeline — from templated expand-contract migrations to the automated checks that catch a risky ALTER TABLE before it ships. Get in touch if you want a second set of eyes on your migration strategy before your next major schema change.