Database Migration Checklist

A complete runbook for safe, zero-downtime database migrations. Covers the expand-contract pattern, dangerous operations to avoid, and a step-by-step deployment checklist.

Operations by Risk Level

The Expand-Contract Pattern

-- GOAL: Rename column "full_name" → "display_name" without downtime

-- PHASE 1: EXPAND (deploy migration first, old app still works)
ALTER TABLE users ADD COLUMN display_name TEXT;

-- PHASE 2: BACKFILL (background job, batch processing)
UPDATE users SET display_name = full_name WHERE display_name IS NULL LIMIT 10000;
-- Repeat in batches until complete

-- PHASE 3: SYNC (keep both in sync during transition via trigger or app code)
-- Trigger approach:
CREATE OR REPLACE FUNCTION sync_display_name()
RETURNS TRIGGER AS $$
BEGIN
  NEW.display_name := COALESCE(NEW.display_name, NEW.full_name);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_sync_display_name
BEFORE INSERT OR UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION sync_display_name();

-- PHASE 4: DEPLOY new app code that reads display_name

-- PHASE 5: CONTRACT (after all app instances use display_name)
DROP TRIGGER trg_sync_display_name ON users;
ALTER TABLE users DROP COLUMN full_name;

Safe NOT NULL Migration

-- Step 1: Add column as nullable (instant)
ALTER TABLE orders ADD COLUMN status TEXT;

-- Step 2: Backfill existing rows in batches (no lock)
UPDATE orders SET status = 'pending'
WHERE id IN (SELECT id FROM orders WHERE status IS NULL LIMIT 5000);
-- Repeat until no nulls remain

-- Step 3: Add CHECK NOT VALID (instant — only validates future writes)
ALTER TABLE orders
  ADD CONSTRAINT orders_status_not_null
  CHECK (status IS NOT NULL) NOT VALID;

-- Step 4: VALIDATE CONSTRAINT during low-traffic window
-- Uses ShareUpdateExclusiveLock (allows reads, blocks DDL only)
ALTER TABLE orders VALIDATE CONSTRAINT orders_status_not_null;

-- Step 5: PostgreSQL 12+ — SET NOT NULL now skips the table scan
-- because it finds the validated check constraint as proof
ALTER TABLE orders ALTER COLUMN status SET NOT NULL;

-- Step 6: Drop the check constraint (redundant now)
ALTER TABLE orders DROP CONSTRAINT orders_status_not_null;

Pre-Migration Deployment Checklist

• ☐ Test migration against a production-size database snapshot (not just dev data)
• ☐ Time the migration on staging — estimate production duration
• ☐ Write and test the rollback script
• ☐ Take a database snapshot immediately before deploying
• ☐ Ensure new app code is backward-compatible with both old and new schema
• ☐ Confirm migration runs in a transaction (for rollback on failure)
• ☐ Check for long-running queries that would block DDL operations
• ☐ Set lock_timeout and statement_timeout to fail fast instead of queue building

Migration Deployment Order (CI/CD)

# Correct order for zero-downtime deployment:
# 1. Deploy migration (expand phase — add new columns/tables)
# 2. Deploy new app code (reads both old and new schema)
# 3. Run backfill jobs (fill new columns)
# 4. Validate migration
# 5. Deploy cleanup (contract phase — drop old columns)

# Flyway / Liquibase:
# - V1__add_display_name.sql  (expand)
# - V2__backfill_display_name.sql
# - V3__drop_full_name.sql    (contract — separate deployment)

# Set timeouts to avoid lock queues
SET lock_timeout = '5s';    -- fail if lock not acquired in 5s
SET statement_timeout = '30s';  -- fail long-running migrations early

Common Migration Mistakes

• Renaming columns directly — Breaks all running app code; use expand-contract instead
• Dropping columns in the same deploy as removing app code — Race condition; always contract after all app instances are updated
• Regular CREATE INDEX on large tables — Takes exclusive lock; always use CONCURRENTLY in production
• Running migrations in the same transaction as app startup — Long migrations block app startup and cause health check failures
• Testing only on small datasets — Index creation and full-table scans are 100x slower on production data
• No rollback plan — Always write and test the down migration before deploying