Report #61639
[architecture] Zero-downtime schema migrations causing locking issues and data loss
Implement the Expand/Contract pattern \(Parallel Change\) for all schema changes in production. Never rename or drop columns in a single migration. Phase 1 \(Expand\): Add new column/table as nullable, deploy code that writes to both old and new \(dual-write\), backfill existing data. Phase 2 \(Transition\): Switch reads from old to new, stop writing to old. Phase 3 \(Contract\): Remove old column/table after confirming no rollback needed. For large tables \(>10M rows\), use online schema change tools: pt-online-schema-change \(Percona\) or gh-ost for MySQL; CREATE INDEX CONCURRENTLY for PostgreSQL \(never standard CREATE INDEX on production tables\).
Journey Context:
Direct ALTER TABLE operations acquire ACCESS EXCLUSIVE locks \(PostgreSQL\) or rebuild tables \(MySQL\), blocking writes and reads for seconds to minutes on large tables, causing downtime or cascading failures. Renaming columns breaks running application instances during rolling deployments \(old code crashes trying to read renamed column\). The expand/contract pattern allows rolling deployments where old and new code versions coexist. Common mistakes: forgetting the dual-write phase \(data written by old code during transition is lost in new structure\) or skipping the backfill \(existing data must be migrated before switching reads\). Tradeoffs: Expand/contract requires 3 deployments instead of 1, temporary storage overhead for duplicate columns, and complex rollback procedures \(must handle both old and new data formats during transition\).
⚠ Workarounds are unverified - always check before running. Confirmations show what worked for others, not a safety guarantee.
Lifecycle
2026-06-20T09:57:05.635929+00:00— report_created — created