How to run a zero-downtime database migration

TL;DR

• Never run a blocking ALTER TABLE directly on a large production table. On MySQL use gh-ost or pt-online-schema-change; on PostgreSQL use CREATE INDEX CONCURRENTLY, NOT VALID constraints, and a strict lock_timeout.
• Use the expand, migrate, contract pattern: add the new structure alongside the old, dual-write and backfill, switch reads, then remove the old structure in a later release.
• Backfill in small batches (1,000 to 10,000 rows) with pauses, ordered by primary key, and monitor replication lag while it runs.
• Every step must be individually deployable and individually reversible. If a step cannot be rolled back on its own, split it.
• The single most common outage cause here is lock queueing: one blocked ALTER makes every later query wait behind it. Timeouts and off-peak windows prevent that.

Why “just run the ALTER” takes sites down

On a table with a few million rows, a schema change stops being a metadata tweak and becomes a bulk operation. Two failure modes cause almost all migration outages:

1. Table rewrites. Operations like changing a column type or (on older engines) adding a column copy the entire table. Writes block or queue for the duration.
2. Lock queueing. Even a fast ALTER needs a brief exclusive lock. If a long-running query holds a conflicting lock, the ALTER waits, and every query that arrives after the ALTER waits behind it. The database looks “down” even though nothing heavy is running. On PostgreSQL this is the classic ACCESS EXCLUSIVE pile-up.

The fix is not courage. It is a pattern plus the right tools.

The expand, migrate, contract pattern

Every zero-downtime schema change, however complex, reduces to three phases shipped as separate deploys:

Phase	What happens	Reversible?
Expand	Add the new column, table, or index alongside the old one. Old code ignores it.	Yes, trivially
Migrate	Application dual-writes old and new. Backfill historical rows. Switch reads to the new structure.	Yes, switch reads back
Contract	Once the new path is proven, stop writing the old structure and drop it.	No, this is the point of no return

The rule that makes it work: at every moment, the running application code must be compatible with both the current schema and the previous one. That is what lets you deploy, observe, and roll back any single step without coordinating a code deploy and a schema change into one atomic event (you can’t, and pretending you can is where downtime comes from).

Step-by-step procedure

1. Classify the change before writing any SQL

• Additive (new nullable column, new table, new index): usually safe, still needs online techniques for indexes on big tables.
• Transformative (rename, type change, moving data, splitting a table): always full expand, migrate, contract. A rename becomes “add new column, dual-write, backfill, switch, drop old”.
• Destructive (drop column or table): contract-phase only, after code that references it is fully gone from production, including background jobs and analytics queries.

2. Expand: create the new structure online

PostgreSQL:

SET lock_timeout = '5s';
ALTER TABLE orders ADD COLUMN customer_ref uuid;
CREATE INDEX CONCURRENTLY idx_orders_customer_ref ON orders (customer_ref);
ALTER TABLE orders ADD CONSTRAINT orders_customer_fk
  FOREIGN KEY (customer_ref) REFERENCES customers(id) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_fk;

The lock_timeout matters more than anything else on this page: if the ALTER cannot get its lock in 5 seconds, it fails cleanly instead of queueing the whole workload behind it. Retry it, do not remove the timeout. Note that CREATE INDEX CONCURRENTLY cannot run inside a transaction and can leave an INVALID index if it fails; check pg_indexes and drop and retry if so.

MySQL: prefer ALTER TABLE ... ALGORITHM=INPLACE, LOCK=NONE where the operation supports it (the server errors out if it does not, which is exactly what you want). For anything that would rewrite the table, use a ghost-table tool:

gh-ost \
  --database=app --table=orders \
  --alter="ADD COLUMN customer_ref BINARY(16)" \
  --max-lag-millis=1500 --chunk-size=1000 \
  --postpone-cut-over-flag-file=/tmp/ghost.postpone \
  --execute

gh-ost copies rows into a shadow table while tailing the binlog, then does a sub-second cutover you control. pt-online-schema-change does the same job with triggers instead of binlog tailing. Both throttle automatically on replication lag.

3. Migrate: dual-write, backfill, switch reads

1. Deploy dual-writes. Application writes go to both old and new columns. Reads still use the old one.
2. Backfill in batches, oldest first, keyed by primary key so each batch is an index range scan:

UPDATE orders SET customer_ref = legacy_customer_uuid(customer_id)
WHERE id BETWEEN :start AND :start + 5000 AND customer_ref IS NULL;

Sleep between batches, watch replication lag and lock waits, and make the job resumable (store the last processed id). A backfill that hammers the primary is just a slow-motion outage. 3. Verify. Count mismatches before switching anything: rows where old and new disagree should be zero, or explained. 4. Switch reads to the new column, ideally behind a feature flag so the switch is instant and instantly reversible.

4. Contract: remove the old structure, later

After the new path has run clean in production (days, not minutes): stop the dual-write, wait one more release cycle, then drop the old column or table during a quiet window. On PostgreSQL, DROP COLUMN is fast but still needs that brief exclusive lock, so keep the lock_timeout habit.

5. Rollback plan, written before you start

• Expand phase fails: drop what you added. No user impact.
• Backfill misbehaves: pause the job. Dual-writes keep new rows correct.
• Read switch shows wrong data: flip the flag back to old reads. Fix, re-verify, retry.
• After contract: there is no rollback, only restore from backup. Which is why contract comes last and slowest.

Common mistakes

• Adding a NOT NULL column with no default in one step instead of add nullable, backfill, then add the constraint (NOT VALID then VALIDATE on Postgres).
• Running the migration and the code change as one deploy, so neither can be rolled back independently.
• Backfilling with one giant UPDATE that bloats the table, saturates replication, and cannot be resumed.
• Forgetting consumers you do not own: read replicas feeding analytics, ETL jobs, and that one cron script that still selects the old column.

When to call for help

Most schema changes go fine. The ones that go badly tend to involve a table that is too big to copy in a maintenance window, replication that cannot absorb the backfill, or a migration that is already half-applied and stuck. If you are staring at one of those, Erzon takes production database incidents from triage, with a first response within one business hour and a flat quote before any work starts, and every fix ships with a written root-cause report so the next migration is boring.