CDC vs Dual Writes: How to Keep Your Systems in Sync

Every backend engineer has faced this situation: your application writes an order to PostgreSQL, then sends the same data to Elasticsearch for search, Redis for caching, and Kafka for downstream consumers. Four writes, four chances for something to go wrong. When one of those writes fails, your systems drift apart, and users see stale search results, phantom inventory, or missing notifications.

This is the dual-write problem, and it is one of the most common sources of data inconsistency in distributed systems. Change data capture (CDC) offers a fundamentally different approach: instead of writing to multiple systems in application code, you write once to your database and let a CDC pipeline propagate changes everywhere else.

In this article, you will learn exactly how dual writes cause data loss and ordering bugs, how CDC eliminates those failure modes, and how to build a CDC pipeline with RisingWave that keeps all your downstream systems consistent without changing your application code.

What Is the Dual-Write Problem?

A dual write occurs when application code updates two or more data stores as part of a single business operation without an atomic transaction spanning both stores. The classic example: your service writes to a relational database and publishes a message to Kafka inside the same request handler.

def place_order(order):
    db.execute("INSERT INTO orders VALUES (%s, %s, %s)",
               order.id, order.customer_id, order.total)
    kafka.produce("orders-topic", key=order.id, value=order.to_json())

This code looks harmless. But consider what happens when the Kafka producer call fails after the database commit succeeds. The order exists in PostgreSQL but never reaches Kafka. Downstream consumers (analytics, notifications, fulfillment) never learn about it.

The reverse scenario is equally dangerous: Kafka accepts the message, but the database transaction rolls back. Now consumers process an order that does not exist.

Why wrapping both in a try/catch does not help

Retrying the Kafka publish after a failure introduces a new problem: duplicate messages if the first attempt actually succeeded but the acknowledgment was lost. And if you retry the database write, you risk inserting duplicate rows unless your schema enforces idempotency.

Distributed transactions (two-phase commit) technically solve this, but they come with severe performance penalties and tight coupling between systems. Most teams avoid them for good reason.

The race condition nobody catches in testing

Even when both writes succeed, ordering is not guaranteed. If two concurrent requests update the same record, the database may apply them in order A then B, while Kafka receives them as B then A. Downstream consumers now have stale data, and no error was raised anywhere. This race condition is nearly impossible to reproduce in a test environment but inevitable at production traffic volumes.

What Is Change Data Capture?

Change data capture is a pattern where changes to a database (inserts, updates, deletes) are captured from the database's transaction log and delivered as a stream of events to downstream consumers. Instead of the application pushing data to multiple destinations, a CDC system pulls changes from a single source of truth.

The key insight: the database already records every change in its write-ahead log (WAL in PostgreSQL, binlog in MySQL). CDC reads this log and converts each entry into an event that can be consumed by Kafka, a streaming database like RisingWave, or any other downstream system.

Because the CDC system reads from the transaction log after the transaction commits, it captures exactly the changes that the database accepted, in the exact order they were committed. No race conditions. No partial writes. No duplicates.

Log-based CDC vs trigger-based CDC

There are two main approaches to CDC:

Log-based CDC reads the database's internal transaction log. This is the preferred approach because it adds zero overhead to write operations and captures changes in commit order. Tools like Debezium and RisingWave's native CDC connectors use this method.

Trigger-based CDC uses database triggers to write change records to a shadow table. This approach adds latency to every write and can cause lock contention under load. It is simpler to set up for small workloads but does not scale well.

For production systems, log-based CDC is the clear winner. The rest of this article focuses on that approach.

How Does CDC Compare to Dual Writes?

The differences between CDC and dual writes go beyond reliability. They affect your architecture, operational overhead, and how you reason about data consistency.

When dual writes still make sense

Dual writes are acceptable when you are updating a cache that serves as a performance optimization (not a source of truth) and you can tolerate brief staleness. For example, invalidating a CDN cache on write is a form of dual write that rarely causes business-critical inconsistency.

But for any system where data loss, duplication, or ordering matters (search indexes, analytics pipelines, event-driven workflows), CDC is the safer choice.

How Does the Outbox Pattern Fit In?

The transactional outbox pattern is a bridge between dual writes and pure CDC. Instead of publishing directly to Kafka from your application, you write an event record to an "outbox" table in the same database transaction as your business data.

BEGIN;

INSERT INTO orders (id, customer_id, total, status)
VALUES ('ord-9821', 'cust-442', 159.99, 'placed');

INSERT INTO outbox (aggregate_id, event_type, payload)
VALUES ('ord-9821', 'OrderPlaced', '{"customer_id":"cust-442","total":159.99}');

COMMIT;

A CDC connector then reads the outbox table and publishes the events to Kafka or another message broker. Because both writes happen in a single database transaction, either both succeed or neither does. The atomicity problem disappears.

The outbox pattern works well but adds complexity: you need an outbox table, a CDC connector watching that table, and cleanup logic to prevent the outbox from growing indefinitely. With a streaming database like RisingWave, you can skip the outbox entirely by using CDC source connectors to capture changes directly from your primary tables.

How Do You Build a CDC Pipeline with RisingWave?

RisingWave provides native CDC connectors for PostgreSQL and MySQL, so you do not need Kafka or Debezium as intermediate infrastructure. Here is a complete example that captures order changes from PostgreSQL and builds a real-time analytics view.

Step 1: Create a CDC source

Connect RisingWave directly to your PostgreSQL database:

-- Create a shared PostgreSQL CDC source
CREATE SOURCE pg_source WITH (
    connector = 'postgres-cdc',
    hostname = 'postgres-host',
    port = '5432',
    username = 'replication_user',
    password = 'your_password',
    database.name = 'ecommerce',
    slot.name = 'risingwave_slot'
);

Step 2: Create tables from the CDC source

Map specific upstream tables to RisingWave tables:

-- Map the orders table
CREATE TABLE orders FROM SOURCE pg_source TABLE 'public.orders' (
    id VARCHAR PRIMARY KEY,
    customer_id VARCHAR,
    total DECIMAL,
    status VARCHAR,
    created_at TIMESTAMPTZ
);

-- Map the order_items table
CREATE TABLE order_items FROM SOURCE pg_source TABLE 'public.order_items' (
    id VARCHAR PRIMARY KEY,
    order_id VARCHAR,
    product_id VARCHAR,
    product_name VARCHAR,
    quantity INT,
    unit_price DECIMAL
);

Step 3: Build a materialized view for real-time analytics

Now create a materialized view that joins orders with their line items and computes running totals:

CREATE MATERIALIZED VIEW order_summary AS
SELECT
    o.id AS order_id,
    o.customer_id,
    o.status,
    o.created_at,
    COUNT(oi.id) AS item_count,
    SUM(oi.quantity * oi.unit_price) AS computed_total
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
GROUP BY o.id, o.customer_id, o.status, o.created_at;

This materialized view updates incrementally as new CDC events arrive. When a row changes in PostgreSQL, RisingWave processes only the delta, not the entire table. You can query it with standard SQL:

SELECT order_id, customer_id, status, item_count, computed_total
FROM order_summary
WHERE status = 'placed'
ORDER BY created_at DESC
LIMIT 10;

Expected output:

  order_id  | customer_id | status  | item_count | computed_total
------------+-------------+---------+------------+----------------
 ord-9821   | cust-442    | placed  |          3 |         159.99
 ord-9818   | cust-118    | placed  |          1 |          49.95
 ord-9815   | cust-337    | placed  |          5 |         324.50
 ord-9812   | cust-201    | placed  |          2 |          89.00
 ord-9809   | cust-556    | placed  |          4 |         212.75

Step 4: Sink to downstream systems

Push the materialized view to Elasticsearch, Kafka, or another destination without changing application code:

CREATE SINK order_summary_sink FROM order_summary
WITH (
    connector = 'kafka',
    topic = 'order-summary-events',
    properties.bootstrap.server = 'kafka:9092',
    type = 'upsert',
    primary_key = 'order_id'
);

This entire pipeline replaces dual writes in your application code. Your app writes to PostgreSQL. RisingWave handles everything else.

What About the Outbox Pattern vs Direct CDC?

Both approaches solve the dual-write problem, but they have different tradeoffs:

If you need full control over the event schema (for example, publishing domain events like OrderPlaced with a curated payload), use the outbox pattern. If your goal is to replicate data to analytics systems, search indexes, or caches, direct CDC is simpler and requires no application changes.

With RisingWave, direct CDC is especially powerful because you can transform, join, and aggregate CDC streams using SQL before sinking them to downstream systems. Your materialized views serve as the transformation layer that the outbox pattern typically delegates to consumer-side code.

How Do You Handle Schema Changes with CDC?

Schema evolution is one of the trickiest parts of any data pipeline. With dual writes, every schema change requires coordinated updates across all producer and consumer code paths. Miss one, and you get deserialization errors or silent data loss.

CDC pipelines handle schema changes more gracefully:

Adding a column: New columns appear in the CDC stream automatically. Downstream consumers that do not need the new field simply ignore it. In RisingWave, you can alter a table to include the new column when you are ready to use it.

Renaming a column: This is a breaking change for both dual writes and CDC. The safest approach is to add the new column, backfill it, update consumers, then drop the old column.

Dropping a column: CDC streams stop including the dropped column. Downstream consumers that depend on it will need updates, but the failure is immediate and visible, not a silent data quality issue.

The key advantage of CDC over dual writes for schema changes: you have one integration point to update instead of N consumer integrations scattered across your codebase.

FAQ

What is the dual-write problem?

The dual-write problem occurs when an application updates two or more data stores (like a database and a message queue) as part of a single operation without atomic transactions spanning both systems. If one write fails or the writes arrive out of order, the systems become inconsistent. This is a common source of data loss and stale reads in microservices architectures.

When should I use CDC instead of dual writes?

Use CDC whenever data consistency matters across systems. CDC is the better choice for feeding search indexes (Elasticsearch), analytics pipelines, event-driven workflows, and cache synchronization. Dual writes are only acceptable for best-effort cache invalidation where brief staleness is tolerable and the cache is not a source of truth.

Does CDC add latency to my application?

No. CDC operates asynchronously by reading from the database's transaction log after the transaction commits. Your application's write latency is unaffected. The end-to-end latency from database commit to downstream delivery is typically sub-second with tools like RisingWave's native CDC connectors, which connect directly to PostgreSQL without intermediate infrastructure.

How is the outbox pattern different from CDC?

The outbox pattern writes domain events to an outbox table in the same database transaction as business data, then uses CDC to publish those events to a message broker. It gives you full control over event payloads but requires application-level changes and outbox table maintenance. Direct CDC captures all changes from source tables automatically without any application code changes, making it simpler for data replication scenarios.

Key Takeaways

• Dual writes cause data inconsistency because there is no atomic transaction spanning multiple systems. Partial failures, retries, and race conditions all produce drift between systems that is hard to detect and harder to fix.
• CDC reads the database's transaction log to deliver every committed change in order, eliminating the root causes of dual-write failures.
• The outbox pattern provides a middle ground with full control over event schemas, but direct CDC is simpler when your goal is data replication.
• RisingWave's native CDC connectors let you capture changes from PostgreSQL and MySQL without Kafka or Debezium as intermediate infrastructure.
• Materialized views in RisingWave transform and join CDC streams using SQL, replacing consumer-side processing logic with declarative queries.

Ready to try this yourself? Try RisingWave Cloud free, no credit card required. Sign up here.

Join our Slack community to ask questions and connect with other stream processing developers.