⚡ ~/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 25 of 54 in Databases & Storage
Senior DevOps / SRE Database Database Migrations & Zero-Downtime Releases Enterprise Azure

Q: A production DB migration succeeds, but the API deployment fails and the DB change isn't backward-compatible. How would you recover safely?

Emergency incident triage and architectural prevention when a database schema migration executes in production, but the API deployment crashes and the schema change cannot be trivially rolled back.

#Database #Migrations #Disaster Recovery #Zero Downtime #Azure
🎙️ Candidate Opening & Architectural Context
"This is a Sev-1 scenario that violates the cardinal rule of continuous delivery: database migrations must always be backward-compatible with the currently running application code. Recovery requires rapid hotfixing or forward migration to minimize downtime."
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: This is a Sev-1 scenario that violates the cardinal rule of continuous delivery: database migrations must always be backward-compatible with the currently running application code. Recovery requires rapid hotfixing or forward migration to minimize downtime.

1

Immediate Triage: Diagnose API Crash & Avoid Destructive Rollback

Do NOT blindly rollback the database if users have already written new transactions, as rolling back could drop columns containing live customer data. Inspect the API boot crash logs to identify the exact schema mismatch.

2

Apply Forward Compatibility Hotfix / Database Bridge View

If a column was renamed, create an alias view or database trigger that maps old column names to new column names, allowing the previous v1 API to continue operating against the v2 schema while the v2 API bug is patched.

-- Creating an alias column or view for temporary backward compatibility
ALTER TABLE users ADD COLUMN legacy_username VARCHAR(255) GENERATED ALWAYS AS (email) STORED;
Advertisement
3

Enforce the Expand/Contract (Parallel Run) Migration Pattern

Mandate that no deployment may ever execute destructive schema changes in Phase 1. Follow the 3-phase rule: 1) Expand (add new columns, dual-write), 2) Deploy app, 3) Contract (drop old columns in a subsequent release).

Pro Tip: Zero-Downtime Principle: Schema changes must be decoupled from application code deployments. Schema first (additive), Code second, Cleanup third.
💡 The Senior SRE Gold Nugget (Key Architectural Takeaway)
"Never perform destructive database changes in step 1. Use the Expand/Contract pattern so current code can always operate against the updated schema."
⚡ 60-Second Elevator Pitch Talking Points
  • Do not execute destructive database rollbacks that risk dropping live customer data.
  • Create a database compatibility bridge (views, triggers, or default constraints) to support the v1 API.
  • Push an emergency roll-forward hotfix to resolve the API startup exception.
  • Enforce the Expand/Contract deployment pattern: all database migrations must be 100% backward-compatible.
⛔ 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 Database Migration Succeeds 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 Database Migration Succeeds 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 Database Migration Succeeds 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 Database Migration Succeeds 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 →