migration-patterns

Migration Patterns

Write safe database migrations that can be applied to production with zero downtime, tested rollback paths, and separated schema and data changes.

When to Use

• User writes a database migration
• User needs to change schema without downtime
• User asks about safe column additions or removals
• User needs to migrate data between schemas
• User plans rollback strategies for migrations

Core Patterns

Zero-Downtime Column Addition

Add columns as nullable or with defaults. Never add a NOT NULL column without a default to an existing table with data.

-- Migration: Add new column
-- Step 1: Add nullable column (instant, no lock)
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- Step 2: Backfill data (can be done in batches)
UPDATE users SET phone = '' WHERE phone IS NULL;

-- Step 3: Add constraint (in a later migration, after backfill completes)
ALTER TABLE users ALTER COLUMN phone SET NOT NULL;
ALTER TABLE users ALTER COLUMN phone SET DEFAULT '';

-- BAD: This locks the table and rewrites all rows on older PostgreSQL versions
ALTER TABLE users ADD COLUMN phone VARCHAR(20) NOT NULL DEFAULT '';

-- GOOD on PostgreSQL 11+: Default is stored in catalog, no rewrite
-- But still verify on your version and table size
ALTER TABLE users ADD COLUMN phone VARCHAR(20) NOT NULL DEFAULT '';

Zero-Downtime Column Removal

Remove columns in three phases across separate deployments to avoid breaking running code.

Phase 1 (code change): Stop reading/writing the column in application code
Phase 2 (migration):   DROP the column from the database

-- Migration file: 003_drop_legacy_status.sql

-- Phase 2: Safe to drop after Phase 1 is deployed
ALTER TABLE orders DROP COLUMN IF EXISTS legacy_status;

Never drop a column that application code still references. Deploy the code change first, then drop the column in the next release.

Zero-Downtime Column Rename

Renaming a column requires a multi-step migration to avoid breaking running code.