Skip to content
webtechos

Designing Zero-Downtime Database Migration Frameworks

Design a zero-downtime database migration framework with expand and contract, safe backfills, lock budgets, sync, cutover gates and rollback.

22 Nov 2026·18 min read·General

A migration that passes in staging can still take production down in under a minute. Picture an ALTER TABLE on a busy orders table. It needs a lock the table cannot give it yet, because one slow report has been holding a transaction open for twenty minutes. The ALTER waits. Every query that arrives after it waits behind it. The connection pool fills, health checks fail, and the incident channel lights up over a change that looked like one line of SQL.

01-lock-queue-pileup

A short lock timeout turns a pile-up into a harmless retry.

Zero-downtime database migration is not a trick for a single risky change. It is a framework, a set of rules, tooling and gates that make every change safe by default. This guide walks through how we would design one, from the phase model to lock budgets, backfills, sync, cutover and rollback.

Short answer. A zero-downtime migration framework is a set of enforced rules, not a clever script. Make every change safe for the old and new application version. Split risky changes into expand, backfill, ramp and contract steps. Cap how long any statement may wait for a lock. Copy data in small throttled batches. Verify before moving reads. Keep the old structure writable until you no longer need to roll back.


Why "safe in staging" migrations still take production down

Three failure modes show up again and again in migration incidents, and a good framework is built around all of them.

  • Lock contention. A schema change that needs a strong lock queues behind existing transactions and blocks everything that follows.

  • Version skew. During a rolling deploy, old and new application code run side by side. A column the old code reads and the new code dropped breaks one of them.

  • Silent divergence. When old and new structures both receive writes, a missed update leaves them disagreeing and nothing throws an error.

Staging rarely reproduces any of these. It has no long-running reports, no overlap between releases, and a fraction of the write volume. That gap is why the framework has to enforce safety mechanically rather than rely on someone remembering.


What a database migration framework actually is

A folder of numbered SQL files is a migration tool. A framework adds the decisions around it. We would expect these seven parts.

02-migration-framework-parts

The seven parts of the framework, grouped by what they do.

Component

What it decides

Change classification

Which changes are additive, which are destructive, and which phase each belongs in

Phase model

The order of expand, migrate and contract across releases

Lock and timeout policy

How long any statement may wait for a lock before it aborts

Backfill runner

How existing rows are copied in small, resumable, throttled batches

Sync layer

How old and new structures stay consistent during the overlap

Verification

What must be proven before reads move to the new structure

Cutover and rollback

How traffic shifts, and how it shifts back

Flyway, Liquibase, Alembic, Prisma Migrate and Rails migrations all handle the first job well, which is versioning and applying changes in order. None of them enforce the other six on their own. That is the part you design.


Rule one, every change must work for two application versions

Treat this as the ground rule of the framework. Every schema change must be safe for the application version that is running now and the one that ships next.

It sounds obvious until you map it onto a rolling deploy. For several minutes, sometimes hours with canaries, both versions talk to the same database. A migration that is only correct for the new version is a migration that breaks the old one.

Two consequences follow.

  • Schema changes and code changes ship as separate releases, coordinated in a fixed order.

  • Destructive changes, such as dropping or renaming, never ship in the same release as the code that stops depending on them.


The expand and contract pattern in seven steps

The expand and contract pattern is the backbone of every zero-downtime schema migration. You add the new structure, move everything over while both exist, then remove the old one. Here is a concrete example, replacing a free-text users.phone column with a normalized users.phone_e164 column.

03-expand-contract-timeline

What reads and writes each column at every step, and how hard it is to undo.

Step

What ships

Why it is safe

1. Expand

Add nullable phone_e164. Add a trigger that sets it from phone using the same normalize_phone() function the app will use

Old code ignores the new column, and new data still flows into it

2. Backfill

A throttled job fills phone_e164 for existing rows

No deploy, no long lock, can pause and resume

3. Dual write

New app version writes both columns in one transaction, still serves reads from phone, and shadow-reads phone_e164 to log mismatches

Both columns stay consistent and users still see the trusted value

4. Ramp reads

A flag moves reads to phone_e164 from 1% up to 100%

Any problem is a flag flip, and the old column is still current

5. Soak

Run at 100% for a full business cycle while still writing both columns

Rare paths such as month-end jobs and exports get time to show up, and rollback is still a flag flip

6. Stop writing the old column

Deploy code that no longer writes phone, then drop the trigger

From here the old column goes stale, so rolling back would need a re-sync

7. Contract

Drop phone after a retention window and a fresh backup

The only step that destroys data, and it comes last

Step 1 in PostgreSQL looks like this.

sql

ALTER TABLE users ADD COLUMN phone_e164 text;

CREATE FUNCTION sync_phone_e164() RETURNS trigger AS $$
BEGIN
  NEW.phone_e164 := normalize_phone(NEW.phone);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER users_sync_phone
BEFORE INSERT OR UPDATE OF phone ON users
FOR EACH ROW EXECUTE FUNCTION sync_phone_e164();

Adding a nullable column is metadata-only. Creating the trigger is not free, because CREATE TRIGGER takes a SHARE ROW EXCLUSIVE lock that blocks writes while it waits and runs. Run it under the lock budget described below, like any other statement that touches the table.

One more detail trips teams up in steps 1 through 5. While the trigger and the application's dual write both exist, they must call the same normalization logic. If they differ, the trigger quietly overwrites what the app just wrote.

Teams get hurt when they collapse several of these steps into one or two releases for speed. The seven-step version feels slow on day one and fast the first time it saves a rollback.


Safe versions of the changes that usually break things

Backward-compatible schema changes follow a small number of recipes. These examples use PostgreSQL, and the MySQL section further down covers where its rules differ.

Change

The risky way

The safe way

Add a column with a default

A volatile default such as clock_timestamp() rewrites the whole table

No default or a constant default is metadata-only on PostgreSQL 11 and later

Add an index

CREATE INDEX blocks writes while it builds

CREATE INDEX CONCURRENTLY, outside a transaction block, then check for an invalid index if it fails

Make an existing column NOT NULL

SET NOT NULL scans the whole table under an exclusive lock

Add CHECK (col IS NOT NULL) NOT VALID, run VALIDATE CONSTRAINT, then SET NOT NULL, which skips the scan on PostgreSQL 12 and later. Drop the redundant check afterward

Add a foreign key

Adding it directly takes a lock that blocks writes on both tables while it scans

Add it NOT VALID, then validate in a separate statement

Rename a column

RENAME COLUMN breaks whichever version still uses the old name

Add the new column, sync, move reads, then drop the old one

Change int to bigint

ALTER COLUMN TYPE rewrites the whole table under an exclusive lock

Add a new column, sync it with a trigger, backfill, swap reads, drop the old column. Plan the swap of keys, sequences and foreign keys separately

Drop a column

Drop it while old code might still select it

Remove all reads and writes in code first, wait a release, then drop

The pattern behind every row is the same. Make the change small enough that the lock is momentary, and move the expensive work into a step that does not hold one.


Lock budgets, the guardrail that prevents most outages

If we could enforce only one rule in a framework, it would be a lock budget. Every metadata change runs with a short lock_timeout. If it cannot get its lock quickly, it gives up and retries, instead of queueing and blocking the application.

python

def run_ddl(conn, statement, attempts=5):
    for i in range(attempts):
        try:
            with conn.transaction():
                conn.execute("SET LOCAL lock_timeout = '3s'")
                conn.execute("SET LOCAL statement_timeout = '30s'")
                conn.execute(statement)
            return
        except LockNotAvailable:
            time.sleep(2 ** i + random.random())
    raise MigrationAborted(statement)

SET LOCAL keeps the timeouts scoped to one transaction, so they cannot leak into a pooled connection. The transaction block also rolls back cleanly when the lock times out, which a plain retry loop would not do.

Pair it with a pre-flight check that refuses to start while a long transaction is touching the target table.

sql

SELECT a.pid, now() - a.xact_start AS age, left(a.query, 80) AS query
FROM pg_stat_activity a
JOIN pg_locks l ON l.pid = a.pid
WHERE l.relation = 'public.orders'::regclass
  AND a.pid <> pg_backend_pid()
  AND a.xact_start < now() - interval '30 seconds';

Index builds need their own policy. CREATE INDEX CONCURRENTLY waits for other transactions to finish at several points, and a short lock_timeout can cancel it during those waits. Run it with lock_timeout set to 0, watch its runtime, and check for an invalid index afterward. Keep the short budget for metadata-only changes such as adding a nullable column, which should finish in milliseconds. A few seconds of waiting there is already a warning.


Backfills that do not hurt production

A backfill is where a safe plan quietly becomes an unsafe one. A single UPDATE across 150 million rows holds locks, floods the write-ahead log, bloats the table and sends replicas far behind.

The framework's backfill runner should follow five rules.

  • Batch by primary key range. Walk the table in key order, not with OFFSET.

  • Make every batch idempotent. Re-running a batch must produce the same result.

  • Checkpoint progress. The job stores the last completed key so a crash resumes instead of restarting.

  • Throttle on replica lag. Pause automatically when lag crosses a threshold.

  • Run it as a separate worker. It should be killable at any moment without affecting the app.

sql

UPDATE users
SET phone_e164 = normalize_phone(phone)
WHERE id > :last_id
  AND id <= :last_id + 5000
  AND phone_e164 IS NULL;

The arithmetic is worth doing before you start. At 150 million rows and 5,000 rows per batch, that is 30,000 batches. If each batch takes 150 ms and the runner pauses another 150 ms between them, the job finishes in about two and a half hours. That is a number you can schedule, communicate and monitor, which a single giant statement never is. The estimate assumes dense IDs. If the key space has large gaps, batch by the next N existing keys instead of a fixed ID range, and add time for any lag pauses.

Give the worker its own database credentials and its own connection limit. A stalled backfill should never be able to starve the application's connection pool.


Keeping old and new structures in sync

While both structures exist, something has to keep them consistent. There are four common mechanisms.

Mechanism

Best for

Main risk

Transactional dual write in the application

Two columns or tables in the same database

Code paths that forget to write both

Database triggers

Old code that you cannot change quickly

Hidden logic and extra write latency

Change data capture (CDC), for example Debezium

Moving data between databases or engines

Lag, ordering and operational overhead

Event-driven sync pipelines

Fan-out to several consumers

Eventual consistency and replay handling

The point that gets lost in many guides is this. Dual writes are safe when both writes land in one transaction in one database, and unsafe when they cross two systems. If the first write commits and the second fails, you have divergence with no error and no alarm. For cross-database migrations, keep the application writing to a single primary and replicate to the other system with CDC or logical replication. Then switch writes once, deliberately, as described in the next section.

A useful habit during the overlap is the shadow read. The app serves the old result and runs the new query alongside it, logging any difference. GitHub's open source Scientist library, first written for Ruby, popularized the idea. It always returns the trusted result, randomizes the order of the two paths, and is usually run on a small sample of requests before being widened. It is easy to copy into a data access layer.


Moving between databases and major versions

Many searches for a zero-downtime database migration are really about moving an entire database, such as a cloud move, an engine change or a major version upgrade. The column-level pattern above does not cover that case, but the same principles apply.

05-cross-database-cutover

Reads can move gradually. The write cutover is one deliberate switch.

  1. Snapshot, then stream. Copy the initial data, then keep the target current with logical replication or CDC until the lag is near zero.

  2. Replicate what replication does not. Logical replication generally does not carry schema changes, and sequence values may need separate handling depending on your version, so script both.

  3. Verify on the target. Run the same checksum and sample diffs described below, plus a load test against the target with production-shaped queries.

  4. Cut over writes once. Pause or fence writes for a few seconds, wait for replication lag to reach zero, then point the application at the target.

  5. Replicate backwards. Stream changes from the new primary back to the old one so a rollback does not lose data written after cutover.

  6. Keep the source around. Retire it only after the target has run under real traffic through a full business cycle.

Be honest about the claim. Reads can move gradually, but the write cutover is usually a short pause measured in seconds. If your product cannot tolerate even that, you need application-level queuing during the fence.


If you run MySQL

The framework is the same, but the engine's rules differ in ways that matter.

  • Metadata locks queue like PostgreSQL locks. A DDL statement waiting behind an open transaction blocks later queries on that table, so the same long-transaction check and a short timeout apply. MySQL's lock_wait_timeout setting controls how long a session waits for a metadata lock.

  • Ask for the algorithm you expect. In MySQL 8.4, adding a column can use ALGORITHM=INSTANT, which is metadata-only. Adding a secondary index uses INPLACE and allows concurrent writes. Changing a column data type needs ALGORITHM=COPY and a full table rebuild with no concurrent writes. Stating the algorithm explicitly makes the statement fail instead of silently choosing a blocking method.

  • Use an online schema change tool for the rebuilds. gh-ost builds a ghost table, copies rows in the background and applies ongoing changes from the binary log instead of triggers. It can throttle and pause, and it lets you hold the final table swap until you are ready. pt-online-schema-change does similar work with triggers.

Note that CREATE INDEX in MySQL also finishes only after transactions that are using the table complete, so the long-transaction check matters here too.


Verification before cutover

A matching row count proves that rows arrived. It does not prove they arrived correctly. The framework should require evidence before reads move.

  • Mismatch count. For a derived column, count rows where the new value disagrees with what the old one implies.

  • Chunk checksums. Compare hashes of ranges of rows between old and new structures.

  • Sampled row diffs. Pull a random sample and compare every migrated field.

  • Constraint validation. Confirm foreign keys, uniqueness and nullability hold on the new structure.

  • Shadow read mismatch rate. Set a hard threshold. Our default would be zero unexplained mismatches over a full business cycle.

sql

-- rows where the new column disagrees with the old one
SELECT count(*) AS mismatches
FROM users
WHERE phone_e164 IS DISTINCT FROM normalize_phone(phone);

-- one hash comparison per 5,000-row chunk
SELECT (id - 1) / 5000 AS chunk,
       md5(string_agg(phone_e164, ',' ORDER BY id))
         = md5(string_agg(normalize_phone(phone), ',' ORDER BY id)) AS match
FROM users
GROUP BY 1
ORDER BY 1;

Write the threshold into the pipeline as a gate. If the gate is a convention in a runbook, it will be skipped the one time it matters.


What to watch while a migration runs

A migration needs its own small dashboard. These are the signals we would put on it for PostgreSQL.

Signal

Where to read it

Why it matters

Lock waiters

pg_stat_activity rows with wait_event_type = 'Lock'

A rising count means a statement is queueing and the app may be next

Replica lag

replay_lag in pg_stat_replication

Backfills and bulk updates push replicas behind, so throttle on it

Dead tuples

n_dead_tup in pg_stat_user_tables

Row rewrites leave bloat that autovacuum has to clear

WAL volume

Growth of pg_current_wal_lsn() over time

Large backfills can generate more log than disks or replicas expect

Error rate and p99 latency

Your APM or metrics stack

The user-facing check on every ramp stage

Shadow mismatch rate

Application metric from the shadow read

The correctness check on every ramp stage


Cutover as a gradual read shift

Never flip all reads in one deploy. Put the read path behind a flag and ramp it with a gate at each stage. At every stage compare error rate, p99 latency and mismatch rate against the old path.

04-read-ramp-gates

Each stage has to pass its gate before the next one starts. The flag is always the way back.

Three details make the flag trustworthy. Evaluate it server side. Fall back to the old path if the flag service is unreachable. Bucket consistently by user or tenant so one customer does not bounce between paths mid-session.

A product platform such as PostHog, which bundles feature flags with analytics, can drive the ramp and segment who sees what. It is not an observability tool, and our review notes that its individual modules trail category specialists, so keep error rate and latency on your existing metrics stack and use the flag platform to control the rollout. Any flag system works as long as turning it off takes seconds and does not need a deploy.


Rollback design

Rollback is a property you build in, not a script you write during an incident. In the seven-step plan above, rollback is a flag flip through step 5 because the old column is still written and still correct. Step 6 is the quiet point of no easy return, since the old column stops receiving writes and begins to go stale. Step 7 is the point of no return.

A few rules keep that honest.

  • Keep writing the old structure through the soak period, and stop only when you are certain you will not need it.

  • Take a fresh backup before the contract step, and keep it through the retention window.

  • Remember that rolling back application code does not roll back schema. Plan the schema reversal separately.

  • Make the contract step its own release, with its own approval.


Where automation and AI assistants fit

Writing migrations and backfills is repetitive, careful work, which makes it a reasonable job for an assistant. An agent such as Claude Code or an AI editor like Cursor can draft the expand step, the trigger and the batch loop quickly, and it is good at translating a plan like the table above into code for a specific ORM.

We would still treat that output like any other pull request from someone new to the codebase. It goes through the same linter and the same lock-budget gate as everything else. Tools such as squawk for PostgreSQL and strong_migrations for Rails flag unsafe statements automatically. pgroll goes further for PostgreSQL by running every migration as expand and contract, using versioned views and triggers so old and new application versions can coexist. Our AI developer tools category covers the assistants in more depth.


Migrating beyond relational tables

The same shape applies to things that are not tables. Changing an embedding model means re-embedding your corpus and building a second index, then shadow-querying it, then shifting traffic. That is expand and contract with a different storage engine. If you are choosing the target for that second index, our comparison of Pinecone and Qdrant for enterprise use covers pricing, benchmarks and compliance, and its FAQ notes that Qdrant ships an official tool for streaming data out of Pinecone while both stay live.


A framework checklist you can adopt this sprint

You do not need to build everything at once. This order gives the best return.

  1. Add a migration linter to CI. Block unsafe statements before review.

  2. Enforce lock and statement timeouts on every migration connection.

  3. Adopt the seven-step phase model for any rename, type change or drop.

  4. Build one reusable backfill runner with key-range batches, checkpoints and lag throttling.

  5. Put the read path behind a flag for every data-shape change.

  6. Define a verification gate with a mismatch threshold.

  7. Make contract a separate release with a fresh backup.


Questions

Frequently asked questions

What is the expand and contract pattern?

It is a phased approach to changing a schema without downtime. You expand by adding the new structure while keeping the old one, migrate by syncing data and moving traffic, and contract by removing the old structure once nothing uses it. It lets old and new application versions run against the same database safely.

Can you do a zero-downtime database migration without dual writes?

Yes. For changes inside one database, a trigger can keep a new column in sync with an old one, so application code does not need to write both. For moves between databases, CDC or logical replication reads the source's change stream and applies it to the target, which avoids having the application write to two systems.

Does ALTER TABLE lock the table in PostgreSQL?

Many forms of ALTER TABLE need a strong lock, and even a fast one can cause an outage if it waits behind a long transaction and blocks the queries behind it. Adding a nullable column is metadata only and quick, but changing a column type usually rewrites the table. Always set a short lock_timeout so a waiting statement aborts instead of queueing.

How do you migrate between database engines without downtime?

Copy a snapshot, stream changes with CDC or logical replication until lag is near zero, verify the target, then cut over writes in one deliberate step. Expect a write pause of seconds, and set up reverse replication so you can roll back without losing new data.

How do you roll back a zero-downtime migration?

Design rollback into the phases. While both structures are still written and consistent, rollback means turning a flag off. Once the application stops writing the old structure, rollback needs a re-sync, and once you drop it, rollback means restoring from backup. That is why contract is a separate, deliberate step.

How big should backfill batches be?

Start small, often a few thousand rows, then tune against replica lag and write latency. The right size is the largest batch that keeps lag and p99 latency inside your limits. Always batch by primary key range and make each batch safe to repeat.

Does adding an index lock the table?

A plain CREATE INDEX in PostgreSQL blocks writes while it builds. CREATE INDEX CONCURRENTLY avoids that, but it cannot run inside a transaction block, it takes longer, and a failed run leaves an invalid index you must drop. In MySQL 8.4, adding a secondary index runs in place and allows concurrent writes.

Is zero downtime worth it for a small application?

If the database is small and you can accept a short maintenance window, a simple migration may be the right call. The framework earns its keep once downtime has a real cost, such as paying customers, background jobs that cannot pause, or several services sharing one database. Even then, the lock budget and linter are cheap enough to adopt on day one.


Conclusion

Zero-downtime migrations are less about clever tooling and more about discipline you can automate. If you adopt only three things, make them the migration linter, the lock timeout policy and the rule that contract is always its own release. Those three stop the majority of avoidable outages for almost no engineering cost.

The seven-step expand and contract flow, the throttled backfill runner and the verification gate are worth building once a table is large enough that a mistake is expensive. Reach for an automated tool such as pgroll, or a CDC pipeline for engine moves, when you run migrations often enough that hand-rolled scripts become the risk.

Skip the framework only when the database is small and a short maintenance window is acceptable. Everywhere else, the slow, boring sequence wins.

WebTechOS accepts no payment for coverage, placement or scores. Where a piece references pricing, it reflects published list rates at the date shown.