CDC and snapshots: do not lose changes in the gap

Fortex is fictional. Evidence in this article comes from PostgreSQL logical decoding in the Docker Lab. The lab demonstrates the ordering boundary; it does not run a complete Debezium cluster.

A gap that lasts only milliseconds

After the payment incident, Hà asked Ben to rebuild the catalog search index. His plan was concise: read every row in products, then start CDC for new changes.

Tom listened and asked, “New since when?”

While the long SELECT scans the table, a merchant changes product 7 from 100 to 110. If the connector starts reading WAL only after that update, the snapshot contains 100 and the stream never contains 110. Both stages report success while the index stays stale forever.

A snapshot is not merely a file copy

CDC bootstrap must join two timelines:

• a baseline, containing rows at a consistent point;
• a change log, containing every change after that point.

Call the log boundary L0. The required relationship is:

$$Projection = Snapshot(L_0) \oplus Changes(L_0, now]$$

The \oplus operator applies changes by identity and ordering; it does not concatenate two lists. If the snapshot represents a point before L0 and an update happens before logging begins, that update falls through the gap.

The Debezium PostgreSQL connector takes a consistent initial snapshot and resumes streaming from the recorded log position. During incremental snapshots it can buffer READ rows and discard a snapshot row when a newer streamed event supersedes it.

Lab: create the slot early and late

The lab runs PostgreSQL 17.6 with wal_level=logical and test_decoding. Both schedules begin with price=100.

In the unsafe schedule, the baseline reads 100, an update writes 110, and the replication slot is created afterward. The slot cannot recover a change from before its boundary, so the projection remains 100.

In the safe schedule, the log boundary is established first, the snapshot reads 100, and the update writes 110. Draining the logical log then moves the projection to 110.

Schedule	Snapshot	WAL contains 110	Final projection
Slot created after update	100	No	100
Slot created before snapshot/update	100	Yes	110

• Report JSON
• Raw log
• Docker Lab source
• Run ID: 6cdf03cd-d54e-417c-a3dc-59f4d9d4462f

This proves an ordering boundary, not Debezium throughput. A full connector also handles schemas, offsets, snapshot modes, and more failure cases.

What about duplicates?

Ben noticed that a row can appear in both the snapshot and stream. Preventing loss does not prevent repetition. A sink should upsert by primary key and use a source position or domain version to reject older state. An append-only sink needs a change identity or a separate materialization step.

An LSN can order changes from one PostgreSQL source. It is not a global clock for comparing events from two independent databases.

A replication slot is operational debt

PostgreSQL retains WAL required by a slot that has not consumed it. The logical decoding documentation explains how a slot preserves state so changes remain available. A connector that stays down can therefore fill the disk with retained WAL.

Production needs to monitor restart_lsn, confirmed_flush_lsn, retained WAL size, connector lag, poison events, snapshot progress, and sink lag. Dropping a slot frees WAL but also abandons that recovery position, often forcing a resnapshot.

When is CDC unnecessary?

For a small table with a maintenance window, briefly stopping writes and copying may be simpler. For a rebuildable projection, a batch on updated_at plus reconciliation can be adequate when delay and timestamp ties are acceptable. CDC earns its complexity when continuous writes, low delay, and a clear replay trail matter.

Ben redrew the bootstrap. Snapshot and CDC were no longer separate boxes. A thick line labeled L0 joined them.

Tom added one note: “Every online migration must answer where writes go while the copy is running.”

The following morning the search index contained every update, but an order projection moved backward from PAID to CREATED. Nothing was missing. The system had trusted the wrong clock.