Zero-Downtime Database Migrations: A Practical Guide

In short: Zero-downtime database migrations work when every schema change is compatible with both the old and the new version of your application at the same time. The standard approach is expand and contract: add first, move the data, switch the code, and remove the old structure only at the very end. Add to that commands that avoid long table locks, and data migrations done in small batches instead of one big step.

Why migrations cause outages

During a deployment, old and new instances of your application run side by side for a while, especially with rolling deployments across several servers or containers. If a migration renames a column, the old code can no longer find it and starts throwing errors. That is the first common cause.

The second is locking. Some schema changes need an exclusive lock on the table, and while it is held every read and write waits. Nobody notices on a small table. On a table with millions of rows, the application may stall for minutes. Worse, if the migration itself is waiting for a lock, every request queues up behind it.

The expand and contract pattern

Phase 1: Expand

Add new structures without changing existing ones. Instead of renaming name to full_name, add full_name as an extra column. Keep it nullable at first so the old code can still insert rows.

Phase 2: Dual write and backfill

The application now writes to both columns. In parallel, a background job fills the new column for all existing rows, in batches of a few thousand rows with short pauses in between. Transactions stay short and the load stays under control.

Phase 3: Switch over

Once all data is copied, the code reads only from the new column. This is the point to add NOT NULL and other constraints.

Phase 4: Contract

Only when you are sure no running version uses the old column do you drop it, in a separate deployment. That separation is the heart of the pattern: each step can be shipped on its own and reverted if needed.

Avoiding locks, with PostgreSQL as an example

Many databases offer variants that do not hold long locks. In PostgreSQL, create indexes with CREATE INDEX CONCURRENTLY so writes keep flowing. Add foreign keys and check constraints with NOT VALID first, then run VALIDATE CONSTRAINT, which needs a weaker lock. Since PostgreSQL 11, adding a column with a constant default is a metadata-only change and therefore fast.

Also set a lock_timeout for migrations. If the migration cannot get its lock within a few seconds, it aborts instead of blocking the whole application, and you retry at a quieter moment. Our database selection guide helps if you are still choosing a database.

Building migrations into your deployment process

Migrations belong in the repository, versioned with the code. Tools like Prisma Migrate, Flyway, Liquibase or your ORM's migration system track which changes have been applied. They run automatically in the CI/CD pipeline, ideally against a staging database with realistic data volume first. Our practical CI/CD guide shows what such a pipeline looks like.

Before any larger migration, have a recent backup that you have actually restored at least once. During and after the rollout, watch error rates, response times and database locks. Good monitoring for web applications surfaces problems before users report them.

Frequently asked questions (FAQ)

Do I need expand and contract for every schema change?

No. A new nullable column or a new table is backwards compatible by nature and can be shipped directly. The pattern pays off for anything that changes or removes existing structures: renames, type changes, new required fields.

How do I roll back a failed migration?

With expand and contract every step is small and backwards compatible, so rolling back the code is usually enough. Destructive steps like dropping a column come last, when a rollback is no longer needed. The tested backup remains your safety net.

Isn't a maintenance window simpler?

For internal systems used during fixed office hours, a maintenance window can be a reasonable choice. For shops, customer portals or SaaS products that should be available around the clock, the effort for zero-downtime migrations is well spent.

How long does a multi-phase migration take?

That depends on data volume and your release rhythm. The phases often spread across several deployments over a few days. It feels slower, but it carries far less risk than one big step.

Conclusion

Zero-downtime migrations depend less on tooling than on discipline: small compatible steps, lock-friendly commands and cleanup at the end. Once that becomes a team habit, schema changes stop being scary and you can evolve the application much more often.

If you run an application with a growing database or are building a new one, we can help as part of our web development work.