DevOps & CloudSoftware Engineering

Database Migrations: Strategies for Zero-Downtime Deployments

Learn how to evolve your database schema without breaking production — expand-and-contract pattern, backfills, and rollback strategies.

Mbeah Essilfie

June 11, 2026 at 06:03 AM

10 min read 1135 views

Database Migrations: Strategies for Zero-Downtime Deployments

The Problem

You need to rename a column. The naive approach:

Deploy migration that renames the column
Deploy app code that uses the new name

But between steps 1 and 2, your running app references the old column name. Errors. Downtime.

The Expand-and-Contract Pattern

Phase 1: Expand — Add the new column alongside the old one.

ALTER TABLE users ADD COLUMN full_name TEXT;
-- Backfill
UPDATE users SET full_name = name;
-- Add trigger to keep both in sync
CREATE TRIGGER sync_name
  BEFORE INSERT OR UPDATE ON users
  FOR EACH ROW EXECUTE FUNCTION sync_name_columns();

Phase 2: Migrate — Update app code to use the new column.

// Before: user.name
// After: user.fullName
const user = await prisma.user.findUnique({
  where: { id },
  select: { fullName: true }, // new column
})

Phase 3: Contract — Remove the old column once all code uses the new one.

ALTER TABLE users DROP COLUMN name;

Each phase is a separate deployment. The app works correctly at every step. Zero downtime.

With Prisma Migrate

# Phase 1: Add column
prisma migrate dev --name add_full_name

# After backfill and code migration:
# Phase 3: Remove old column
prisma migrate dev --name remove_name

Common Patterns

Adding a NOT NULL column

-- 1. Add as nullable
ALTER TABLE posts ADD COLUMN reading_time INT;

-- 2. Backfill
UPDATE posts SET reading_time = CEIL(LENGTH(content) / 1000);

-- 3. Add constraint (after backfill completes)
ALTER TABLE posts ALTER COLUMN reading_time SET NOT NULL;

Adding an index without locking

-- CONCURRENTLY avoids table locks (PostgreSQL)
CREATE INDEX CONCURRENTLY idx_posts_published ON posts (published_at);

Rollback Strategies

Forward-only with feature flags — Disable the new feature, don't roll back the DB
Reverse migrations — Write explicit down migrations
Blue-green databases — Expensive but safest for critical changes