Snapshot to Stream: Establishing a Position Without Losing Writes

A capture stream that begins at position $P$ describes every change committed after $P$. It says nothing about the rows that existed before $P$, so a new consumer needs both a copy of the table as of $P$ and the log from $P$ onward. Copying the table takes minutes to hours. Reading the log starts now. The two operations must agree on a single instant, and nothing in a general-purpose database makes that free.

Every capture system solves the same problem: define a point in the source’s history, copy the table as of that point, and start the log there. The three families of solution differ in who holds the source still while the copy runs, and each pays a different price.

The window that has to be empty

Suppose the snapshot is taken with a plain SELECT and the log position is read after the copy completes. Any transaction that committed during the copy is in neither the snapshot (which reflects an earlier state, or several states as the scan advances) nor the log stream (which starts after the copy). Rows are lost, and nothing reports it.

Reverse the order: read the position first, then copy. Any transaction that commits between the position read and the copy’s snapshot is in the log and also in the snapshot, so it arrives twice. Duplicates are recoverable, since an idempotent sink absorbs them, while gaps are not. This asymmetry is why the safe ordering is always position-before-copy, and why reconciling a load is a check on the gap direction rather than on the duplicate direction.

But even the safe ordering is not sufficient on its own, because a copy that runs for an hour is not a snapshot at a single point. Under read-committed isolation, each statement in a multi-statement scan sees a different snapshot, so a table copied in chunks over an hour contains rows from many points in time. A row modified halfway through can appear in the copy in its old form, while its modification is also in the log, and the sink sees the old version before the new one only if the copy chunk containing it was read before the modification. If the chunk was read after, the sink sees the new version and then the log’s change, which is harmless. The harmful case is an update and a delete: the copy reads the row before the update, and the log delivers the delete, and the result is consistent. The harmful case is a row inserted into a chunk already copied: it appears in the log only, and the sink has it once. Consistent. The genuinely harmful case is a row whose key falls in a chunk not yet copied, which is deleted before the copy reaches it: the log delivers the delete for a row the sink has never seen, and a sink that treats delete-of-absent as an error will halt.

Chunked copying therefore needs either a consistent snapshot for the whole copy or a per-chunk watermark with log replay, and the two approaches lead to different designs.

Holding a snapshot open for the length of the copy

The most direct solution uses the engine’s own snapshot mechanism. The consumer opens a transaction at repeatable-read or an exported snapshot, records the log position that corresponds to that snapshot, and copies the table within the transaction. Every statement in the copy sees the same point in time, so the copy is internally consistent, and the recorded position is the boundary.

The mechanism is real and specified, but its cost is not small. A transaction held open for hours prevents vacuum from reclaiming dead tuples, because those tuples may still be visible to the open snapshot. On a table with heavy update traffic, the bloat grows at the rate of the update volume times the copy duration. The same holds for the log: the position is held from the moment it is recorded, so the server retains log for the whole copy, and the retention pressure described in log-based capture applies for hours rather than seconds.

The practical consequence is that snapshot-and-copy in one transaction works for small tables and short copies, and becomes an operational hazard as the table grows. Engines and capture systems that use this approach document a maximum transaction age, and a copy that exceeds it is aborted and retried rather than allowed to run.

Consistent snapshots exported to a separate reader

A refinement separates the snapshot from the copy. One process establishes the snapshot and exports it, another attaches to the exported snapshot and performs the copy. The exported snapshot carries the same visibility rules but not the same transaction, so the copying session can commit between chunks and the snapshot holder can remain a lightweight transaction.

PostgreSQL’s pg_export_snapshot and SET TRANSACTION SNAPSHOT provide exactly this: a session exports a snapshot identifier, another session adopts it, and both see the same point in time. The exporting transaction still has to stay open, so the vacuum problem remains, but the copying work moves off it. In practice the exporting session is idle apart from holding the snapshot, which reduces lock contention and lets the copy be parallelised across several readers sharing one snapshot.

The log position is recorded in the exporting session, which is the session whose snapshot defines the boundary. Getting this wrong, by recording the position in the copying session, reintroduces the gap, because the copying session’s view is the exported snapshot while its log position is read at a later moment.

Watermark per chunk, with log replay on top

The third family avoids holding anything open. The copy is chunked by key range, and each chunk records the log position at the moment the chunk was read. The stream is then consumed from the earliest chunk position, and changes are applied to the sink. Because the copy and the stream overlap, a row can arrive from the copy and again from the stream, so the sink must be idempotent by primary key.

This is the design most capture systems use, because it tolerates arbitrarily long copies, allows the copy to be paused and resumed, and never holds a snapshot open. Its correctness rests on three conditions:

• Every chunk’s read is internally consistent, or the chunk is small enough that the inconsistency is bounded and corrected by the stream. A chunk read under read-committed can miss a row inserted into the middle of the range after the chunk’s scan passed that point; the stream will deliver that insert, because the insert’s log position is after the chunk’s recorded position. So the missing row is repaired.
• The stream starts at or before the earliest chunk position. Starting later leaves a gap for the chunk whose position is earliest.
• Applying a change to a row the sink does not yet have must be tolerated. A delete or update arriving before the copy reaches that row is applied to nothing, and the sink must either buffer it or apply it as a tombstone that the later copy insert respects.

The bookkeeping for a chunk is small enough to state exactly, and its ordering is the whole of its correctness:

for lo, hi in key_ranges:
    rows = read_chunk(lo, hi)          # re-read entirely if the process dies here
    pos = current_log_position()       # monotonic; greater than every earlier chunk's
    sink.write(rows, version=pos)      # stamped so a later stream change wins
    meta.record_chunk(lo, pos)         # durable only after the sink write

Recording the position before the sink write converts a crash into a gap; recording it after converts the same crash into a duplicate that the version stamp absorbs.

The third condition is the subtle one. If the sink is a table with plain inserts, a copy insert that arrives after a stream delete of the same key will insert a row the source no longer has. The sink needs either a tombstone table consulted by the copy writer, or an upsert with a version column so the older copy version loses to the newer delete. A version column derived from the log position works because log positions are monotonic: every stream change carries a position greater than the chunk’s, so a copy row stamped with the chunk position can be rejected by any stream change for the same key.

Which position is the boundary

The boundary must be a position in the same ordering the stream uses. For a log-based feed that means the log’s own position: a WAL LSN, a binlog file and offset, a change stream’s resume token. For a polling feed, it means the composite cursor from cursor columns and incremental boundaries, and the copy must be filtered to cursor <= boundary while the stream starts from cursor > boundary.

Using a wall-clock time as the boundary is the common mistake. A timestamp does not identify a point in the log, so the stream cannot be started exactly there; it starts at the first record at or after that time, and any transaction that committed before that record but after the timestamp is missed. Timestamps are usable as a coarse boundary with an overlap window, and not otherwise.

Restart during the copy

A copy that takes hours will be interrupted. The design must specify what happens when the process dies mid-chunk.

The chunk positions must be durable before the chunk’s data is considered delivered, or the restart re-copies the chunk. Re-copying is harmless if the sink upserts, and expensive if the chunk is large. Recording the position after the chunk’s write completes gives at-least-once behaviour for the chunk; recording before gives at-most-once and a gap on failure. The same ordering argument as for offsets applies, which is the subject of offset commit ordering.

A restart must also re-establish the stream position. If the stream advanced past the earliest chunk position while the copy was interrupted, and the chunk is re-copied from a later point, the stream cannot be rewound. The safe pattern is to hold the stream position at the earliest unfinished chunk until the copy completes, which costs retention for the duration of the copy, the same cost as holding a snapshot but paid in log bytes rather than in dead tuples.

The residual duplicate window is not optional

Every one of these designs delivers some rows twice, and the only question is how many. The snapshot-and-hold design duplicates nothing if the boundary is exact, but the boundary is exact only if the position is read inside the same transaction as the snapshot, and even then a transaction committing during the position read can straddle it.

The chunked design duplicates everything modified during the copy, which for a long copy over a hot table is the working set.

The conclusion is architectural rather than procedural: a capture pipeline’s sink must be idempotent on (key, version) regardless of how careful the snapshot phase is. The snapshot phase determines how much duplication occurs, and the sink determines whether duplication is observable. Systems that claim otherwise are claiming an atomic snapshot-and-stream boundary that most sources cannot provide.