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
⚡
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.
Advertisement