⚡ ~/naveed Interview Prep
⚡ Portfolio Home 👤 About Naveed Bio & Credentials 📫 Contact Naveed Get In Touch ✍️ Engineering Blog Deep Dives 🎯 Interview Hub 1,000+ Scenarios ☸️ Kubernetes Mastery Hub 24 Modules 🎮 DevOps Arcade & Quizzes Subnet Blitz ⚡ 🗺️ DevOps Roadmaps PDFs & Guides 🤖 Morpheus Analysis AI Quant ↗ 🛠️ Developer Tools Utilities 🧪 Labs & Experiments 📄 Interactive CV & Certs 🔗 All Links & Socials ⚡ Join The Dispatch (Weekly SRE Newsletter) →
← Back to All Databases & Storage Interview Questions Scenario 53 of 54 in Databases & Storage
Staff SRE / Principal Architect Database Schema Migrations & High Availability J.P. Morgan Technical Loop

Q: How do you handle zero-downtime database migrations in a distributed application?

Step-by-step architectural execution of the Expand and Contract pattern to migrate database schemas across distributed microservices with zero downtime.

#Database #Zero Downtime #Migrations #PostgreSQL #Flyway #Expand-Contract #System Design
🎙️ Candidate Opening & Architectural Context
"In distributed microservices, running synchronous schema migrations (like renaming columns or adding NOT NULL constraints) during deployments breaks active service replicas. Zero-downtime database migrations mandate the Expand and Contract (Parallel Change) pattern across multiple decoupled deployment phases so that both old and new application versions function simultaneously."
Advertisement
⚡ Recommended Practice Lab

Want to master this scenario in a live sandbox? KodeKloud's PostgreSQL Database Administration & High Availability Course covers this exact problem with hands-on terminal drills.

🛠️ Production Runbook & Step-by-Step Resolution

Direct Answer: In distributed microservices, running synchronous schema migrations (like renaming columns or adding NOT NULL constraints) during deployments breaks active service replicas. Zero-downtime database migrations mandate the Expand and Contract (Parallel Change) pattern across multiple decoupled deployment phases so that both old and new application versions function simultaneously.

1

Phase 1: Expand (Additive Non-Breaking Schema Change)

Add new columns or tables without destructive constraints. Never rename a column directly. If transitioning from phone to phone_number, add phone_number as a nullable column. Run this migration in CI before deploying code.

-- Step 1: Add new column safely
ALTER TABLE customers ADD COLUMN phone_number VARCHAR(32);
2

Phase 2: Dual-Writing Application Release

Deploy Application Version 2.0. The code writes new data to BOTH phone and phone_number, but continues reading from phone. If a rollback to Version 1.0 is required, Version 1.0 remains 100% operational because phone is still updated.

Additive Schema Change→Deploy Dual-Write App→Async Data Backfill→Switch Primary Read→Drop Legacy Column
Advertisement
3

Phase 3: Backfill Historical Data in Batches

Run an asynchronous background worker to copy historical values from phone to phone_number. Execute backfills in small indexed batches (e.g. 5,000 rows with sleep intervals) to prevent table locking or replication lag.

-- Batch backfill query with sleep to avoid locks
UPDATE customers 
SET phone_number = phone 
WHERE id BETWEEN 1 AND 5000 AND phone_number IS NULL;
4

Phase 4 & 5: Switch Read Traffic & Contract (Deprecate Old Column)

Deploy Application Version 2.1 to read exclusively from phone_number. Add NOT NULL constraints using VALIDATE CONSTRAINT (which avoids table locks in PostgreSQL). In a subsequent sprint, drop the legacy phone column once zero services query it.

Pro Tip: Golden Rule: Never bundle a breaking database schema migration and an application code release in the same deployment step.
💡 The Senior SRE Gold Nugget (Key Architectural Takeaway)
"Execute database migrations using the Expand and Contract pattern across separate releases: 1) Add nullable column, 2) Dual-write in code, 3) Backfill historical rows in batches, 4) Switch reads, 5) Drop old column."
⚡ 60-Second Elevator Pitch Talking Points
  • Apply the Expand-Contract pattern to ensure database backward compatibility at all times.
  • Make all database schema additions non-breaking (nullable columns without defaults).
  • Deploy dual-writing application versions so both legacy and modern replicas remain functional.
  • Backfill historical records in rate-limited background batches before deprecating old columns.
⛔ Critical Candidate Mistakes to Avoid in This Scenario

Senior hiring managers and bar raisers evaluate how you prioritize during outages. Avoid these common anti-patterns:

  1. Applying exclusive-lock DDL table migrations on live production databases during peak traffic windows when configuring Zero Downtime Database DDL migrations that acquire an exclusive table lock block all concurrent queries, queuing connections until the database connection pool exhausts.
  2. Allowing high-frequency queries to trigger full table scans without appropriate composite indexing when configuring Zero Downtime Database Sequential table scans saturate database CPU and disk I/O, evicting frequently queried data from the buffer cache and causing cascading timeouts.
  3. Disabling referential integrity constraints during bulk ETL imports without post-import consistency validation when configuring Zero Downtime Database Skipping relational consistency checks risks introducing orphaned records and referential integrity violations into production tables.
  4. Relying on database snapshot backups that have never been verified through automated restoration drills when configuring Zero Downtime Database Backups that have never been tested with automated restore verification frequently fail during real disaster recovery scenarios.
Advertisement
Want more Databases & Storage scenarios?
Explore our complete collection of scenario-based Databases & Storage interview runbooks.
Browse All Databases & Storage Questions →