1. The Perils of Locking Table Alterations
Executing `ALTER TABLE users RENAME COLUMN email TO contact_email;` on a table with 100 million rows acquires an exclusive table lock that blocks all incoming SELECT and UPDATE queries, causing service timeouts and downtime.
Zero-downtime schema migrations require decoupling application deployments from database alterations.
2. The 5-Phase Expand and Contract Strategy
Phase 1 (Expand): Add the new column/table without touching existing fields (`ALTER TABLE users ADD COLUMN contact_email VARCHAR(255);`).
Phase 2 (Dual Write): Deploy application code that reads from the old column but writes simultaneously to both old and new columns.
Phase 3 (Backfill): Run a background worker job copying historical data in regulated chunks (e.g. 1,000 rows per batch) from the old column to the new column.
Phase 4 (Read Switch): Switch application reads to the new column once backfill verification confirms 100% data parity.
Phase 5 (Contract): Remove the old write code and safely drop the deprecated column.
Model this architecture on the HLDForge Canvas
Turn static box diagrams into living, collaborative systems. Drag and drop microservices, Kafka clusters, and cache tiers. Run automated SPOF detection and STRIDE threat modeling in real time.
Written by HLDForge Architecture Team
Lead Database Reliability Engineer
The HLDForge Architecture Team publishes practical engineering guides, real-world case studies, and high-concurrency blueprints to help engineers build scalable and fault-tolerant software.