How to Migrate a Database Without Downtime

Most early products accept a few minutes of maintenance downtime for schema changes. Then the product gains customers in several time zones, or a contract with uptime commitments, and the maintenance window disappears. The technique we use to remove that window is well established, and it works the same way for most changes.

A zero downtime database migration is a change to a live database's structure or data that happens while the application keeps serving users, with no outage and no failed requests. It works by splitting one risky change into several safe ones, each compatible with the code running at that moment.

This post explains why schema changes cause downtime, gives a seven-step method, walks through a worked example, covers the tools that help with large tables, and lists the mistakes that most often cause outages.

Why Schema Changes Cause Downtime in the First Place

Downtime during a migration usually has one of three causes, and we see all three in early codebases.

• Locks. Some schema changes lock a table while they run, so reads or writes queue up behind them until requests time out.
• Version mismatch. During a deploy, old and new application instances run side by side. If the schema changes underneath them, one version breaks.
• Long rewrites. Changes that rewrite every row take time proportional to the table size, which is fine on day one and painful in year two.

Not every change is dangerous. The PostgreSQL documentation notes that adding a column with no default, or with a non-volatile default, does not require a rewrite of the table, while changes such as some column type conversions do rewrite the entire table and its indexes. Knowing which kind you are making is the first step.

How to Run a Zero Downtime Database Migration Step by Step

We use an expand and contract sequence for any change that the current code could not survive. Each step below is its own deploy, and the system works correctly after every one of them.

1. Expand the Schema Without Removing Anything

Add the new column, table or index alongside the old structure. New columns start nullable or with a simple default, so existing inserts keep working and the change completes quickly.

Nothing reads or writes the new structure yet. This step should be boring, and if it is not, the change needs to be split further.

2. Deploy Code That Works With Both Shapes

Release application code that tolerates the old and new schema at the same time. It can read the new field when present and fall back to the old one when it is not.

This is the step teams most often skip, and it is the one that prevents errors while old and new application instances run side by side during a deploy.

3. Write to Both Old and New Structures

Start writing every change to both places, so new data lands in the new structure from this moment on. Stripe described this as the first phase of a four-step dual writing pattern it used for large online migrations, followed by moving reads, moving writes and removing old data.

Dual writes need care inside transactions, so both writes succeed or fail together. We also log any write that reaches one structure but not the other, because those gaps are exactly what the verification step needs to find later.

4. Backfill Existing Rows in Small Batches

Copy historical data into the new structure with a background job that processes a limited number of rows at a time and pauses between batches. Small batches keep locks short and let normal traffic continue.

Make the job safe to stop and restart, and track progress so you know when it has finished. We run it during quieter hours where possible and watch database load while it runs, slowing it down if normal queries start to suffer.

5. Verify, Then Switch Reads to the New Structure

Before switching, compare old and new data for a sample of records, or for all of them if the table allows. Then release code that reads from the new structure, ideally behind a feature flag so you can switch back instantly.

Watch error rates and query latency closely for a few days, because problems with real traffic patterns often show up only after the switch.

6. Contract by Removing the Old Structure

Once nothing reads or writes the old column or table, stop dual writing and remove it in a later release. Confirm with logs or query statistics that the old structure is truly unused before dropping it.

Dropping is the one irreversible step, so we keep a backup of the old data until the team is confident the migration is complete.

7. Keep a Rollback Path at Every Stage

Because each step is small, each has a simple reverse. Reads can switch back, dual writes can stop and an added column can be ignored. That is far safer than one large change with a rollback script nobody has tested.

We write the rollback for each step into the migration plan before starting. It is part of how we approach product engineering for growing products, where a failed change should cost minutes rather than an outage.

A Worked Example of Changing a Column Safely

Here is how we would apply the sequence to a common change. Suppose a users table stores email verification as a true or false flag, and the product now needs the verification time instead.

1. Add a nullable verified-at timestamp column. Existing code ignores it.
2. Deploy code that sets the timestamp on new verifications, reads it when present, and falls back to the old flag when it is empty.
3. Run a batched backfill that sets the timestamp for users already marked as verified.
4. Compare the two columns, then switch all reads to the timestamp.
5. Stop writing the old flag and drop it in a later release.

At no point does the running code depend on something that does not exist yet, and at no point does a single statement have to rewrite the whole table. That property, more than any tool, is what makes the migration safe.

Tools That Make Large Table Changes Safer

For large or busy tables, a few tools reduce locking further. We reach for them when a plain schema change would hold a lock for too long.

• Concurrent index builds. In PostgreSQL, CREATE INDEX CONCURRENTLY builds an index without taking locks that prevent concurrent inserts, updates, or deletes, at the cost of a slower build with some caveats.
• Online schema change tools for MySQL. GitHub's gh-ost is a triggerless online schema migration solution that copies the table in the background and swaps it in, and Percona's pt-online-schema-change takes a similar approach.
• Logical replication, for moving data to a new database or major version while the old one keeps serving traffic.
• Migration frameworks that keep every change in version control and run it through the same deploy pipeline as code.

Tools help, but they do not replace the sequence above. A change that breaks running code will still break it, however gently the table is altered.

Database Migration Mistakes That Cause Outages

The migration outages we see described most often trace back to one of these.

• Deploying code that expects the new schema before the migration has run, or the reverse.
• Renaming or dropping a column in the same release that stops using it.
• Backfilling millions of rows in one transaction, holding locks and bloating the database.
• Adding a NOT NULL constraint or unique index to a large table without checking how long it will lock.
• Testing the migration only on a small development database, where every change looks instant.
• Having no tested way back when something goes wrong halfway through.

Many of these become easier to avoid when the schema was designed with change in mind. Our post on how database schema decisions in week one show up later covers the choices that make migrations rarer. For broader growth planning, see our guide to preparing for much more traffic without over-engineering.

Treat Every Schema Change as a Small Release

A zero downtime database migration is less about clever tooling than about patience. Expand first, make the code tolerant of both shapes, move data in small batches, switch over behind a flag and remove the old structure last. In our experience, teams who follow that order turn schema changes from scheduled outages into routine releases.

If you are planning a risky migration on a live product, Better Software can scope it with you before any change reaches production.

Frequently Asked Questions

How to Migrate a Database Without Facing Downtime?

We migrate without downtime by splitting the change into small, compatible steps. We add new structures first, deploy code that handles old and new shapes, backfill data in batches, switch reads and writes, and remove the old structure last. Every step can be reversed on its own.

What Is Zero Downtime Deployment?

Zero downtime deployment means releasing a new version of an application while users keep working, with no outage or failed requests. We achieve it with rolling or blue-green releases plus schema changes that stay compatible with both the old and new code during the switch.

How Long Does Data Migration Take?

It depends on data volume, how busy the system is and how carefully the work is batched. A small schema change can finish in seconds, while a large backfill can run for days in the background. We plan for the slow, safe version rather than the fast, risky one.

When to Run Database Migrations?

We run additive migrations as part of normal deploys, before the code that uses them. Destructive steps, such as dropping a column, run in a later release once nothing depends on the old structure. Large backfills run in the background during quieter traffic periods where possible.

How to Update Database Without Downtime?

To update a database without downtime, we make sure every change is compatible with the code already running. In practice that means additive changes first, batched data updates, concurrent index builds where supported, and removing anything old only after the application has stopped using it.