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.
What to notice: your code doesn't appear anywhere. A connector is configuration, sent to the worker's REST API.
- Connect runs as workers: Java processes that join a group, like consumers, and share the
work. Each connector is split into tasks (up to
tasks.max), the units that move the data. - A converter turns records into bytes and back:
JsonConverter, orAvroConverterwith the Schema Registry (lesson 5.1), as in the lab. - A single message transform (SMT) changes one record on its way: renames its topic, drops a field, keeps only part of it.
- The workers keep all their state in Kafka: connector configurations, the source connectors'
positions and the statuses, in three topics (
connect-configs,connect-offsets,connect-status).
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:
- A snapshot: on first start, it reads every existing row and writes one event per row, with
op: r(read). - 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.
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 connector that is stopped, failing or deleted makes WAL pile up. Deleting a connector does not delete its slot.
- A running connector can do the same. It only confirms a position when an event from a table it
captures reaches it. If its tables are quiet while other tables are busy, its slot holds all the
busy tables' WAL. Debezium's answer is a heartbeat: every few seconds the connector writes to
a small table it captures (
heartbeat.action.query), and confirms the position of that event.
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
- Give the connector its own database user with the
REPLICATIONattribute andSELECTon the captured tables. Have the DBA create the publication. - Set
max_slot_wal_keep_size, alert on WAL held by each slot (pg_replication_slots), use heartbeats, and drop the slot when you remove a connector. - The topic prefix becomes part of the Avro schema names, which allow only letters, digits and
underscores. A prefix with a hyphen fails, unless you set
schema.name.adjustment.mode=avro. - Table changes travel too: a new column becomes a new schema version, checked by the registry (lesson 5.2). Agree on compatible table changes with the database owners.
- Run at least two Connect workers in production, and keep the three internal topics replicated 3 times.
- Capturing an application's tables exposes its internal design. For events meant for other teams, the outbox pattern is cleaner (lesson 9.3).
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
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
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:
"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?
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:
"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
- Kafka Connect runs connectors from configuration: workers run tasks, converters handle the bytes, SMTs change records on the way.
- Debezium snapshots a table, then streams every change from the WAL as an event with
before,after,opandsource. - A delete becomes a
devent plus a tombstone;ExtractNewRecordStateturns the stream into a "latest row" topic. - A replication slot keeps WAL until the connector confirms it: stopped, deleted and even idle connectors can fill the disk.
- Use heartbeats,
max_slot_wal_keep_sizeand slot monitoring, and drop the slot when you remove a connector.