Zero Downtime DB Migration: Expand, Switch, Contract

Yes, the standard pattern for a zero-downtime database migration is expand, switch, contract. You add the new schema or data alongside the old, migrate reads and writes to it, then remove the old version — all while the application stays live.

Why a Simple Migration Isn’t Enough

A typical migration script drops a column, renames a table, or changes a constraint in one transaction. That works fine on a development database or during a scheduled maintenance window. But in production, that single transaction locks tables, blocks queries, and can take minutes or hours on large datasets. During that time, your application either errors out or queues requests that time out. For a service with 24/7 availability requirements, even a few seconds of downtime can mean lost revenue, failed transactions, or a pile of support tickets.

The expand-switch-contract pattern avoids this by breaking the migration into phases. Each phase is backward-compatible: the database and the application can run with both the old and new versions of the schema at the same time. You only remove the old version after the new one is fully in use and verified.

Phase 1: Expand

In the expand phase, you add the new structures without removing or breaking the old ones. This usually means:

Adding new columns (nullable or with a default value)
Creating new tables
Adding new indexes (carefully, as they can slow writes)
Backfilling data into new columns or tables in batches

The key rule: the old schema must still work exactly as before. If you add a status_new column, the old status column stays. If you create a orders_v2 table, the old orders table remains fully functional. During this phase, the application can be deployed with code that reads from both old and new structures, but it must still be able to fall back to the old ones if something goes wrong.

Backfilling is often the most time-consuming part. For example, if you’re splitting a users table into users and user_profiles, you need to copy millions of rows. Doing this in a single INSERT ... SELECT will lock the source table for the entire duration. Instead, batch the copy in chunks of a few thousand rows, with a small sleep between batches, to avoid holding locks for too long and to keep the replication lag manageable.

Phase 2: Switch

Once the new structures are populated and verified, you switch the application to use them. This is the trickiest part because it must be atomic — or at least appear atomic to the outside world.

There are two common approaches:

Dual-write with a cutover flag: The application writes to both old and new structures for a while. Then, after verifying data consistency, you flip a feature flag that makes reads and writes go exclusively to the new structures. The old structures are kept but no longer actively used.
Rename-based switch: In a single transaction, you rename the old table to orders_old and the new table to orders. This is fast and atomic, but it requires that the new table is already fully populated and that no writes are happening during the rename (or that you use a tool like pt-online-schema-change that handles this).

The rename approach is simpler but has a catch: if the new table is missing rows that were written to the old table during the backfill, you lose data. That’s why the dual-write approach is safer for high-write systems. You run dual writes for a period long enough to catch up any lag, then verify row counts and checksums before switching.

During the switch, you should also consider the order of operations for foreign keys and indexes. If the new table has different constraints, you may need to add them before the switch or right after, and that can take time.

Phase 3: Contract

After the switch is successful and the application has been running on the new schema for a while (hours or days, depending on your confidence), you remove the old structures. This is the contract phase:

Drop old columns that are no longer read or written
Drop old tables
Remove old indexes
Remove the dual-write code from the application

This phase can also cause downtime if done carelessly. Dropping a column that is still referenced by a query will error out. Dropping a large table can lock the buffer pool or generate a lot of I/O. The safest way is to first mark the column or table as unused in the application (so no code references it), then drop it in a low-traffic period, and finally run VACUUM FULL or OPTIMIZE TABLE to reclaim space if needed.

One common mistake is to skip the contract phase entirely, leaving dead columns and tables in the database. That adds clutter, slows down queries (because the query planner has to consider more indexes), and makes future migrations harder. Always finish the job.

Tools and Techniques That Help

You don’t have to do this by hand. Several tools implement the expand-switch-contract pattern automatically:

gh-ost (GitHub’s online schema migration tool for MySQL) — creates a shadow table, copies data incrementally, and then uses binary log replay to catch up, finally swapping tables with a rename.
pt-online-schema-change (Percona Toolkit) — similar approach, uses triggers to capture changes during the copy.
Liquibase and Flyway — version control for your schema, but they don’t handle the online part; you still need to design your changes in phases.
PostgreSQL’s pg_repack — useful for reclaiming space after a migration, but not for the migration itself.

These tools are especially useful for large tables (millions of rows) where a simple ALTER TABLE would take too long. They also reduce the risk of human error in the switch phase.

Common Pitfalls and How to Avoid Them

Even with the right pattern, things can go wrong. Here are the most common issues:

Forgetting to update all application code: If one service still reads from the old column, the contract phase will break it. Use a code search to find all references before dropping anything.
Not testing the rollback: The expand phase is easy to roll back (just drop the new stuff). The switch phase is harder. Practice the switch on a staging environment that mirrors production data volume.
Ignoring replication lag: If you have read replicas, the backfill and switch can cause lag. Monitor replica lag and pause the migration if it gets too high.
Running out of disk space: The expand phase doubles the size of the table (old + new). Make sure you have enough disk, and clean up old data after the contract.
Locking on the switch: Even a rename takes a metadata lock. Schedule it during low traffic, or use a tool that handles the lock gracefully.

A related misconception is that you need a maintenance window even with this pattern. You don’t — that’s the whole point. But you do need a period of low traffic to perform the switch safely, because even a brief lock can cause a spike in latency.

When You Can Skip This Pattern

Not every migration needs expand-switch-contract. If your database is small (a few thousand rows), a simple ALTER TABLE will finish in milliseconds. If you have a maintenance window that customers expect (like a nightly batch), you can use it. If you’re migrating to a completely new database engine, you might use a different strategy like blue-green deployment or change data capture (CDC) to stream changes to the new system.

But for any schema change on a large, always-on database, this pattern is the safest and most standard approach. It’s also a prerequisite for zero-downtime deployments in microservices architectures, where multiple services share a database and each service may be deployed at different times.

Frequently Asked Questions

How long should the expand phase take?

As long as it needs to complete the backfill without causing performance issues. For a table with 100 million rows, it might take hours. The key is to run it in batches and monitor the database load. You can pause or throttle the backfill if the database becomes slow.

Can I do a zero-downtime migration with a foreign key change?

Yes, but it’s more complex. You often need to drop the old foreign key, add the new one, and ensure referential integrity during the switch. This usually requires a longer dual-write period and careful validation. Some databases allow adding a foreign key with NOT VALID and then validating it later, which avoids locking the table.

What if I need to rename a column?

Rename is one of the easiest changes because it’s metadata-only in most databases (e.g., PostgreSQL, MySQL 8.0+). The tricky part is that old application code may still reference the old name. The solution is to add a new column with the new name, backfill it, switch the code, then drop the old column — that’s expand-switch-contract applied to a column.

Does this pattern work for sharded or distributed databases?

Yes, but you have to run the phases on each shard independently. You also need to coordinate the switch across shards to avoid inconsistent reads. Tools like gh-ost work on a single shard; for a distributed system, you may need a custom orchestration layer.

What is the biggest risk of the switch phase?

Data loss or inconsistency. If you switch to a new table that is missing rows, or if dual-writes are not perfectly synchronized, you can end up with divergent data. Mitigate this by running consistency checks (row counts, checksums) before the switch, and keep the old table available for rollback until you’re confident.

Can I use this pattern for a migration that changes data types?

Yes. Add a new column with the new type, backfill it with a conversion (e.g., CAST), switch the application to read/write the new column, then drop the old one. This avoids the long lock that a direct ALTER TABLE ... ALTER COLUMN TYPE would take.

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.

More cards in this drawer

The whole Numbers and Powers drawer →