Sharon Ben-Moshe is the founder of sqlfmt, a browser-based SQL formatter and validator with misspelled-keyword suggestions across PostgreSQL, MySQL, SQLite, SQL Server, and BigQuery.
Learn the expand-and-contract pattern for zero-downtime database migrations. Safely rename columns, add NOT NULL constraints, and migrate data in batches.
Quick answer
The expand-and-contract pattern (parallel run) achieves zero-downtime schema changes by breaking destructive migrations into three distinct deployment phases: 1) Expand (add the new column or table without breaking existing code), 2) Parallel Run & Backfill (application dual-writes to old and new, and historical data is backfilled in batches), and 3) Contract (switch reads entirely to the new schema and safely drop the old column).
FAQs
Why does renaming a column cause downtime?
Renaming a column directly in production (`ALTER TABLE users RENAME COLUMN email TO user_email`) causes immediate downtime: application instances running the previous release crash with 'column email does not exist' until new code deploys.
How do you add a NOT NULL column to a billion-row table without locking?
First, add the column as NULLABLE (instant in Postgres/MySQL). Second, backfill existing rows with defaults in small batches. Third, add a CHECK constraint with `NOT VALID` (which requires no lock) and validate it concurrently. Finally, add the NOT NULL constraint.
What is dual-writing in database migrations?
Dual-writing occurs during the transitional phase where application code writes simultaneously to both old and new columns/tables. This ensures both legacy and new application versions see consistent data during rolling zero-downtime deployments.
How should historical data backfills be executed?
Never run `UPDATE large_table SET new_col = old_col;` in a single transaction. It locks the table, inflates WAL logs, and causes replication lag. Backfill in indexed batches (e.g., 5,000 rows per batch) with small sleep pauses.
In modern 24/7 web applications, maintenance windows are a relic of the past. The expand-and-contract migration pattern allows engineering teams to deploy non-backward-compatible schema changes without dropping user requests or locking tables.
Key takeaways
Phase 1 (Expand): Add new columns or tables alongside existing ones without modifying existing columns.
Phase 2 (Backfill): Dual-write incoming changes and backfill historical rows in small indexed batches.
Phase 3 (Contract): Shift application reads to the new schema, stop dual-writing, and drop obsolete columns.
Never run full-table UPDATE or DELETE queries in a single production transaction.
The 3-phase lifecycle: Renaming a column safely
Instead of renaming `full_name` to `display_name` in one destructive migration, execute these steps across deployments:
Deploy Migration 1: Add display_name as NULLABLE. Deploy App v1: Reads full_name, writes to both full_name and display_name (dual-write).
Run background worker: Backfill existing rows where display_name IS NULL in batches of 5,000.
Deploy App v2: Reads and writes display_name. full_name is now deprecated.
Deploy Migration 2: Drop the legacy full_name column.
Batched backfill script pattern
-- Batch backfill historical records safely without table locksDO $$DECLARE v_batch_size INT := 5000; v_rows_updated INT;BEGIN LOOP UPDATE core.users SET display_name = full_name WHERE id IN ( SELECT id FROM core.users WHERE display_name IS NULL LIMIT v_batch_size FOR UPDATE SKIP LOCKED ); GET DIAGNOSTICS v_rows_updated = ROW_COUNT; EXIT WHEN v_rows_updated = 0; -- Brief pause to allow replication and vacuum to keep pace PERFORM pg_sleep(0.05); COMMIT; END LOOP;END $$;