Database Schema Migrations with Zero Downtime: The Expand-Contract Pattern

The Problem at 3 AM

Your team lands a contract requiring you to split a full_name column into first_name and last_name across 200 million rows. The naive approach: ALTER TABLE users DROP COLUMN full_name, ADD COLUMN first_name VARCHAR, ADD COLUMN last_name VARCHAR. You run it during “low traffic” at 2 AM. Postgres acquires an ACCESS EXCLUSIVE lock. For 47 minutes, your entire application is offline because every query touching users is blocked. The on-call engineer gets paged. The customer escalates. You revert, losing four hours of data. This is a schema migration war story that has happened at every company operating relational databases at scale.

The Expand-Contract pattern eliminates this failure mode entirely.