Change Data Capture: Real-Time Data Synchronization

Consider a common scenario: you have an orders table in Postgres, but search uses Elasticsearch, caching uses Redis, the data warehouse uses ClickHouse, and an event must also be sent to the fulfillment microservice.

What do you do after every database write?

The most intuitive approach is to "dual-write" in application code: first INSERT INTO orders ..., then index into es, then XADD to a Redis Stream, and then publish a Kafka message. It looks straightforward, but it is actually a time bomb. This article explains why CDC (Change Data Capture) is a better solution to this class of problem, how it works, and when it is worth adopting.

1. What CDC Solves: Let Database Changes Flow "Automatically" to Downstream Systems

The core idea of CDC can be summarized in one sentence: the database already knows about every change, so we capture those changes and send them to every downstream system that needs them.

Application code no longer has to "notify" every downstream system. Instead, a component sits beside the database, extracts insert / update / delete events in real time, and broadcasts them. Each downstream system subscribes to the events it needs.

                   ┌──────────┐
                   │ Postgres │ (WAL is continuously written)
                   └────┬─────┘
                        │ CDC component reads the log
                        ▼
                   ┌──────────┐
                   │  Kafka   │ (change event stream)
                   └──┬─┬─┬───┘
            ┌────────┘ │ └────────┐
            ▼          ▼          ▼
        ┌──────┐  ┌─────────┐  ┌─────────────┐
        │  ES  │  │Warehouse│  │Microservices│
        └──────┘  └─────────┘  └─────────────┘

The database becomes the entire system's single source of truth, with every downstream representation derived from it automatically, consistently, and in real time.

2. How It Works: Read the Transaction Log Instead of Querying Tables

The most important question about CDC is how it obtains changes. There are two fundamentally different approaches, and their quality differs by an order of magnitude.

The inferior approach: query-based CDC. Periodically run SELECT * FROM orders WHERE updated_at > ? and compare the differences. This is "pseudo-CDC" and is fundamentally just polling.

The better approach: log-based CDC. Databases already write transaction logs continuously for crash recovery. Postgres calls this log the WAL (Write-Ahead Log), while MySQL calls it the binlog. These logs record every row-level change in full. A CDC component pretends to be a "replica" and parses structured change events from the log.

Postgres WAL excerpt (simplified):
  BEGIN tx=12345
  UPDATE public.orders SET status='paid' WHERE id=99
  COMMIT tx=12345

Event emitted after CDC parsing (simplified):
{
  "op": "u",                  // update
  "before": {"id":99,"status":"pending"},
  "after":  {"id":99,"status":"paid"},
  "source": {"tx":12345, ...},
  "ts_ms": 1718000000000
}

Why is log-based CDC decisively better? There are three reasons:

1. No missed changes: The log records every commit, including multiple changes within the same transaction, giving you the complete history.
2. No disruption: It does not require locking application tables, creating triggers, or scanning tables, so it has almost no impact on the production database.
3. Ordering guarantees: The log is written in commit order. When downstream systems consume it in order, they see the data's true sequence of changes.

3. Tooling: Debezium + Kafka Is the De Facto Standard, and Postgres Has Native Options

Debezium is currently the most widely used open-source CDC project. It runs on the Kafka Connect framework and provides Source Connectors for Postgres, MySQL, MongoDB, SQL Server, and other databases. Its architecture follows a fixed three-stage pattern:

[ PG / MySQL ] --Debezium Source--> [ Kafka topic ] --consume--> [ Downstream / Sink ]
                                        (a topic named pg.public.orders)

On the Postgres side, logical replication must first be enabled: set wal_level to logical, create a replication slot, and let Debezium subscribe to the WAL change stream through that slot. Debezium converts the changes into standard Kafka messages and publishes them. Downstream systems can use a JDBC Sink Connector to write them to ES/ClickHouse, or microservices can implement their own consumers.

If the full Kafka Connect stack feels too heavy, the Postgres ecosystem offers lighter alternatives: native logical replication using CREATE PUBLICATION / SUBSCRIPTION, which is suitable for Postgres-to-Postgres synchronization, or the pglogical extension. Debezium Server, a lightweight variant, can also publish directly to Pulsar, Kinesis, or Redis Stream and bypass Kafka.

Alternatives to Debezium include Canal from Alibaba for MySQL binlog and Maxwell for a minimal MySQL-to-Kafka pipeline. But if you need to support multiple database types and robust schema evolution handling, Debezium remains the preferred choice.

4. Why It Beats Dual Writes: Dual Writes Can Never Clear the Consistency Hurdle

Dual-write code usually looks like this:

db.execute("INSERT INTO orders ...")
es.index("orders", body=...)        # What if this fails?
redis.set(cache_key, ...)

This creates three unavoidable problems:

• Partial failures: If the DB write succeeds but the ES write fails, the data is inconsistent. Add retries? Add a transaction? Transactions cannot span these systems. Add compensation logic? Code complexity explodes.
• Out-of-order writes: Under concurrency, A's DB write may succeed while ES has not refreshed yet, and B may write to ES first. Downstream systems then see an out-of-order final state rather than the true sequence of changes.
• Scattered code: Every code path that writes to the database must remember to update every downstream system. When a new developer adds an endpoint and forgets one write, the systems silently become inconsistent.

CDC moves this responsibility from "every piece of application code that writes to the database" to a single source: the database log. The application only writes to the database, while changes flow to downstream systems automatically and accurately. Downstream systems consume what the database actually committed, in commit order, so there is no case where "one write succeeds and another fails." Consistency is guaranteed structurally rather than depending on a programmer's memory.

5. Why It Beats Polling: Latency and Load Are Debts You Cannot Pay Off

Polling, such as running a sync script once per minute, looks simple but performs poorly on both key metrics:

• Latency: It starts at the minute level. After a user updates a profile, the search result does not change until the next polling cycle, creating a disjointed experience.
• Load: Each SELECT * scans the entire table for updated_at > now() - interval. Once the table grows, this becomes disastrous; without an appropriate index, it can overwhelm the DB immediately.

CDC is event-driven: changes are pushed only when they occur, with zero overhead when nothing changes. Latency is typically subsecond, ranging from a few hundred milliseconds to a few seconds between commit and downstream visibility. The production database bears the cost of reading the WAL rather than scanning tables, which is an entirely different order of load.

In short, polling trades database compute for simplicity, while CDC trades a middleware stack for real-time updates and low load.

6. Use Cases: Where CDC Truly Shines

In practice, CDC is most common and most worthwhile in four scenarios:

1. Synchronizing search indexes (Elasticsearch / OpenSearch). The DB is the structured source of truth, while ES specializes in full-text search. CDC streams changes into ES in real time, producing a responsive search experience with natural decoupling.
2. Maintaining cache consistency. Redis cache + DB is a classic problem, and techniques such as double deletion and delayed double deletion are inelegant. Using CDC to observe changes and invalidate the corresponding keys precisely is cleaner than any application-layer workaround.
3. Loading data warehouses / ETL. Stream changes from an OLTP database into ClickHouse, Snowflake, or another data warehouse in real time for live reporting, replacing T+1 offline batch processing.
4. Event-driven microservices. When the order service changes a status, fulfillment, risk control, and notification services must all know. Instead of manually calling publish in the order service, let CDC turn "an order-table change" directly into an event stream. That is a true "integration event," rather than an abuse of domain events.

A simple criterion is: whenever a change in database state is itself a meaningful business event, use CDC.

7. Costs: Do Not Pretend It Is Free

CDC is not a silver bullet. Its costs are real:

• Operational complexity rises sharply. You must maintain the entire Debezium + Kafka Connect + Kafka stack, monitor lag, handle rebalances, and reserve enough disk space for Kafka. This is a substantial burden for a small team.
• Schema changes (DDL) are the biggest minefield. Adding a column, changing a type, or renaming something may leave the CDC component unable to understand the new schema, interrupting the stream or causing incorrect event parsing. Debezium supports integration with a schema registry, but operations such as CREATE TYPE still require careful handling and must be rehearsed before deployment.
• Ordering conflicts with partitioning. Preserving order for an entire table requires a single partition, but a single partition limits throughput. Partitioning by primary key enables concurrency, but you must ensure that events for the same primary key always enter the same partition. This must be resolved during the design phase.
• Switching from a full load to incremental capture (snapshot). On initial startup, existing data must be copied before switching to incremental changes. Snapshotting a large table can place significant load on the database, so it needs a carefully chosen window.

Summary

• The essence of CDC: Capture changes from database transaction logs such as WAL or binlog and push them to downstream systems through events, instead of using application dual writes or scheduled polling.
• Why it beats dual writes: Consistency is structurally guaranteed rather than dependent on programmer memory, and application code returns to the clean responsibility of "only writing to the database."
• Why it beats polling: Subsecond latency and low-impact load instead of minute-level latency and full-table scans.
• The mainstream stack: Debezium + Kafka Connect + Kafka. The Postgres ecosystem also offers lighter options such as native logical replication and pglogical.
• The costs are real: Operating a middleware stack, managing schema-change hazards, and balancing ordering against throughput.

In one sentence: when "a database change" is itself a business event and more than one downstream system needs it, CDC is the cleanest solution; otherwise, dual writes or polling are sufficient, so do not adopt CDC for its own sake.