Zero-Downtime Database Migration Strategies

Introduction

Database migrations seem simple until you’re changing a live schema with millions of rows and little tolerance for downtime. A poorly planned change can lock a table, break an older application version during rollout, or leave data only partly migrated.

This guide covers backward-compatible changes, expand-contract deployments, rollback strategies, and tools for shipping schema updates safely. It also examines production failures, migration windows, and how to verify each phase before removing old structures.

Understanding the Problem

When you’re working with a live database, every schema change carries risk. Adding a column seems innocuous until you realize it locks the table. Renaming a column sounds safe until the old code still running on some server tries to write to the old name. The problem isn’t the change itself—it’s the multiple versions of your application that exist during a deployment window.

Modern deployments use rolling updates or blue-green deployments. During these deployments, version N and version N+1 of your application are both running simultaneously. Your database migration must be compatible with both versions. This constraint is the foundation of all zero-downtime migration strategies.

The Expand-Contract Pattern

The expand-contract pattern, sometimes called the “two-phase migration,” works well for breaking schema changes. The core idea: never make a breaking change in a single step. Break it into three phases instead.

Phase 1: Expand

In the expand phase, you add new structure without removing the old. Suppose you want to rename a user_name column to display_name. Instead of renaming directly, you add the new column alongside the old one.

ALTER TABLE users ADD COLUMN display_name VARCHAR(255);

Your application code now writes to both columns. Version N writes to user_name, and version N+1 writes to display_name. A background job backfills the new column with data from the old column.

Phase 2: Migrate

Once the expand phase is complete and all historical data has been backfilled, you update the application code to stop writing to the old column. The old column becomes read-only.

The migrate phase is where the actual transition happens. During expand, your application writes to both columns simultaneously — version N uses the old column and version N+1 uses the new one. In migrate, you deploy code that stops writing to the old column entirely. You need to audit every write path. With an ORM, you might only change a model definition. With raw SQL scattered across a codebase, you need a systematic search-and-replace, preferably verified by a static analysis check that catches any remaining references to the old column name.

After deploying the migrate-phase code, the old column is still there but nothing writes to it. It stays readable in case any version of the application still reads it — which should be none, assuming your deployment fully rolled out. You can verify this by checking application logs for writes to the old column, or by adding a temporary counter that increments on every read from the old column. This gives you a concrete signal before you proceed to contract.

Here’s what catches people: reads can linger even after writes stop. If your application falls back to the old column when the new one is NULL, removing the old column while that code path still exists causes incorrect behavior. The contract phase should only begin once writes have migrated and all read paths are reading from the new column. A shadow-read validation — comparing values from old and new columns for a sample of rows — catches these edge cases before they become production incidents.

-- Application now writes to display_name only
-- Old user_name column is kept for backward compatibility

Phase 3: Contract

After you have verified that no code is writing to the old column and all reads have been migrated, you can safely remove the old structure.

ALTER TABLE users DROP COLUMN user_name;

This three-phase approach ensures that at every point during the migration, some version of your application can function correctly.

flowchart LR
    subgraph Phase1["Phase 1: Expand"]
        A1[Add new column<br/>display_name] --> B1[Deploy vN+1 writes<br/>to both columns]
        B1 --> C1[Background job<br/>backfills new column]
    end

    subgraph Phase2["Phase 2: Migrate"]
        C1 --> D1[Stop writing to<br/>old column]
        D1 --> E1[Old column<br/>becomes read-only]
    end

    subgraph Phase3["Phase 3: Contract"]
        E1 --> F1[Verify no writes<br/>to old column]
        F1 --> G1[DROP old column<br/>user_name]
    end

    Phase1 --> Phase2 --> Phase3

The expand-contract pattern is a specific application of a broader principle: backward-compatible migrations. A migration is backward-compatible when it works correctly whether the application before or after the migration is running.

Adding Columns Without Locking

PostgreSQL’s MVCC (Multi-Version Concurrency Control) means that most DDL changes no longer require exclusive locks like they used to. However, some operations still cause issues.

Before PostgreSQL 11, adding a column with a default could rewrite the table. PostgreSQL 11 introduced a metadata-only optimization for non-volatile defaults: PostgreSQL stores the missing value in the catalog and supplies it when older rows are read. A volatile default, such as clock_timestamp(), can still require evaluating a value for every row and rewriting the table.

-- Safe in PostgreSQL 11+
ALTER TABLE users ADD COLUMN is_verified BOOLEAN DEFAULT FALSE;

For older versions, use a separate step:

-- Step 1: Add column without default
ALTER TABLE users ADD COLUMN is_verified BOOLEAN;

-- Step 2: Add default separately
ALTER TABLE users ALTER COLUMN is_verified SET DEFAULT FALSE;

-- Step 3: Backfill existing rows
UPDATE users SET is_verified = FALSE WHERE is_verified IS NULL;

Index Creation

A regular CREATE INDEX blocks writes to the table while it builds, which can cause downtime on a busy, large table. Use CREATE INDEX CONCURRENTLY to allow writes during the build; it takes longer and cannot run inside a transaction.

-- Creates index without blocking writes
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

Note that CREATE INDEX CONCURRENTLY cannot run inside a transaction. Your migration tool must support transactional migrations that can also run non-transactional commands.

When to Use / When Not to Use

Use expand-contract migrations when you need to rename or remove columns that existing code still references, change column types requiring data transformation, split or merge tables, or make any schema change that breaks the current application version.

Use backward-compatible migrations when adding columns with safe defaults (PostgreSQL 11+), adding indexes concurrently, adding constraints as NOT VALID before validating, or working with rolling deployments where old and new app versions coexist.

Do not use expand-contract when the change is purely additive (new table, new column that nothing touches — just deploy it), when you have a maintenance window for a coordinated full migration, or when the risk of multi-version confusion outweighs the risk of a coordinated deployment.

Never do the following in production: single-step column renames or type changes on large tables; non-concurrent index creation on tables with active writes; migrations inside the same transaction as application code deployment.

Rollback Strategies

Manual migration management does not scale. As your team grows and your database schema becomes more complex, you need tools that track migration history, enforce ordering, and help with rollback.

Flyway

Flyway is a mature, open-source migration tool that works with virtually any database. Migrations are stored as versioned SQL scripts.

V1__Create_initial_schema.sql
V2__Add_users_table.sql
V3__Add_email_index.sql

Flyway tracks which migrations have been applied and applies pending ones on startup. It supports undo migrations (in paid version) and baseline migrations for existing databases.

Configuration:

flyway.url=jdbc:postgresql://localhost:5432/mydb
flyway.user=flyway
flyway.password=secret
flyway.locations=filesystem:./migrations

Liquibase

Liquibase uses a changelog file (XML, JSON, YAML, or SQL) to define migrations. This provides better cross-database compatibility since Liquibase handles the dialect translation.

<changeSet id="1" author="alex">
  <addColumn tableName="users">
    <column name="display_name" type="VARCHAR(255)"/>
  </addColumn>
</changeSet>

Liquibase tracks changes via a changelog table, allowing it to apply only new changes. It also supports rollbacks via rollback SQL or rollback scripts.

Prisma Migrate

If you are using Prisma as your ORM, Prisma Migrate integrates directly with your schema definition. You define your schema in schema.prisma, and Prisma generates and tracks migrations.

model User {
  id          String   @id @default(cuid())
  email       String   @unique
  displayName String?
  createdAt   DateTime @default(now())
}

Prisma Migrate generates SQL migrations based on your schema changes. It maintains a migration history table and supports development, staging, and production environments.

The main advantage is tight integration with your ORM layer. The downside is vendor lock-in—if you switch away from Prisma, you need to migrate your migration system too.

Adding Columns Without Locking Tables

This deserves its own section because it is such a common operation and a common source of production incidents.

The Problem with DEFAULT values

Before PostgreSQL 11, adding a column with a DEFAULT value caused a table rewrite. For a table with 100 million rows, this could take hours and lock the table the entire time.

Even in PostgreSQL 11+, certain operations still cause table locks:

• Changing a column’s type
• Changing a column’s NOT NULL constraint
• Adding a CHECK constraint

Techniques for Safe Column Addition

1. Add nullable columns first: Columns that allow NULL never require a table rewrite.

2. Add columns with non-blocking defaults: PostgreSQL 11+ handles volatile defaults efficiently for column addition.

3. Use concurrent index creation: As mentioned earlier, always use CONCURRENTLY for indexes on large tables.

4. Avoid constraints initially: Add CHECK constraints as NOT VALID, then validate later:

-- Add constraint without scanning
ALTER TABLE orders ADD CONSTRAINT positive_total CHECK (total >= 0) NOT VALID;

-- Validate without locking
ALTER TABLE orders VALIDATE CONSTRAINT positive_total;

5. Break up large alterations: For very large tables, consider a multi-step approach where you add a trigger to maintain the new column rather than backfilling all at once.

Common Pitfalls / Anti-Patterns

Teams that have run hundreds of migrations still fall for the same traps. The difference between a smooth migration and a 3am incident is usually one of these:

Running migrations during peak traffic. Zero-downtime does not mean risk-free. Migrations consume database resources, and a backfill running during peak hours saturates connection pools, spikes latencies, and can create lock contention that affects user queries. Check your monitoring dashboards before scheduling — and schedule for low-traffic windows even when you think you can get away without it.

Forgetting to set statement_timeout. A runaway UPDATE on a large table can hold locks for hours and cascade into application timeouts. Set statement_timeout per-session or per-transaction before running any batch job. Use SET LOCAL statement_timeout = ‘30s’ inside the transaction, and tune autovacuum with ALTER TABLE … SET (autovacuum_vacuum_threshold = 50) for the migration duration.

Not testing rollback procedures. Expand-contract makes application rollback straightforward — route traffic back to the old version. The problem is nobody actually tests it until it is 2am and the rollback is not working. If your rollback relies on a feature flag or traffic shifting mechanism, verify it under load in staging first. Skip this step and you are gambling.

Mixing DDL and DML in the same transaction. PostgreSQL does not allow all DDL inside a transaction — CREATE INDEX CONCURRENTLY, REINDEX, and VACUUM FULL all break this. Mixing them with DML in a single transaction aborts the entire thing. Keep schema changes and data migrations in separate steps, and verify your migration tool handles non-transactional commands before you need them.

Skipping the NOT VALID step for constraints. Adding a CHECK or FOREIGN KEY constraint without NOT VALID makes PostgreSQL scan the entire table and acquire a ShareLock that blocks writes. Add constraints as NOT VALID first, then validate separately. The validation step is cheap and non-blocking. The initial scan is not.

Not using CONCURRENTLY for index creation. On tables with active writes, CREATE INDEX blocks writes for the duration of the build. On large tables, that can mean minutes of blocked writes. CREATE INDEX CONCURRENTLY builds the index in multiple phases with intermediate commits, so writes continue uninterrupted. Always use it in production — and double-check that your migration tool does not wrap it in a transaction that defeats this.

Best Practices

After working on this for years, a few practices consistently make migrations safer.

Test on production-size data: A migration that takes 2 seconds on your laptop might take 2 hours on production. Test against realistic data volumes.

Monitor table statistics: Run ANALYZE after major migrations to ensure the query planner has accurate information.

Have a rollback plan: Not for the migration itself, but for the deployment. Can you route traffic back to the old application version if something goes wrong?

Schedule during low traffic: Even with all precautions, migrations are safer during low-traffic windows.

Keep migrations small and focused: Large migrations are harder to review and harder to rollback. One change per migration makes debugging easier.

Production Failure Scenarios

Trade-Off Table: Migration Tools

Capacity Estimation: Migration Window Sizing

Migration window sizing determines how long your migration will take and how much downtime you actually need.

Batch size and migration time formula:

rows_per_batch = batch_size_bytes / avg_row_width_bytes
total_batches = total_rows / rows_per_batch
migration_time_minutes = total_batches × batch_interval_seconds / 60

For a 100M row table adding a NOT NULL column with a default value:

• Average row width: 200 bytes
• Batch size: 50MB
• Rows per batch: 50MB / 200 = 250,000 rows
• Total batches: 100M / 250K = 400 batches
• Batch interval (including index rebuild): 5 seconds
• Total migration time: 400 × 5 / 60 = ~33 minutes

Cutover window:

Backfill duration is not the same as downtime. In an expand-contract migration, the backfill runs while the application remains available; measure cutover time separately by timing the schema lock and switchover steps in a production-sized rehearsal. A shadow-table migration also needs a final synchronization and cutover, whose duration depends on write volume, validation, and the database’s locking behavior. Do not estimate downtime by multiplying the number of backfill batches by the batch interval.

Validation time formula:

validation_time = table_row_count × (1 / validation_rows_per_second)

Validating a migrated table against the original (row count, checksum) at 100K rows/second for a 100M row table: ~17 minutes. Plan for validation time equal to or greater than the migration itself.

Real-World Case Study: Stripe’s Zero-Downtime Migration Toolkit

Stripe runs one of the largest PostgreSQL deployments in the financial technology space, processing billions of dollars in transactions annually. Their migration toolkit is open-source and represents years of production experience.

The problem Stripe solved: At their scale, a naive ALTER TABLE statement blocking for even 30 seconds would result in thousands of failed payments. They needed a migration system that worked across multiple application versions simultaneously, with the ability to roll back instantly if something went wrong.

Key practices from Stripe’s approach:

• Shadow tables: Create an identical copy of the table to migrate. Applications write to both old and new tables during the expand phase.
• Backfill with minimal locks: Use batched updates with row-level locking rather than table-level locks. Stripe’s tooling uses WHERE id > last_processed_id ORDER BY id LIMIT batch_size pattern.
• Validation before switchover: Compare row counts, checksums, and a random sample of rows between shadow and original tables before promoting the shadow.
• Backward-compatible application code: Every migration is paired with an application deployment that can read both old and new schemas. Old code never breaks—new columns are simply ignored.
• Instant rollback: If validation fails, drop the shadow table and roll back application code. No schema changes to undo.

The lesson: Stripe’s toolkit proves that “zero-downtime” is not marketing—it’s a specific set of patterns (shadow table, dual write, validation) that require upfront investment but pay off at scale.

Observability Checklist

Migrations need the same monitoring treatment as any production infrastructure change. Without it, you are flying blind — you cannot tell if a migration is progressing, stalled, or quietly breaking something unrelated.

Track migration duration end to end. Log start time, end time, and total duration for every migration step in a migration audit table with the script version. Compare actuals against your capacity planning estimates. A migration running 10x longer than expected is a signal — usually lock contention, a missing index, or batch sizing that turned out to be wrong for your data shape.

Monitor backfill progress at the row level. Batched backfills should emit per-batch metrics: rows processed, rows remaining, processing rate, and elapsed time. Plot these during execution. A flatline in rows processed while time keeps ticking means the batch is stuck. A declining rate over time means lock contention is worsening.

Set up lock contention alerts. Monitor pg_stat_activity.blocking_pids and pg_locks.mode. Queries blocked for more than 30 seconds usually mean a migration is holding a lock that is blocking application traffic. The alert should fire before the connection pool saturates — not after. During migration windows, also watch pg_stat_activity.wait_event_type and pg_stat_activity.wait_event.

Watch table size during the expand phase. The expand phase temporarily doubles storage — old and new columns or tables coexist. Monitor pg_relation_size, pg_total_relation_size, and disk usage. If a table grows beyond available space mid-migration, you get a partial-migration failure that is messy to clean up. Reserve at least 2x the target table size before starting expand-phase migrations.

Track error rates per write path during expand-contract. The expand phase means the application writes to both old and new structures simultaneously. Watch error rates per write path. A spike on the old column write path during expand means old code is encountering the new structure and failing — recoverable if caught early, catastrophic if you only notice after the columns have diverged.

Watch connection pool saturation during migration windows. Backfill jobs, dual-write paths, and constraint validation all consume connections. Monitor active_connections against max_connections and your pool limits (HikariCP maximumPoolSize, PgBouncer max_client_conn). Pool saturation kills application traffic with connection timeouts even when the migration itself is running cleanly.

Security and Compliance Notes

Migrations modify the structure that protects your data. Security and compliance cannot be bolted on after the fact — they need to be part of the migration design from the start.

Use least-privilege accounts for migrations. The migration runner should not be the database owner or a shared admin account. Create a migration_writer role with only the privileges that specific migration step needs — scoped to specific schemas. Separate this from a migration_admin role that can run contract-phase drops. Separate credentials for the migration tool mean you can audit which step ran under which identity, and revoke access without disrupting application connectivity.

Log every DDL statement with identity and version. PostgreSQL’s log_statement captures DDL, but supplement this with an explicit migration audit table in each schema — storing the executing identity, timestamp, and migration script version. This survives log retention periods and gives you a tamper-evident record that satisfies compliance requirements mandating change tracking. Database logs rotate and get deleted; an audit table does not.

Verify backups before any destructive migration step. The contract phase — DROP COLUMN, DROP TABLE, or DROP INDEX — removes structures that may be needed by an older application version. Before running it, confirm a recent backup exists and that you can restore it. Restore to a separate instance and verify the data is intact. Keep the old structure until rollback to the previous application version is no longer required.

Mask PII before the expand phase exposes it. If your migration creates new columns or tables containing PII that application accounts can read, apply data masking before the expand phase makes that data accessible. Use PostgreSQL masking extensions or application-level column access control. The expand phase widens your data access surface temporarily — account for that in your security design, not after the fact.

Factor compliance requirements into the expand-contract sequence. GDPR right-to-erasure may need to preserve delete capability in the new column structure. SOC2 change management may require pre-approval of migration scripts. PCI-DSS may require segmentation of cardholder data columns. GDPR, SOC2, PCI-DSS, and HIPAA all impose different constraints on schema changes. Review your data classification and applicable frameworks before designing the expand-contract sequence — not after.

Store migration credentials in a secrets manager. Migration tools need database credentials. These belong in HashiCorp Vault, AWS Secrets Manager, or GCP Secret Manager — injected at runtime, never hardcoded in config files or environment variables that persist in logs or CI/CD artifacts. Rotate these credentials after each major migration cycle. A high-privilege migration account is a production secret — treat it like one.

Quick Recap Checklist

• Zero-downtime migrations require backward-compatible schema changes that work with multiple app versions
• The expand-contract pattern breaks breaking changes into three phases: add new, migrate data, remove old
• Always use CONCURRENTLY for index creation on tables with active writes
• Add columns as nullable first; add NOT NULL constraints after backfilling
• Use NOT VALID to add CHECK constraints without scanning rows
• Migration tools (Flyway, Liquibase, Prisma Migrate) track which migrations have been applied
• Test migrations on production-size data — 2 seconds on laptop can mean 2 hours in production
• Schedule migrations during low-traffic windows even with zero-downtime techniques
• Keep migrations small and focused — one change per migration is easier to debug and rollback
• Always have a rollback plan for the deployment (not the migration itself)
• Monitor table statistics after major migrations — run ANALYZE to update query planner

Related Posts

• Database Scaling Strategies - Horizontal and vertical scaling approaches
• DevOps Roadmap - Database deployment, CI/CD pipelines, and infrastructure-as-code patterns overlap heavily with zero-downtime migration tooling and practices
• Schema Design Fundamentals - Core principles of effective schema design
• Deployment Strategies - Modern deployment patterns that enable zero-downtime

Interview Questions

Further Reading

• PostgreSQL ALTER TABLE Documentation — Locking behavior for DDL operations
• Flyway Documentation — Versioned migration management
• Liquibase Documentation — Change log-based migrations
• Prisma Migrate — ORM-integrated schema migrations
• pg-migrate — Node.js database migration tool

Conclusion

The core principle: during a deployment, old and new versions of your application both hit the same database at the same time. Your migration has to work with both.

Expand-contract handles this. Add the new column alongside the old one. Deploy code that writes to both. Backfill. Deploy code that writes to the new column only. Drop the old column. No step in that sequence breaks an older application version.

The mechanical parts matter too. Use CONCURRENTLY when creating indexes on live tables. Add nullable columns first. Use NOT VALID when adding constraints. Flyway or Liquibase track what has run so failed migrations leave a recoverable state.

The payoff is a database you can change without ceremonies, maintenance windows, or engineers sweating over a deploy at midnight.