Move Data Without Downtime

Moving a database sounds suspiciously simple. Copy the data, change the connection string, celebrate.

Unfortunately, your users were not informed that they should stop doing things while the copy runs. Somebody just signed up. Somebody changed their address. Somebody deleted the exact record your script is currently admiring.

That is the interesting part of a migration: how do we move the existing data while correctly handling everything that changes during the move?

Airbnb's Mussel migration is a great example at enormous scale. Here is the underlying problem, followed by a smaller example we can reason through without owning a petabyte.

What Zero Downtime Means

The goal is to keep the product available and preserve every acknowledged write during the migration.

That does not mean every request has identical latency. A controlled handoff might briefly queue requests while the new database catches up. If that queue outlasts the application's timeout budget, users still experience an outage. Calling it a queue does not make the outage disappear.

There are two separate promises to protect: availability and correctness. A website that stays online while quietly losing orders has achieved neither of the things we actually wanted.

Start With One Source of Truth

During the copy, the old database remains the authority. The application continues reading and writing there. The new database is a replica under construction, not a second independent place to accept business writes.

The basic arrangement is:

Application → Old database
                  ├→ Consistent snapshot → New database
                  └→ Committed change log → New database

The snapshot brings over existing rows. The log brings over committed inserts, updates, and deletes that happen around that snapshot boundary.

This is change data capture, usually called CDC. For example, Debezium's PostgreSQL connector coordinates an initial snapshot with continued changes from the database log. The important property is the connection between those two stages, not the logo on the connector. Debezium explains that handoff here.

Copy Without Leaving a Gap

The dangerous approach is “finish copying, then start watching for changes.” A row can change between those steps and never reach the destination.

Instead, use a migration tool that establishes a consistent snapshot and the corresponding log position. Keep the required log history available while the copy runs, then replay from that boundary. PostgreSQL's exported snapshots support exactly this relationship between a database snapshot and its change stream. PostgreSQL snapshot documentation.

Imagine this sequence:

Snapshot boundary: order 42 is pending
During the copy:   order 42 becomes paid
Snapshot loaded:  destination has pending
Change replayed:  destination has paid

If a tool overlaps the copy and replay, it needs an explicit reconciliation strategy. An older snapshot row must never overwrite a newer change. Copying first and replaying afterward is easier to explain; overlapping them safely needs more coordination.

Copy in bounded chunks and save progress. Watch source latency, target capacity, and the amount of retained log data. Throttle the migration before it starts competing with customers for the last available database connection.

Make Retries Harmless

A worker can apply a change and crash before recording that it finished. After restarting, it may receive the same change again.

This is why idempotency matters. Applying the same source change twice should produce the same result as applying it once.

The destination writer needs a few rules:

• Keep a stable record key. Match the same source row to the same destination row.
• Preserve order for each key. Do not let an older update replace a newer value. Use the source's ordering metadata or a source maintained version, not whichever machine's clock looks most confident.
• Handle deletes too. Where events can arrive out of order, retain a deletion marker and its version until older events can no longer resurrect the record.
• Checkpoint after durable writes. When possible, commit the applied changes and checkpoint together. Otherwise, make replay safe before acknowledging progress.
• Preserve transaction boundaries. If an order and its items commit together, do not expose a half applied transaction to readers on the new database.

That last point depends on the migration pipeline and its configuration. Parallel consumers do not automatically preserve transactions spanning multiple tables. AWS DMS, for example, distinguishes transaction preserving apply from faster batch modes with different integrity tradeoffs. DMS migration guidance.

Why Not Just Write Twice?

It is tempting to add one more database call to the application:

Write to old database: succeeds
Write to new database: times out
Application crashes before retrying

Now the databases disagree, and nobody has a durable record saying the second write still needs work.

Two network calls are not one transaction. Dual writes need a recovery mechanism.

CDC avoids that particular gap by following changes already committed to the authoritative database. Another option is a transactional outbox: update the business row and an outbox record in the same transaction, then let a separate worker deliver committed outbox events. Delivery still needs retries, ordering, and deduplication. Every relevant write path must participate. AWS's outbox explanation.

Airbnb had another useful starting point: writes already entered a durable Kafka log, and both stores could consume it. Their dual write pipeline was not just two database calls and a hopeful expression. Airbnb's migration account.

Prove the Copy Is Correct

“The job finished” tells us that a program stopped running. I would like slightly more evidence before pointing production at its output.

Check row counts, missing keys, field values, and checksums over comparable data. Then check business rules: does every order still have its items? Are monetary values, timestamps, and nulls interpreted the same way?

Comparisons need a common point in the change history, or a comparison process that accounts for replication delay and rechecks mismatches. Comparing a moving source with a target that is behind can make healthy replication look broken. DMS validation documentation.

Shadow reads add another kind of evidence. Send selected real queries to both systems, return the old system's answer, and compare the new answer privately. Check query results and latency without exposing experimental responses to customers.

During a gradual read rollout, keep freshness requirements explicit. A user who just updated their address should not immediately see the old address. Keep that read on the source, or wait until the target has applied the relevant commit before serving it there.

Switch Who Accepts Writes

Changing reads and changing write ownership are different operations. The second needs a clear boundary.

For a system with a controlled application write path, I would plan the handoff like this:

1. Route new writes into a bounded queue and let existing source transactions finish.
2. Fence the old write path so stale application instances, workers, and pooled connections cannot keep committing there.
3. Record the final committed source position. Wait until the destination has durably applied everything through it, including complete transactions.
4. Complete final validation and database readiness checks.
5. Switch write ownership, release queued requests to the new database, and monitor errors and latency.

The queue and fencing are architecture requirements, not features every migration tool supplies. If the application cannot support that handoff within its latency budget, plan a short maintenance window instead of promising literal zero downtime.

Also check more than rows. Schema objects, permissions, triggers, indexes, and ID generators need attention. PostgreSQL logical replication does not automatically copy schema changes or sequence state; a copied table can look perfect while its next generated ID collides with an existing row. PostgreSQL replication restrictions.

A Checkout Migration

Here is an illustrative use case, not a claim about a specific company's production system.

A shop is moving its orders database to a new PostgreSQL cluster. Customers must still place orders, and payment callbacks must still update them.

• Before copying: inventory every writer, including the checkout API, payment callback handler, scheduled jobs, and admin tools. Give retried checkout requests an idempotency key.
• During copying: keep all business writes on the old cluster. Take a coordinated snapshot and stream committed changes into the new cluster.
• When payment arrives: order 42 changes from pending to paid on the source. CDC carries that committed update to the target, even if the snapshot contained the earlier value.
• When a worker restarts: replaying the update leaves order 42 paid. Applying replicated rows must not send a second receipt or charge the customer again.
• Before switching: validate orders with their items and totals, shadow selected reads, and verify the destination can keep up with incoming changes.
• At the handoff: queue requests, fence all old writers, drain through the final commit position, prepare sequences, and move ownership to the new cluster.

The payment callback arriving during the handoff is not discarded. It waits in the controlled write path and is processed once the new owner is ready. Retries use the same payment event ID so a timeout does not become a duplicate business action.

Rollback Needs Fresh Data

Before the destination accepts independent writes, falling back to the old source is relatively straightforward.

Afterward, the old source is missing new changes unless we deliberately keep it updated. Sending traffic back to a stale database is not a rollback. It is a second incident.

A reversible write cutover needs a tested reverse replication path or another durable replay mechanism, compatible schemas, and another fenced handoff. Prevent replication loops. Verify the old database has caught up before giving it ownership again.

If that path does not exist, say so in the runbook. Keep backups and define a recovery plan, but do not describe restoring yesterday's backup as preserving today's acknowledged writes.

The useful mental model is simple: copy the past, capture the present, verify both, and transfer ownership once. The database can change. The user's data should not become a casualty of the move.