Schema migrations evolve database structures over time. By executing the Expand-and-Contract design pattern and keeping migrations backward-compatible, teams deploy updates with zero downtime.
1. What is the primary purpose of the 'Expand-and-Contract' pattern in database schema migrations?
Interview Questions
1 questions
Cheatsheet
Download-ready reference
Cheatsheet
Expand-and-Contract Deploying breaking schema changes in backward-compatible phases.
Up migration Applies schema additions/modifications.
Down migration Reverts migration changes (rollback).
Replication Lag Replicas taking time to apply migrations.
Postgres Concurrent Index:
CREATE INDEX CONCURRENTLY idx_user_id ON orders (user_id);
Migration Order:
1. Run DB Migration (Expand).
2. Deploy code (Dual-write/read transition).
3. Run data backfill script.
4. Deploy code (Read new, drop old reference).
5. Run DB Migration (Contract / Drop old).
As applications evolve, database structures must change. Schema Migrations are version-controlled script files (usually SQL) that update database tables.
Migrations are applied sequentially:
Up Migrations: Apply changes (e.g. CREATE TABLE, ADD COLUMN).
Down Migrations (Optional): Rollback changes (e.g. DROP TABLE, DROP COLUMN).
In production, code deployments and database migrations cannot happen at the exact same millisecond. If you run a migration that deletes or renames a column while production servers are running old code, the application will crash.
To achieve zero-downtime deployments, all database changes must be backward-compatible with the currently running application code.
The Expand-and-Contract Pattern (Three-Step Deploy)#
To execute breaking schema changes (like renaming a column from username to login), implement the Expand-and-Contract pattern across three phases:
Step 1: EXPAND - Database: Add the new column 'login' (keep 'username'). - Code: Write to BOTH columns; read from 'username'. Step 2: DATA MIGRATE & TRANSITION - Script: Copy all historical data from 'username' to 'login'. - Code: Read/write from 'login'; fallback/sync 'username'. Step 3: CONTRACT - Code: Remove all reference code to 'username'. - Database: Safely drop the old column 'username'.
This ensures that the database always supports both the old code and the new code during deployment windows.
1. Renaming columns directly in a single migration#
Running ALTER TABLE users RENAME COLUMN username TO login instantly crashes running application instances that are querying username. Always use Expand-and-Contract.
2. Running index creation on large tables blocking writes#
Running CREATE INDEX idx ON orders (user_id) locks the table for writes on large tables, causing backend requests to time out. In production PostgreSQL, always use CONCURRENTLY:
CREATE INDEX CONCURRENTLY idx_user_id ON orders (user_id);
Never write default values that trigger full table writes: Adding a column with a default value (e.g. DEFAULT 'active') in older database engines rewrites every single row, locking the table. Use nullable columns with application defaults instead.
Run migrations before code deploys: Always ensure the database expands to support the new schema before rolling out the new server instances.
Automate migration locks: Ensure your migration runner acquires transactional locks (or uses tools like Prisma Migrate lock records) to prevent concurrent runners from applying the same scripts.