Database Migrations Without Downtime in Production

// BREAKING: Rename column in one step — old app crashes immediately
// ❌ ALTER TABLE users RENAME COLUMN name TO full_name;
 
// NON-BREAKING: Expand-contract over 3 deployments
// Step 1 (expand): Add new column, dual-write
// Step 2 (migrate): Backfill data, switch reads
// Step 3 (contract): Remove old column
 
interface MigrationStep {
  phase: "expand" | "migrate" | "contract";
  sql: string;
  requiresDeployment: boolean;
  backwardCompatible: boolean;
}
 
const renameColumnMigration: MigrationStep[] = [
  {
    phase: "expand",
    sql: `
      ALTER TABLE users ADD COLUMN full_name VARCHAR(255);
 
      -- Trigger for dual-write: old writes update new column too
      CREATE OR REPLACE FUNCTION sync_name_to_full_name()
      RETURNS TRIGGER AS $$
      BEGIN
        NEW.full_name = COALESCE(NEW.full_name, NEW.name);
        RETURN NEW;
      END;
      $$ LANGUAGE plpgsql;
 
      CREATE TRIGGER trg_sync_name
      BEFORE INSERT OR UPDATE ON users
      FOR EACH ROW EXECUTE FUNCTION sync_name_to_full_name();
    `,
    requiresDeployment: false,
    backwardCompatible: true,
  },
  {
    phase: "migrate",
    sql: `
      -- Backfill existing rows in batches
      UPDATE users SET full_name = name
      WHERE full_name IS NULL
      LIMIT 10000;
      -- Run repeatedly until no rows remain
    `,
    requiresDeployment: true, // Deploy app reading from full_name
    backwardCompatible: true,
  },
  {
    phase: "contract",
    sql: `
      DROP TRIGGER trg_sync_name ON users;
      DROP FUNCTION sync_name_to_full_name();
      ALTER TABLE users DROP COLUMN name;
    `,
    requiresDeployment: false,
    backwardCompatible: false, // Old app must no longer be running
  },
];

-- ❌ Locks table while rewriting all rows (PostgreSQL < 11)
ALTER TABLE orders ADD COLUMN priority INTEGER NOT NULL DEFAULT 0;
 
-- ✅ Two-step: add nullable, then set default separately
ALTER TABLE orders ADD COLUMN priority INTEGER;
ALTER TABLE orders ALTER COLUMN priority SET DEFAULT 0;
 
-- Backfill in batches (no table lock)
UPDATE orders SET priority = 0
WHERE priority IS NULL AND id IN (
  SELECT id FROM orders WHERE priority IS NULL LIMIT 10000
);
 
-- After backfill complete, add NOT NULL constraint
ALTER TABLE orders ALTER COLUMN priority SET NOT NULL;

// Batched backfill to avoid long-running transactions
async function backfillColumn(
  db: Database,
  table: string,
  column: string,
  defaultValue: unknown,
  batchSize: number = 10_000
): Promise<{ totalUpdated: number }> {
  let totalUpdated = 0;
 
  while (true) {
    const result = await db.query(
      `UPDATE ${table} SET ${column} = $1
       WHERE ${column} IS NULL
       AND id IN (
         SELECT id FROM ${table}
         WHERE ${column} IS NULL
         LIMIT $2
       )`,
      [defaultValue, batchSize]
    );
 
    totalUpdated += result.rowCount;
 
    if (result.rowCount === 0) break;
 
    // Small delay between batches to reduce load
    await new Promise((r) => setTimeout(r, 100));
  }
 
  return { totalUpdated };
}

-- ❌ Locks table for the duration of index creation
CREATE INDEX idx_orders_status ON orders (status);
 
-- ✅ Builds index without blocking writes
CREATE INDEX CONCURRENTLY idx_orders_status ON orders (status);

// Migration runner that validates index creation
async function createIndexSafely(
  db: Database,
  indexName: string,
  table: string,
  columns: string[]
): Promise<void> {
  const columnList = columns.join(", ");
 
  await db.query(
    `CREATE INDEX CONCURRENTLY IF NOT EXISTS ${indexName}
     ON ${table} (${columnList})`
  );
 
  // Verify index is valid (CONCURRENTLY can produce invalid indexes)
  const result = await db.query(
    `SELECT indisvalid FROM pg_index
     WHERE indexrelid = $1::regclass`,
    [indexName]
  );
 
  if (!result.rows[0]?.indisvalid) {
    // Drop invalid index and retry
    await db.query(`DROP INDEX IF EXISTS ${indexName}`);
    throw new Error(
      `Index ${indexName} was created in invalid state — dropped and needs retry`
    );
  }
}

interface MigrationSafetyCheck {
  name: string;
  check: (sql: string) => { safe: boolean; reason?: string };
}
 
const safetyChecks: MigrationSafetyCheck[] = [
  {
    name: "no-drop-column-without-feature-flag",
    check: (sql) => {
      if (/DROP\s+COLUMN/i.test(sql)) {
        return {
          safe: false,
          reason: "DROP COLUMN requires expand-contract pattern. " +
                  "Use feature flag to stop reading the column first.",
        };
      }
      return { safe: true };
    },
  },
  {
    name: "no-rename-column",
    check: (sql) => {
      if (/RENAME\s+COLUMN/i.test(sql)) {
        return {
          safe: false,
          reason: "RENAME COLUMN breaks running application. " +
                  "Use add-copy-drop pattern instead.",
        };
      }
      return { safe: true };
    },
  },
  {
    name: "no-not-null-without-default",
    check: (sql) => {
      if (/SET\s+NOT\s+NULL/i.test(sql) && !/SET\s+DEFAULT/i.test(sql)) {
        return {
          safe: false,
          reason: "SET NOT NULL on existing column requires backfill first. " +
                  "Ensure all rows have values before adding constraint.",
        };
      }
      return { safe: true };
    },
  },
  {
    name: "index-concurrently",
    check: (sql) => {
      if (/CREATE\s+INDEX(?!\s+CONCURRENTLY)/i.test(sql)) {
        return {
          safe: false,
          reason: "CREATE INDEX without CONCURRENTLY locks the table. " +
                  "Use CREATE INDEX CONCURRENTLY.",
        };
      }
      return { safe: true };
    },
  },
];
 
function validateMigration(sql: string): {
  safe: boolean;
  violations: string[];
} {
  const violations = safetyChecks
    .map((check) => check.check(sql))
    .filter((result) => !result.safe)
    .map((result) => result.reason!);
 
  return { safe: violations.length === 0, violations };
}

interface MigrationWithRollback {
  version: string;
  description: string;
  up: string;
  down: string | null; // null = irreversible
  preConditions: string[];
  estimatedDuration: string;
}
 
const migration: MigrationWithRollback = {
  version: "20251111_001",
  description: "Add priority column to orders",
  up: `
    ALTER TABLE orders ADD COLUMN priority INTEGER;
    ALTER TABLE orders ALTER COLUMN priority SET DEFAULT 0;
  `,
  down: `
    ALTER TABLE orders DROP COLUMN IF EXISTS priority;
  `,
  preConditions: [
    "No application code references 'priority' column yet",
    "Table has fewer than 50M rows (for safe ALTER)",
  ],
  estimatedDuration: "< 1 second (nullable column add)",
};