Zero-Downtime Database Migrations

Of all the parts of a system, the database is the most unforgiving. You can roll application code out and back again, restart a service, discard a version — but the database holds state you cannot regenerate, it outlives every single version of the code, and all running instances share it at the same time. A bug in the code hits one request; a mistake in the database hits all of them and often cannot be undone.

That is exactly why database changes are so feared that many teams banish them to a maintenance window: stop the system, change it, start it up again. That is honest, but it doesn't scale — the more important the system, the more expensive every minute of downtime. This text shows the other way: how a schema evolves under full load without stopping the system. The patterns apply across frameworks and databases; which individual operation locks how heavily depends on the particular system and should be looked up there. The principle stays the same.

Why database changes are dangerous

Three properties make the database dangerous, and they act together.

The first is shared use across multiple versions. During a rolling deployment, the old and the new version of the code run against the same schema simultaneously for a period of time. A schema that only fits the new version breaks the old one, which is still serving requests — and vice versa. Every change therefore has to fit both running versions at every moment. An example: if the new version renames a column, the old version is still looking for it under the old name at that very moment — and every one of its requests fails until the last old instance has been replaced. The bug isn't in the code but in two versions meeting at a schema that only fits one of them.

The second is locking behavior. Some structural changes take a lock that blocks reads or writes on a table while they run. On a small table, this is invisible; on a large one, the same operation can take minutes and bring the whole system to a standstill in that time — an outage without a single logical error, caused purely by duration and locking.

The third is irreversibility. Code can be rolled back; deleted data cannot. A destructive change — a dropped column, a deleted table — is a one-way street. You can correct forward, but not backward.

Zero-downtime migration is the discipline of avoiding all three: staying compatible while two versions are running; keeping locks short and operations concurrent; and postponing anything destructive until it is safe.

Expand / contract

The underlying pattern is always the same: you never change things in place; you add the new, move over to it and remove the old — in separate, individually deployable, backward-compatible steps.

Expand extends the schema additively: a new column, a new table, without anything disappearing. Migrate bridges the gap: for a while you write to old and new at the same time and backfill the existing data in the background. Contract shrinks things back down: only when nothing reads or writes the old anymore is it removed. Each of these steps is backward-compatible on its own — and therefore individually deployable and individually reversible, except for the last one.

Diagram: no field is changed; it is added, filled, and then the old one is removed.

A concrete example — a column name is to be renamed to full_name, which in a single step would break the old version. Expand: you add the new column full_name additively without touching name. Migrate: from now on the code writes to both columns, and a background job copies the existing values from name to full_name. Once everything is filled and verified, the code switches reads over to full_name — up to this point, every step can be rolled back. Contract: when no version reads or writes name anymore, the old column is removed. A dangerous rename has become five harmless steps.

The recommendation: break every breaking change down into expand, migrate and contract instead of doing it in one step. The price is that one change becomes several deployments over days, and two states have to be maintained side by side for a while. We decide differently for a system with an acceptable maintenance window and a small amount of data; there, a one-off change within the window is simpler and cheaper than the multi-step dance.

Backward-compatible schema evolution

The rule behind expand/contract can be stated precisely: every schema state has to fit the currently running code version and the next one. As long as that holds, you can deploy and roll back at any time without touching the database.

Whether a single operation satisfies this rule in one step depends on its type — and, depending on the database, on the details:

The additive half is almost always harmless; the removing and the modifying halves never are. Every rename, every type change, every removal becomes a chain of an additive step, a move and a late cleanup.

Two of the safe paths deserve an explanation, because they often come as a surprise. In many databases, an index can be created concurrently without locking the table for writes — slower than the locking path, but without downtime. And a constraint can be introduced in two phases: first effective only for new rows, without immediately checking the existing data, and then validated in a separate, non-locking step. In both cases, you break a locking operation down into an invisible one — the exact procedure varies by database, the pattern does not.

The recommendation: allow only schema states that fit the running and the next code version, and break everything else down. The price is thinking ahead on every change — you plan two or three steps where, naively, there would be one. We decide differently for an internal tool that can be stopped briefly; there, the simplicity of a one-step change outweighs avoiding a minute of downtime.

The deploy sequence

Compatibility doesn't happen by itself; it comes from the order in which schema and code deployments interlock. The invariant is simple: the schema is expanded before the code that needs it, and cleaned up after the code that no longer uses it. In between, the steps run in a fixed order.

In detail: first the schema is expanded (step 1). Then you deploy code that writes to old and new at the same time and, where available, reads from the new (step 2). Concurrently, the backfill fills in the existing data (step 3). Only after that do you deploy code that reads only the new (step 4). And at the very end, when no running instance touches the old anymore, it is removed (step 5). No step assumes that another has already been fully rolled out — each is complete and compatible on its own.

Diagram: expand first, then move over, remove last — every state in between is compatible with both versions.

A schema change and the breaking use of it are never packed into the same deployment. Every app deployment is rolling, and at every moment every running instance finds a schema that fits it.

The recommendation: deploy schema and code separately and in this order — expand before use, contract after replacement. The price is a longer delivery cycle with several coordinated steps instead of a single release. We decide differently for a purely additive change with nothing being replaced — a new table for a new feature; there, one step suffices, because nothing existing is being switched over.

Feature flags and migrations

Deploying the code and activating the new behavior are two different things — and a feature flag separates them. Schema and backfill can have been in place for a long time while the new behavior is still switched off behind a flag. Once the backfill has been verified and consistency confirmed, the flag switches reads over to the new column. If something is wrong, you switch back — without a new deployment, in seconds.

This turns a risky cutover date into a reversible, observable switch. That is the real gain: the most dangerous second of a migration — switching over reads — is decoupled from the deployment and can be reversed at any time. A flag also lets you run the switchover not as all-or-nothing but initially for a small share of traffic; you observe, and expand when nothing deviates. That way, even the last, most dangerous second becomes gradual and observable instead of a leap into the dark.

The recommendation: decouple the switchover of reads from the deployment with a feature flag. The price is additional state: a flag is a code branch that has to be tested and — importantly — removed again after the migration; a forgotten flag is permanent debt. We decide differently for a small, low-risk switchover whose reversal via a quick redeploy is cheaper than maintaining a flag.

Data migrations vs. schema migrations

Two things are often lumped together but behave completely differently. A schema migration changes the structure — it is usually short but can lock. A data migration moves or transforms rows — it can be huge and long and must never run in a single locking step.

The critical mistake is putting a large data migration into the deploy step: it blocks the deployment and may lock the table. The right way is to run it as a controlled background job — in batches, idempotent and resumable, throttled so it doesn't overload the database, and with visible progress.

Batch size is the central adjustment knob here: small batches keep each transaction short and locks minimal but require more passes; large batches are faster but risk long transactions and pressure on the database. Between batches, the job pauses briefly, observes the load and throttles itself when response times or replication lag rise. A checkpoint after each batch makes it resumable: if it aborts, it doesn't start over but picks up from the last checkpoint — and because it is idempotent, a batch processed twice does no harm either.

The recommendation: run data migrations concurrently, batched, idempotent and throttled — separate from the deploy. The price is more machinery than a single script: progress tracking, resumption and throttling all have to be built. We decide differently for small amounts of data that can be rewritten in a short, non-critical step; there, a one-off run is simpler than a resumable job.

Rollback strategy

The most important insight of zero-downtime migration is also the most reassuring: you roll back the code, not the database. Because every schema state is backward-compatible, rolling back the application is always safe — the extra column simply sits there unused and bothers no one. The database doesn't need to be touched for this.

The only exception is the contract step. A dropped column doesn't come back. That is why contract is the last step, carried out only once confidence has been built through real traffic and nobody needs the old anymore. Until then, you keep the old column and its data.

This has a practical consequence for migration tooling. Many tools allow a "down" migration that automatically reverses a change. For additive steps, it is harmless; for the contract step, it is better not to write one at all. An automatic reversal meant to restore a dropped column along with its data cannot bring the data back — it only creates a deceptive structure without content and lulls you into a false sense of security. The honest way back from a mistake after the contract is always forward: rebuilding what is missing, not turning back the clock.

The recommendation: design every migration so that a rollback is possible purely by rolling back the code, and postpone anything destructive until last. The price is that the old structure runs alongside for a while, and you need the discipline to actually carry out the contract step later. We decide differently for an additive change with no destructive part; there, nothing is final, and the care applies only to locking behavior.

Observability during the migration

A zero-downtime migration depends on seeing what is happening — hope is not a strategy. Four things need to be monitored, from the very first minute, not only after the first incident.

First, locks: if a migration is waiting for a lock or holding one, requests pile up — that has to be visible immediately. Second, replication lag: a heavy backfill can make replicas fall behind, so reads see stale data. Third, error rate and response times during and after each step, so a regression can be attributed to a step right away. Fourth, the consistency of the dual writes: before switching over reads, you compare old and new and switch only once they match. You don't run this comparison once but continuously throughout the migrate phase — via sampling or a checksum of both columns — until the discrepancy is consistently zero. A remaining discrepancy is almost always a forgotten write path that isn't dual-writing yet, and that is exactly what you want to find before switching over reads.

The recommendation: monitor locks, replication lag, error rate and dual-write consistency across every step. The price is instrumentation and vigilance that produce nothing visible on a smooth day. We decide differently for a small, additive change without a backfill; there, normal operational monitoring is enough, because neither locks nor lag nor dual writes are involved.

Common mistakes

The same patterns keep turning a calm migration into an outage:

• The breaking change in a single step — renaming, dropping, changing a type without expand/contract.
• Schema and code change in the same deployment, so the old version breaks on the new schema.
• A long-locking operation on a large table in the middle of a deploy.
• The backfill in a single huge transaction that locks and starts over from scratch if it aborts.
• A data migration that is neither idempotent nor resumable.
• The old column removed too early, while the old version is still running.
• A destructive database rollback that loses data irretrievably.
• No monitoring of locks and replication lag — the migration runs blind.
• The contract step that never comes: flag and transitional column stay forever, the intermediate state becomes the permanent state.

Decision checklist

Questions to ask before every schema change. They are diagnostic questions, not verdicts.

• Does every schema state fit the running and the next code version? Otherwise the rolling deployment breaks.
• Is every step individually deployable and reversible — except the final contract? An irreversible intermediate step is a hidden maintenance window.
• Does an operation take a long lock on a large table? Then run it concurrently or break it down.
• Is the data migration batched, idempotent, resumable, throttled — and outside the deploy? Large backfills don't belong in the deployment step.
• Can we go back purely by rolling back the code, without touching the database? That is the litmus test for backward compatibility.
• Is the destructive contract step postponed until confidence is established? Removal is final.
• Are we monitoring locks, replication lag, error rate and dual-write consistency? Migrating blind means hoping.
• Is there an owner and a date for removing the transitional column and the flag? Otherwise the intermediate state stays.

FAQ

Do we need this for every change? No. An additive, nullable column or a new table is a single safe step. The full expand/contract sequence only applies to breaking changes — renaming, dropping, changing a type. The effort is determined by the type of change, not by habit.

Isn't a short maintenance window simpler? Sometimes, yes. For small systems, small amounts of data and tolerable downtime, a one-off change within the window is cheaper than the multi-step sequence. Zero downtime pays off when downtime is expensive or when the data volume makes an operation long enough that "short" no longer holds.

How long do we keep the old column? Until nothing reads or writes it anymore and confidence has been built through real traffic. Then — and only then — comes the contract step. The old column is your return ticket; you don't throw it away while you might still need it.

Does this also apply to schemaless databases? Yes. "Schemaless" only means that the schema lives implicitly in the code instead of in the database. The shape of the data still changes, old and new versions read the same documents, and expand/contract applies unchanged — extend additively, move over, remove the old field last.

Who runs the data migration? A controlled background job, not the deploy. It runs concurrently, in batches, throttled and resumable, with visible progress. That way it blocks neither the deployment nor the database and can be paused and resumed at any time.

What if the backfill takes days? That's fine. It runs online and throttled without disrupting the system; the switchover of reads simply waits until it is finished and verified. A long backfill is not a problem as long as it is concurrent and resumable — only a locking one would be.

Further reading

• Legacy modernization without a big bang — the same expand/contract idea applied to entire systems.
• API versioning without breaking clients — additive, backward-compatible change at the interface.
• When tests really pay off — why the dual-write consistency check is the most important test of the migration.
• Idempotency and PostgreSQL: JSONB vs. tables — related building blocks at the data layer.

It is grounded in the Batunet Engineering Method: build in small, reversible steps, design for failure, observability from day one.

Nobody notices a successful database migration. No maintenance window, no midnight nerves, no moment when everything is at stake — just a column that appears, fills up and eventually disappears, while the system keeps running the whole time.