Under the Hood: Zero-Downtime Database Migrations
Renaming or modifying a database column in a distributed system cannot happen instantaneously. At any moment during a zero-downtime deployment, older application code (v1) and newer application code (v2) run concurrently. The industry standard pattern for zero-downtime schema evolution is the Expand and Contract Pattern (Parallel Run).
The Expand and Contract Migration Lifecycle
Ghost Tables & Tools (GitHub gh-ost / pt-online-schema-change)
For complex schema mutations (like changing column types or primary keys on MySQL), enterprise teams use shadow table tools. pt-online-schema-change keeps the shadow table in sync with triggers; gh-ost is triggerless and tails the binary log. The gh-ost flow:
- Create a phantom shadow table with the new schema:
_orders_new. - Stream modifications from the primary database binary log (binlog) to continuously sync inserts/updates into the shadow table.
- Run a throttled background copier to migrate historical rows without saturating CPU or replication lag.
- Cut over with an atomic swap (
RENAME TABLE orders TO _orders_old, _orders_new TO orders;) coordinated by gh-ost's lock-and-rename algorithm (a sentry table blocks the RENAME until gh-ost is ready). Queries on the table are blocked briefly, and the window depends on waiting for in-flight queries.
Worked example: renaming name to full_name on a 200M-row table
| Step | DDL / code change | Why it is safe |
|---|---|---|
| 1. Expand | ALTER TABLE users ADD COLUMN full_name text; | A nullable column with no default is a metadata-only change in Postgres and MySQL 8 (instant ADD COLUMN) |
| 2. Dual-write | Deploy v2: writes both columns, still reads name | v1 and v2 can coexist; v1 ignores the new column |
| 3. Backfill | UPDATE users SET full_name = name WHERE id BETWEEN ? AND ? AND full_name IS NULL; in batches of 5-10k rows | Short transactions, bounded lock time and WAL, throttled on replica lag |
| 4. Verify | SELECT count(*) WHERE full_name IS DISTINCT FROM name returns 0 | Proves the backfill and dual-writes are correct |
| 5. Switch reads | Deploy v3: reads full_name, still dual-writes | Rollback to v2 remains possible |
| 6. Stop old writes | Deploy v4: writes only full_name | Nothing reads name anymore |
| 7. Contract | ALTER TABLE users DROP COLUMN name; with a lock_timeout | Metadata-only in Postgres; space is reclaimed on later rewrites |
Backfill math: 200M rows in 10k-row batches is 20,000 batches. At about 50 ms each plus a 50 ms sleep, that is about 33 minutes, while replicas stay within a second of lag. An unbatched single UPDATE would rewrite 200M tuples in one transaction, doubling table size and blocking vacuum for its whole duration.
Which operations are dangerous?
| Change | PostgreSQL | MySQL 8 InnoDB |
|---|---|---|
| Add nullable column | Instant | Instant |
| Add column with constant default | Instant (PG 11+) | Instant (8.0.12+) |
| Change column type (int to bigint) | Full table rewrite under ACCESS EXCLUSIVE | Table copy; use gh-ost |
| Add NOT NULL | Full scan; use a CHECK constraint NOT VALID, then VALIDATE (PG 12+ can then set NOT NULL without a scan) | Table rebuild |
| Add foreign key | Use NOT VALID, then VALIDATE CONSTRAINT | Online, but checks the whole table |
| Add index | CREATE INDEX CONCURRENTLY | ALGORITHM=INPLACE, LOCK=NONE |
Failure modes
- Lock queue pile-up: even an instant ALTER needs a brief ACCESS EXCLUSIVE lock. If it waits behind a 10-minute report query, all traffic queues behind it. Use
SET lock_timeout = '2s'and retry. - ORM caching: some ORMs cache column lists (for example Rails'
ignored_columnsexists for this reason), so dropping a column can break running processes. - CDC and analytics consumers (Debezium, warehouses) often break on renames even when the app does not.