Database Migration Strategies for Zero-Downtime Deployments

-- Example: renaming a column from 'name' to 'full_name'
 
-- ❌ Dangerous: breaks all running application instances instantly
ALTER TABLE users RENAME COLUMN name TO full_name;
 
-- ✅ Phase 1: EXPAND — add new column alongside old one
ALTER TABLE users ADD COLUMN full_name VARCHAR(255);
 
-- Copy existing data
UPDATE users SET full_name = name WHERE full_name IS NULL;
 
-- Add trigger to keep columns in sync during transition
CREATE OR REPLACE FUNCTION sync_user_name()
RETURNS TRIGGER AS $$
BEGIN
  IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
    IF NEW.full_name IS NULL AND NEW.name IS NOT NULL THEN
      NEW.full_name := NEW.name;
    ELSIF NEW.name IS NULL AND NEW.full_name IS NOT NULL THEN
      NEW.name := NEW.full_name;
    END IF;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER user_name_sync
  BEFORE INSERT OR UPDATE ON users
  FOR EACH ROW EXECUTE FUNCTION sync_user_name();

-- ✅ Phase 2: MIGRATE — deploy application code 
-- that writes to both columns and reads from full_name
-- Wait until all instances are running the new code
 
-- ✅ Phase 3: CONTRACT — remove old column and trigger
-- Only after all application instances use full_name
DROP TRIGGER user_name_sync ON users;
DROP FUNCTION sync_user_name();
ALTER TABLE users DROP COLUMN name;

-- PostgreSQL safe operations (no table lock / instant):
ALTER TABLE users ADD COLUMN bio TEXT;
-- Adding a nullable column without default: instant ✅
 
ALTER TABLE users ADD COLUMN status VARCHAR(20) DEFAULT 'active';
-- Adding column with DEFAULT: instant in PG 11+ ✅
-- (Older PostgreSQL rewrites the entire table — dangerous)
 
ALTER TABLE users ALTER COLUMN bio TYPE TEXT;
-- Changing to a compatible type: usually safe ✅
 
-- PostgreSQL dangerous operations (table lock / rewrite):
 
-- ❌ Adding NOT NULL to existing column with default
ALTER TABLE users ALTER COLUMN bio SET NOT NULL;
-- Scans entire table to verify — locks table on older PG
 
-- ✅ Safe alternative: use a CHECK constraint
ALTER TABLE users ADD CONSTRAINT bio_not_null
  CHECK (bio IS NOT NULL) NOT VALID;
-- NOT VALID means don't check existing rows yet
 
-- Then validate in a separate step (concurrent-safe):
ALTER TABLE users VALIDATE CONSTRAINT bio_not_null;
-- Validates without blocking writes

// Migration helper that enforces safe patterns
class SafeMigration {
  // Check if a migration is safe before running
  async analyzeMigration(sql: string): Promise<MigrationAnalysis> {
    const dangerous = [
      { pattern: /ALTER TABLE.*RENAME COLUMN/i, 
        risk: 'Breaks existing queries' },
      { pattern: /ALTER TABLE.*DROP COLUMN/i, 
        risk: 'Breaks existing queries' },
      { pattern: /ALTER TABLE.*ALTER COLUMN.*TYPE/i, 
        risk: 'May rewrite table' },
      { pattern: /ALTER TABLE.*SET NOT NULL/i, 
        risk: 'Full table scan with lock' },
      { pattern: /CREATE INDEX(?!.*CONCURRENTLY)/i, 
        risk: 'Blocks writes during build' },
    ];
 
    const risks = dangerous
      .filter(d => d.pattern.test(sql))
      .map(d => d.risk);
 
    return {
      safe: risks.length === 0,
      risks,
      sql,
    };
  }
}

-- ❌ Standard index creation: blocks writes
CREATE INDEX idx_users_email ON users (email);
-- On a 100M row table, this blocks inserts/updates 
-- for minutes
 
-- ✅ Concurrent index creation: doesn't block writes
CREATE INDEX CONCURRENTLY idx_users_email ON users (email);
-- Takes longer but allows normal operations
 
-- ⚠️ If concurrent index creation fails, it leaves 
-- an INVALID index. Check and clean up:
SELECT indexrelid::regclass, indisvalid 
FROM pg_index 
WHERE NOT indisvalid;
 
-- Drop invalid index and retry:
DROP INDEX CONCURRENTLY IF EXISTS idx_users_email;
CREATE INDEX CONCURRENTLY idx_users_email ON users (email);

-- Unique constraints also need careful handling
 
-- ❌ Adding unique constraint directly locks table
ALTER TABLE users ADD CONSTRAINT users_email_unique 
  UNIQUE (email);
 
-- ✅ Create unique index concurrently, then add constraint
CREATE UNIQUE INDEX CONCURRENTLY idx_users_email_unique 
  ON users (email);
 
-- Then add constraint using the existing index (instant):
ALTER TABLE users ADD CONSTRAINT users_email_unique 
  UNIQUE USING INDEX idx_users_email_unique;

// Batch data migration with progress tracking
async function migrateInBatches(
  pool: Pool,
  config: {
    table: string;
    batchSize: number;
    transform: string;
    whereClause: string;
  }
): Promise<void> {
  let totalMigrated = 0;
  let hasMore = true;
 
  while (hasMore) {
    const result = await pool.query(`
      WITH batch AS (
        SELECT id FROM ${config.table}
        WHERE ${config.whereClause}
        ORDER BY id
        LIMIT $1
        FOR UPDATE SKIP LOCKED
      )
      UPDATE ${config.table} t
      SET ${config.transform}
      FROM batch b
      WHERE t.id = b.id
      RETURNING t.id
    `, [config.batchSize]);
 
    totalMigrated += result.rowCount ?? 0;
    hasMore = (result.rowCount ?? 0) === config.batchSize;
 
    console.log(`Migrated ${totalMigrated} rows`);
 
    // Brief pause to let other queries through
    await new Promise(resolve => setTimeout(resolve, 100));
  }
 
  console.log(`Migration complete: ${totalMigrated} total rows`);
}
 
// Usage: populate the new full_name column
await migrateInBatches(pool, {
  table: 'users',
  batchSize: 5000,
  transform: "full_name = name",
  whereClause: "full_name IS NULL",
});

-- ❌ Adding foreign key directly: validates all rows with lock
ALTER TABLE orders 
  ADD CONSTRAINT fk_orders_user 
  FOREIGN KEY (user_id) REFERENCES users (id);
 
-- ✅ Add constraint without validation, then validate separately
ALTER TABLE orders 
  ADD CONSTRAINT fk_orders_user 
  FOREIGN KEY (user_id) REFERENCES users (id) 
  NOT VALID;
-- Instant: only enforces constraint on new/updated rows
 
-- Validate existing data without blocking writes:
ALTER TABLE orders VALIDATE CONSTRAINT fk_orders_user;
-- Scans table but allows concurrent writes

// Migration versioning that supports zero-downtime deploys
interface MigrationPhase {
  phase: 'expand' | 'migrate-data' | 'contract';
  sql: string;
  requiresAppVersion?: string;
}
 
const renameMigration: MigrationPhase[] = [
  {
    // Deploy this BEFORE new app code
    phase: 'expand',
    sql: `
      ALTER TABLE users ADD COLUMN full_name VARCHAR(255);
      -- Add sync trigger here
    `,
  },
  {
    // Deploy app v2 (reads full_name, writes both)
    // Wait for ALL instances to be on v2
    phase: 'migrate-data',
    requiresAppVersion: '2.0.0',
    sql: `
      -- Batch update existing rows
      -- This runs as a background job, not a blocking migration
    `,
  },
  {
    // After ALL instances on v2 AND data migration complete
    phase: 'contract',
    requiresAppVersion: '2.0.0',
    sql: `
      DROP TRIGGER user_name_sync ON users;
      ALTER TABLE users DROP COLUMN name;
    `,
  },
];