Cheat sheet · Shipping
Zero-downtime migrations
The problem
A shop wants customer phone numbers in one normalised format, in a new column of a 40-million-row table, without stopping checkout. A plain rename breaks the pods that still run the old code, and a 5 ms ALTER stops every query because it waits for a lock behind a long report. The safe way is expand and contract: add a nullable column, write both, backfill in batches, verify and switch reads, stop the old writes, and drop the old column last, with a way back at every phase.
What you learn
- Why a rolling deploy means two app versions on one schema, and why a rename breaks the old one
- Locks on a big table: the lock queue behind a long query, lock_timeout, concurrent indexes
- Expand and contract: nullable column, dual write, batched backfill, verify, switch reads, drop last
- Adding NOT NULL without a long lock, and which phase can still be rolled back
Diagram as code
The lesson's final design as text: paste it into Jira, GitLab, GitHub, Notion or your docs, where Mermaid renders by itself. There are D2 and PlantUML (Confluence, IntelliJ) versions too.
Mermaid: flowchart
flowchart LR
n_users(["Shoppers"])
n_db[("Customers DB")]
n_ci("Migrations")
n_new["App (new)"]
n_ci --> n_db
n_users --> n_new
n_new --> n_dbMermaid: request sequence
sequenceDiagram
participant n_ci as Migrations
participant n_db as Customers DB
participant n_old as App (old)
participant n_new as App (new)
participant n_filler as Backfill job
Note over n_ci,n_db: One migration, then the deploy
n_ci->>n_db: -- migration 0042, runs right before the rollout
n_db-->>n_ci: ALTER TABLE 12 ms
Note over n_old,n_db: The old pods ask for a column that is gone
n_old->>n_db: 14:00:03 migration 0042 done
Note over n_ci,n_db: A 5 ms ALTER stops the whole table
n_ci->>n_db: activity on customers 14:20:41
Note over n_ci,n_db: Expand: a nullable column, a lock timeout, a concurrent index
n_ci->>n_db: -- 0043 expand
n_db-->>n_ci: ALTER TABLE 4 ms
Note over n_new,n_db: The new version writes both columns
n_new->>n_db: -- v2: writes both columns, still reads the old one
n_db-->>n_new: UPDATE 1
Note over n_filler,n_db: Backfill in small batches
n_filler->>n_db: -- backfill, batch 3 112 of 8 000
n_db-->>n_filler: UPDATE 4987 140 ms
Note over n_new,n_db: Verify, then switch reads
n_new->>n_db: -- before switching reads
n_db-->>n_new: count = 0
Note over n_ci,n_db: Contract: make the old column optional, the new one required
n_ci->>n_db: -- 0044 contract, before v4 stops writing phone
n_db-->>n_ci: VALIDATE 3 min 40 s
Note over n_ci,n_db: Drop the old column last
n_ci->>n_db: -- 0045 contract, 7 days after the last reader of phone
n_db-->>n_ci: ALTER TABLE 3 msD2
direction: right
n_users: "Shoppers" {
shape: rectangle
}
n_db: "Customers DB" {
shape: cylinder
}
n_ci: "Migrations" {
shape: rectangle
style.multiple: true
}
n_new: "App (new)" {
shape: rectangle
}
n_ci -> n_db
n_users -> n_new
n_new -> n_dbPlantUML: request sequence
@startuml
participant "Migrations" as n_ci
database "Customers DB" as n_db
participant "App (old)" as n_old
participant "App (new)" as n_new
participant "Backfill job" as n_filler
note over n_ci, n_db : One migration, then the deploy
n_ci -> n_db : -- migration 0042, runs right before the rollout
n_db --> n_ci : ALTER TABLE 12 ms
note over n_old, n_db : The old pods ask for a column that is gone
n_old -> n_db : 14:00:03 migration 0042 done
note over n_ci, n_db : A 5 ms ALTER stops the whole table
n_ci -> n_db : activity on customers 14:20:41
note over n_ci, n_db : Expand: a nullable column, a lock timeout, a concurrent index
n_ci -> n_db : -- 0043 expand
n_db --> n_ci : ALTER TABLE 4 ms
note over n_new, n_db : The new version writes both columns
n_new -> n_db : -- v2: writes both columns, still reads the old one
n_db --> n_new : UPDATE 1
note over n_filler, n_db : Backfill in small batches
n_filler -> n_db : -- backfill, batch 3 112 of 8 000
n_db --> n_filler : UPDATE 4987 140 ms
note over n_new, n_db : Verify, then switch reads
n_new -> n_db : -- before switching reads
n_db --> n_new : count = 0
note over n_ci, n_db : Contract: make the old column optional, the new one required
n_ci -> n_db : -- 0044 contract, before v4 stops writing phone
n_db --> n_ci : VALIDATE 3 min 40 s
note over n_ci, n_db : Drop the old column last
n_ci -> n_db : -- 0045 contract, 7 days after the last reader of phone
n_db --> n_ci : ALTER TABLE 3 ms
@endumlPlantUML: components
@startuml
left to right direction
agent "Shoppers" as n_users
database "Customers DB" as n_db
collections "Migrations" as n_ci
component "App (new)" as n_new
n_ci --> n_db
n_users --> n_new
n_new --> n_db
@endumlRecap
Six rules for zero-downtime migrations.
1) During a rollout the old and the new version run on one schema: every change must work for both, so never rename in place. 2) Every DDL statement on a hot table gets a lock_timeout and a retry; build indexes concurrently. 3) Expand: add a nullable column without a default, then write both. 4) Backfill in small, restartable batches, only after all pods write both, and watch replica lag. 5) Verify, switch reads gradually, then stop the old writes; add NOT NULL as NOT VALID plus VALIDATE. 6) Drop last, after the rollback window: until then, each phase can be undone by deploying the previous version.
Numbers: at the start and at the end
| Metric | Start | End |
|---|---|---|
| Checkout errors | 0.1% | 0.1% |
| Longest blocked query | 0 s | 2 s |
| Rows backfilled | 0% | 100% |
| Time to roll back | 15 min | 3 min |
Steps
- Case One migration, then the deploy
Phone numbers are typed in by hand:
90 123-45-67,+998 (90) 1234567. - Problem The old pods ask for a column that is gone
The migration finishes at 14:00:03.
- Problem A 5 ms ALTER stops the whole table
Second try, without a rename:
ADD COLUMN phone_e164. - Fix Expand: a nullable column, a lock timeout, a concurrent index
The migration now sets
lock_timeout = 2s: if the lock is not free in 2 seconds, the ALTER gives up instead of blocking the queue, and the pipeline tries again 30 seconds later. - Fix The new version writes both columns
Version v2 rolls out.
- Fix Backfill in small batches
Once all 12 pods run v2, a job fills
phone_e164for the old rows: 5,000 rows per batch by id range, 140 ms each, then a 50 ms pause. - Fix Verify, then switch reads
Before anything reads the new column, a query checks that every row agrees with the old one: 0 differences.
- Fix Contract: make the old column optional, the new one required
The next version, v4, stops writing
phone. - Fix Drop the old column last
v4 has run for 7 days, query logs show no reader of
phone, and nobody plans to roll back to v3. - Recap Recap
Six rules for zero-downtime migrations.
Terms
- Expand and contract
- Changing a schema in phases: first add something new that old code ignores, write both and move the data, and remove the old part only once nothing uses it.
- Backfill
- Filling a new column or table for rows that already exist. On a big table it runs in small, restartable batches, with pauses and an eye on replica lag.
- Lock queue
- The line of statements waiting for a table lock. An exclusive lock waiting behind a long query also stops every query after it, which is why DDL runs with a lock_timeout.
- NOT VALID constraint
- A constraint that applies only to new writes at first; the old rows are checked later by VALIDATE without blocking reads or writes. The safe way to add NOT NULL or a foreign key to a big table.