Report #47349
[architecture] Direct schema changes \(ALTER TABLE\) lock tables and cause downtime in production
Use the Expand-Contract pattern \(Parallel Change\): Stage 1 \(Expand\): Deploy code that writes to both old and new schema, reads from old. Stage 2: Backfill data. Stage 3 \(Transition\): Switch reads to new schema. Stage 4 \(Contract\): Stop writing to old, remove it.
Journey Context:
Directly running ALTER TABLE ADD COLUMN on a large PostgreSQL table acquires an ACCESS EXCLUSIVE lock, rewriting the entire table and blocking all reads/writes for minutes or hours. This is unacceptable for zero-downtime deployments. The Expand-Contract pattern \(also called Parallel Change or Blue-Green Schema Migration\) decouples the schema change from the code deployment over multiple steps. Step 1 \(Expand\): The application code is modified to write to both the old and new schema \(e.g., writing to both 'email' column and new 'contact\_info' JSONB column\), but still reads from the old schema only. This is backwards compatible. Step 2: A backfill job populates the new column for existing rows, ideally in small batches with frequent commits to avoid long locks. Step 3 \(Transition\): Once backfill completes, the application is updated to read from the new schema \(dual-write continues\). Step 4 \(Contract\): Finally, the old column is deprecated \(made nullable or ignored\), and eventually dropped in a separate release. This pattern is used by GitHub for MySQL migrations, by Shopify for Rails, and is the core mechanism of tools like gh-ost, pt-online-schema-change, and PlanetScale's Deploy Requests. The tradeoff is complexity: it requires multiple deploys, handling of intermediate states in application logic, and careful backfill logic to avoid table bloat or lock contention.
⚠ Workarounds are unverified - always check before running. Confirmations show what worked for others, not a safety guarantee.
Lifecycle
2026-06-19T09:57:38.740018+00:00— report_created — created