mufassall

Cheat sheet · Shipping

Zero-downtime migrations

15 min · Advanced · mufassall.dtwin.uz/en/l/zero-downtime-migrations/

← Open the lesson

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
Final scene
60%

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_db

Mermaid: 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 ms

D2

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_db

PlantUML: 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
@enduml

PlantUML: 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
@enduml

Recap

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

MetricStartEnd
Checkout errors0.1%0.1%
Longest blocked query0 s2 s
Rows backfilled0%100%
Time to roll back15 min3 min

Steps

  1. Case One migration, then the deploy

    Phone numbers are typed in by hand: 90 123-45-67, +998 (90) 1234567.

  2. Problem The old pods ask for a column that is gone

    The migration finishes at 14:00:03.

  3. Problem A 5 ms ALTER stops the whole table

    Second try, without a rename: ADD COLUMN phone_e164.

  4. 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.

  5. Fix The new version writes both columns

    Version v2 rolls out.

  6. Fix Backfill in small batches

    Once all 12 pods run v2, a job fills phone_e164 for the old rows: 5,000 rows per batch by id range, 140 ms each, then a 50 ms pause.

  7. Fix Verify, then switch reads

    Before anything reads the new column, a query checks that every row agrees with the old one: 0 differences.

  8. Fix Contract: make the old column optional, the new one required

    The next version, v4, stops writing phone.

  9. 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.

  10. 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.