Databases
Advanced 14 min read Databases

CDC and Replication

Turn your primary database into the event log

Change Data Capture streams committed row changes — inserts, updates, deletes — from the database into downstream systems, transforming the database itself into the event source for pipelines and analytics.

CDClog-based capturereplication slotDebeziumWAL tailingreplica lag
Prerequisites: PostgreSQL In Depth
Before we start

What you will learn

  • Differentiate query-based, trigger-based, and log-based capture
  • Set up a logical replication slot responsibly
  • Handle schema evolution and ordering on the consuming side
Introduction

What is this?

CDC lets you publish the row-level changes your database commits, without touching application code. Log-based capture tails the transaction log (Postgres WAL, MySQL binlog), translating commits into events — the foundation for replica pipelines, event sourcing, cache invalidation, and streaming into lakes.

Motivation

Why do you need it?

  • Warehouse/streaming wants source-table truth without polling or double writes.
  • It removes most of 'how did the DB and the warehouse diverge?'.
  • REQUIRED coordinator note: kafka-topics (de-streaming) is the usual downstream hop for these changes.
Think about it

Mental model

Imagine

A courtroom stenographer recording everything verbatim

Think of

Log-based CDC

The stenographer captures exactly what happened in order, without the speakers having to pause and repeat. Downstream rooms (pipelines) subscribe to the transcript instead of poking the speakers.

Mechanics

How it works

  • Query-based capture polls SELECT with watermarks (laggy, cheap to start).
  • Trigger-based capture writes change records into an outbox table (atomic with the transaction).
  • Log-based capture reads the WAL/binlog via a replication slot, reconstructing inserts/updates/deletes including before/after values.
  • A consumer keeps the slot position; an unread slot grows WAL and can fill disk (slot hygiene is production-critical).
  • Ordering guarantees: within a table and partition, changes arrive in commit order; across tables, logical ordering is not scalar.

Log-based CDC pipeline.

Transaction commits
Replication slot
Debezium/kafka connect
Stream / topic
Downstream
Reference

Key commands

SELECT * FROM pg_replication_slots;

See active slots, their lag, and their WAL retention impact.

SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn) ...

How far the consumer lag is behind the newest WAL position.

SELECT pg_drop_replication_slot('slot_name');

Remove an abandoned slot before it fills the disk.

Hands-on

Examples

An outbox table makes application changes CDC-able

An outbox table makes application changes CDC-able
-- App transaction updates the orders row and appends a change outbox row:
BEGIN;
  INSERT INTO orders(id, state) VALUES (101, 'shipped') ON CONFLICT (id) DO UPDATE SET state = 'shipped';
  INSERT INTO outbox(payload, topic) VALUES ('{"id":101,"state":"shipped"}', 'orders.changes');
COMMIT;
-- The consumer reads the outbox table as an ordered, atomic stream.

Wrapping application writes and the outbox event in one transaction is atomic; even log-less capture can't lose the event without losing the change.

Real world

In production

  • A warehouse ingestion subscribes to order changes via Debezium into Kafka, decoupling load spikes from the OLTP DB.
  • A search index refreshes on document updates using the same CDC stream the analytics pipeline uses.
  • An event-sourced ledger derives its event log from committed database transactions, not from dual writes in app code.
Guidelines

Best practices

  • Prefer log-based capture wherever correctness and latency matter; reserve polling for prototyping.
  • Monitor slots: confirmed_flush_lsn lag, and disk if a consumer dies.
  • Keep the outbox pattern if you cannot run logical replication (MySQL/Postgres edge cases, no heartbeat).
  • Design the schema so CDC events map to a stable topic/partition strategy for ordering.
Watch out

Common mistakes

  • Letting a slot run without a consumer until the DB fills the disk — the classic CDC outage.
  • Adding new columns without the connector's schema-change handling, which can crash the stream.
  • Assuming total ordering across all tables; partitioning and commit boundaries mean order is per-partition.
When things break

Debugging tips

  • Compare the consumer's last event with the source's LSN to separate network lag from decode lag.
  • Inspect Debezium's heartbeat and offsets to catch silent stalls before alert thresholds.
  • When duplicates appear, check consumer offset commits and RESTART semantics vs AT_LEAST_ONCE.
Interviews

Interview checkpoint

Test yourself

Quick quiz

0/2
1

Which capture approach emits changes as part of the same DB transaction as the application's own writes?

2

A replication slot that is never consumed will grow the WAL indefinitely.

Go deeper

Official documentation

PostgreSQL logical replication
docs
Debezium documentation
docs
MySQL binlog
docs