Zero-Downtime Database Migrations: Expand-Contract Pattern and Safe Schema Changes

Database migrations are the most dangerous part of a deployment. A migration that takes an exclusive table lock on a 50M-row table will block all reads and writes for minutes. A column rename deployed simultaneously with application code will break the old version — which is still running during rolling deployment.

Most teams learn this the hard way. This guide covers the patterns that keep migrations safe.

---

Why Migrations Are Dangerous

Naive migration:
1. Deploy new code that uses column `user_name`
2. Migration runs: rename `name` → `user_name`
3. Old pods (still running `name`) break immediately

Naive lock scenario:
ALTER TABLE events ADD COLUMN processed boolean DEFAULT false;
-- On a 100M row table: takes 30-120 seconds
-- Holds AccessExclusiveLock: ALL queries on table are blocked
-- Result: production outage during migration

---

The Expand-Contract Pattern

Expand-Contract (also called parallel-change) separates schema changes into safe, backward-compatible steps.

Example: Renaming a Column (name → full_name)

❌ Dangerous approach:

-- Step 1: rename column (breaks old code immediately)
ALTER TABLE users RENAME COLUMN name TO full_name;
-- Step 2: deploy new code that uses full_name
-- Old pods crash between step 1 and step 2

✅ Safe expand-contract approach:

-- Phase 1: EXPAND
-- Add new column alongside old one (non-breaking)
ALTER TABLE users ADD COLUMN full_name TEXT;

-- Copy existing data to new column
UPDATE users SET full_name = name WHERE full_name IS NULL;

-- Trigger to keep both columns in sync during transition
CREATE OR REPLACE FUNCTION sync_name_columns()
RETURNS TRIGGER AS $$
BEGIN
  IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
    IF NEW.name IS NOT NULL AND NEW.full_name IS NULL THEN
      NEW.full_name := NEW.name;
    END IF;
    IF NEW.full_name IS NOT NULL AND NEW.name IS NULL THEN
      NEW.name := NEW.full_name;
    END IF;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

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

// Phase 2: Deploy new code
// New code writes to BOTH columns; reads from full_name
// Old code still works (reads/writes name column)
async function updateUser(id: string, fullName: string) {
  await db.query(
    'UPDATE users SET full_name = $1, name = $1 WHERE id = $2',
    [fullName, id]
  );
}

-- Phase 3: CONTRACT (after all old pods are gone)
-- Remove old column, trigger, and backfill trigger
DROP TRIGGER sync_user_name_columns ON users;
DROP FUNCTION sync_name_columns();
ALTER TABLE users DROP COLUMN name;

-- Make new column NOT NULL if needed
ALTER TABLE users ALTER COLUMN full_name SET NOT NULL;

Timeline: Phase 1 (expand) → Deploy new app version → Phase 3 (contract) at next deployment.

---

Safe Index Creation

CREATE INDEX takes a ShareLock — reads still work but writes are blocked for the duration. CREATE INDEX CONCURRENTLY takes no blocking lock but takes longer.

-- ❌ Blocks writes for minutes on large tables
CREATE INDEX idx_events_user ON events(user_id);

-- ✅ Non-blocking (takes ~3× longer but no write blocking)
CREATE INDEX CONCURRENTLY idx_events_user ON events(user_id);

-- ✅ Safe: add constraint using existing index
-- First create index concurrently
CREATE UNIQUE INDEX CONCURRENTLY idx_users_email_unique ON users(email);
-- Then add constraint using the index (instant)
ALTER TABLE users ADD CONSTRAINT users_email_key UNIQUE USING INDEX idx_users_email_unique;

Important: CREATE INDEX CONCURRENTLY cannot run inside a transaction. Run it as a standalone statement.

---

NOT NULL Constraints Without Locking

Adding NOT NULL to an existing column rewrites the entire table in PostgreSQL < 15. Use check constraints instead:

-- ❌ Full table rewrite (blocks for minutes on large tables)
ALTER TABLE users ALTER COLUMN email SET NOT NULL;

-- ✅ Two-phase approach (PostgreSQL 12+):
-- Phase 1: Add NOT VALID constraint (instant — doesn't validate existing rows)
ALTER TABLE users ADD CONSTRAINT users_email_not_null
  CHECK (email IS NOT NULL) NOT VALID;

-- Phase 2: Validate (uses ShareUpdateExclusiveLock, doesn't block reads or writes)
ALTER TABLE users VALIDATE CONSTRAINT users_email_not_null;

-- Phase 3 (PostgreSQL 15+): Now you can set NOT NULL without rewrite
-- because PostgreSQL knows all rows already satisfy the constraint
ALTER TABLE users ALTER COLUMN email SET NOT NULL;
ALTER TABLE users DROP CONSTRAINT users_email_not_null;

---

Adding Columns Safely

-- ✅ Adding a nullable column: instant, no lock issues
ALTER TABLE users ADD COLUMN preferences JSONB;

-- ✅ Adding column with DEFAULT (PostgreSQL 11+): instant
-- PostgreSQL 11+ stores default in catalog without rewriting rows
ALTER TABLE users ADD COLUMN notification_count INTEGER NOT NULL DEFAULT 0;

-- ❌ Volatile defaults still cause rewrites in some versions
ALTER TABLE users ADD COLUMN created_at TIMESTAMPTZ DEFAULT NOW();
-- Use: ADD COLUMN created_at TIMESTAMPTZ, then UPDATE in batches

-- Batch backfill for large tables
DO $$
DECLARE
  batch_size INT := 10000;
  last_id UUID := '00000000-0000-0000-0000-000000000000';
  rows_updated INT;
BEGIN
  LOOP
    UPDATE users
    SET created_at = NOW()
    WHERE id > last_id
      AND created_at IS NULL
    ORDER BY id
    LIMIT batch_size
    RETURNING id INTO last_id;

    GET DIAGNOSTICS rows_updated = ROW_COUNT;
    EXIT WHEN rows_updated = 0;

    -- Brief sleep between batches to avoid lock contention
    PERFORM pg_sleep(0.01);
  END LOOP;
END $$;

---

Prisma Migration Safety Checklist

Prisma generates migrations automatically — but auto-generated migrations aren't always safe.

// schema.prisma — this change looks innocent...
model User {
  id    String @id
  name  String  // Changing to: fullName String
}

-- Prisma generates: (NOT SAFE)
ALTER TABLE "User" RENAME COLUMN "name" TO "fullName";
-- This breaks existing pods immediately

Workflow for safe Prisma migrations:

# 1. Generate migration (review before applying)
npx prisma migrate dev --create-only --name add_full_name_column

# 2. Edit the generated migration file to use expand-contract
# migrations/20260522_add_full_name_column/migration.sql

# 3. Apply in development
npx prisma migrate dev

# 4. Apply in production (with --skip-generate to avoid schema changes)
npx prisma migrate deploy

Review checklist for every migration:

Before applying any migration to production:
- [ ] Does it create an index without CONCURRENTLY?
- [ ] Does it rename a column (breaks rolling deploys)?
- [ ] Does it drop a column still referenced by old code?
- [ ] Does it add NOT NULL to a table with existing rows?
- [ ] Does it run without a transaction wrapper (required for CONCURRENTLY)?
- [ ] Estimated row count: will this lock for more than 1 second?

Run with: EXPLAIN (ANALYZE, BUFFERS) on all DML statements
Check lock duration: pg_stat_activity during migration

---

Migration in CI/CD Pipeline

# .github/workflows/deploy.yml
jobs:
  migrate:
    runs-on: ubuntu-latest
    steps:
      - name: Run migrations
        run: |
          # Apply migrations before deploying new code
          # This ensures DB schema is ready before app code uses it
          npx prisma migrate deploy
        env:
          DATABASE_URL: ${{ secrets.PRODUCTION_DATABASE_URL }}

      # Verify migration success before proceeding
      - name: Verify migration
        run: |
          npx prisma db execute --stdin <<< \
            "SELECT COUNT(*) FROM _prisma_migrations WHERE finished_at IS NULL AND rolled_back_at IS NULL"

  deploy:
    needs: migrate  # Only deploy after migration succeeds
    runs-on: ubuntu-latest
    steps:
      - name: Deploy application
        run: |
          aws ecs update-service \
            --cluster production \
            --service api \
            --force-new-deployment

---

Foreign Key Constraints

Adding foreign keys on large tables takes an AccessShareLock that blocks DROP TABLE and schema changes, but not DML. Safe to add, but validate timing:

-- Add FK with NOT VALID (instant — doesn't check existing rows)
ALTER TABLE orders
  ADD CONSTRAINT orders_user_id_fkey
  FOREIGN KEY (user_id) REFERENCES users(id)
  NOT VALID;

-- Validate later (ShareUpdateExclusiveLock — safe)
ALTER TABLE orders VALIDATE CONSTRAINT orders_user_id_fkey;

---

Working With Viprasol

We design and execute database migration strategies for production systems — expand-contract implementations, zero-downtime schema changes, migration review processes, and CI/CD integration. Database migrations are where many production incidents start; rigorous processes prevent them.

→ Talk to our team about database migration strategy.

---

See Also

• PostgreSQL Performance Optimization — EXPLAIN ANALYZE and query tuning
• Database Connection Pooling — connection management during migrations
• DevOps Best Practices — migration in CI/CD pipelines
• Database Sharding — migrations at scale
• Web Development Services — database architecture and engineering