Zero-Downtime Database Migrations — Schema Changes Without Locking Your Tables

-- View active locks and what they're blocking
SELECT
  l.pid,
  l.usename,
  l.application_name,
  l.state,
  l.query,
  l.locktype,
  l.relation::regclass,
  l.mode,
  l.granted,
  w.pid AS blocked_by_pid
FROM pg_stat_activity l
LEFT JOIN pg_stat_activity w ON l.blocking_pids @> ARRAY[w.pid]
WHERE l.query NOT ILIKE '%pg_stat_activity%'
ORDER BY l.pid;

-- Check current lock holders
SELECT
  relation::regclass,
  mode,
  granted,
  usename,
  application_name
FROM pg_locks
JOIN pg_stat_activity USING (pid)
WHERE NOT granted;

-- Lock strength reference:
-- AccessShare (SELECT) - doesn't block anything
-- RowShare (SELECT FOR SHARE) - doesn't block most operations
-- RowExclusive (UPDATE/DELETE) - blocks RowExclusive, Exclusive, AccessExclusive
-- Exclusive - blocks Exclusive and AccessExclusive
-- AccessExclusive (ALTER TABLE, DROP) - blocks everything, evil

-- Simulate lock wait to test timeout handling
BEGIN;
LOCK TABLE users IN AccessExclusive MODE;
-- Connections will block here waiting for lock

-- PHASE 1: EXPAND (add new column, non-blocking)
-- No lock needed, compatible with concurrent writes
ALTER TABLE users ADD COLUMN email_new VARCHAR(255);

-- PHASE 2: POPULATE OLD DATA (backfill in batches)
-- Never do UPDATE users SET ... without WHERE; it locks the table
-- Batch by 1000 rows, commit frequently, allow vacuum

DO $$
DECLARE
  batch_size INT := 1000;
  total_rows INT;
  processed INT := 0;
BEGIN
  SELECT COUNT(*) INTO total_rows FROM users WHERE email_new IS NULL;

  WHILE processed < total_rows LOOP
    UPDATE users
    SET email_new = email
    WHERE ctid IN (
      SELECT ctid FROM users
      WHERE email_new IS NULL
      LIMIT batch_size
    );

    processed := processed + batch_size;
    COMMIT;

    -- Log progress
    RAISE NOTICE 'Backfilled %/%', processed, total_rows;
  END LOOP;
END $$;

-- PHASE 3: DUAL-WRITE IN APPLICATION
-- Update application to write to both email and email_new
-- Old writes: INSERT ... (email, email_new) VALUES ($1, $1)
-- New writes: INSERT ... (email_new) VALUES ($1)
-- Duration: enough time for max connection lifetime + safety margin

-- PHASE 4: VERIFY DATA INTEGRITY
SELECT COUNT(DISTINCT email) AS email_count,
       COUNT(DISTINCT email_new) AS email_new_count,
       COUNT(CASE WHEN email = email_new THEN 1 END) AS matching
FROM users;

-- PHASE 5: CONTRACT (remove old column, application updated to use new)
ALTER TABLE users DROP COLUMN email;

-- PHASE 6: RENAME (optional, for clean API)
ALTER TABLE users RENAME COLUMN email_new TO email;

-- Add constraint after rename
ALTER TABLE users ADD CONSTRAINT email_unique UNIQUE (email);

-- FAST: Add nullable column (instant, no table rewrite)
ALTER TABLE products ADD COLUMN sku VARCHAR(50);

-- SLOW: Add NOT NULL column with default (rewrites entire table)
-- This will lock writers for duration of scan + write
ALTER TABLE products ADD COLUMN status VARCHAR(20) NOT NULL DEFAULT 'active';

-- BETTER: Use expand/contract for NOT NULL columns
-- Step 1: Add nullable column
ALTER TABLE products ADD COLUMN status VARCHAR(20);

-- Step 2: Backfill default values (batched, see backfill pattern)
UPDATE products SET status = 'active' WHERE status IS NULL;

-- Step 3: Add NOT NULL constraint
ALTER TABLE products ALTER COLUMN status SET NOT NULL;

-- Step 4: Add default for future inserts
ALTER TABLE products ALTER COLUMN status SET DEFAULT 'active';

-- For PostgreSQL 11+, use CONCURRENTLY
ALTER TABLE products ADD CONSTRAINT status_not_null
  CHECK (status IS NOT NULL) NOT VALID;

ALTER TABLE products VALIDATE CONSTRAINT status_not_null;

-- Fastest approach in modern Postgres: combined operation
ALTER TABLE products
  ADD COLUMN status VARCHAR(20) DEFAULT 'active' NOT NULL,
  ADD CONSTRAINT status_check CHECK (status IN ('active', 'inactive', 'archived'));

-- RISKY: Direct rename (breaks application immediately)
-- ALTER TABLE users RENAME COLUMN user_id TO id;

-- SAFE: Expand/contract pattern for renames
-- Step 1: Create new column with same definition
ALTER TABLE users ADD COLUMN id BIGINT;

-- Step 2: Populate from old column
UPDATE users SET id = user_id WHERE id IS NULL;

-- Step 3: Create unique constraint on new column
ALTER TABLE users ADD CONSTRAINT id_unique UNIQUE (id);

-- Step 4: Update application to use both columns
-- INSERT: INSERT INTO users (user_id, id) VALUES ($1, $1)
-- SELECT: SELECT id FROM users WHERE id = $1

-- Step 5: Drop old column once fully migrated
ALTER TABLE users DROP COLUMN user_id CASCADE;

-- Step 6: Rename if desired
ALTER TABLE users RENAME COLUMN id TO id_canonical;

-- Pragmatic shortcut: Use database view to map old name to new
CREATE VIEW users_legacy AS
  SELECT user_id, name, email FROM users RENAME COLUMN id TO user_id;

-- Application queries old view, no code changes needed
SELECT * FROM users_legacy WHERE user_id = $1;

-- BLOCKING: Index creation locks writers (BAD)
-- CREATE INDEX idx_email ON users (email);

-- NON-BLOCKING: Index creation runs concurrently (GOOD)
CREATE INDEX CONCURRENTLY idx_email ON users (email);

-- CONCURRENTLY limitations:
-- - Requires 2x index size in temporary space
-- - Slower than blocking index creation
-- - Requires second full table scan
-- - Cannot be part of transaction block

-- Check index build progress
SELECT
  schemaname,
  tablename,
  indexname,
  idx_scan,
  idx_tup_read,
  idx_tup_fetch
FROM pg_stat_user_indexes
WHERE indexname LIKE 'idx_%'
ORDER BY idx_scan DESC;

-- Partial indexes (faster to build, smaller)
CREATE INDEX CONCURRENTLY idx_active_users ON users (email)
  WHERE status = 'active';

-- Multi-column indexes for common queries
CREATE INDEX CONCURRENTLY idx_users_org_email ON users (org_id, email);

-- Expression index
CREATE INDEX CONCURRENTLY idx_email_lower ON users (LOWER(email));

-- Best practice: Create multiple indexes in parallel
-- Spawn separate migrations for each index
CREATE INDEX CONCURRENTLY idx_users_created_at ON users (created_at);
CREATE INDEX CONCURRENTLY idx_users_email ON users (email);
CREATE INDEX CONCURRENTLY idx_users_status ON users (status);

#!/bin/bash
# gh-ost configuration for production table migration

GHOST_HOST="prod-db-primary"
GHOST_PORT=5432
GHOST_DATABASE="myapp"
GHOST_TABLE="events"
GHOST_USER="postgres"
GHOST_PASSWORD="$PG_PASSWORD"

# Schema change: Add new column and index
GHOST_ALTER="ADD COLUMN event_type_id BIGINT, ADD INDEX idx_event_type (event_type_id)"

# Run migration in dry-run mode first
gh-ost \
  --host="$GHOST_HOST" \
  --port="$GHOST_PORT" \
  --database="$GHOST_DATABASE" \
  --table="$GHOST_TABLE" \
  --user="$GHOST_USER" \
  --password="$GHOST_PASSWORD" \
  --assume-rbr \
  --alters="$GHOST_ALTER" \
  --test-on-replica \
  --initially-drop-ghost-table \
  --initially-drop-old-table \
  --skip-foreign-key-checks \
  --max-load="Threads_running=20" \
  --critical-load="Threads_running=50" \
  --chunk-size=1000 \
  --throttle-control-replicas="replica-1,replica-2" \
  --dry-run

# Run actual migration with monitoring
gh-ost \
  --host="$GHOST_HOST" \
  --port="$GHOST_PORT" \
  --database="$GHOST_DATABASE" \
  --table="$GHOST_TABLE" \
  --user="$GHOST_USER" \
  --password="$GHOST_PASSWORD" \
  --assume-rbr \
  --alters="$GHOST_ALTER" \
  --test-on-replica \
  --initially-drop-old-table \
  --max-load="Threads_running=15" \
  --critical-load="Threads_running=40" \
  --chunk-size=1000 \
  --approve-renamed-columns \
  --execute

# Migration: Add orders.total_discount column

## Phase 1: Schema Expansion (Migration 001)
- [x] Add nullable column: ALTER TABLE orders ADD COLUMN total_discount DECIMAL(10,2)
- [x] Create index: CREATE INDEX CONCURRENTLY idx_orders_discount ON orders (total_discount)
- [x] Verify index creation succeeded
- [x] Code review approval
- [x] Test on staging mirror
- [x] Deploy to production during low-traffic window
- [x] Monitor index build completion

## Phase 2: Application Deployment (Version 2.5)
- [x] Update Order model to include total_discount field
- [x] Implement dual-write logic: write to both amount and total_discount
- [x] Update: INSERT/UPDATE operations write to both columns
- [x] SELECT queries read from total_discount with fallback to amount
- [x] Unit tests verify dual-write logic
- [x] E2E tests verify data consistency
- [x] Deploy to canary (5% traffic)
- [x] Monitor metrics for 30 minutes
- [x] Deploy to 50% traffic
- [x] Deploy to 100% traffic
- [x] Run schema validation query 10x: SELECT COUNT(DISTINCT total_discount, amount) FROM orders

## Phase 3: Data Validation (After 48 hours)
- [x] Run integrity check: SELECT COUNT(CASE WHEN total_discount != amount THEN 1 END) FROM orders
- [x] Audit any mismatches
- [x] Backfill missing data for old orders
- [x] Verify constraint eligibility: ALTER TABLE orders ALTER COLUMN total_discount SET NOT NULL

## Phase 4: Constraint Addition (Migration 002)
- [x] Add NOT NULL constraint: ALTER TABLE orders ALTER COLUMN total_discount SET NOT NULL
- [x] Deploy to production
- [x] Verify constraint is active: SELECT constraint_name FROM information_schema.table_constraints WHERE table_name = 'orders'

## Phase 5: Code Cleanup (Version 2.6+)
- [x] Remove dual-write logic
- [x] Remove fallback read logic (amount column)
- [x] Clean up column rename: DROP COLUMN amount
- [x] Deploy

## Rollback Plan
If Phase 1 fails: Drop new column with DROP COLUMN total_discount
If Phase 2 fails: Stop dual-write deployment, revert to Version 2.4
If Phase 3 fails: Manual data remediation, extend timeline

-- SAFE: Additive changes (always reversible)
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
-- Rollback: ALTER TABLE users DROP COLUMN phone;

-- SAFE: Index creation (instant removal)
CREATE INDEX CONCURRENTLY idx_email ON users (email);
-- Rollback: DROP INDEX CONCURRENTLY idx_email;

-- RISKY: Direct column drops without expand/contract
-- Rollback: restore from backup, losing recent data

-- SAFE: Constraints with validation
ALTER TABLE users ADD CONSTRAINT email_format
  CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,}$')
  NOT VALID;

-- NOT VALID allows existing violating rows
-- VALIDATE only locks briefly
ALTER TABLE users VALIDATE CONSTRAINT email_format;
-- Rollback: ALTER TABLE users DROP CONSTRAINT email_format;

-- Pre-migration validation script
DO $$
BEGIN
  -- Check prerequisite conditions
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.tables
    WHERE table_name = 'users'
  ) THEN
    RAISE EXCEPTION 'Table users does not exist';
  END IF;

  -- Check disk space
  IF (SELECT pg_database_size(current_database()) / 1024 / 1024 / 1024) > 500 THEN
    RAISE EXCEPTION 'Less than 100GB free space required';
  END IF;

  -- Check replica lag (if applicable)
  RAISE NOTICE 'Prerequisites met, safe to proceed';
END $$;