Designing for Scale · Change Data Capture (CDC)

The problem this post solves

When a user updates their profile, that change needs to be saved to the primary database, updated in the Redis cache, and sent to Elasticsearch so it can be found.

The naive way to build this is to have the application server write to all three systems synchronously. This is called a dual-write (or tri-write), and it is a distributed systems anti-pattern. It guarantees that the systems will eventually fall out of sync, requiring expensive reconciliation jobs to fix the drift.

This post covers the shift from application-driven synchronization to log-driven synchronization (Change Data Capture), making the database the singular source of truth for the entire architecture.

First principles

Architecture: The Transaction Log

Every serious database (PostgreSQL, MySQL, Oracle) uses a Write-Ahead Log (WAL) or a transaction log. Before the database actually modifies a row on disk, it appends a record of the intended change to this sequential log. This log is what allows the database to recover from a crash.

It is also the perfect, totally ordered, immutable record of everything that has ever happened to the data.

Change Data Capture (CDC)

Change Data Capture (CDC) is the process of tapping into that transaction log and broadcasting it as a stream of events.

The Flow:

1. The application executes a simple UPDATE users SET name = 'Bob' WHERE id = 1. The application considers the job done.
2. PostgreSQL writes this change to the WAL and commits the transaction.
3. The CDC engine (like Debezium) acts as a replica. It reads the WAL and sees the change.
4. It formats the change into a JSON payload containing the before state and the after state, and pushes it to a Kafka topic.
5. Downstream workers (search, caching, analytics) listen to the Kafka topic and apply the changes to their respective systems.

Handling The Stream

The payload produced by a CDC engine is much richer than a simple event trigger.

A standard application event might say: User 1 updated their profile. The downstream search worker receives this and must query the primary database to find out what changed, adding load back onto the database.

A CDC payload says: Table: users, Operation: UPDATE, Before: {name: 'Alice'}, After: {name: 'Bob'}. The downstream worker has everything it needs to update the search index without ever querying the primary database.

The Outbox Pattern

Sometimes, relying purely on the database's internal WAL is too low-level. The WAL records physical changes to tables, not business events.

If creating a "User Account" requires inserting rows into the users, billing, and preferences tables, the CDC engine will emit three separate table-level events. Downstream systems have to stitch these back together to understand that a "User Created" business event occurred.

The solution is the Transactional Outbox Pattern.

Failure modes

Database log truncation. If the CDC engine crashes and stays down for hours, the primary database will continue writing to its WAL. Eventually, the database will truncate old logs to save disk space. When the CDC engine comes back up, the logs it needs are gone. The system must perform a full snapshot (reading the entire table from scratch) to recover, which is an extremely heavy operation.

Infinite loops. If a cache invalidation worker reads a CDC event, updates the cache, and accidentally triggers an application flow that writes back to the primary database, it creates an infinite loop. Workers consuming CDC streams must be strictly read-only regarding the primary data source.

Schema changes. If a developer drops a column in the primary database, the CDC payload changes shape instantly. Downstream workers parsing the JSON will crash. Schema registries (like Confluent Schema Registry) must be used to enforce compatibility rules before the database change is allowed.

What to carry forward