Database migrations
On this page 8 topics
Overview
Topics
- Expand-contract
- zero-downtime schema changes
- rollback strategies
Learn first
2.5 h of practice (estimate)
Learning outcomes
- Apply expand-contract to rename or restructure columns without downtime.
- Identify migrations that take heavy locks and use safe alternatives (
CREATE INDEX CONCURRENTLY, lock timeouts). - Backfill large tables in batches.
- Plan rollback for each migration step.
Lessons
Lab and exercises
Lab
MID-020-LAB1 · Zero-downtime rename
Outcomes: 1–4 · Time: ~3 h Rename customer.name to full_name across several releases of Shop while a load test runs. Use Flyway for each step, a batched backfill, lock_timeout, and a written rollback for each release. Acceptance: zero failed requests during the whole migration; a rollback note per step.
Exercises (assessment: migration plan review)
| ID | Outcome | Task |
|---|---|---|
| MID-020-EX01 | 1 | Write an expand-contract plan to split address into street and city. |
| MID-020-EX02 | 1 | Explain what each running app version needs from the schema at each step of your plan. |
| MID-020-EX03 | 2 | Classify these as safe or risky and give the safe form: add index, add NOT NULL column, add foreign key, change column type. |
| MID-020-EX04 | 2 | Show a migration that sets lock_timeout and creates an index concurrently; explain why it cannot run in a transaction. |
| MID-020-EX05 | 3 | Write a resumable batched backfill for 50 M rows and explain its batch size and pauses. |
| MID-020-EX06 | 4 | Fill a rollback table for each step of EX01. |
| MID-020-EX07 | 4 | Review a one-step migration plan (rename + drop + add NOT NULL) and list its failure modes. |
Show solutions
- EX01 Release 1 add nullable
street,city; 2 dual-write; 3 backfill by parsing; 4 read new; 5 stop writingaddress; 6 dropaddress. - EX02 Old app: needs the old columns until it is gone; new app: tolerates missing data in the new columns until backfill finishes.
- EX03 Index: use
CONCURRENTLY.NOT NULLcolumn: add nullable, backfill, then constraint (or constant default). Foreign key:NOT VALIDthenVALIDATE. Type change: new column plus backfill plus swap, not an in-place rewrite. - EX04
CREATE INDEX CONCURRENTLYwaits for existing transactions and cannot be wrapped in a transaction block; configure Flyway to run that script outside a transaction. - EX05 Loop by id range of 5 000 rows, each in its own transaction,
WHERE new IS NULL, a short sleep, and a stored progress marker; check replication lag. - EX06 Expand steps: redeploy the prior app, leave columns; backfill: rerun or clear; switch reads: redeploy old; contract: restore from backup (point of no return).
- EX07 Old instances fail on rename or drop;
NOT NULLfails on existing nulls or blocks while scanning; no rollback; table lock queue pile-up. - The "volatile default" mistake is discussed in EX03 and the quiz.
Quiz
Outcomes: O1 expand-contract · O2 lock-safe DDL · O3 batched backfills · O4 rollback planning.
A self-check for you: nothing is graded, saved or sent anywhere. Choose an answer, then check it. You can try again as often as you like.
Practical part
Migration plan review: find the risky steps in a given plan and rewrite them as safe, reversible steps with rollbacks. Pass: quiz 8/10, all risky steps fixed, and the lab with zero failed requests.
Common mistakes
Rename in one step; adding a NOT NULL column with a volatile default on a huge table.
Assessment
Migration plan review.
Career relevance
Large migrations (SRB-017).