Every schema migration tool will happily tell you how to change a table. Almost none of them will tell you the thing that actually matters in production: whether that change requires rewriting the table, how long the rewrite will take, and whether it will lock out every query until it finishes. The gap between “the migration ran” and “the application stayed up the whole time” is where most migration incidents live.
The standard approach — generate a migration, run it during a deploy, hope it’s fast — breaks down the moment your tables get big. Adding a column to a table with a few hundred million rows is not an instantaneous operation, and under the default semantics it can block reads and writes for the duration. The fix is not a better tool. It’s understanding which operations are safe, which need the CONCURRENTLY keyword, and which need to be split into multiple deploy cycles.
The Two Distinct Problems
Zero-downtime migration is really two problems stacked on top of each other, and conflating them is why teams get burned.
Problem one is the lock. Schema changes take locks. Some take a brief metadata-only lock; others need to rewrite every row and hold an exclusive lock for the whole rewrite. The danger isn’t the migration itself — it’s the queue behind it. In PostgreSQL, a long-running ALTER TABLE waiting for its lock blocks every subsequent query that touches the same table, including plain SELECTs. A five-minute migration against a busy table turns into a full application outage because hundreds of queries piled up behind it.
Problem two is the version skew. During a rolling deploy, old and new application code run simultaneously against the same schema. Old code doesn’t know about the new column; new code can’t depend on the old shape. Any migration strategy that assumes the schema and the application change atomically is fiction. They never change together.
Every pattern below addresses one or both of these problems. Tools like golang-migrate, Atlas, or Flyway will run whatever SQL you give them — but they won’t decide for you whether that SQL is safe to run against a live table.
Step 1: Expand
The core discipline is the expand-contract pattern: never destroy anything until nothing depends on it anymore. Each migration goes through three phases across multiple deploys.
In the expand phase, you only add things. Additive changes are the safest category because most of them can be metadata-only. Adding a nullable column is an instant catalog update — no row rewrite, no table scan. The new column simply doesn’t exist for old rows until someone writes to them.
-- Fast: metadata-only, no row rewrite
ALTER TABLE orders ADD COLUMN fulfillment_status TEXT;
-- Also fast, with a NOT NULL column in two steps:
ALTER TABLE orders ADD COLUMN priority INT;
-- backfill happens separately (see Step 2), then:
ALTER TABLE orders ALTER COLUMN priority SET NOT NULL;The trap is the default value. Adding a column with a VOLATILE default (or, before PostgreSQL 11, any default) forces a full table rewrite. Since PostgreSQL 11, a constant default is stored in the catalog and old rows just return it on read — so ADD COLUMN status TEXT DEFAULT 'pending' is cheap on modern versions. But check which version you’re actually running before you assume it.
Indexes deserve special mention. A CREATE INDEX on a large table takes a lock that blocks writes for the entire build. The concurrent variant builds the index without blocking writes, at the cost of taking longer and doing more work:
-- Blocks writes for the whole build. Never on a large live table:
CREATE INDEX idx_orders_status ON orders (status);
-- Builds without blocking writes:
CREATE INDEX CONCURRENTLY idx_orders_status ON orders (status);Two caveats with CONCURRENTLY: it cannot run inside a transaction, which means migration tools configured to wrap every migration in one will reject it. And if the build fails, it leaves an INVALID index behind that you must drop and rebuild. Check for invalid indexes after any concurrent build fails — they occupy disk and are silently ignored by the planner, which is worse than not having them.
Step 2: Backfill in Batches
Once the nullable column exists, old rows have NULL in it. Filling those rows is a data migration, and doing it as a single UPDATE is the classic way to take down a production database. A monolithic update holds row locks across the whole table, generates one enormous transaction’s worth of WAL, bloats the table, and pushes replication lag through the roof.
The fix is batching: update a bounded number of rows per transaction, commit, sleep briefly, repeat. Each batch holds locks only for milliseconds, WAL is generated in digestable chunks, and replicas stay caught up.
-- Run repeatedly until it reports 0 rows remaining.
UPDATE orders
SET fulfillment_status = 'unfulfilled'
WHERE id IN (
SELECT id FROM orders
WHERE fulfillment_status IS NULL
ORDER BY id
LIMIT 5000
);
-- Affects 5000 rows. Re-run until done.Batch size is a tuning knob, not a constant. Start around a few thousand rows, then watch replica lag and lock waits and adjust. On a busy primary, a batch that takes 50ms is fine; one that takes 5 seconds is not. Run backfills through a job runner or a small script with progress tracking — a backfill that dies halfway should resume cleanly, and re-running the query above is naturally idempotent because the WHERE clause only matches unfinished rows.
Only after the backfill completes do you add the NOT NULL constraint. That constraint addition scans the table under an exclusive lock, so on very large tables the safe sequence is to add a CHECK (priority IS NOT NULL) constraint as NOT VALID, run VALIDATE CONSTRAINT (which takes a weaker lock for the validation pass), and only then issue SET NOT NULL — modern PostgreSQL can use the already-validated check constraint to prove the column and skip the scan.
Step 3: Dual-Write, Then Migrate Reads
Now comes the version-skew problem. Suppose you’re renaming user_account_id to customer_id. During the transition, some application instances write the old column and some write the new one. If you copy data once and switch over, rows written by old instances after the copy will have a stale or missing value in the new column.
The standard solution is dual-writing. In the expand phase, new code writes both columns; a trigger covers code paths that haven’t been updated yet:
CREATE OR REPLACE FUNCTION sync_customer_id() RETURNS trigger AS $$
BEGIN
NEW.customer_id := NEW.user_account_id;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER orders_sync_customer_id
BEFORE INSERT OR UPDATE ON orders
FOR EACH ROW EXECUTE FUNCTION sync_customer_id();With both columns staying in sync, you backfill historical rows in batches, then flip reads to the new column in application code and deploy. Only when no code path reads the old column anymore do you remove the trigger and the column — the contract phase.
This is why a rename is never one deploy. The honest sequence looks like:
- Deploy 1: add the new column plus the sync trigger. Nothing reads it yet.
- Batch-backfill historical rows while the system runs.
- Deploy 2: read and write the new column everywhere. The old column is now dead weight.
- Deploy 3 (days later): drop the trigger, then drop the old column.
Each deploy is individually boring. That’s the goal — a migration plan where every step is reversible and none of them holds a dangerous lock.
The Contract Phase and Its Timing
The contract phase — dropping columns, removing triggers, deleting the old table — is where the destructive changes live, and the timing rules are strict. Dropping a column is fast (metadata-only), but it will fail if any code still references it, and in a rolling deploy “old code” includes instances from the previous release that may keep running for minutes or, in slower organizations, weeks.
Wait until the previous version is fully retired before contracting. For shared databases with multiple consuming services, this gets harder: you need to coordinate with every team whose code touches the table. A practical rule is to make the old column invisible in the expand phase (stop selecting it, stop exposing it in APIs), watch your error dashboards for a full traffic cycle, and only then drop it. If a dormant code path wakes up after the drop, the error will be immediate and obvious — but it will still be an outage, so the observation window matters.
Tools can help with the mechanical parts. Declarative migration tools can diff desired schema against actual schema and generate the DDL, and several migration checkers now lint migrations for known-dangerous operations before they reach production. But the expand-contract sequencing is your job. No tool knows that the old column is still read by a cron job that runs on Sundays.
Wrapping Up
Zero-downtime migrations come down to three habits: know which DDL operations rewrite tables and which are metadata-only; never run a data backfill as a single transaction on a large table; and treat version skew as the normal state of the world, not an edge case. The expand-contract structure makes every change additive first, destructive last, and reversible at every point in between.
If you’re starting from a codebase with a single giant migrations folder full of monolithic ALTERs, you don’t need to rewrite history. Adopt the pattern for new changes, and build up the muscle of asking, for every migration: what locks does this take, how long will it hold them, and what happens to the requests that arrive while it’s running?