Using Change Data Capture Instead of Application Dual-Writes

The migration plan called for every service that writes orders to also write the new orders_v2 store. Six services write orders. Four were updated; one was owned by another team and shipped two weeks late; one was a nightly batch job nobody remembered. The reconciliation job found 38,000 rows missing in the new store, and a handful that differed because two services wrote in different orders under concurrency. Application dual-writes put the burden of consistency on every writer, and every writer is a place to get it wrong. Change data capture (CDC) moves the burden to one place: it reads the database’s own change log — PostgreSQL’s WAL through logical decoding, MySQL’s binlog — and streams every committed change, in commit order, to the target. This guide explains when CDC is the better synchronisation mechanism for a migration, how to set it up with logical replication or Debezium, and how to cut over. It extends Dual-Write Synchronization.

Symptom / Error Signatures

Application dual-writes are failing you when:

• Reconciliation between source and target finds missing rows traceable to a writer that was never updated.
• Rows differ between stores after concurrent updates, because two writers updated source and target in different orders.
• Failures between the first and second write leave the stores inconsistent, and retry logic becomes complex.
• Adding a writer (a new service, a support tool, an ad-hoc fix) silently bypasses the dual-write.

CDC has its own signals to watch once adopted: growing replication-slot lag (pg_replication_slots.confirmed_flush_lsn falling behind), WAL accumulating on the primary because a slot is not consumed, and connector errors in Debezium such as schema-change handling failures.

Root Cause Analysis

Every database already writes a complete, ordered log of committed changes for durability and replication. PostgreSQL exposes it through logical decoding: a logical replication slot decodes WAL into row changes for a publication’s tables, consumed either by native logical replication (a PostgreSQL subscriber) or by tools such as Debezium. MySQL exposes the binlog in row format, consumed by Debezium, Maxwell or the replication protocol. Reading the log gives three guarantees that application dual-writes cannot: every committed change is seen, regardless of which code made it; changes arrive in commit order; and a change is emitted only if it committed.

CDC is not free. A PostgreSQL replication slot retains WAL until the consumer confirms it, so a stalled consumer can fill the primary’s disk. Schema changes on replicated tables must be coordinated with the consumer. And the target lags the source by the pipeline’s latency, so reads that need immediate consistency must still go to the source until cut-over.

Immediate Mitigation

If an application dual-write migration is already producing gaps:

1. Reconcile and repair what exists. Run the comparison described in reconciling divergence between dual-written tables and backfill missing or differing rows from the source.

2. Stand up CDC for the table, then retire the dual-writes. For a PostgreSQL-to-PostgreSQL move, native logical replication is the simplest pipeline.

-- PostgreSQL 15+ · on the source · requires wal_level = logical and REPLICATION privilege for the subscriber role
-- WARNING: a slot retains WAL until consumed; monitor its lag or the primary's disk can fill.
CREATE PUBLICATION orders_pub FOR TABLE orders;
-- ROLLBACK PATH: DROP PUBLICATION orders_pub;

-- PostgreSQL 15+ · on the target · the table must already exist with compatible columns
CREATE SUBSCRIPTION orders_sub
  CONNECTION 'host=source-db dbname=app user=replicator password=***'
  PUBLICATION orders_pub
  WITH (copy_data = true);   -- initial snapshot, then streaming
-- ROLLBACK PATH: DROP SUBSCRIPTION orders_sub;  (also drops the remote slot)

3. Watch slot lag from the source.

-- PostgreSQL · read-only · WAL retained for each logical slot
SELECT slot_name, active,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) AS retained
FROM pg_replication_slots WHERE slot_type = 'logical';

Permanent Fix / Long-Term Pattern

Use CDC as the default synchronisation mechanism for migrations that move data to another table, database or store, and keep application dual-writes for narrow cases where the target needs transformation that only application code can do. Structure the migration as: snapshot plus stream until the target converges; reconcile continuously; switch reads to the target (behind a flag); switch writes to the target; and — if you need a rollback path — start reverse replication from target to source before switching writes. The overall cut-over is described in dual-writing across two databases during a cutover.

Operate the pipeline like production infrastructure. Alert on slot lag and on connector health; set max_slot_wal_keep_size (PostgreSQL 13+) so an abandoned slot cannot fill the disk; coordinate schema changes on published tables — logical replication does not replicate DDL, so add columns on the target first, then the source. Replica and slot lag belong on the same dashboards as other migration signals, per alerting on replication lag during backfills.

A few details decide whether updates and deletes replicate correctly. Logical replication identifies rows on the target by the source’s replica identity, which defaults to the primary key; tables without a primary key need ALTER TABLE ... REPLICA IDENTITY FULL (slow for updates on large tables) or a unique index designated as the identity. Debezium emits update and delete events with the key and, depending on the replica identity, the old row values; sinks that upsert by key handle both. Sequences are not replicated by logical replication either, so before writes move to the target, set its sequences above the source’s current values, or new inserts on the target will collide with replicated rows.

Verification Checklist

• The source has wal_level = logical (PostgreSQL) or row-based binlog (MySQL), and the publication or connector covers exactly the intended tables.
• The initial snapshot completed and streaming continued from the snapshot position without gaps.
• Reconciliation shows zero differences before reads switch.
• Slot lag and retained WAL are monitored with alerts, and max_slot_wal_keep_size is set.
• Schema changes on replicated tables follow a documented order (target first, then source).

Frequently Asked Questions

Is CDC eventually consistent? Yes. The target lags the source by the pipeline’s latency — usually well under a second for native logical replication, somewhat more through Kafka. Reads that need to see a just-committed write must use the source until cut-over.

Does logical replication copy schema changes? No. PostgreSQL logical replication replicates row changes, not DDL. Add new columns to the target before the source, so incoming rows always fit.

What happens if the consumer stops? The replication slot keeps WAL on the source until the consumer resumes. Without a limit, that can fill the primary’s disk; max_slot_wal_keep_size caps retention, at the cost of invalidating the slot if the limit is reached.

When are application dual-writes still the right choice? When the target’s data needs transformation that only application logic can produce, or when the target is not a database that CDC tooling can write to. Even then, consider CDC into a queue followed by an application consumer, which keeps ordering and completeness.