Migrations You Can Roll Forward
The migration was four words of SQL.
ALTER TABLE users RENAME COLUMN full_name TO name;It passed review in about nine seconds, because what is there to review. It ran clean in CI. Staging, which runs a single instance, was perfectly happy. Then it went out to production behind an ordinary rolling deploy, and for the ninety seconds it took the new pods to replace the old ones, checkout returned 500s for roughly half of all traffic.
Nothing was wrong with the SQL. The column really was renamed, instantly, exactly as written. The problem was everything around it. During the deploy, old instances were still running SELECT id, full_name FROM users, the column named full_name no longer existed, and Postgres did the only correct thing it could: it threw ERROR: column "full_name" does not exist (SQLSTATE 42703) on every one of those queries until the last old pod died.
That is the whole subject of this article. Your schema is code, it has to change, and it has to change while the previous version of your app is still running against it. Get that one idea and most migration disasters stop happening to you.
Your schema is code
Before the interesting failures, the boring foundation, because skipping it is how teams end up applying SQL by hand at 2am.
A migration is a file. It has a version (a timestamp or a sequence number), a small piece of SQL that moves the schema forward, and usually a matching piece that moves it back. The files live in your repo next to the code that depends on them. A tool applies them in order, records which ones it has run in a table, and refuses to run the same one twice.
-- migrations/0042_add_display_name.sql
ALTER TABLE users ADD COLUMN display_name text;The recording table is the entire trick. It is usually called schema_migrations or similar, and it is dull on purpose:
SELECT version, applied_at FROM schema_migrations ORDER BY version;
-- version | applied_at
-- --------------+----------------------------
-- 0040_... | 2026-07-02 09:14:11.203+00
-- 0041_... | 2026-07-09 15:31:55.881+00
-- 0042_... | 2026-07-18 08:02:40.117+00When a migration runs, the tool writes its version into that table inside the same transaction as the change, so a migration can never be half-applied and still marked done. On the next deploy the tool lists the files, subtracts the versions already in the table, and runs only what is left.
Which tool you use matters less than people argue about. As of mid-2026 the common Node choices are Prisma Migrate (Prisma 7, which moved its engine to TypeScript and WASM in late 2025), Drizzle Kit, Knex’s built-in migrations, and node-pg-migrate for raw SQL without an ORM. If you have already picked an ORM or query builder, use the migrations it ships with. They all implement the same ordered-ledger loop above, and everything in the rest of this article is about the SQL you put in the files, not the tool that runs them.
One rule, right now, before anything else. Never edit a migration that has already run. Once 0042 is recorded in some database somewhere, its file is frozen forever. Change it and you get one of two flavours of pain: tools that checksum migration files will refuse to start (“migration 0042 was modified after being applied”), and tools that do not will simply never re-run it, so your edit silently applies nowhere. Wrong migration? You do not fix it in place. You write 0043.
The rule that ruins naive migrations
Here is the sentence that the rename in the intro violated, and that almost every dangerous migration violates. During a rolling deploy, the old code and the new code run at the same time, against the same schema.
Think about what a deploy actually is. You do not stop the world, swap the binary, and start again. That would be downtime, which is the thing you are trying to avoid. Instead the platform brings up new instances a few at a time and drains the old ones, so for a window of anywhere from thirty seconds to several minutes there are two versions of your application live at once, both connected to one database.
So every migration has to be compatible with both the version of the code that is on its way out and the version coming in. A rename is a single atomic swap that only the new code understands, which means there is no schema state where both versions are happy. Drop a column the old code still reads: same failure. Add a NOT NULL column with no default and let the old code insert a row without it: the insert fails with a null-violation on the way in. Retype a column so the old code’s bound parameters no longer match: errors, or worse, silent truncation.
This is the same constraint you meet when you evolve a public API, where old and new clients coexist and you cannot break either. If you have read API versioning, the pattern below will feel familiar, because it is the same pattern pointed at a database instead of an HTTP contract.
Expand and contract
The fix is to stop thinking of a schema change as one step. You never rename. You never retype in place. You make the change in a sequence of small, individually-safe steps, each of which leaves the database compatible with the code on either side of it. The pattern has a name, expand and contract, and once it is your default you stop writing migrations that can take the site down.
Turning full_name into display_name the safe way is five steps spread across a few deploys:
In SQL and prose, the five steps:
-
Expand. Add the new column, nullable, with no
NOT NULLand no forced default. This is backward compatible: the old code does not mentiondisplay_name, so it neither reads nor writes it, and adding a nullable column does not touch existing rows.ALTER TABLE users ADD COLUMN display_name text; -
Backfill. Copy the existing data across in batches (the next section is entirely about doing this without hurting anyone). After this, every historical row has a
display_name. -
Dual-write. Deploy code that writes both columns on every insert and update. This is the subtle one. While old and new instances overlap, an old instance still writes only
full_name, so without dual-writing, rows it touches would leavedisplay_namestale. Having the new code keep both in sync closes that gap. -
Switch reads. Deploy code that reads
display_nameinstead offull_name. Nowfull_nameis write-only, kept alive purely for the old instances that have not finished draining. -
Contract. Once you are certain no running instance references
full_name(which usually means waiting for the previous deploy to fully complete, sometimes a day later), drop it.ALTER TABLE users DROP COLUMN full_name;
Five migrations and a few deploys to do the work of one four-word rename. It feels absurd the first time. It stops feeling absurd the first time the four-word version pages you.
Notice the ordering rule hiding in there: expand migrations ship ahead of the code that needs them, contract migrations ship behind the code that stopped needing the old thing. Additive changes go out first and wait for the code to catch up; destructive changes go out last, after the code has already moved on.
Click through both paths below. The naive rename never reaches a state where both versions are green, which is exactly why it takes the site down. Expand and contract keeps both green at every step and only removes the old column at the very end.
The operations that lock a busy table
Compatibility is only half the story. The other half is that some perfectly compatible migrations still take an exclusive lock on the table and hold it long enough to freeze production. Worse, the danger is not always obvious from the statement, and it interacts with how Postgres queues locks in a way that surprises people.
Most ALTER TABLE forms need an ACCESS EXCLUSIVE lock, the strongest one, which conflicts with everything including plain SELECT. Here is the part that bites. When your ALTER asks for that lock and some ordinary long-running query is already holding a weaker lock on the table, your ALTER waits. While it waits, it sits at the head of the lock queue, and every new query that needs any lock on that table now queues behind your ALTER. One slow query plus one waiting DDL statement is enough to stall every reader and writer of the table until the slow query finishes.
The blanket defence is to make every migration give up fast rather than block the world. Set a short lock_timeout (and a statement_timeout) at the top of the migration, so a change that cannot get its lock quickly aborts instead of freezing the queue, and you retry it later:
SET lock_timeout = '2s';
SET statement_timeout = '30s';
-- now the risky statement, which aborts in 2s if it cannot grab the lockNote that even the “instant” migrations still need a momentary ACCESS EXCLUSIVE lock, so they too can get stuck behind a long transaction. Fast is not the same as unblockable. With that groundwork, here are the four operations that most often go wrong, and the safe version of each.
A new NOT NULL column
The naive version:
ALTER TABLE orders ADD COLUMN status text NOT NULL DEFAULT 'pending';Good news first: since Postgres 11, adding a column with a non-volatile default no longer rewrites the table. Postgres stores the default in the catalog and hands it back for old rows on read, so the statement above is fast. Two traps remain. A volatile default rewrites the whole table under ACCESS EXCLUSIVE, and gen_random_uuid() is volatile, so ADD COLUMN id uuid DEFAULT gen_random_uuid() will rewrite every row and every index. And adding NOT NULL to an existing column scans the entire table under a lock to prove there are no nulls.
The safe way to make an existing column NOT NULL uses a validated CHECK as a stepping stone:
-- 1. record the rule instantly; enforced on new rows, existing rows not scanned
ALTER TABLE orders ADD CONSTRAINT orders_status_nn CHECK (status IS NOT NULL) NOT VALID;
-- 2. backfill any existing NULLs (see the next section), then:
-- 3. scan to validate, but under a lock that does NOT block reads or writes
ALTER TABLE orders VALIDATE CONSTRAINT orders_status_nn;
-- 4. now SET NOT NULL skips its own scan, because the valid CHECK already proves it
ALTER TABLE orders ALTER COLUMN status SET NOT NULL;
-- 5. optional: drop the now-redundant CHECK
ALTER TABLE orders DROP CONSTRAINT orders_status_nn;That fourth step is the payoff, and it is genuinely in the manual: SET NOT NULL normally scans the whole table, “however, if a valid CHECK constraint exists which proves no NULL can exist, then the table scan is skipped.”
Changing a column’s type
ALTER TABLE events ALTER COLUMN id TYPE bigint; -- rewrites the whole table + indexesA real type change (int to bigint, text to jsonb) rewrites every row and every index under ACCESS EXCLUSIVE. A handful of no-op changes are metadata-only and safe, such as widening or dropping a varchar(n) length limit, but assume a rewrite unless you have checked. The safe path is expand and contract again: add id_new bigint, backfill it in batches, keep it in sync, then swap the columns in one quick transaction and drop the old one later.
Adding an index
CREATE INDEX ON orders (customer_id); -- SHARE lock: blocks writes for the whole buildA plain CREATE INDEX takes a SHARE lock, which lets reads continue but blocks every write to the table for the entire build. On a large table that is minutes of failed inserts. The fix is one keyword:
CREATE INDEX CONCURRENTLY ON orders (customer_id);CONCURRENTLY builds the index without blocking reads or writes. It costs you two passes over the table (slower), it cannot run inside a transaction block (so most migration tools need a flag to run this statement outside their usual wrapping transaction, and you cannot batch it with other DDL), and if it fails partway it leaves an INVALID index behind that you must DROP INDEX and rebuild. Worth it every time on a busy table. There is a matching DROP INDEX CONCURRENTLY. For the full story on what indexes cost and when they help, see Indexes.
Adding a foreign key
ALTER TABLE orders ADD CONSTRAINT orders_customer_fk
FOREIGN KEY (customer_id) REFERENCES customers (id);Adding a foreign key validates every existing row in orders against customers while holding a lock that blocks writes. Split it in two, the same NOT VALID trick as before:
-- instant: enforced on new and changed rows, existing rows not scanned
ALTER TABLE orders ADD CONSTRAINT orders_customer_fk
FOREIGN KEY (customer_id) REFERENCES customers (id) NOT VALID;
-- later, in its own transaction: scans existing rows under SHARE UPDATE EXCLUSIVE,
-- which does not block reads or writes
ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_fk;Backfilling without melting the database
Step two of expand and contract said “in batches,” and glossed over why. Here is the why. The obvious backfill is one statement:
-- Do NOT run this on a large, live table.
UPDATE users SET display_name = full_name;On a ten-million-row table that single UPDATE is a small catastrophe. Because of MVCC it writes a brand-new version of every row, so the table can nearly double in size before autovacuum catches up. It generates one enormous write-ahead-log burst in a single transaction, which your replicas have to replay, so replication lag spikes and read replicas fall behind. It holds row locks on everything it has touched until it commits. And if it dies at 90%, the whole thing rolls back and you start from zero.
Walk the table in chunks instead. Each batch is its own small transaction that commits, releases its locks, and lets autovacuum and the replicas breathe before the next one:
Driving that from your app, keyed on the primary key so each batch is a cheap indexed range:
import { setTimeout as sleep } from "node:timers/promises";
async function backfillDisplayName(db) {
let lastId = 0;
for (;;) {
const { rows } = await db.query(
`UPDATE users
SET display_name = full_name
WHERE id IN (
SELECT id FROM users
WHERE id > $1 AND display_name IS NULL
ORDER BY id
LIMIT $2
)
RETURNING id`,
[lastId, 5000]
);
if (rows.length === 0) break; // caught up
lastId = rows[rows.length - 1].id; // advance the cursor
await sleep(100); // let vacuum + replicas catch up
}
}The WHERE display_name IS NULL clause makes the whole thing idempotent and resumable: rerun it after a crash and it skips the rows already done. Each UPDATE touches at most 5,000 rows, commits, and releases. Tune the batch size and the pause to your hardware; if replication lag climbs, shrink the batch or lengthen the sleep. This is intentionally not run inside a migration file, because a migration is one transaction and the entire point here is many small ones. Backfills usually run as a separate script or a background job, kicked off after the expand migration has landed.
Rollback is mostly a lie
Every migration tool offers a down migration, and beginners assume that is the safety net. For live data changes, it mostly is not, and leaning on it is how a bad migration becomes a worse incident.
Consider what a down migration can actually restore. Reverse a CREATE TABLE with DROP TABLE: clean, genuinely reversible. Reverse a DROP COLUMN by re-adding the column, and you get the column back with the right type and none of the data, because the bytes were freed the moment you dropped it. Reverse a migration that transformed data (normalised phone numbers, split a name into two fields) and you cannot, because you did not keep the originals. The down migration reruns and lies to you about having undone anything.
There is a second problem specific to the rolling-deploy world. “Rolling back” a release means redeploying the old code, and that old code now meets whatever schema state you are currently in, which may already be a step ahead of it. That is the exact old-and-new-together problem from the top of the article, except now it is unplanned and happening during an incident.
So the real strategy is forward-fix. When a migration is wrong, you do not reverse it, you write the next migration that corrects it. This is precisely why expand and contract is safe to begin with: every step is independently deployable, and the “undo” for a bad expand is simply to not run the contract. The old column is still sitting right there.
Rules that keep you out of trouble
The whole article compresses to a short list you can pin above your desk:
- Old and new code run together. Every migration must be compatible with the release before it and the release after it.
- Expand and contract by default. Add the new shape, backfill, dual-write, switch reads, drop the old shape. Additive changes ship ahead of the code; destructive changes ship behind it.
- Never edit a migration that has run. It is frozen. Write the next one.
- Assume
ALTER TABLEtakes the strongest lock until you have confirmed otherwise, and set a shortlock_timeoutso a migration aborts instead of freezing the lock queue. CONCURRENTLYfor indexes,NOT VALIDthenVALIDATEfor constraints. These are the safe forms of the two most common locking operations.- Backfill in batches, in a separate step, never one giant
UPDATE. - Fix forward. Down migrations are for local dev, not production recovery.
- Test the migration against a production-sized copy, because a statement that is instant on a thousand rows can lock a table for ten minutes on a hundred million.
None of this is exotic, and none of it is going to change soon. Postgres has behaved this way for years and the version numbers move but the locking rules do not. The teams that ship schema changes calmly are not smarter, they just internalised that the database has two users during every deploy and wrote their migrations for both of them.
Summary
- A migration is a versioned file applied in order and recorded in a ledger table; the tool that runs them (Prisma Migrate, Drizzle Kit, Knex, node-pg-migrate) is a detail, the SQL inside is what matters.
- During a rolling deploy the old and new code run at once against one schema, so every migration must work for both. This is what a naive rename, drop, or retype violates.
- Expand and contract replaces one dangerous change with a safe sequence: expand (add nullable), backfill, dual-write, switch reads, contract (drop). It is the same coexistence discipline as API versioning.
- Some compatible migrations still lock the table. Most
ALTER TABLEforms takeACCESS EXCLUSIVE, and a waiting one parks at the front of the lock queue and freezes everything behind it. A shortlock_timeoutbounds the damage. - The safe forms of the risky operations: a nullable add plus validated
CHECKbeforeSET NOT NULL, expand-and-contract for type changes,CREATE INDEX CONCURRENTLY, andADD CONSTRAINT ... NOT VALIDthenVALIDATE CONSTRAINT. - Backfill huge tables in batches keyed on the primary key, each its own committed transaction, never one giant
UPDATEthat bloats the table and blows out the WAL. - Rollback is mostly a lie for data. Dropped and transformed data is gone, and redeploying old code recreates the coexistence problem. Fix forward with a new migration; keep down migrations only where they are cheap and honest.