Database Migration Strategies for Production

Database Migration Strategies for Production

Database migrations in production are one of the riskiest operations in software development. A bad migration can cause downtime, data loss, or corrupted state. The key is making migrations backward-compatible and reversible.

The Golden Rule

Never deploy a migration that breaks the currently running code. Your old code and new code must both work with the database during the migration window.

Zero-Downtime Migration Patterns

Adding a Column

Safe — existing code ignores new columns:

-- Migration 1: Add column with default
ALTER TABLE users ADD COLUMN display_name TEXT DEFAULT '';

Removing a Column (Three-Phase)

You cannot just drop a column — running code may reference it.

Phase 1: Deploy code that stops reading/writing the column. Phase 2: Verify no queries reference the column (check logs, monitoring). Phase 3: Drop the column.

-- Only after Phase 2 is confirmed
ALTER TABLE users DROP COLUMN legacy_field;

Renaming a Column (Four-Phase)

-- Phase 1: Add new column
ALTER TABLE users ADD COLUMN full_name TEXT;

-- Phase 2: Backfill data
UPDATE users SET full_name = name WHERE full_name IS NULL;

-- Phase 3: Deploy code that reads/writes both columns, prefers new one

-- Phase 4: Drop old column (after all code uses new column)
ALTER TABLE users DROP COLUMN name;

Backfilling Data

Large backfills should run in batches to avoid locking the table:

async function backfillInBatches(batchSize = 1000) {
  let lastId = "";
  let processed = 0;

  while (true) {
    const batch = await prisma.user.findMany({
      where: {
        id: { gt: lastId },
        displayName: null,
      },
      take: batchSize,
      orderBy: { id: "asc" },
    });

    if (batch.length === 0) break;

    await prisma.$transaction(
      batch.map((user) =>
        prisma.user.update({
          where: { id: user.id },
          data: { displayName: user.name },
        })
      )
    );

    lastId = batch[batch.length - 1].id;
    processed += batch.length;
    console.log(Backfilled \${processed} records);

    // Pause between batches to reduce DB load
    await new Promise((r) => setTimeout(r, 100));
  }
}

Rollback Strategies

Reversible Migrations

Always write a down migration:

// Prisma doesn't support down migrations natively,
// but you can use raw SQL or a migration tool that does

// Up
await prisma.$executeRawALTER TABLE posts ADD COLUMN summary TEXT;

// Down (keep this ready)
await prisma.$executeRawALTER TABLE posts DROP COLUMN summary;

Blue-Green Database Pattern

For major schema changes, maintain two database versions:

Create the new schema in a staging database.

Set up replication from old to new.

Switch application traffic to the new database.

If problems arise, switch back to the old database.

Migration Checklist

Before running a production migration:

[ ] Migration tested against a copy of production data.

[ ] Rollback plan documented and tested.

[ ] Migration is backward-compatible with current code.

[ ] Backfill runs in batches with progress logging.

[ ] Monitoring alerts are set up for error rates and latency.

[ ] Migration runs during low-traffic hours.

[ ] Team is available to respond to issues.

Locking Considerations

Some DDL operations lock the table. Know which ones are safe:

| Operation | Locks Table? | Safe Online? |

|-----------|-------------|-------------|

| ADD COLUMN (nullable) | No* | Yes |

| ADD COLUMN (with default) | No* | Yes (Postgres 11+) |

| DROP COLUMN | Brief lock | Usually safe |

| ADD INDEX | Yes (without CONCURRENTLY) | Use CONCURRENTLY |

| RENAME COLUMN | Brief lock | Risky — use add/copy/drop |

*Behavior varies by database engine.

Conclusion

Production migrations require discipline: always maintain backward compatibility, backfill in batches, have a rollback plan, and test against production-like data. The extra effort prevents the kind of incidents that wake people up at 3 AM.