The Desi Architect
Notes / Docker Sandboxes / Zero-Downtime Database Schema Migrations: The Expand/Contract Pattern
Docker Sandboxes · Tier 4: Architect & Chaos · Lab 49

Zero-Downtime Database Schema Migrations: The Expand/Contract Pattern

Perform relational database schema migrations on high-traffic tables without downtime. Master the 5-phase Expand and Contract pattern: instant nullable column expansion, dual-write synchronization triggers, non-blocking batched historical backfills, safe constraint validation with NOT VALID, and clean legacy column decommissioning.

Time30 minutes
StackPostgreSQL 16Ports 15480
MemoryAbout 80 MB
Before thisLab 05SQL Transaction Isolation Levels &
Download lab (.zip) View steps
Verified runWindows, Mac, LinuxBash and PowerShell
  1. 1PrepareWhat you will learn
  2. 2PredictGuess before you run
  3. 3RunStep by step
  4. 4ObserveWhat you should see
  5. 5Break itChange one thing
  6. 6Clean upLeave nothing running
On this page
  1. 00 / What You Will Learn
  2. 01 / Why It Matters
  3. 02 / What We Are Building
  4. 03 / Words You Need
  5. 04 / Predict First
  6. 05 / Step by Step
  7. 06 / What You Should See
  8. 07 / Look Inside
  9. 08 / Break It, Fix It
  10. 09 / In Real Projects
  11. 10 / Interview Corner
  12. 11 / Clean Up

00 /What You Will Learn

By the end you will be able to
  • Identify why naive in-place column renames cause immediate application outages during rolling deployments.
  • Execute Phase 1 (Expand) by adding nullable columns with sub-millisecond catalog locks.
  • Deploy Phase 2 (Dual-Write) synchronization triggers to keep legacy and modern columns identical.
  • Run Phase 3 (Batched Backfill) to update millions of rows in indexed chunks without table-level write lockouts.
  • Enforce non-null column integrity safely in Phase 4 using NOT VALID and VALIDATE CONSTRAINT without exclusive locks.
  • Complete Phase 5 (Contract) by dropping synchronization triggers and retiring deprecated columns cleanly.

Before you start

Finish Lab 04 (SQL Basics with PostgreSQL) and Lab 24 (Relational Transactions & ACID Isolation Levels) first. You should understand SQL transactions, primary key indices, and how concurrent client connections query a database. Docker Desktop must be running.