Bulk Copy and Change Stream: Making an Online Copy Consistent

The online copy problem is stated simply: take a copy of a live dataset, then apply everything that changed while the copy was in progress, and end up with a state indistinguishable from the source at the moment of the switch. The statement hides the hard part, which is that “everything that changed” is only meaningful relative to a point in time, and the copy does not have a single point in time unless it was made to have one.

A copy has no time unless the source gives it one

Copying a table row by row produces a result that corresponds to no moment in the source’s history. If the copy takes an hour, a row read at the start reflects the state at the start and a row read at the end reflects the state at the end, and a row that was updated in between may be read once in its old form and never in its new form, or the reverse. The copy is not stale; it is incoherent.

Two mechanisms give a copy a time. The first is a point-in-time snapshot: the storage layer presents a consistent view of the data as of a single instant, and subsequent writes do not appear in that view even though they proceed. Copy-on-write filesystems, storage array snapshots, and MVCC databases all provide this in different forms. The second is a position in a change stream, recorded at the instant the snapshot is taken. With a snapshot and a position, the copy plus the changes after that position equals the source as of the position.

Databases expose both, and the ordering between them is the subtle part. In PostgreSQL logical replication, a snapshot is taken and the transaction that created it has an identifier; replication slot positions are expressed as a log sequence number, and the consistent point recorded for a new subscription corresponds to the snapshot. In MySQL, a consistent dump is taken with a transaction that has recorded its binary log coordinates, and the dump file carries those coordinates in its header. In both cases the rule is the same: the position must be captured inside the same snapshot context as the data, not afterwards and not before. Capturing it after the copy starts means changes made during the copy are replayed twice for some rows and not at all for others.

This is the reason a naive implementation fails in a specific and quiet way. If the copy is taken without a snapshot and the stream is started at the position recorded when the copy begins, every row updated during the copy arrives at the destination in its final form from the stream and in its old form from the copy, and the order in which the two are applied decides which one survives. Some destinations apply the stream last and converge; others apply the copy last and lose the updates. The failure is deterministic per row and invisible without a comparison.

The catch-up phase terminates only if the source is bounded

Once the copy completes, the destination has the snapshot state and must apply the backlog of changes accumulated since the recorded position. The backlog’s size is the change rate multiplied by the copy duration. If the source produces changes faster than the destination can apply them, the backlog grows rather than shrinks, and the catch-up never completes. This is the fundamental constraint of the approach: the apply rate must exceed the change rate, with enough margin to drain the accumulated backlog.

Two things can break that inequality. The first is a burst: a nightly batch that rewrites a large fraction of the dataset produces changes faster than any apply process can consume, and the backlog that accumulates during the burst must be drained afterwards. A cutover scheduled immediately after a burst will not converge. The second is amplification: a single source change can require many destination operations. A row update becomes an index update, a materialised view refresh, and a cache invalidation. If amplification exceeds the ratio of apply capacity to change rate, the backlog diverges permanently.

Long-running transactions interact badly with both. A transaction that stays open for hours holds a position in the stream that cannot be passed, because its changes are not yet committed and cannot be applied. The backlog measured by bytes continues to grow while the visible position stalls. A cutover waiting on the position to reach the head will wait for the transaction to end, which may be never. The mitigation is to identify and bound transaction duration at the source before the copy begins, and to treat any transaction exceeding a threshold as a blocker rather than an anomaly.

What the change stream must carry that a log does not

A physical replication log carries page-level changes. It is efficient and it is useless for a migration that changes the storage engine, the schema, or the vendor, because the destination cannot interpret the pages. Logical decoding reconstructs row-level operations, which is what a heterogeneous migration needs, at the cost of more CPU at the source and a dependence on the schema being published in a form the decoder understands.

Even logical change streams lose information that the application may depend on. The three common gaps:

• Ordering across partitions. A stream that is partitioned for throughput guarantees order within a key, not across keys. Two changes to related rows in different partitions can arrive in either order, and a destination that enforces a foreign key will reject the pair regardless of order.
• Transaction boundaries. Row-level streams often emit changes without marking which transaction they belonged to. A destination applying them one at a time cannot reproduce the atomicity the source had, so a partial failure leaves a state the source never occupied.
• Schema changes. A DDL statement in the middle of the stream changes the meaning of subsequent records. Destinations that apply records with a fixed schema either fail or silently truncate.

A stream that carries transaction identifiers and commit markers, and that pauses on DDL, is worth considerably more than one that does not. The cheap way to obtain these properties is to use the source’s own logical replication facility, which was designed with them, rather than a trigger-based capture that reconstructs them imperfectly.

The switch is a decision, not an event

The destination is caught up when the applied position equals the source’s current position and the source is no longer accepting writes. Those two conditions cannot both hold while the source serves traffic, so the switch requires a moment in which writes stop. The duration of that moment is the write downtime, and it is the only downtime the approach imposes.

The sequence has a standard shape. First, quiesce writers at the source, either by draining them or by putting the source into a read-only mode. Second, wait for the apply position to reach the final source position. Third, verify that the counts and checksums agree. Fourth, redirect clients. Fifth, release the source’s read-only state if rollback remains possible, or leave it in place if the source is being retired.

Steps two and three are where plans slip. The wait in step two is bounded by the apply rate and the residual backlog, and the residual backlog is whatever accumulated while the quiesce was in progress, which is not zero because draining takes time. The verification in step three is bounded by the size of the dataset if it is done exhaustively, and by the probability of a missed discrepancy if it is sampled. Neither bound is knowable in advance with precision, so the write downtime has a floor set by physics and a ceiling set by the verification method chosen.

A practical consequence: the copy and catch-up can run for days, but the switch window should be measured in minutes, and the plan should be rehearsed against a copy of the source rather than against the source itself. A rehearsal on a full-size copy reveals the apply rate, the verification duration, and the behaviour of the long transactions, none of which can be estimated reliably from first principles.

When the stream cannot be obtained at all

Some sources expose no ordered change stream. A file share, an object store, a mainframe batch system, and a third-party API without a cursor all fall in this category. The substitute is a change detection pass: enumerate the source twice, compare, and copy the differences. It works, and its cost is proportional to the size of the enumeration rather than the size of the change, so it converges slowly on large datasets and it cannot distinguish a delete from a rename without a stable identifier.

Where that cost is unacceptable, the alternative is to shorten the copy rather than to make it incremental: reduce the dataset that must move by leaving archival data behind, or accept a stop-and-copy window. The choice between these is a choice about how the boundary was drawn, and it is worth revisiting the boundary before investing in machinery that the source cannot support. Once a stream exists, its lag is also the quantity that tells an operator whether the destination is keeping up, which makes lag measurement the central operational signal during the sequencing of the cutover.