⚡ ~/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 50 of 54 in Databases & Storage
Lead DevOps / Platform Architect Database Zero-Downtime Migrations Production Migration
🎯 Target Role / Context: Lead DevOps / Platform Architect Interview Scenario

Q: A high-throughput PostgreSQL table ('users', 40 million rows) requires renaming column 'phone' to 'phone_number' and adding a NOT NULL constraint with default values, while maintaining 99.999% API availability. Running a standard 'ALTER TABLE users ADD COLUMN ... NOT NULL DEFAULT ...' or renaming the column locks the table with ACCESS EXCLUSIVE, queuing thousands of web requests until the reverse proxy 504 timeouts. How do you design and execute a multi-phase Expand-Contract migration without downtime or table lock starvation?

Design and execute an Expand-Contract database schema refactoring on a 40-million row table, avoiding ACCESS EXCLUSIVE locks and application downtime.

#PostgreSQL #Database Migrations #Expand-Contract #Zero Downtime #Schema Changes #Flyway #Liquibase
🎙️ Candidate Opening & Architectural Context
"In relational databases, destructive DDL commands like column renames, drops, and adding constrained columns acquire heavy table locks (e.g., ACCESS EXCLUSIVE in PostgreSQL). Even if the DDL takes 100 milliseconds, any queued read or write query behind it is blocked, rapidly exhausting application connection pools and triggering cascading HTTP 504 gateway timeouts. The Expand-Contract (Parallel Run) pattern decouples database schema changes from application deployments across phased releases."
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 relational databases, destructive DDL commands like column renames, drops, and adding constrained columns acquire heavy table locks (e.g., ACCESS EXCLUSIVE in PostgreSQL). Even if the DDL takes 100 milliseconds, any queued read or write query behind it is blocked, rapidly exhausting application connection pools and triggering cascading HTTP 504 gateway timeouts. The Expand-Contract (Parallel Run) pattern decouples database schema changes from application deployments across phased releases.

⚡

Situation: Schema Migration Causes Severe Gateway Outages

⚡

Task: Execute Phased Refactoring Without Table-Level Lock Contention

Advertisement
⚡

Action: Four-Phase Expand-Contract Execution

⚡

Result: 100% Zero-Downtime Deployment & Zero Queued Locks

💡 The Senior SRE Gold Nugget (Key Architectural Takeaway)
"Never rename columns or add inline NOT NULL constraints directly in production relational databases. Always utilize the Expand-Contract pattern: add new column, dual-write in code, backfill in chunks, validate constraints asynchronously, and drop old columns in a subsequent release."
⚡ 60-Second Elevator Pitch Talking Points
  • Destructive DDL like column renames and inline constraints acquire ACCESS EXCLUSIVE locks.
  • Use the Expand-Contract pattern: add nullable column, dual-write in application, backfill in batches.
  • Add constraints with NOT VALID and validate later to avoid blocking concurrent database writes.
⛔ 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. Confusing ALB 502 Bad Gateway (target connection reset) with 504 Gateway Timeout (upstream response timeout) when configuring Zero Downtime Expand 502 indicates the backend target closed the TCP connection prematurely or returned malformed HTTP, whereas 504 means the target failed to respond before idle timeout.
  2. Setting target deregistration delay to zero instead of allowing connection draining when configuring Zero Downtime Expand Instant target deregistration severs in-flight HTTP requests during auto-scaling scale-in or rolling deployments, causing immediate user-facing drops.
  3. Failing to query CloudWatch ELB metrics (HTTPCode_ELB_5XX vs HTTPCode_Target_5XX) during load balancer outages when configuring Zero Downtime Expand Comparing load balancer generated errors against target generated errors immediately isolates whether the issue lies in the proxy tier or application runtime.
  4. Overlooking Security Group rules on backend EC2/ECS instances that block health check traffic from the ALB when configuring Zero Downtime Expand Target instances must explicitly permit inbound HTTP/HTTPS traffic from the ALB security group on the configured container/application port.
Advertisement
Want more Databases & Storage scenarios?
Explore our complete collection of scenario-based Databases & Storage interview runbooks.
Browse All Databases & Storage Questions →