Dinesh/ Blog
data engineering

Designing for Scale · Change Data Capture (CDC)

Build CDC and transactional-outbox reasoning around coordinated snapshots, durable intent, replay identity, schema evolution and WAL retention.

12 min read
Checking device speech…
Lesson preparation & details

Level: advanced

Designing for scale · Lesson 10 of 20 ↗
Loading this browser’s progress…

By the end, you should be able to

  • Explain the dual-write crash window and durable retry intent
  • Design a coordinated snapshot and change-stream pipeline
  • Implement local outbox and destination dedup transactions
  • Plan recovery after log gaps, schema changes and stale replay

Bring with you

Sources checked: . A source check does not change the publication or update date.

Editorial review: · What review means

In this article · 10 sections

The problem this post solves

A profile update must reach the primary database, a search index and a cache. Three sequential writes expose a partial-failure window: the database commits Bob's name, then the process crashes before updating search. Retrying can repair this if the intent is durable and operations are idempotent; an in-memory retry loop alone cannot recover intent lost with the process.

CDC moves the source of derived-state updates from application call sequencing to a database change stream. It can reduce coupling, but it does not guarantee perfect convergence, require Kafka specifically, or eliminate reconciliation. This lesson designs the acknowledgment, snapshot, ordering and recovery boundaries that make the approach useful.

First principles

  1. A local transaction can atomically commit changes within its database boundary. A later call to another system is outside that boundary.
  2. If the second call times out, the caller may not know whether it took effect. Undoing the first committed change is a new compensating action, not rolling back the original transaction.
  3. Retries need stable identities and a durable source of pending work. Otherwise a crash loses the knowledge that something remains to publish.
  4. Recording business state and publication intent in one transaction closes that local gap. A relay can retry after restart, but still needs a downstream replay contract.

Distributed transactions may coordinate some participants at an operational cost; they are not impossible or universally performance-destroying. For independently managed search/cache projections, an outbox or log-based pipeline often offers a simpler explicit eventual-update contract. Polling a durable outbox can also work; CDC is one transport choice.

Architecture: The Transaction Log

PostgreSQL writes WAL records before affected data pages must be durably written, enabling crash recovery under the configured durability rules. Logical decoding interprets changes into a consumer-facing stream; raw physical WAL is not itself a stable business-event API. MySQL binlog-based capture is another mechanism, not identical to PostgreSQL's physical WAL format.

A source log has positions and retention, not an eternal immutable record of all history. Transactions may contain uncommitted work; logical connectors emit committed changes according to their protocol. Multiple source databases/shards do not automatically share one global total order. Define the ordering unit and persist source positions.

Change Data Capture (CDC)

Change Data Capture (CDC) taps into that transaction log and broadcasts changes as a stream of events. Follow the diagram from the application's one database write to the two downstream consumers: the search and cache updates no longer require the application to coordinate three separate writes.

One database write feeds a log-based capture pipeline with separate search and cache workers. The Debezium-to-WAL arrow means reads, not event delivery. Full node and relationship text follows.
One database write feeds a log-based capture pipeline with separate search and cache workers. The Debezium-to-WAL arrow means reads, not event delivery. Full node and relationship text follows.

Figure: The CDC → WAL arrow is a labelled read action, not the direction of event delivery. Debezium uses logical decoding and the replication protocol; raw physical WAL is not a business-event API. Search and cache are independent asynchronous projections—not one transaction or a fixed freshness guarantee. Snapshot, replay and stale-version boundaries remain essential.

CDC figure text equivalent — all nine nodes and eight relationships
  • Application Server → Primary Database (PostgreSQL): 1. Write.
  • Primary Database (PostgreSQL) ⇢ Write-Ahead Log: 2. Appends to WAL (dashed relationship).
  • CDC Engine (Debezium) → Write-Ahead Log: 3. Reads WAL. This points from the reader to what it reads, not from WAL back into the database.
  • CDC Engine (Debezium) → Kafka / Message Bus: 4. Publishes Event.
  • Kafka / Message Bus → Search Index Worker: 5. Consumes; Search Index Worker → Elasticsearch.
  • Kafka / Message Bus → Cache Invalidation Worker: 5. Consumes; Cache Invalidation Worker → Redis Cache.

Following one profile update through that flow:

  1. The application commits UPDATE users SET name='Bob' WHERE id=1. Return success only after the chosen database acknowledgment, not merely after issuing SQL.
  2. A configured PostgreSQL logical replication slot/publication and Debezium connector expose committed table changes. Provision appropriate permissions, replica identity, topic naming and durable connector offsets.
  3. At initialization the connector coordinates a source snapshot with a streaming position so updates during the scan are captured. A snapshot alone followed by “start now” can miss changes in between.
  4. Records are routed by a stable entity key when per-entity order is required. Do not claim a global order across Kafka partitions. Persist connector progress, but design for restart/snapshot duplicates.
  5. Search and cache consumers apply version-aware updates/deletes and checkpoint only after their effect boundary is safe. A stale retry must not resurrect a deleted profile or overwrite its newer name.

Worked position trace: snapshot position 100 contains user 1 at version 7. While it is copied, changes at positions 101 and 102 set version 8 then delete at version 9. A coordinated apply can install the snapshot and then replay 101–102. If an old snapshot row arrives last, an unversioned upsert would resurrect the user. Retain version/tombstone information or use the connector's documented coordinated snapshot procedure; raw SQL row count equality does not prove equivalence.

Trade-off: independent dual writes may be acceptable for explicitly best-effort derived state or when backed by durable reconciliation, but they are not atomic by default. CDC decouples application latency from downstream processing and supports multiple projections, at the cost of connector operations, log retention, schema evolution and lag. Source and downstream resource use are still coupled through backpressure. No fixed milliseconds-to-seconds freshness guarantee follows from “CDC.”

Handling The Stream

A logical event usually includes a key, operation kind, source metadata and available row fields. Debezium PostgreSQL's before contents depend on replica identity; the default may include only key information for update/delete. Unchanged TOASTed values may not be repeated. A consumer must not assume every event is a full old/new row image. A schema contract should state what omitted fields mean, how delete events and Kafka tombstones differ, and how snapshots are identified.

A high-level application event may carry the complete projection too; it need not be a content-free “user changed” notification. Choose row CDC when raw table changes are the useful interface. Choose a business outbox when consumers need explicit semantics such as AccountOpened across several source tables. The source transaction can order its own events, but consumers spanning partitions may need transaction metadata or an explicit aggregate version to assemble a consistent view.

Partition by stable aggregate identity when updates for one aggregate need ordering. Idempotency suppresses repeats of one event; it does not stop distinct old events arriving after newer ones. Compare aggregate versions, apply missing sequences or rebuild when a gap matters. Do not use a wall-clock timestamp as proof of causality across unsynchronized sources.

The Outbox Pattern

Write the account and its business event in the same local transaction. The event needs a stable ID, aggregate ID, aggregate version, type and versioned payload. The relay reads only committed outbox rows, publishes them, and records progress. A crash after publish but before progress can duplicate the event; a crash before publish leaves a durable intent to retry.

Debezium's Outbox Event Router documents an event id, aggregatetype, aggregateid, type and payload shape. Its default aggregate ID is used as the emitted key, which supports per-aggregate partition ordering. Schema compatibility and payload semantics remain the application's responsibility.

This runnable SQLite fixture demonstrates the local atomicity and duplicate-effect boundary. SQLite is not a Debezium connector in this example, and :memory: tests transaction logic, not crash persistence. The destination is a second database to make its independent transaction visible.

import json
import sqlite3
 
source = sqlite3.connect(':memory:')
source.executescript("""CREATE TABLE users(id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE outbox(id TEXT PRIMARY KEY, aggregateid INTEGER,
                   version INTEGER, type TEXT, payload TEXT);""")
try:
    with source:
        source.execute('INSERT INTO users VALUES(1, ?)', ('Alice',))
        raise RuntimeError('crash before intent')
except RuntimeError:
    pass
assert source.execute('SELECT COUNT(*) FROM users').fetchone()[0] == 0
 
payload = json.dumps({'name': 'Bob'}, sort_keys=True)
with source:
    source.execute('INSERT INTO users VALUES(1, ?)', ('Bob',))
    source.execute('INSERT INTO outbox VALUES(?,?,?,?,?)',
                   ('e7', 1, 1, 'UserCreated.v1', payload))
assert source.execute('SELECT COUNT(*) FROM outbox').fetchone()[0] == 1
 
dest = sqlite3.connect(':memory:')
dest.executescript("""CREATE TABLE seen(id TEXT PRIMARY KEY, payload TEXT);
CREATE TABLE search_users(id INTEGER PRIMARY KEY, version INTEGER, name TEXT);""")
event = source.execute('SELECT id, aggregateid, version, payload FROM outbox').fetchone()
def consume(event):
    event_id, user_id, version, body = event
    with dest:
        prior = dest.execute('SELECT payload FROM seen WHERE id=?', (event_id,)).fetchone()
        signature = json.dumps([user_id, version, body])
        if prior:
            if prior[0] != signature:
                raise ValueError('conflicting event ID')
            return False
        dest.execute('INSERT INTO seen VALUES(?,?)', (event_id, signature))
        dest.execute("""INSERT INTO search_users VALUES(?,?,?)
          ON CONFLICT(id) DO UPDATE SET version=excluded.version, name=excluded.name
          WHERE excluded.version > search_users.version""",
          (user_id, version, json.loads(body)['name']))
    return True
 
assert consume(event)
assert not consume(event)  # relay restarted after publish, before checkpoint
assert dest.execute('SELECT name FROM search_users').fetchone()[0] == 'Bob'
assert consume(('e8', 1, 2, json.dumps({'name': 'Carol'})))
assert consume(('late', 1, 1, payload))  # accepted event; stale projection update ignored
assert dest.execute('SELECT name FROM search_users').fetchone()[0] == 'Carol'
source.close(); dest.close()
print('rollback leaves no user; duplicate e7 has one effect; v1 cannot overwrite v2')

The destination commits its event receipt and local projection together. In production, retain that identity through all replay windows, validate payloads before effects, handle concurrent deliveries via unique constraints/transaction retries, and make destination persistence meet the durability requirement. If the destination were email instead of a database, this local transaction would not make the send atomic.

Exercise: the relay marks e7 delivered before publishing, then crashes. Answer: e7 can be permanently skipped. Publish then checkpoint permits duplicates but retains retryable intent; destination idempotency handles that uncertainty. An outbox retention job must not delete entries still needed by relay recovery or audit.

Failure modes

WAL retention and slot health. A stalled logical slot can retain WAL and exhaust disk. A configured retention cap, invalidated slot or unavailable log can instead force resnapshot. At a hypothetical 20 MB/s WAL rate, ten minutes retain 12 GB; a 100 GB free-space budget lasts at most 5,000 seconds (83.3 minutes) before other disk use and safety margin. Alert on retained bytes, free disk, source/connector progress and destination lag. Throttling or pausing upstream writes is sometimes safer than waiting for disk exhaustion. A slot is not a backup.

Recovery after a gap. Stop pretending an unavailable position can be replayed. Determine whether archived source logs or another valid replica can supply it under the connector's supported procedure. Otherwise build a new consistent snapshot/projection, catch up from its coordinated position, validate keys and versions, then switch the serving alias. Preserve the old projection until reconciliation succeeds; a full rebuild can miss deletes if it merely upserts into old state without removing absent rows.

Schema evolution. Expand first: introduce a compatible optional field and deploy readers that accept old and new versions. Change writers/connector transforms next, backfill if needed, verify old-reader retirement and replay compatibility, then contract/drop the old field. A schema registry validates registered message schemas under its configured rules; it cannot automatically stop arbitrary PostgreSQL DDL. Include old retained events in compatibility tests and quarantine unsupported payloads with an owner rather than retrying forever.

Feedback loops. A projection worker that writes back to its source can trigger another event. It need not be forbidden from all source writes, but it must have an explicit loop-prevention design: separate command/projection paths, origin tags, idempotent state transitions and a stop condition. A “cache invalidated” event must not reissue an unconditional source update each time it is consumed.

Privacy and ordering. Avoid exporting secrets or unnecessary personal data; retention and deletion policies include logs, snapshots and projections. Tombstones and event IDs are correctness state with their own retention. Global order or exactly-once downstream behavior is not inherited merely because a source transaction committed.

Failure exercises with answers

  • e7 is published twice; did source atomicity fail? No. The source business row and intent were atomic; relay transport was replayed. The consumer must atomically record identity with its effect.
  • A table scan runs from position 100 to 150, then streaming starts at 151; is it safe? Not without coordinated snapshot semantics. A row read before its update at 120 can miss that update permanently. Start/retain changes at a known compatible snapshot boundary and apply according to the connector's protocol.
  • Dropping name breaks a consumer despite registry compatibility mode. Why? Database DDL did not automatically pass through the registry's producer contract validation. Use expand/contract migration and versioned events; verify both live and replay payloads.
  • Can all consumers recover from a 24-hour outage with 12-hour event retention? Not by replaying that bus alone. Restore from a valid alternative log or coordinated source snapshot and reconcile deletes; either increase retention or state the rebuild path.

What to carry forward

The source-of-truth decision, durable intent and version/identity contracts matter more than the product arrows. A complete design names the snapshot boundary, capture position, ordering key, acknowledgment and checkpoint order, destination dedup/effect transaction, retention budget, schema migration and recovery after lost history. That is how CDC makes a derived index maintainable without claiming perfect universal consistency.

Sources and review boundary

Primary passages checked 2026-10-07; mutable documentation is bound to saved source hashes in the accompanying claim evidence. Numerical examples are explicit teaching assumptions. The bounded Python examples were run locally; no cloud/distributed deployment, PostgreSQL cluster, broker, object store, load benchmark or regional drill was executed. Compilation and diagram parsing do not establish deployed-system correctness.

  • Debezium PostgreSQL connector stable docs — A coordinated initial snapshot/change position covers updates during the scan.; Debezium blocking-snapshot coordination can duplicate events.; Debezium PostgreSQL before images depend on replica identity.; Unchanged TOAST values may be absent from PostgreSQL CDC events.
  • Debezium Outbox — Outbox events have a stable unique identity for deduplication.; Outbox aggregate ID supplies the emitted Kafka key by default.
  • PostgreSQL 16 warm standby — Slots retain WAL at possible disk cost, while limits can trade away catch-up history.; Asynchronous WAL shipping exposes acknowledged-state loss if the primary copy is destroyed before shipping.
  • Apache Kafka 4.1 design — Exactly-once external effects need destination cooperation, not necessarily an all-Kafka boundary.; Destination output and consumption progress can be committed together.

Pause / Recall / Apply

Can you explain it without the page?

Close the example. Reconstruct the core idea, then change one assumption. Mark complete when you’re ready; you can always undo it.

Stored in this browser only. No account, no sync. Clearing browser data removes your record.