A change-data-capture projector from Postgres into denormalized read models with ordering guarantees, exactly-once-effect checkpointing, and replay-from-LSN rebuilds that are byte-identical to the incremental projection, plus the fix for the lost-update bug that most hand-rolled CDC pipelines ship with: changes from transactions that commit out of LSN order.
-informational){kind=icon}
- Read-heavy services need denormalized documents (an order with its items and totals in one read), and keeping them current from a normalized source by hand is where data quietly diverges; this projector rebuilds documents from source on every change so they cannot drift.
- Rebuilding the whole read model from scratch must produce exactly what the incremental pipeline produced, or a rebuild is a guess; here replay-from-LSN is byte-identical, verified by an oracle.
- Hand-rolled CDC loses updates from transactions that commit out of LSN order, a bug invisible until production concurrency; this repo reproduces it in a test and fixes it with a transaction-visibility watermark.
CQRS and read models are the standard answer to "the write model is normalized but the read path needs a fast denormalized shape". The projector that keeps the read side current from the write side's change stream looks simple, and the simple version has two failure modes that only appear under load. The first is drift: apply changes as deltas to the existing document and any duplicate, reorder, or missed event corrupts a running total that nobody notices for weeks. The second is subtler and worse: the change log assigns positions (LSNs) when a change is written, but transactions commit in a different order than they write, so a projector that advances its checkpoint to the highest committed LSN it has seen will step over a lower LSN that was still in-flight, and that change is lost permanently.
This projector avoids both. It rebuilds each affected document from current source state rather than mutating the previous document (ADR-001), which makes projection idempotent: reprocessing a change recomputes the same document, so a crash-and-resume or a full replay cannot corrupt anything, and the document is a pure function of source state at an LSN. That purity is exactly what makes replay-from-LSN byte-identical. For the out-of-order commit hazard, each change is stamped with its transaction id and the projector only advances across changes whose transaction has settled (xid below the current snapshot xmin), stopping at the first in-flight change so the checkpoint never overtakes an uncommitted lower LSN (ADR-002).
The change stream here is emitted by triggers into a log table with a monotonic LSN, which a production deployment replaces with a real logical-replication reader (Debezium/pgoutput); the projector consumes it identically. The read model is Postgres JSONB documents standing in for a MongoDB collection, with a motor adapter for the real thing behind the same write.
This is the project's war story, reproduced as a test (test_out_of_order_commit_does_not_lose_updates) and fixed with the visibility watermark. Details below.
| Technology | Role in this project | Why chosen here |
|---|---|---|
| PostgreSQL 16 | Source, change log, checkpoint, read-model store | The checkpoint advance and the doc write commit in one transaction; the visibility watermark uses txid_current() and pg_snapshot_xmin |
| Triggers -> WAL table | Change capture | Emits the same ordered change stream a logical-replication reader would, runnable without a replication slot |
| MongoDB (prod adapter) | Denormalized read models | Modeled here as JSONB documents; motor writes the identical documents to a Mongo collection |
| asyncpg | Projector + writers | Async batch projection; the loop reaches ~900 changes/s live and ~2,900/s on replay |
| Rebuild-from-source | Projection strategy | Idempotent and replay-safe by construction (ADR-001) |
| Visibility watermark | Ordering correctness | The fix for out-of-order commit loss (ADR-002) |
| pytest + pytest-cov | Suite | 7 tests incl. the out-of-order regression and byte-identical replay; 94 percent measured |
| GitHub Actions | CI | Postgres service container; tests + benchmark smoke |
Prerequisites: Python 3.11+, Docker (Postgres), git.
git clone https://github.com/<you>/cdc-read-model-projector.git
cd cdc-read-model-projector
docker run -d -p 5432:5432 -e POSTGRES_USER=ledger -e POSTGRES_PASSWORD=ledger -e POSTGRES_DB=cdc postgres:16
python -m venv .venv && source .venv/bin/activate
pip install -e ".[dev]"
pytest --cov=cdc # includes the out-of-order-commit regression
python benchmark/bench.py # propagation latency, throughput, replay rebuildProjecting in code:
from cdc.projector import Projector
proj = Projector(pool)
await proj.catch_up() # apply new changes to the read model
await proj.replay_from(0) # rebuild the entire read model, byte-identicalMeasured on 2,000 orders / 6,971 changes (benchmark/results/bench.json), 2 vCPU, DB-stamped timestamps:
| Metric | Value |
|---|---|
| Propagation lag (commit to read model) | p50 4.3 ms, p95 7.0 ms, p99 8.6 ms |
| Live projection throughput | ~920 changes/s (under concurrent writes) |
| Full replay-from-LSN rebuild | 6,971 changes in 2.4 s (~2,900/s) |
| Divergence after replay | 0 (byte-identical, oracle-verified) |
ADR-001: rebuild documents from source rather than mutating deltas, which buys idempotency and the byte-identical replay for free. ADR-002: advance the checkpoint by a transaction-visibility watermark, not max LSN, and why that is the only correct answer under out-of-order commits.
- A real logical-replication reader. Triggers emit the same change stream; swapping in Debezium/pgoutput changes the source of the log, not the projector. The trigger approach is a legitimate CDC pattern in its own right.
- A live MongoDB. The read model is JSONB documents modeling a collection; the
motoradapter writes the identical documents to Mongo behind the same call. - Schema evolution of the source. Handled by rebuild-from-source for additive changes; a document-shape migration is a full replay, which this supports.
The projector reads the change log and source rows and writes documents; no external calls, DSN from config. The read model can contain denormalized copies of source data, so retention and access mirror the source's. The change log is append-only and is the audit trail of every change with its LSN and transaction id.
| Failure | Detection | Behaviour | Recovery |
|---|---|---|---|
| Out-of-order transaction commit | Visibility watermark | Lower in-flight LSN is not skipped; applied in order once settled | The war story; regression-tested |
| Projector crash mid-batch | Checkpoint not advanced | Re-reads from last committed checkpoint; rebuild is idempotent | No loss, no double-effect; tested |
| Duplicate change delivery | Rebuild-from-source | Recomputes the same document | Idempotent by construction (ADR-001) |
| Read model corrupted / lost | Oracle divergence | replay_from(0) rebuilds byte-identical |
Tested; 2.4 s for ~7k changes |
| Long-running writer transaction | Watermark lags behind it | Projection of everything after it waits (correctness over freshness) | Watch oldest-in-flight-transaction age; ADR-002 |
| Burned LSN gap (rolled-back txn) | No row at that LSN | Nothing to wait for; watermark advances normally | By design; would hang a naive gap-wait |
The bug I am proudest of catching is the one most hand-rolled CDC pipelines ship to production without knowing. My first projector advanced its checkpoint to the highest LSN of the changes it had read and committed. It passed every happy-path and crash test. Then I wrote a concurrency test that does the one thing production does constantly: two transactions where the one with the higher LSN commits first. Transaction A inserts an item (LSN 5) and stays open; transaction B inserts an item (LSN 6) and commits; the projector runs, sees LSN 6 committed and LSN 5 still in-flight, processes LSN 6, and advances the checkpoint to 6; then A commits, and its LSN 5 is now less than the checkpoint and is never read again. The item is gone from the read model forever, and nothing errors.
The naive instinct is to wait for LSN gaps to fill before advancing, but that hangs forever the first time a rolled-back transaction burns a sequence value and leaves a permanent hole. The correct fix is to advance by transaction visibility, not LSN value: each change carries its txid_current(), and the projector reads pg_snapshot_xmin(pg_current_snapshot()), the boundary below which every transaction is settled. It applies changes with xid below the watermark, in LSN order, and stops at the first change whose transaction might still be running, so the checkpoint can never overtake an uncommitted lower LSN, and burned gaps are irrelevant because there is no row to wait for. The regression test now fails on the old code and passes on the new. The lesson is the one real CDC connectors encode by reading the WAL in commit order: log position is not commit order, and a projector that conflates them loses data exactly when it is busiest.
A second, smaller bug from the same build: my first propagation-latency measurement read 4 seconds because I computed it as time-since-commit-until-now at query time, which for early orders is the whole run; stamping a projected_at when the projector writes each document gave the real lag of 4.3 ms p50. Both fixes are in the history.
- Debezium/pgoutput logical-replication reader replacing the trigger-based log.
motorMongoDB adapter wired as the default read-model store.- Parallel projectors partitioned by aggregate key, with the watermark per partition, to scale past single-projector throughput.
- Snapshot + incremental bootstrap so a new read model does not replay the entire history.
- First metric to watch in production: projector checkpoint lag (newest LSN minus checkpoint) and oldest-in-flight-transaction age; the second bounds how far the first can legitimately fall behind.
MIT