Change data capture: log-based CDC, Debezium and the outbox pattern

How do the change data capture methods compare?

Polling needs a column like LAST_UPDATE_TIMESTAMP on every captured table, kept correct by every writer. A query cannot return a row that no longer exists, so deletes need a soft-delete flag or a side table, and a row inserted and deleted between polls is never captured at all.

Triggers see every change, but a PostgreSQL trigger runs in the same transaction as the statement that fired it: every write pays for the capture, and an error in the trigger rolls back the business change. Microsoft says custom tracking built from triggers, timestamp columns and extra tables often carries a high performance overhead.

Reading the log gets every change in its exact order of application, with no extra columns, and a stopped reader resumes where it left off. The cost moves to the log, which the database must keep until the reader has it.

How does Debezium capture changes?

Debezium is open source (Apache Software License 2.0), and version 3.7 is tested with PostgreSQL 14 to 18, MySQL 8.0, 8.4 and 9.7, SQL Server 2017, 2019 and 2022, and Oracle 19c, 21c, 23ai and 26ai. It runs in three ways:

• On Kafka Connect, the most common. By default each table's changes go to a topic named after the table, and sink connectors carry them to other systems.
• As Debezium Server, an application that streams changes without Kafka to sinks such as Amazon Kinesis, Google Cloud Pub/Sub, Apache Pulsar, JDBC databases and Apache Iceberg tables.
• As the Debezium engine, a library inside your own Java application.

Each change becomes a record keyed by the row's primary key, with an envelope as its value. An update to order 1042 (illustrative values, fields trimmed):

{
  "before": { "id": 1042 },
  "after": { "id": 1042, "status": "paid", "currency": "CAD" },
  "source": {
    "version": "3.7.0.Final",
    "connector": "postgresql",
    "db": "orders",
    "schema": "public",
    "table": "orders",
    "snapshot": false,
    "ts_ms": 1791536400000,
    "txId": 88123,
    "lsn": 3049721912
  },
  "op": "u",
  "ts_ms": 1791536400412
}

op is c (create), u (update), d (delete), r (snapshot read), t (truncate) or m (message). before holds only id because the table's REPLICA IDENTITY is DEFAULT; FULL adds every column. source carries the transaction ID and the log sequence number (LSN), and its ts_ms is when the database made the change. The top-level ts_ms is when the connector processed it, so the gap, 412 ms here, is the capture lag.

A delete produces a d event (before set, after null), then a tombstone, the same key with a null value, so Kafka compaction can remove the key (tombstones.on.delete, true by default). A primary key change produces a delete and a tombstone for the old key, then an event with the new key.

Snapshots and signals

A new connector first snapshots the existing rows as r events, then streams from the log. The default snapshot.mode, initial, snapshots only when no offsets are stored; when_needed also snapshots when the stored position is gone from the server; no_data never snapshots. A connector stopped mid-snapshot starts a new snapshot on restart.

For a large table, or to re-read one later, use an incremental snapshot: Debezium reads the table in chunks (1,024 rows by default) while streaming continues, and resumes after an interruption. Start one with a signal, a row inserted into the table named in signal.data.collection; the connector's user needs INSERT on it.

CREATE TABLE public.debezium_signal (
  id   varchar(42) PRIMARY KEY,
  type varchar(32) NOT NULL,
  data varchar(2048) NULL
);

INSERT INTO public.debezium_signal (id, type, data)
VALUES ('resnap-orders-1', 'execute-snapshot',
        '{"data-collections": ["public.orders"], "type": "incremental"}');

PostgreSQL's logical decoding does not carry DDL, so the connector cannot report schema changes as events, and it does not support them at all during an incremental snapshot. Coordinate migrations with consumers.

How do you set up PostgreSQL for CDC?

Debezium reads PostgreSQL through logical decoding, which extracts committed changes from the write-ahead log (WAL) through an output plug-in. Use pgoutput, always present since PostgreSQL 10; the connector's default, decoderbufs, is maintained by the Debezium community.

1. Server settings in postgresql.conf:

wal_level = logical                     # read only at server start
max_slot_wal_keep_size = 102400         # MB; example cap, default -1 (unlimited)
idle_replication_slot_timeout = 172800  # seconds; example, default 0 (off); PostgreSQL 18

wal_level can only be set at server start, so book a restart. On Amazon RDS, set rds.logical_replication to 1 instead (the instance may need a restart) and grant the rds_replication role; Cloud SQL requires pgoutput.

2. A role, grants and the publication. Create the publication yourself and set publication.autocreate.mode to disabled: the default, all_tables, runs CREATE PUBLICATION ... FOR ALL TABLES when none exists, which needs a superuser.

CREATE ROLE debezium REPLICATION LOGIN;
GRANT SELECT ON public.orders, public.order_lines TO debezium;   -- for snapshots

CREATE TABLE public.debezium_heartbeat (id int PRIMARY KEY, ts timestamptz NOT NULL);
GRANT INSERT ON public.debezium_signal TO debezium;
GRANT INSERT, UPDATE ON public.debezium_heartbeat TO debezium;

CREATE PUBLICATION dbz_orders FOR TABLE
  public.orders, public.order_lines, public.debezium_signal, public.debezium_heartbeat;

FULL logs every old column value. Use it where consumers need complete before images or large values in every update: PostgreSQL moves large values out of the row into TOAST storage and leaves an unchanged TOAST value out of the change unless it is in the replica identity, so Debezium sends __debezium_unavailable_value.

3. The connector:

{
  "name": "orders-cdc",
  "config": {
    "connector.class": "io.debezium.connector.postgresql.PostgresConnector",
    "database.hostname": "orders-db.internal",
    "database.dbname": "orders",
    "database.user": "debezium",
    "database.password": "${fileProvider:/etc/kafka-connect/orders-db.properties:dbPassword}",
    "topic.prefix": "orders",
    "plugin.name": "pgoutput",
    "slot.name": "debezium_orders",
    "publication.name": "dbz_orders",
    "publication.autocreate.mode": "disabled",
    "table.include.list": "public.orders,public.order_lines",
    "signal.data.collection": "public.debezium_signal",
    "heartbeat.interval.ms": "60000",
    "heartbeat.action.query": "INSERT INTO public.debezium_heartbeat (id, ts) VALUES (1, now()) ON CONFLICT (id) DO UPDATE SET ts = EXCLUDED.ts"
  }
}

Topics are named topicPrefix.schemaName.tableName, here orders.public.orders. Give every connector its own slot.name (the default is debezium). Kafka's FileConfigProvider, registered on the workers as fileProvider, reads the password from a file and keeps it out of the connector config. The connector runs one task, whatever tasks.max says.

How do you keep a replication slot from filling the disk?

A replication slot is PostgreSQL's bookmark for one consumer: slots persist across crashes and know nothing about the state of their consumers, and PostgreSQL keeps the WAL and catalog rows a slot requires, even with nothing connected. Stop the connector for a long weekend, or delete it and forget the slot, and WAL piles up until the disk fills; in extreme cases the database shuts down to prevent transaction ID wraparound.

A running connector holds WAL too when its tables change rarely and others change often; the heartbeat above fixes that, and its table must be in the publication. Two settings cap the damage, at the price of the slot:

• max_slot_wal_keep_size, added in PostgreSQL 13, limits the WAL slots may retain at checkpoint time (default -1, unlimited); a slot that would need more is marked invalid.
• idle_replication_slot_timeout, added in PostgreSQL 18, invalidates a slot inactive for longer than the timeout (default 0, off).

A lost slot is no longer usable, so recovery means a new slot and a new snapshot. Set the cap above the WAL your longest tolerable outage writes, and alert well before it:

SELECT slot_name, active, wal_status, inactive_since, invalidation_reason,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal,
       pg_size_pretty(safe_wal_size) AS headroom
FROM pg_replication_slots
WHERE slot_type = 'logical';

In pg_replication_slots, wal_status is reserved, extended, unreserved (some required WAL goes at the next checkpoint) or lost; safe_wal_size is the WAL that can still be written before the slot is lost (null without a cap). Alert on growing retained WAL, on unreserved and on any inactive slot.

Two rules from the Debezium PostgreSQL documentation:

• One slot per connector, never dropped while in use. When connectors share a slot, each change reaches only one of them, with no warning, and a connector's stored position exists only while its slot does.
• Plan failovers and upgrades. Through PostgreSQL 15, logical slots exist only on the primary; 16 can create them on replicas, synchronized by hand; 17 and later support failover slots (slot.failover=true, plus synchronized_standby_slots on the primary). For a major upgrade, block writes, let the connector drain, stop it, drop the slot, upgrade, and recreate the slot before writes resume, or the connector silently skips the changes in between.

What do MySQL, SQL Server and Oracle need?

MySQL. The MySQL connector reads the binlog in row format with full row images. In MySQL 8.4, binary logging is on by default, in row format, and binlog_row_image defaults to full (binlog_format itself is deprecated), but check your server:

# my.cnf, as in Debezium's MySQL documentation
server-id                  = 223344
log_bin                    = mysql-bin
binlog_format              = ROW
binlog_row_image           = FULL
binlog_expire_logs_seconds = 864000   # 10 days; the MySQL 8.4 default is 2592000 (30 days)

The connector's user needs SELECT, RELOAD, SHOW DATABASES, REPLICATION SLAVE, REPLICATION CLIENT; binlog_row_value_options must not be PARTIAL_JSON; on Amazon RDS, the binlog needs automated backups enabled. The connector keeps the DDL it reads from the binlog in a schema history topic for its own use. If the binlog it needs was purged while it was down, it fails and asks for a new snapshot (or takes one with snapshot.mode=when_needed), so keep retention longer than any outage you plan to survive.

SQL Server. Debezium reads the change tables that SQL Server's change data capture fills from the transaction log through SQL Server Agent jobs; the connector needs SQL Server 2016 SP1 or later, Standard or Enterprise. To enable CDC, a sysadmin runs sys.sp_cdc_enable_db in the database, then a db_owner runs sys.sp_cdc_enable_table for each table, with @role_name naming a gating role (created if missing) or NULL for none.

The cleanup job runs daily at 2 A.M. and by default keeps 4,320 minutes (3 days) of changes, so a connector stopped for longer finds its oldest changes gone. The log cannot truncate until the capture job has gathered the changes, even in the simple recovery model. A new column does not reach an existing change table: a DBA refreshes the capture table, and a second capture instance can carry the new structure beside the old one.

Oracle. The Oracle connector needs archive log mode (Debezium's example enables it from mount state) and supplemental logging. It reads redo and archive logs through LogMiner by default, or OpenLogReplicator or XStream (database.connection.adapter). XStream is a commercial component of Oracle GoldenGate, so check your licence.

What does CDC guarantee about delivery and order?

Debezium provides at-least-once delivery: no change is missed, but some can be delivered more than once. After a crash, the connector resumes from the last offset Kafka Connect recorded and re-sends what came after it; how many repeats depends on the offset flush period and the write volume just before the crash.

Kafka Connect 3.3.0 and later, in distributed mode, can run Debezium's PostgreSQL, MySQL, SQL Server, Oracle, MariaDB and MongoDB connectors exactly once (exactly.once.source.support=enabled on every worker, exactly.once.support=required on the connector), but Debezium says it remains unclear whether that implementation is fully correct. Keep consumers idempotent either way.

Order holds per key. The record key is the primary key (message.key.columns overrides it), and Kafka writes records with the same key to the same partition, which every consumer reads in the order written. All changes to order 1042 arrive in order; different rows, tables and topics share no order. Truncate events have a null key, so they keep their place only on single-partition topics.

Idempotent consumers make repeats harmless:

• Upsert and delete by key. Debezium's changes are idempotent: a sequence of events always ends in the same state.
• Skip what you have seen. Store the last LSN applied per key and skip events at or below it, which also guards side effects such as emails. The dead letter queue guide covers idempotency keys and safe replay.
• Compact topics that copy a table. Kafka keeps at least the last value for each key and never reorders records, but removes tombstones after delete.retention.ms (24 hours by default), so a consumer that lags longer misses deletes.

How does the outbox pattern use CDC?

Table-level CDC publishes your schema, so a renamed column becomes every consumer's problem. For business events such as OrderPaid, the tempting code updates the database, then publishes a message: a dual write, where a failure in one of the two might leave the data inconsistent. The transactional outbox removes it: the application writes the event to an outbox table in the same transaction as the change, and a connector publishes the outbox rows. Both commit or neither does.

Debezium's outbox event router expects this table by default:

CREATE TABLE public.outbox (
  id            uuid         PRIMARY KEY,  -- unique event ID, sent as the "id" header
  aggregatetype varchar(255) NOT NULL,     -- routes to topic outbox.event.<aggregatetype>
  aggregateid   varchar(255) NOT NULL,     -- the message key: one order's events stay in order
  type          varchar(255) NOT NULL,
  payload       jsonb
);

BEGIN;
UPDATE public.orders SET status = 'paid' WHERE id = 1042;
INSERT INTO public.outbox (id, aggregatetype, aggregateid, type, payload)
VALUES (gen_random_uuid(), 'order', '1042', 'OrderPaid',
        '{"orderId": 1042, "amount": "129.00", "currency": "CAD"}');
COMMIT;

Run the router in its own connector, with its own slot and a publication holding only the outbox; Debezium says a connector that applies it should capture outbox tables only.

table.include.list=public.outbox
tombstones.on.delete=false
transforms=outbox
transforms.outbox.type=io.debezium.transforms.outbox.EventRouter
transforms.outbox.table.expand.json.payload=true
value.converter=org.apache.kafka.connect.json.JsonConverter

The router warns on an UPDATE (table.op.invalid.behavior=warn) and filters out DELETE operations, so a scheduled job can remove published rows, and tombstones.on.delete=false keeps those deletes from emitting tombstones. It is not compatible with the MongoDB connector, which has its own router. Event-driven architecture shows where these events fit, and CDC can also keep a legacy database and its replacement in step during a strangler fig migration.

How do you land change events in a lake or warehouse?

Land the raw events first: append every event, unchanged, to a bronze table, the raw layer of a medallion architecture, so anything downstream can be rebuilt by replay. Then merge into a current-state table.

Debezium's new record state extraction turns the envelope into a row: it keeps after and adds the metadata you list. By default it drops delete events, because most consumers cannot yet handle them; rewrite instead turns each delete into a row of the old values with __deleted set to true:

transforms=unwrap
transforms.unwrap.type=io.debezium.transforms.ExtractNewRecordState
transforms.unwrap.delete.tombstone.handling.mode=rewrite
transforms.unwrap.add.fields=op,table,lsn,source.ts_ms

Records then carry __op, __table, __lsn and __source_ts_ms. A batch can hold several changes to one row, or the same change twice, and Iceberg's MERGE INTO throws an error when more than one source row updates a target row. Keep the latest change per key, and compare LSNs so an older event cannot overwrite newer data:

-- Spark SQL on Iceberg
MERGE INTO lake.silver.orders t
USING (
  SELECT * FROM (
    SELECT b.*, row_number() OVER (PARTITION BY id ORDER BY __lsn DESC) AS rn
    FROM lake.bronze.orders_cdc_batch b
  ) latest
  WHERE rn = 1
) s
ON t.id = s.id
WHEN MATCHED AND s.__lsn > t.lsn THEN
  UPDATE SET t.status = s.status, t.lsn = s.__lsn, t.deleted = (s.__op = 'd')
WHEN NOT MATCHED THEN
  INSERT (id, status, lsn, deleted) VALUES (s.id, s.status, s.__lsn, s.__op = 'd');

Deletes become a flag, so the key keeps its LSN and a replayed older event cannot bring the row back; readers use a view that filters out deleted. Bronze keeps the history; where analysts need every version, model it, for example as data vault satellites. A bank's regulatory data platform learned this with append-only Iceberg raw tables fed partly by CDC: without de-duplication to the latest version above them, the first correction is counted twice.

Managed services can replace the connector. AWS DMS runs full load plus CDC, or CDC only; on PostgreSQL it reads through logical replication slots with the test_decoding plug-in, so the slot rules apply, and AWS states there are no SLAs for CDC latency, which can increase to several minutes or longer. Google Datastream is a serverless CDC and replication service into BigQuery or Cloud Storage, and Microsoft Fabric Mirroring continuously replicates sources such as Azure SQL Database and Azure Database for PostgreSQL into OneLake.

What breaks in production, and how do you fix it?

When should you not use change data capture?

• Small or slow-changing tables. A reference table of a few thousand rows that changes weekly is cheaper to copy in full each night, deletes included, with no slot or connector to run.
• The event already exists. If the source already publishes what you need through an API, a queue or a webhook, subscribe to it.
• Consumers need business meaning. Rows describe storage; OrderShipped describes the business. Use the outbox or the application's own events.
• No owner. A CDC pipeline is a production system, with slots to watch, connectors to upgrade and schema changes to coordinate. Without someone on call, a nightly extract that fails loudly is safer than a stream that fails quietly.

Roll out your first CDC pipeline

1. Pick one flow. One database, a few tables, one target, and a named owner on each side.
2. Prepare the source. Check wal_level, replica identities and disk headroom (PostgreSQL), binlog settings and retention (MySQL) or the edition and SQL Server Agent (SQL Server), and book any restart. Grant the minimum, and set the WAL cap, heartbeat and alerts before the connector starts.
3. Rehearse with production-like volume. Time the snapshot, stop the connector for an hour and watch retained WAL, kill it mid-snapshot, and count duplicates after a restart.
4. Build the consumer for duplicates and deletes. Raw events in bronze, rewrite for deletes, de-duplication by LSN and idempotent merges.
5. Go live and reconcile. Compare row counts and checksums between source and target daily until they have matched for several weeks.

Computese's data platform service runs change data capture on Debezium with schema evolution handled, and the integrations service adds durable queues ordered per key, idempotent consumers and dead-letter queues with safe replay.