Database Migrations With Zero Downtime: Strategies That Actually Work

Database migrations are the most anxiety-inducing part of deploying web applications. Code deployments can be rolled back in seconds. A bad database migration can corrupt data, cause hours of downtime, or — in the worst case — lose data permanently.

I have caused two production outages from bad migrations. One locked a table for 40 minutes during a column rename on a 50-million-row table. The other deployed code that expected a new column before the migration actually ran. Both were preventable with the patterns I am about to share.

The Fundamental Challenge

In a continuously deployed application, you cannot assume that the database schema and the application code change simultaneously. There is always a window — seconds to minutes — where either:

The OLD code is running against the NEW schema (migration runs before new code deploys), or

The NEW code is running against the OLD schema (new code deploys before migration runs).

Both scenarios must work without errors. This constraint drives every pattern in this post.

Pattern 1: Expand-Contract (Add-Migrate-Remove)

The safest approach for any schema change:

Step 1 (Expand): add the new column/table without removing or modifying existing structures. The old code ignores the new column. The new code can optionally read from it.

Step 2 (Migrate): deploy new code that writes to both old and new structures. Backfill existing data from the old structure to the new one. Both old and new code can coexist during this step.

Step 3 (Contract): once all data is migrated and the new structure is proven, deploy code that only uses the new structure. Then remove the old column/table in a subsequent migration.

This three-step approach means there is never a moment where the application breaks due to missing columns or unexpected schema changes. Each step is independently deployable and reversible.

Example: renaming a column from userName to display_name.

Step 1: ADD COLUMN display_name (nullable). Old code still reads userName.

Step 2: Deploy code that writes to both. Backfill: UPDATE users SET display_name = userName WHERE display_name IS NULL.

Step 3: Deploy code that reads display_name. Drop userName column.

Pattern 2: Non-Locking Migrations

In PostgreSQL and MySQL, some ALTER TABLE operations lock the table, blocking all reads and writes until complete. On a large table, this means downtime.

Operations that lock in PostgreSQL:

Adding a column with a non-null default (before PostgreSQL 11)

Adding a NOT NULL constraint without a default

Changing a column's type

Creating an index without CONCURRENTLY

Operations that do NOT lock:

Adding a nullable column

Adding a column with a default (PostgreSQL 11+)

Dropping a column (marks as dropped, does not rewrite)

Creating an index CONCURRENTLY

Adding a constraint NOT VALID (validated in a separate step)

The rule: never run a locking operation on a table with active traffic. Instead, decompose the operation into non-locking steps.

Adding NOT NULL constraint safely:

Add the column as nullable (non-locking)

Backfill all existing rows (batched UPDATE in small chunks)

Add a NOT NULL constraint with NOT VALID (non-locking) — this validates new rows only

VALIDATE CONSTRAINT (acquires a weak lock that allows reads/writes but blocks other schema changes — takes time proportional to table size but does not block traffic)

Creating an index safely:

CREATE INDEX CONCURRENTLY instead of CREATE INDEX. The concurrent version takes longer but does not lock the table. It requires a separate transaction (cannot be inside a migration transaction), so your migration tool needs to support this.

Pattern 3: Backfill in Batches

Never backfill millions of rows in a single UPDATE statement. It creates a massive write transaction that:

Locks a large number of rows

Fills the WAL (Write-Ahead Log) with redo data

Can cause replication lag on read replicas

Might be killed by statement timeout

Instead, backfill in small batches:

Update 1,000 rows at a time with a WHERE clause that targets unprocessed rows: UPDATE users SET display_name = userName WHERE display_name IS NULL LIMIT 1000. Run this in a loop with a small delay (100ms) between batches.

For large tables (millions of rows), this takes minutes to hours. Run it as a background job, not as part of the deployment. Monitor progress and adjust batch size based on the load it creates.

Pattern 4: Dual-Write for Data Moves

When moving data between tables or changing its structure:

Start writing to BOTH the old and new locations simultaneously.

Backfill historical data from old to new.

Verify the new location has all the data (count check, checksum).

Switch reads to the new location.

Stop writing to the old location.

(Later) Drop the old table/column.

The dual-write phase ensures no data is lost during the transition. Even if the backfill misses some rows, the dual-write captures all new activity.

Pattern 5: Feature Flags for Schema Dependencies

When new code requires a new column, use a feature flag to gate the new code path:

Deploy the migration that adds the column.

Deploy the new code behind a feature flag (disabled).

Verify the migration is complete on all database replicas.

Enable the feature flag.

If anything goes wrong, disable the flag. The old code path still works with the old schema. You have decoupled the database change from the code change.

Prisma-Specific Patterns

Prisma generates migrations as SQL files, which gives you control over the exact operations. But Prisma's default migration behavior has pitfalls:

Prisma generates DROP COLUMN for removed fields. This is destructive and immediate. Instead, manually edit the migration to keep the column and remove it in a later migration after code no longer references it.

Prisma adds NOT NULL constraints immediately. For large tables, manually change this to the add-nullable, backfill, validate pattern.

Prisma creates indexes synchronously. For PostgreSQL, manually add CONCURRENTLY to CREATE INDEX statements (and remove the migration from the transaction).

Always review Prisma's generated SQL before applying. Never blindly run prisma migrate deploy in production.

Testing Migrations

Test migrations against production-like data volumes. A migration that takes 10ms on your local database with 100 rows might take 40 minutes on production with 50 million rows. Use a staging database with realistic data volumes for migration testing.

Test both forward AND rollback. Every migration should have a rollback plan. Test that rollback works without data loss. For expand-contract, the rollback of step 1 is dropping the new column — verify existing data is unaffected.

Test with active traffic. Run the migration while simulating load against the database. Verify that application requests continue succeeding (no timeouts, no errors) during the migration.

Monitoring During Migrations

Watch these metrics during production migrations:

Query latency: p50 and p99 response times. A spike indicates lock contention.

Active connections: a sudden increase indicates queries are queueing behind a lock.

Replication lag: if replicas fall behind, reads may return stale data.

Error rate: any increase in application errors during migration indicates a problem.

Table lock waits: direct monitoring of lock acquisition in the database.

Set up alerts before running migrations. If latency exceeds 2x normal or error rate exceeds 0.1%, abort the migration.

Our Migration Workflow

At AIDToolStack, every migration goes through:

Write migration (Prisma generates, we review and edit)

Test on local with realistic data

Test on staging

Deploy to production during low-traffic hours (early morning)

Monitor for 10 minutes post-migration

Deploy the code that uses the new schema

We have not had a migration-related outage since adopting this workflow. The extra process adds 30 minutes to each migration but saves the hours of incident response that bad migrations cause.

The most important lesson: treat database changes with the same rigor as security changes. They are irreversible (you cannot easily un-drop a column with data) and affect every user simultaneously. Take your time, test thoroughly, and always have a rollback plan.