34 min · theory + hands-on practical

Change data capture from PostgreSQL

Some of Brightlane's most important data isn't born as events: it lives in a database table. This lesson streams every change of that table into Kafka, without touching the application that writes it. It also shows the one risk that comes with it, a database log that grows quietly until the disk is full.

Theory

Example: billed with yesterday's prices

Brightlane's tariffs live in the pricing team's PostgreSQL database. Billing gets them from a CSV file the database exports every night at 02:00.

On a Monday at 09:00, the pricing team raised the price at twelve coastal sites by 6 cents per kWh. Billing kept charging the old prices until the next export, at 02:00 on Tuesday. That made 412 sessions too cheap, and finance only found out at the end of the month. The obvious fix, "export every minute", would still miss changes, put load on the database, and never tell billing about a deleted tariff.

Kafka Connect

Kafka Connect is the part of Apache Kafka that moves data between Kafka and other systems without you writing a producer or consumer. You configure connectors, plug-ins that know one system. A source connector reads from a system and writes to Kafka; a sink connector reads from Kafka and writes to a system.

A Kafka Connect worker runs two connectors. The source connector's task reads changes from PostgreSQL, passes each record through transforms and a converter, and writes it to a Kafka topic. The sink connector's task reads from a topic, converts and transforms each record, and writes it to the reporting database. You manage connectors through the worker's REST API. The worker stores connector configurations, source positions and statuses in three Kafka topics.

What to notice: your code doesn't appear anywhere. A connector is configuration, sent to the worker's REST API.

Change data capture with Debezium

PostgreSQL writes every change to its write-ahead log (WAL) before it changes the table itself. With wal_level=logical, it can decode that log into row changes for other programs. Reading those row changes and turning them into events is change data capture (CDC).

Debezium is a family of open-source source connectors for CDC. Its PostgreSQL connector works in two phases:

  1. A snapshot: on first start, it reads every existing row and writes one event per row, with op: r (read).
  2. Streaming: from then on, it writes one event per change: c (insert), u (update), d (delete). After a delete it also writes a tombstone (lesson 4.3), so a compacted topic can forget the row.

An UPDATE on the tariffs table is written to PostgreSQL's write-ahead log. The replication slot remembers how far the connector has confirmed reading, so PostgreSQL keeps the log from there. Through the publication, pgoutput streams the change to the Debezium task, which writes a change event keyed by tariff_id, with op u, the row before and after, and source details such as the log position. At start-up the connector first takes a snapshot of every existing row, with op r.

What to notice: the event has both the old and the new row, and the key is the table's primary key.

Each event's key is the row's primary key, so all changes to one tariff land in one partition, in order. The value holds before, after, op, and source: the database, table, transaction id and log position. PostgreSQL only logs the old row in full if the table has REPLICA IDENTITY FULL; otherwise before is empty for updates.

Debezium uses pgoutput, the decoding plug-in built into PostgreSQL, and a publication: the list of tables whose changes are streamed.

Replication slots: the risk

A replication slot is PostgreSQL's record of how far a reader of the WAL has confirmed reading. PostgreSQL keeps all WAL after that point, however much it is. That's what makes CDC lossless: a connector that was down for an hour continues exactly where it stopped. It's also the risk:

A full disk doesn't stop the connector; it stops the database. Since PostgreSQL 13, max_slot_wal_keep_size limits how much WAL a slot may hold. Beyond it the slot is invalidated: CDC needs a new snapshot, but the database survives.

Flattening events for consumers

Many consumers only want each row's current state, not the change history. Two SMTs do that inside the connector: ExtractNewRecordState keeps only the after row and turns a delete into a tombstone, and RegexRouter renames the topic. The result fits a compacted topic: the latest tariff per id, which billing reads in module 7.

In production

Practical

Note

Notebook: code/module-06/01-change-data-capture.ipynb · Lab services: kafka-1..3, schema-registry, postgres, connect · Time: ~16 min

What you'll do: register a Debezium connector, read its snapshot and change events, add a second connector that flattens them into a compacted topic, and watch two replication slots hold WAL while both connectors report RUNNING.

Start the lab's connect profile from the code/ folder. The first start builds two small images and downloads Debezium's, which takes a few minutes:

docker compose --profile connect up -d --wait

Step 1: The Connect worker

GET /
{'version': '4.3.1', 'commit': '26b251a451ce941d', 'kafka_cluster_id': 'ZpMZEQqGS768XpROns7_Ug'}
GET /connector-plugins
sink    io.debezium.connector.jdbc.JdbcSinkConnector  3.7.0.Final
...
source  io.debezium.connector.postgresql.PostgresConnector  3.7.0.Final
...

Step 2: The pricing database

('tar-site-C01-1', 'site-C01', Decimal('0.5600'), Decimal('0.1500'))
('tar-site-C02-1', 'site-C02', Decimal('0.5200'), Decimal('0.1500'))
('tar-site-C03-1', 'site-C03', Decimal('0.5300'), Decimal('0.1500'))
36 tariffs

Step 3: Register a CDC connector

code/module-06/01-change-data-capture.ipynb
cdc = {
    "connector.class": "io.debezium.connector.postgresql.PostgresConnector",
    "plugin.name": "pgoutput",                         # PostgreSQL's built-in logical decoding
    "database.hostname": "postgres", "database.port": "5432", "database.dbname": "pricing",
    "database.user": "brightlane", "database.password": "brightlane-lab",
    "topic.prefix": PREFIX,
    "table.include.list": "public.tariffs",
    "slot.name": "m06l01_pricing_cdc", "publication.name": "m06l01_pricing_cdc",
    "publication.autocreate.mode": "filtered",
    "time.precision.mode": "connect",                  # dates as Avro dates
    "topic.creation.default.replication.factor": "3", "topic.creation.default.partitions": "1",
}
lab.connect_api("PUT", "/connectors/m06l01-pricing-cdc/config", cdc)

The brokers don't create topics on their own (auto.create.topics.enable=false), so the connector creates its topics through topic.creation.default.*.

PUT /connectors/m06l01-pricing-cdc/config
RUNNING ['RUNNING']

Step 4: Read the snapshot

36 events; the first one:
key: {'tariff_id': 'tar-site-N01-1'}
{
 "op": "r",
 "before": null,
 "after": {
  "tariff_id": "tar-site-N01-1",
  "site_id": "site-N01",
  "price_per_kwh": "0.4900",
  "idle_fee_per_min": "0.1000",
  "valid_from": "2026-01-01"
 },
 "source": {
  "snapshot": "first",
  "table": "tariffs",
  "lsn": 92982744,
  "txId": 864
 }
}

Prices arrive as exact decimals (Decimal('0.4900') in Python), not as floating-point numbers.

Step 5: Change a price, delete a tariff

code/module-06/01-change-data-capture.ipynb
lab.pg("UPDATE tariffs SET price_per_kwh = 0.5900 WHERE tariff_id = 'tar-site-N03-1'")
lab.pg("DELETE FROM tariffs WHERE tariff_id = 'tar-site-V10-1'")
offset 36, key {'tariff_id': 'tar-site-N03-1'}:
   {"op": "u", "before": {..., "price_per_kwh": "0.5100", ...}, "after": {..., "price_per_kwh": "0.5900", ...}, "source": {"snapshot": "false", "table": "tariffs", "lsn": 92982784, "txId": 865}}
offset 37, key {'tariff_id': 'tar-site-V10-1'}:
   {"op": "d", "before": {"tariff_id": "tar-site-V10-1", ...}, "after": null, "source": {"snapshot": "false", "table": "tariffs", "lsn": 92982984, "txId": 866}}
offset 38, key {'tariff_id': 'tar-site-V10-1'}:
   None  (a tombstone)

Change it: the latest tariff per id, in a compacted topic

A second connector applies two SMTs:

code/module-06/01-change-data-capture.ipynb
         "transforms": "unwrap,route",
         "transforms.unwrap.type": "io.debezium.transforms.ExtractNewRecordState",
         "transforms.route.type": "org.apache.kafka.connect.transforms.RegexRouter",
         "transforms.route.regex": r"m06l01_state\.public\.(.*)",
         "transforms.route.replacement": f"{PREFIX}.$1"}

The first record, then the update and the delete in m06l01_pricing.tariffs:

0 {'tariff_id': 'tar-site-N01-1'} {'tariff_id': 'tar-site-N01-1', 'site_id': 'site-N01', 'price_per_kwh': Decimal('0.4900'), 'idle_fee_per_min': Decimal('0.1000'), 'valid_from': datetime.date(2026, 1, 1)}
35 {'tariff_id': 'tar-site-N03-1'} {'tariff_id': 'tar-site-N03-1', 'site_id': 'site-N03', 'price_per_kwh': Decimal('0.6100'), 'idle_fee_per_min': Decimal('0.1000'), 'valid_from': datetime.date(2026, 1, 1)}
36 {'tariff_id': 'tar-site-V09-1'} None

Break it: a busy table nobody captures

The pricing service also keeps an audit table that no connector captures. It writes 100,000 rows. Predict first: the connectors are healthy. How much WAL does PostgreSQL keep for them?

code/module-06/01-change-data-capture.ipynb
lab.pg("INSERT INTO price_audit (entry) SELECT repeat('price check ', 40) FROM generate_series(1, 100000)")
connectors: {'m06l01-pricing-cdc': 'RUNNING', 'm06l01-pricing-state': 'RUNNING'}
('m06l01_pricing_cdc', True, '59 MB')
('m06l01_pricing_state', True, '59 MB')

Then the first connector gets a heartbeat that writes to a small table it captures:

code/module-06/01-change-data-capture.ipynb
    "heartbeat.interval.ms": "5000",
    "heartbeat.action.query": "INSERT INTO debezium_heartbeat VALUES (1, now()) "
                              "ON CONFLICT (id) DO UPDATE SET ts = excluded.ts"})

30 seconds later:

('m06l01_pricing_cdc', True, '586 kB')
('m06l01_pricing_state', True, '60 MB')

What just happened

The connector's task opened a replication slot and a publication for tariffs, read all 36 rows as op: r snapshot events, and then switched to streaming. The price change arrived with the old and the new row, because the table logs full rows (REPLICA IDENTITY FULL). The delete arrived as a d event followed by a tombstone. Nobody exported a file, and billing could see the 09:00 price change within a second. The second connector flattened the same changes into plain rows and tombstones in a compacted topic. In the break-it step, 100,000 audit rows produced about 59 MB of WAL. Neither connector captures that table, so neither received an event, confirmed a position or let PostgreSQL free the WAL, even though both reported RUNNING. On a production database this grows until the disk is full. The heartbeat gave the first connector an event to confirm every 5 seconds, and its slot dropped to under 1 MB; the second still held 60 MB.

Clean up

The last cell deletes the connectors and their slots and publications, the topics and schemas, and resets the database. If anything gets stuck, docker compose down -v in the code/ folder resets the whole lab.

Key takeaways

Next: Sinks, errors and dead letters in Connect