Software · 04 / 06 · Kafka · Debezium · PostgreSQL
CDC Pipeline
Streams every MySQL insert, update and delete through Debezium and Kafka into PostgreSQL, then proves the copy is identical, even after crashes.
- MySQL 8.4
- Debezium 3.0
- Kafka 3.9
- Python
- PostgreSQL 16
- Docker
Why I built this
Companies keep their live database separate from analytics, search and reporting. Nightly copies are always a day stale, and writing to two places from app code slowly drifts apart. Change data capture is how fintech and e-commerce teams keep those copies in sync within milliseconds, without silently getting balances or reports wrong.
How do you keep a second copy of a live database in sync within milliseconds, and know it is right instead of hoping? I wanted to stream every change across, crash the pipeline midway, then prove the copy matched row by row.
What I built
Debezium reads MySQL's binlog and publishes every row change to Kafka. A sink I wrote applies the changes to PostgreSQL idempotently, keyed on primary key and binlog position, and evolves the schema on the fly.
At the end of every run it waits for catch-up and compares every table row by row, plus a SHA-256 per table.
How it works
Each target row stores the binlog position that wrote it, so replaying an old change is a no-op.
How it stays correct
Debezium keys by primary key, so each row lives in one partition, handled by one consumer at a time.
ON CONFLICT ... WHERE target.position < incoming.position.
Commit PostgreSQL first, then the Kafka offset. At-least-once delivery, idempotent apply.
New columns are added in the same transaction as the data, under an advisory lock.
Measured
- Run
- 1,000,021 changes at 3,100/s, 3 sinks, a column added at 500k, a sink SIGKILLed at 700k
- Lag
- p50 89 ms, p95 141 ms, p99 183 ms
- Verification
- All 5 tables identical, row by row and by SHA-256, across 440k rows
- Tests
- 33 tests, 93% coverage, including kills inside and between commits