Change Data Capture: CDC Pipelines Designed, Built and Run

Change data capture methods and CDC tools compared

There are four ways to find out what changed in a database. Log-based CDC reads the transaction log, so it sees every committed insert, update and delete, including deletes, in commit order, with little load on the tables. The other three each give something up. Most CDC tools, open source or managed, are a packaged form of one of them.

Native logical replication in PostgreSQL or MySQL replication is a fifth option when the target is another instance of the same database. It is often the right answer for a single source and a single destination, and we say so when the review shows it.

How log-based CDC works in SQL Server, PostgreSQL and MySQL

Each database exposes its transaction log differently, and the source database needs a few settings changed before a connector can read it. Those changes usually need a DBA's approval, so we write the change list and prove it in a test environment first.

• SQL Server — Change data capture is enabled per table, which creates a capture instance and a change table. A capture job reads the transaction log and fills the change table, and the SQL Server Agent must be running for it to work. Debezium reads those change tables.
• PostgreSQL — Logical decoding turns the write-ahead log into row changes. It needs wal_level set to logical, a replication slot per consumer and an output plugin such as pgoutput, which is built in. The slot keeps WAL until the consumer confirms it, so an unattended slot can fill the disk.
• MySQL — The connector reads the binary log, which must be in row format with full row images, and the log must be kept longer than the longest outage the pipeline has to survive.

Log-based capture reads the log instead of querying tables, so its load on the source is usually lower than triggers or polling, but it is not zero. SQL Server writes and cleans change tables inside the database, and a PostgreSQL slot holds disk. We size both before go-live.

Where the changes go: RabbitMQ or Kafka

The capture side and the transport are separate decisions, and the transport should follow the consumers.

• RabbitMQ — Fits when services need routed events and each consumer reads from its own queue. Debezium Server publishes to an exchange with a routing key taken from the table or the record key, and an exchange fans one change out to as many queues as needed. There is no cluster of brokers, Connect workers and a registry to run for a handful of tables.
• Kafka — Fits when Kafka is already your backbone, when many sources feed many consumers, or when consumers must replay history from an offset. Debezium then runs on Kafka Connect. The Kafka cluster and the Connect cluster become part of what you operate.
• RabbitMQ Streams — A middle path for replay and large fan-out without a Kafka cluster, through the Debezium Server stream sink.
• Neither — If the consumer is a warehouse and nothing else needs the events, a managed CDC service or an ELT tool is simpler than any broker.

Debezium Server runs exactly one connector per instance, so several sources mean several instances. Its offsets default to a local file, which is lost on a container's ephemeral disk, so where they live is a design decision made before the first snapshot.

Running CDC consumers: what goes wrong and how to prevent it

Most CDC incidents are not in the capture. They are in the queue and consumer behind it. These are the settings we check first on a RabbitMQ-based pipeline.

How we set prefetch on ordered consumers

A change event is only safe to apply if the events before it for the same row have been applied. RabbitMQ round-robins messages across consumers on a queue unless the queue is declared as single active consumer, and a consumer that holds a batch of unacknowledged messages and then fails hands that whole batch back for redelivery. In our field experience a prefetch of 1 on the queue of an ordered CDC consumer kept events in order where a larger prefetch did not. Pair it with one active consumer per queue, or with a routing key that sends one table's events to one consumer. This is our practice rather than a RabbitMQ rule: a prefetch of 1 trades throughput for a smaller in-flight window, so test it against your own load.

Dead-letter poison events with a delivery limit

A poison event is one that a consumer can never process, such as a payload it cannot parse. Without a limit it is redelivered forever and everything behind it waits. On quorum queues the delivery limit defaults to 20 from RabbitMQ 4.0, and a message that reaches it is dropped, or dead-lettered if a dead-letter exchange is configured. Set the exchange, and use the at-least-once dead-letter strategy, which requires the reject-publish overflow setting and can produce duplicates at the target. Alert on the dead-letter queue and write down how someone inspects, fixes and replays what lands there.

Plan for the connector to stop and restart

When the broker or the database goes away, a connector can stop. The pipeline survives if the process is restarted by its supervisor, such as Kubernetes, and if its offsets sit on storage that persists, because the connector resumes from the last committed offset. Offsets are committed periodically, so after an outage some events can arrive twice. Delivery is at least once, not exactly once, and consumers must be idempotent on the primary key. On PostgreSQL the same holds inside the database: a slot's position is persisted at checkpoint, so after a crash recent changes can be sent again.

Hold the source log long enough

A connector that is down needs the database to keep its log. If the log is purged during an outage there is no position to resume from and the answer is a new snapshot. Set retention longer than the longest outage you must survive, and alert before it runs out. On PostgreSQL an unconsumed replication slot does the opposite and keeps WAL until the disk fills, so slot lag needs an alert as well.

Trace one change from end to end

Stamp each event with an identifier that travels in a message header and appears in the consumer's logs. When a row is wrong downstream, a trace identifier turns a search across three systems into one query. Time-ordered identifiers such as UUID version 7 also sort by creation, which helps when reading logs.

A worked example: Debezium Server into RabbitMQ at American National

American National Insurance needed CDC for SQL Server on Kubernetes and evaluated Kafka. Running Kafka and Kafka Connect was out of proportion to the volume and routing the pipeline needed, so Kafka was judged too complex for the job. AceMQ assessed the requirement and designed a standalone Debezium deployment that publishes change events straight to RabbitMQ exchanges, keeping SQL Server change capture and adding more flexible routing.

After go-live: four tiers of cover

Who this is not for, and what we do not promise

• Warehouse-only loads — If the only consumer is a warehouse and nobody else needs the events, a managed CDC service or an ELT tool will be cheaper than a broker pipeline. We will say so in the review.
• One database, one copy — A single PostgreSQL or MySQL source feeding one replica is usually better served by the database's own replication.
• Exactly-once delivery — We do not promise it. CDC delivery is at least once, and the design makes consumers idempotent.
• Latency figures in advance — End-to-end delay depends on the source log, the connector, the broker and the consumers. We measure it in your environment and do not quote a number beforehand.
• Connector development — We configure, run and fix Debezium and Kafka Connect connectors. We do not write new connectors.
• Oracle, Db2 and MongoDB sources — Oracle, Db2 and MongoDB sources are scoped with your DBAs, with specialist partners where needed, and proven in a test environment before production.