Zero-downtime database migrations

Reference Card

• Origin/Prior Art: Martin Fowler’s Evolutionary Database Design.
• Related Practices: Expand/Contract pattern, Schema versioning.
• Key Concepts: Backward compatibility, View-based migrations, Table locks.
• Primary Failure Modes: Migrations locking a massive table and taking down production, Tightly coupling schema changes to application code.

Introduction

In a modern CI/CD pipeline, application code can be deployed dozens of times a day via rolling updates or Blue/Green deployments with zero impact on users. However, the database is stateful. If a deployment renames a critical column in the database from user_name to full_name, the old version of the application (which is still running during the rollout) will immediately crash because it cannot find the column it expects.

Zero-downtime database migrations are the architectural patterns required to modify database schemas while multiple versions of the application code are running simultaneously. It dictates that schema changes must be completely decoupled from code deployments.

Mental Model

Imagine rebuilding a bridge while cars are currently driving over it. You cannot simply blow up the old bridge and tell cars to wait while you pour new concrete (a maintenance window). You must build the new bridge directly next to the old one (Expand). You redirect traffic to the new bridge slowly. Once every car is using the new bridge, you can safely demolish the old one (Contract).

Operational Pattern

The industry standard for zero-downtime migrations is the Expand and Contract Pattern. It splits a single destructive change into four distinct, backward-compatible phases, deployed over days or weeks.

Example: Renaming first_name to full_name

1. Expand (Database): Add the new column full_name to the database. Do not delete or rename the old column. The database now has both.
2. Migrate (Application): Deploy new application code that writes data to both columns simultaneously, but still reads from the old first_name column. In the background, run a script to backfill the old rows, copying data from first_name into full_name.
3. Transition (Application): Deploy new application code that now reads exclusively from full_name.
4. Contract (Database): Only when no active code is touching the old column, run a final database migration to drop the first_name column.

Failure Modes

The Exclusive Table Lock

The most devastating database failure is a migration script running ALTER TABLE users ADD COLUMN phone VARCHAR(255) on a Postgres database with 50 million rows, without setting a default value carefully. Depending on the database engine version, this operation might require an exclusive write lock on the entire table. The migration script will hang for 15 minutes, blocking all incoming customer registrations and completely freezing the application. Engineers must deeply understand the locking mechanics of their specific database engine before running DDL (Data Definition Language) queries in production.

Coupled Deployments

If an organization attempts to deploy the database schema change and the new application code in the exact same GitHub Action step, they are guaranteed to experience downtime. The schema change will finish seconds before the new application boots up, meaning the old application instances will throw 500 errors for those few seconds. Migrations must be run and verified before the application deployment begins.

Security

When expanding schemas (e.g., adding a new ssn_encrypted column), the transition phase creates a dangerous window where sensitive data exists in two places simultaneously. If the old, unencrypted column is forgotten and never contracted (deleted), it remains a permanent security liability.

Operational Guidance

Treat database migrations as first-class software artifacts. They should be checked into version control, peer-reviewed by a DBA or senior engineer, and tested in a staging environment against a production-sized dataset. Never allow application ORMs (like Hibernate or Entity Framework) to automatically run schema migrations on startup in a production environment. Migrations must be executed by a dedicated, isolated pipeline step, ensuring they are run exactly once.

Related topics

• Rolling updates
• Blue-green deployment

Sources & further reading

• Prisma: Expand and Contract Pattern - Prisma