Zero-Downtime Database Migrations

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

Quick answer. Zero-downtime migrations rely on the expand-contract pattern: add new schema, backfill and dual-write, switch reads, then drop the old column once no running code uses it. This avoids both renames that break old pods during rolling deploys and ALTERs that take AccessExclusiveLocks blocking all queries on large tables.

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;

---

What Viprasol Offers

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.

---

More on This Topic

• 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