Most deploys that take a site down for a few minutes aren't code problems. They're schema changes: a column renamed while the old version of the app is still running, or an ALTER TABLE that locks a busy table for longer than anyone expected.
The fix is a pattern called expand and contract (also known as parallel change). Instead of changing the schema in one step, you split the change into small steps. Each step works with both the old and the new version of your code. Here's how it works, with PostgreSQL examples.
Why one-step migrations break
During a rolling deploy, old and new app instances run side by side for a few minutes. Say you ship this as a single migration:
ALTER TABLE users RENAME COLUMN name TO full_name;
The new code works, but every old instance still querying name starts throwing errors until the deploy finishes. Rolling back is just as bad, because the old code can't read the new column either.
That leads to one rule: every schema change must be compatible with the code that's running now and the code that's about to run.
The pattern in six steps
Here's how to rename users.name to users.full_name without downtime.
1. Expand: add the new column
ALTER TABLE users ADD COLUMN full_name text;
Adding a nullable column without a default only changes metadata in PostgreSQL, so it's fast even on large tables. Since PostgreSQL 11, a constant default is fast too. A volatile default such as random() still rewrites the whole table.
Nothing reads this column yet, so the running code isn't affected.
2. Write to both columns
Deploy code that writes to both name and full_name on every insert and update, but still reads from name.
await db.query(
'UPDATE users SET name = $1, full_name = $1 WHERE id = $2',
[fullName, userId]
);
If you can't update every writer (other services, admin scripts), a trigger can keep the columns in sync during the transition:
CREATE OR REPLACE FUNCTION sync_full_name() RETURNS trigger AS $$
BEGIN
NEW.full_name := NEW.name;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER users_sync_full_name
BEFORE INSERT OR UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION sync_full_name();
Make a note to drop this trigger later. Step 5 explains when.
3. Backfill existing rows in batches
Older rows still have an empty full_name. Don't fix them with one giant UPDATE. A single large update holds row locks for the whole transaction and produces a burst of WAL and dead tuples, which can slow down replicas and everything else on the database.
Update in batches instead:
UPDATE users
SET full_name = name
WHERE id IN (
SELECT id FROM users
WHERE full_name IS NULL AND name IS NOT NULL
LIMIT 5000
);
Run it in a loop until it updates zero rows, with a short pause between batches:
const sleep = (ms) => new Promise((r) => setTimeout(r, ms));
let updated;
do {
const res = await db.query(BACKFILL_SQL);
updated = res.rowCount;
console.log(`Backfilled ${updated} rows`);
await sleep(200);
} while (updated > 0);
The name IS NOT NULL filter matters. Without it, rows with a null name match on every pass and the loop never ends. On very large tables, walk through primary key ranges (WHERE id BETWEEN x AND y) instead, so each batch uses the index.
4. Switch reads to the new column
Once the backfill finishes, verify it:
SELECT count(*) FROM users
WHERE full_name IS DISTINCT FROM name;
When that returns 0, deploy code that reads from full_name. Keep writing both columns in this release, so you can still roll back to the previous version without losing data.
5. Stop writing the old column
In the next release, write only full_name. Two things to do first:
- If you added the sync trigger, drop it. Otherwise it overwrites
full_namewith a stalenameon every update. - If
nameisNOT NULL, relax it, or inserts that skip it will fail:
DROP TRIGGER IF EXISTS users_sync_full_name ON users;
ALTER TABLE users ALTER COLUMN name DROP NOT NULL;
6. Contract: drop the old column
Wait until the new code has been stable for a while and nothing else reads name (reports, cron jobs, other services, BI dashboards). Then drop it:
ALTER TABLE users DROP COLUMN name;
This is fast in PostgreSQL too. The column is marked as dropped, and the table isn't rewritten.
Watch out for locks
Even "instant" DDL needs a brief ACCESS EXCLUSIVE lock. If a long-running query is holding a lock on the table, your ALTER TABLE waits behind it, and every new query on that table queues behind your ALTER TABLE. That's how a metadata-only change takes a site down.
Set a lock timeout so the migration fails fast instead of blocking traffic:
SET lock_timeout = '5s';
ALTER TABLE users ADD COLUMN full_name text;
If it times out, nothing changed. Retry a little later.
A few other changes that need care:
-
Indexes: use
CREATE INDEX CONCURRENTLY. It's slower, but it doesn't block writes. It can't run inside a transaction block, so most migration tools need it in a separate, non-transactional migration. -
Adding NOT NULL to a big table: add
CHECK (full_name IS NOT NULL) NOT VALIDfirst, then runVALIDATE CONSTRAINTseparately. Validation doesn't block normal reads and writes. On PostgreSQL 12+,SET NOT NULLcan then use the validated constraint and skip the full table scan. -
Foreign keys: same idea. Add them with
NOT VALID, then validate. - Changing a column type: most type changes rewrite the table. Treat them like a rename: new column, dual writes, backfill, switch reads, drop.
Quick checklist
- [ ] Every migration works with both the current and the next app version
- [ ] New columns are nullable or use a constant default
- [ ] Dual writes are live before the backfill starts
- [ ] Backfill runs in small batches with a stop condition
- [ ] Data is verified before reads switch
- [ ] Sync triggers are dropped before the old column stops being written
- [ ]
lock_timeoutis set on every DDL migration - [ ] Indexes are created
CONCURRENTLY - [ ] The old column is dropped only after nothing reads it
It takes more releases than a single migration. But each release is small, easy to roll back and boring to deploy, and that's what you want.
Prism Infoways is a software company that offers custom software development and related IT services.
This post was drafted with AI assistance.
Top comments (1)
The dual-write phase has an important rolling-deploy race: older instances may still write only the old column after the backfill has passed a row. Keep a one-way sync trigger in place until every old writer is gone, then verify mismatches again before switching reads; when dropping the trigger, explicitly define the rollback boundary because old code can no longer safely write.