CDC is a consistent snapshot joined to a log position; the seam is where rows go missing

pattern
established
fragment

Scope. The snapshot/stream alignment is guaranteed by PostgreSQL only for a snapshot exported when the slot is created (or a watermark scheme like DBLog); the card’s claims about Debezium describe its documented PostgreSQL connector, and other databases and connectors expose different primitives. Exactly-once apply is not provided by any of these layers — it is the sink’s job.

Versions. PostgreSQL 18 (flow verified on 18.6: CREATE_REPLICATION_SLOT … SNAPSHOT 'export', SET TRANSACTION SNAPSHOT, pgoutput proto_version 4; import fails once the exporting connection runs another command); failover slots need PostgreSQL 17+. Debezium 3.6 PostgreSQL connector.

Conflicts with: Lock-free incremental snapshot ↔ schema changes during the snapshot

Primary sources: PostgreSQL 18 — Logical decoding concepts (replication slots, exported snapshots)

References: PostgreSQL 18 — Streaming replication protocol (CREATE_REPLICATION_SLOT, START_REPLICATION), PostgreSQL 18 — SET TRANSACTION, PostgreSQL 18 — Logical replication message flow (Relation messages, proto_version), PostgreSQL 18 — Logical replication restrictions (DDL is not replicated), Debezium 3.6 — PostgreSQL connector (snapshots, incremental snapshots, primary key updates, WAL disk space, failure behaviour)

Other claims on this card, typed separately:

- pattern
- established
A watermark-bracketed, primary-key-chunked select, with chunk rows dropped for every key changed between the watermarks, interleaves a full table extract with the change log without locks and without a chunk row overwriting a newer logged change, given non-stale reads and a log in commit order.
Primary sources: Andreas Andreakis, Ioannis Papapanagiotou — DBLog: A Watermark Based Change-Data-Capture Framework (2020)

A change stream is not a table: it starts at some log position, and everything before it has to come from a snapshot. Correctness is a single invariant — the snapshot contains exactly the commits up to position P, and the stream starts at P. PostgreSQL provides it directly: creating a logical slot can export a snapshot showing exactly the state after which every change is in the slot’s stream. “Copy the table, then start from the current LSN” leaves a gap (lost changes); the reverse order leaves an overlap (duplicates, harmless only for an idempotent sink — 22).

What the seam does not fix. Delivery is at-least-once at every layer: a slot persists its position only at checkpoint, so after a server crash recent changes are sent again, and Debezium can emit duplicates while it recovers — the sink applies by primary key and skips events not newer than the LSN it already holds (21). Per-row order survives only if the message key is the primary key (92); Debezium turns a primary-key update into a delete for the old key and a create for the new one. The slot pins WAL and the catalog rows VACUUM would remove, so a stalled consumer fills the primary’s disk, and a database whose captured tables are quiet needs heartbeats to let the slot advance. DDL is not replicated: pgoutput describes a table’s shape only in the relation message sent before its next change, so a new column is seen when a row changes, not when it is added — schema changes go through expand — migrate — contract (17, 87). A slot that did not follow a failover (logical slots sync to standbys only from PostgreSQL 17, with FAILOVER) means a new snapshot.

Resnapshot without stopping the stream. A full re-copy under a lock does not scale to a live table, and replay (80) needs it routinely. DBLog’s watermark algorithm does it without locks: write a low watermark into the log, select the next primary-key chunk, write a high watermark; while reading the log between the two, drop from the chunk every key that changed there; at the high watermark emit what is left. A chunk row therefore never overwrites a newer logged change. It needs a primary key with an efficient range scan and reads that see every commit made before them. Debezium’s incremental snapshots follow the same design (1024-row chunks by default), and its PostgreSQL connector does not support schema changes while one is running.

fragment

PostgreSQL 18 · replication protocol

-- Session 1: a replication connection, e.g. psql "dbname=shop replication=database"
CREATE_REPLICATION_SLOT cdc_orders LOGICAL pgoutput (SNAPSHOT 'export');
--  slot_name  | consistent_point | snapshot_name       | output_plugin
--  cdc_orders | 0/1799278        | 0000007E-00000002-1 | pgoutput
-- The exported snapshot lives only until this connection runs its next command.

-- Session 2: copy exactly the state the slot's stream continues from
BEGIN ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION SNAPSHOT '0000007E-00000002-1';
COPY orders TO STDOUT (FORMAT binary);
COMMIT;

-- Session 1, once session 2 has imported the snapshot:
START_REPLICATION SLOT cdc_orders LOGICAL 0/1799278
  (proto_version '4', publication_names 'orders_pub');
-- Streams every change committed after the snapshot and none before it.
-- After a server crash the slot may resend from its last checkpointed position,
-- so the sink upserts by primary key and ignores events whose LSN it already applied.