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.
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."
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
Action: Four-Phase Expand-Contract Execution
Result: 100% Zero-Downtime Deployment & Zero Queued Locks
- 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.
Senior hiring managers and bar raisers evaluate how you prioritize during outages. Avoid these common anti-patterns:
- 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.
- 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.
-
Failing to query CloudWatch ELB metrics (
HTTPCode_ELB_5XXvsHTTPCode_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. - 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.