Changing a schema while a million users are using it. The rule that makes it possible: the old code and the new code must both work against the intermediate state.
Prerequisites: Relational Modeling, Deployment Strategies Time to read: ~24 minutes
You need to rename users.email to users.email_address.
ALTER TABLE users RENAME COLUMN email TO email_address;
One line. And it will take your site down, because:
During a rolling deploy, old and new code run simultaneously. That’s not an edge case — it’s the definition of a rolling deploy, and it lasts minutes.
Time Servers 1–5 (old code) Servers 6–10 (new code)
──────────────────────────────────────────────────────────
t0 SELECT email —
t1 migration runs: column renamed
t2 SELECT email → 💥 ERROR SELECT email_address ✅
t3 SELECT email → 💥 ERROR SELECT email_address ✅
... (until the deploy finishes)
Half your fleet is throwing errors for the duration of the deploy. And if you need to roll back the code, the new column name is still there and the old code still fails.
🚨 The core rule, and everything in this chapter follows from it:
Every migration must leave the database in a state where both the previous and the next version of the application work correctly.
This means most schema changes are not one migration. They’re three to five deploys.
The general solution, also called parallel change.
1. EXPAND — add the new thing. Old code is unaffected.
2. MIGRATE — backfill data into the new thing.
3. DUAL — new code writes to both, reads from the new.
4. VERIFY — confirm they agree.
5. CONTRACT — remove the old thing.
Each step is independently deployable and independently reversible.
Step 1 — Add the new column (deploy 1: schema only)
ALTER TABLE users ADD COLUMN email_address TEXT;
Old code doesn’t know it exists. Nothing breaks. Fully reversible.
Step 2 — Write to both (deploy 2: code)
user.email = value
user.email_address = value # write both
# still read from the old column
🚨 Both old and new code work: old code reads/writes email (still correct), new code writes both.
Step 3 — Backfill (a background job, not a migration)
UPDATE users SET email_address = email
WHERE email_address IS NULL AND id BETWEEN :start AND :end; -- batched
See below on why batching matters.
Step 4 — Read from the new column (deploy 3: code)
value = user.email_address # read new, still write both
Reversible: if something’s wrong, redeploy step 2 and reads go back to the old column.
Step 5 — Stop writing the old column (deploy 4: code)
user.email_address = value # only the new one now
Step 6 — Drop the old column (deploy 5: schema)
ALTER TABLE users DROP COLUMN email;
🚨 Wait days or weeks before this, and only when no code path references it. Once dropped, rolling back to any earlier version is impossible without a restore. This step is the one that’s genuinely irreversible.
Six steps to rename a column. That’s the actual cost of zero downtime, and being able to walk through it is a strong senior signal.
The answer depends on your database and version — but the general shape holds.
| Operation | Note |
|---|---|
ADD COLUMN (nullable, no default) |
Metadata-only in modern Postgres and MySQL 8 |
ADD COLUMN ... DEFAULT |
Safe in Postgres 11+ and MySQL 8+ (stored in metadata, not backfilled) |
CREATE INDEX CONCURRENTLY |
Postgres — doesn’t block writes. Always use this. |
DROP INDEX CONCURRENTLY |
Same |
Adding a CHECK constraint NOT VALID, then VALIDATE separately |
Two steps, no long lock |
| Renaming an index | Metadata only |
| Operation | Why | Do this instead |
|---|---|---|
ALTER COLUMN TYPE |
🚨 Full table rewrite with an exclusive lock | Add a new column, backfill, swap |
SET NOT NULL |
Full table scan holding a lock | Add a NOT VALID check constraint, validate, then set |
ADD FOREIGN KEY |
Locks and scans both tables | NOT VALID, then VALIDATE CONSTRAINT |
CREATE INDEX (without CONCURRENTLY) |
Blocks writes for the whole build | Always use CONCURRENTLY |
RENAME COLUMN / RENAME TABLE |
Instant, but breaks running code | Expand–contract |
DROP COLUMN |
Instant, but irreversible and breaks old code | Contract phase only, after a waiting period |
| Adding a column with a volatile default | Rewrites every row | Add nullable, backfill in batches |
🚨 The lock queue problem — the one that catches people out. In Postgres, a migration needing an
ACCESS EXCLUSIVE lock waits behind any running query. And every subsequent query queues behind
the waiting migration. So a “one second” ALTER TABLE that gets stuck behind a 5-minute analytics
query blocks all traffic to that table for 5 minutes.
The mitigation is essential and easy to state:
SET lock_timeout = '3s'; -- fail fast rather than queue
SET statement_timeout = '30s';
ALTER TABLE users ADD COLUMN ...;
If it can’t get the lock in 3 seconds, it fails and you retry — instead of taking the site down.
🎙️ Mentioning lock_timeout unprompted is a very strong operational signal.
-- ❌ Locks the table, holds a huge transaction, bloats the WAL, blocks vacuum
UPDATE users SET email_address = email;
On 100 million rows this runs for hours, holds locks, generates enormous WAL, and if it fails at 95% you start over.
# ✅ Batched, resumable, throttled
last_id = load_checkpoint() or 0
while True:
rows = db.execute("""
UPDATE users SET email_address = email
WHERE id > :last_id AND email_address IS NULL
ORDER BY id LIMIT 1000
RETURNING id
""", last_id=last_id)
if not rows:
break
last_id = max(r.id for r in rows)
save_checkpoint(last_id)
time.sleep(0.1) # throttle — leave headroom for real traffic
The rules for any backfill:
WHERE ... IS NULL clause means re-running is safe.🚨 Watch replication lag during a backfill. A fast backfill generates WAL faster than replicas can apply it, so replicas fall minutes behind — and if you serve reads from them, users see stale data across the whole application. Monitor it and throttle dynamically.
Moving from one database to another entirely — Postgres to Cassandra, or one Postgres to a sharded cluster. Same principle, larger scale.
flowchart TB
P1["1 · Dual write<br/>write both, read old"] --> P2["2 · Backfill<br/>historical data"]
P2 --> P3["3 · Verify<br/>compare continuously"]
P3 --> P4["4 · Shadow read<br/>read both, serve old, log differences"]
P4 --> P5["5 · Flip<br/>read new, keep writing both"]
P5 --> P6["6 · Decommission<br/>stop writing old"]
🚨 Steps 3 and 4 are what people skip, and they’re what makes it safe.
Shadow reads are the key technique: read from both systems, serve the old system’s answer, and log any disagreement. You get production-traffic validation with zero user risk. Run it for days until the mismatch rate is zero, and investigate every difference — each one is a bug you would otherwise have shipped.
Verification should be continuous and automated: row counts, checksums over ranges, and spot comparisons. Do not rely on “it looked fine when we checked.”
Keep the ability to roll back through step 5. Once you stop writing to the old system, going back means replaying everything — so stay dual-writing longer than feels necessary.
A useful shortcut: CDC can perform the dual-write for you without touching application code — stream changes from the old database into the new one. This is often dramatically simpler than modifying every write path.
Separate schema deploys from code deploys. Never in the same release. Schema first (additive), then code, then a later schema change to remove things. This is what makes each step reversible.
Every migration needs a rollback plan. For additive changes it’s trivial. For destructive ones, the honest answer is often “restore from backup” — which means you should think very carefully before running them.
Test against production-sized data. A migration that takes 200 ms on 10,000 rows takes 40 minutes on 100 million. Staging with a thousand rows tells you nothing about lock duration.
Migrations must be idempotent and forward-only. Down-migrations are a fiction in production —
you don’t run migrate down on a live database, you write a new forward migration that undoes the
change. Most teams eventually stop writing down-migrations entirely.
Use a migration tool — Flyway, Liquibase, Alembic, Rails migrations, golang-migrate. Version control, ordering, and an applied-migrations table are table stakes. Tools like gh-ost and pt-online-schema-change (MySQL) perform table rewrites without long locks by building a shadow table and swapping it.
Feature flags decouple the migration from the behaviour change. Deploy the code that uses the new column behind a flag, migrate, then enable the flag — and disable it instantly if something’s wrong, with no deploy. → Feature Flags
| Decision | Gain | Cost |
|---|---|---|
| Expand–contract | Zero downtime; reversible at each step | 4–6 deploys instead of 1; days or weeks of elapsed time |
| Big-bang migration with downtime | Fast, simple, one step | Downtime; risky; no gradual rollback |
| Batched backfill | No long locks; resumable; throttleable | Slower; more code |
| Shadow reads | Production validation with no user risk | Double the read load; work to build |
| Dual writes | Rollback stays possible | Two systems must be kept consistent |
lock_timeout |
A stuck migration fails instead of blocking the site | You must retry |
⚖️ And a genuinely valid alternative: schedule downtime. For an internal tool, a B2B product with known usage windows, or a small user base, a 10-minute maintenance window at 3 a.m. Sunday is simpler, safer, and cheaper than a six-deploy expand–contract dance. 🎙️ Saying that out loud is a maturity signal, not a weakness — the question is whether the downtime cost exceeds the complexity cost.
ALTER TABLE locked tables for hours on their scale.
It creates a shadow table, copies rows in throttled batches, applies ongoing changes from the
binlog, and swaps atomically — with the ability to pause and abort at any point.UPDATE without batching on a large table.CREATE INDEX CONCURRENTLY. A plain CREATE INDEX blocks writes for the whole
build.lock_timeout. The lock queue can take down a table that the migration itself would only
have locked for a second.lock_timeout to a few seconds so a blocked migration fails fast. Otherwise it queues
behind a long query and every subsequent query queues behind *it — a one-second ALTER takes the
table offline for minutes.”*1. Break it deliberately. Run an app with two instances against Postgres. Rename a column while both are serving traffic. Watch one instance error. Then redo it with expand–contract and watch it work. Twenty minutes, and the lesson is permanent.
2. Cause the lock queue. In one session, start a long query:
BEGIN; SELECT pg_sleep(60) FROM users LIMIT 1;
In a second, run ALTER TABLE users ADD COLUMN x INT; — it waits. In a third, run a simple
SELECT * FROM users LIMIT 1 — it also waits, behind the ALTER. That’s the queue that takes sites
down. Now add SET lock_timeout = '2s'; and watch the ALTER fail fast instead.
3. Time a real migration. Create a 50-million-row table. Run CREATE INDEX and time it, then
CREATE INDEX CONCURRENTLY while writing to the table. Note that the first blocks writes and the
second doesn’t.
4. Write a proper backfill. Batched, checkpointed, throttled on replication lag. Kill it halfway and confirm it resumes. This is code you will write repeatedly in your career.