Change Data Capture with Debezium and Kafka: Postgres Guide

Change Data Capture with Debezium and Kafka: Postgres Guide

Most analytics pipelines still copy data out of operational databases with a nightly batch job and a WHERE updated_at > :last_run filter. It works until someone asks for fresher data, a row is hard-deleted, or a table turns out to have no reliable timestamp. Change data capture (CDC) solves all three by reading the database’s own transaction log and turning every insert, update and delete into an event.

This guide is for data engineers who want to stream changes from PostgreSQL into Kafka with Debezium, and from there into a warehouse or lakehouse. It covers how log-based CDC works, the database and connector setup, what the events look like, how to apply them downstream with MERGE, and the pitfalls that cause most production incidents.

Log-based CDC vs the alternatives

There are three common ways to capture changes from a relational database. Log-based CDC is the only one that sees every change without adding load to your queries or your application code.

ApproachHow it worksCaptures deletesMain drawback
Query-based (polling)Periodic SELECT on a timestamp or ID columnNo (hard deletes are invisible)Misses intermediate updates; needs a trustworthy updated_at
Trigger-basedTriggers write changes to audit tablesYesExtra write load on every transaction; triggers to maintain
Log-based (Debezium)Reads the write-ahead log through logical replicationYesMore moving parts; replication slots need monitoring

How Debezium reads PostgreSQL

Debezium is an open-source CDC platform that usually runs as a set of source connectors on Kafka Connect. For PostgreSQL the flow is:

  1. PostgreSQL writes every committed change to its write-ahead log (WAL).
  2. A logical replication slot decodes the WAL using the built-in pgoutput plugin and remembers how far the consumer has read.
  3. A publication defines which tables are included in the stream.
  4. The Debezium connector reads from the slot, converts each row change to an event and writes it to a Kafka topic, one topic per table by default.
  5. Sink connectors or stream processors apply those events to the target system.

The first time the connector starts it takes a consistent snapshot of the existing rows, then switches to streaming from the log position where the snapshot began, so nothing is missed in between.

Step 1: Prepare the database

Logical decoding needs wal_level = logical, which requires a restart. On managed services this is a parameter rather than a config file edit, for example rds.logical_replication on Amazon RDS. Then create a dedicated role and a publication:

-- postgresql.conf (restart required)
-- wal_level = logical

-- A dedicated, least-privilege role for the connector
CREATE ROLE debezium WITH LOGIN REPLICATION PASSWORD '********';
GRANT USAGE ON SCHEMA public TO debezium;
GRANT SELECT ON public.orders, public.customers TO debezium;

-- Create the publication yourself so the connector does not need
-- ownership rights on the tables
CREATE PUBLICATION dbz_publication
  FOR TABLE public.orders, public.customers;

-- Optional: emit the full old row on UPDATE and DELETE
ALTER TABLE public.orders REPLICA IDENTITY FULL;

With the default replica identity, the before image of a delete contains only the primary key, and updates carry no old values unless the key itself changed. REPLICA IDENTITY FULL gives you the full previous row at the cost of more WAL volume, so enable it only on tables where you need it.

Step 2: Register the connector

Connectors are created by posting JSON to the Kafka Connect REST API. A minimal, production-minded configuration looks like this:

{
  "name": "shop-postgres-cdc",
  "config": {
    "connector.class": "io.debezium.connector.postgresql.PostgresConnector",
    "database.hostname": "pg-primary.internal",
    "database.port": "5432",
    "database.user": "debezium",
    "database.password": "${file:/opt/secrets/pg.properties:password}",
    "database.dbname": "shop",
    "topic.prefix": "shop",
    "plugin.name": "pgoutput",
    "slot.name": "debezium_shop",
    "publication.name": "dbz_publication",
    "publication.autocreate.mode": "disabled",
    "table.include.list": "public.orders,public.customers",
    "snapshot.mode": "initial",
    "heartbeat.interval.ms": "10000",
    "transforms": "unwrap",
    "transforms.unwrap.type": "io.debezium.transforms.ExtractNewRecordState",
    "transforms.unwrap.add.fields": "op,source.ts_ms,source.lsn"
  }
}

A few of these settings deserve a note:

  • topic.prefix names the topics, so changes to public.orders land in shop.public.orders. Treat it as permanent: changing it later means new topics and a new snapshot.
  • slot.name must be unique per connector. Two connectors sharing one slot will steal each other’s changes.
  • The password is read through a Kafka Connect config provider rather than stored in plain text.
  • The unwrap transform flattens the event to just the new row and adds metadata columns (__op, __source_ts_ms, __source_lsn), which most sinks find easier to consume. How deletes are represented after flattening is configurable and the option names differ between Debezium versions, so check the documentation for the version you run.

What a change event looks like

Without the flattening transform, every event value is an envelope with the row before and after the change, source metadata and an operation code:

{
  "before": { "order_id": 1001, "status": "PENDING", "total": 59.90 },
  "after":  { "order_id": 1001, "status": "SHIPPED", "total": 59.90 },
  "source": { "db": "shop", "schema": "public", "table": "orders",
              "lsn": 24023128, "txId": 5567, "ts_ms": 1763370000000 },
  "op": "u",
  "ts_ms": 1763370000123
}

The op field is c for create, u for update, d for delete and r for rows read during a snapshot. The Kafka message key is the table’s primary key, so all changes to one row go to the same partition and stay in order. By default a delete is followed by a tombstone (same key, null value) so that log-compacted topics can eventually remove the row.

Step 3: Apply changes downstream with MERGE

Land the events in a staging table first, then merge them into the target. A micro-batch can contain several changes to the same key, so keep only the latest one, ordered by log sequence number (LSN) rather than by arrival time:

MERGE INTO analytics.orders AS t
USING (
  SELECT *
  FROM (
    SELECT s.*,
           ROW_NUMBER() OVER (
             PARTITION BY order_id
             ORDER BY __source_lsn DESC
           ) AS rn
    FROM staging.orders_changes s
  ) ranked
  WHERE rn = 1
) AS c
ON t.order_id = c.order_id
WHEN MATCHED AND c.__op = 'd' THEN DELETE
WHEN MATCHED AND c.__source_lsn > t.source_lsn THEN UPDATE SET
  status     = c.status,
  total      = c.total,
  source_lsn = c.__source_lsn
WHEN NOT MATCHED AND c.__op <> 'd' THEN INSERT
  (order_id, status, total, source_lsn)
  VALUES (c.order_id, c.status, c.total, c.__source_lsn);

Storing the last applied LSN on the target row makes the merge idempotent. Kafka Connect source connectors deliver at least once by default, so after a restart you may see the same events again; the LSN comparison means replays are harmless. If you need history rather than current state, append every event instead and build a slowly changing dimension from the stream.

Re-snapshotting without downtime

Sooner or later you will add a table or need to reload one. Debezium’s incremental snapshots read a table in chunks while streaming continues, and are triggered by inserting a row into a signalling table (set with signal.data.collection and included in the publication):

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

Common pitfalls

WAL growth from a stalled slot

A replication slot makes PostgreSQL keep WAL until the consumer confirms it. If the connector is stopped, or simply forgotten in a test environment, WAL accumulates until the disk fills and the database stops accepting writes. Monitor slot lag and alert on it:

SELECT slot_name,
       active,
       pg_size_pretty(
         pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)
       ) AS retained_wal
FROM pg_replication_slots;

Setting max_slot_wal_keep_size (PostgreSQL 13 and later) caps how much WAL a slot can hold. The trade-off is that a slot which exceeds the cap is invalidated and the connector needs a new snapshot, which is still better than a full disk on the primary. Drop slots you no longer use with pg_drop_replication_slot.

Quiet tables on a busy server

If the captured tables rarely change but other tables or databases on the same server are busy, the connector has nothing to acknowledge and the slot’s position does not move, so WAL still builds up. heartbeat.interval.ms makes the connector emit regular heartbeat events so that it can confirm its position. Where the activity is in a different database, add heartbeat.action.query to write to a small heartbeat table so there is always a change to capture.

TOAST columns that go missing

PostgreSQL stores large text and JSON values out of line (TOAST). When an update does not touch such a column, its value is not written to the WAL, and Debezium sends the placeholder __debezium_unavailable_value instead. A naive merge then overwrites good data with that string. Either skip the column when the placeholder is present, or set REPLICA IDENTITY FULL on the table.

Schema changes and failover

  • Adding a nullable column flows through automatically, but renames and type changes can break consumers. Use Avro or Protobuf with a schema registry and a compatibility rule so that breaking changes fail early.
  • New tables must be added to both the publication and table.include.list.
  • A replication slot belongs to one server. Plan what happens on failover: recent PostgreSQL releases can synchronise slots to a standby, while on older versions a promoted replica has no slot and the connector must re-snapshot.
  • Ordering is guaranteed per key, not across tables. Do not assume that a parent row arrives before its child rows in a different topic.

Key takeaways

  • Log-based CDC captures every insert, update and delete, including hard deletes that polling can never see.
  • On PostgreSQL you need wal_level = logical, a replication role, a publication and one replication slot per connector.
  • Flatten events with ExtractNewRecordState and keep the operation and LSN as metadata columns.
  • Make the downstream MERGE idempotent by deduplicating on the key and comparing LSNs, because delivery is at least once.
  • Monitor replication slot lag from day one, use heartbeats on quiet databases, and handle the TOAST placeholder before it reaches your tables.

Start with one or two tables, get the monitoring and the merge logic right, and then widen the publication. A CDC pipeline that is boring to operate is worth far more than one that is merely fast.

Comments

No comments yet. Why don’t you start the discussion?

Leave a Reply

Your comment appears after we check it.