PostgreSQL CDC Internals: Logical Decoding, Replication Slots, and Debezium

Under the hood of PostgreSQL Change Data Capture lies Logical Decoding—a database engine capability that transforms raw binary disk modifications from the Write-Ahead Log (WAL) into a stream of logical row-level mutation events (INSERT, UPDATE, DELETE).

Understanding how PostgreSQL replication slots, logical decoding plugins (pgoutput), and Debezium coordinate is critical for configuring production CDC without causing database storage outages.

---

1. Physical vs Logical Replication in PostgreSQL

• Physical Replication: Streams raw binary disk page modifications. The replica must be an identical byte-for-byte clone running the same OS and PostgreSQL version.
• Logical Replication: Decodes WAL entries into discrete SQL row changes, allowing selective table filtering, schema transformations, and cross-platform streaming into Apache Kafka.

---

2. The PostgreSQL Replication Slot & The Fatal Disk-Full Trap

A Replication Slot is a server-side PostgreSQL feature that ensures the primary database does not delete WAL segments from disk until the connected CDC client (Debezium) has acknowledged processing them up to a specific Log Sequence Number (LSN).

The Inactive Replication Slot Hazard:

If a Debezium connector fails and is left unrepaired, PostgreSQL will accumulate WAL files forever to protect the slot's position. Within days, the database disk reaches $100\%$ capacity, causing PostgreSQL to enter emergency read-only mode or crash completely.

The Production Safety Fix: max_slot_wal_keep_size

Introduced in PostgreSQL 13, configure max_slot_wal_keep_size to set a hard ceiling on retained WAL bytes:

If an inactive slot exceeds 50 GB of lag, PostgreSQL automatically invalidates the slot to protect database uptime, throwing WAL segment has already been removed to Debezium on reconnect.

---

3. REPLICA IDENTITY FULL: Capturing Before-and-After State

By default, PostgreSQL's REPLICA IDENTITY for a table is set to DEFAULT (Primary Key only).

When an UPDATE occurs, the WAL records the primary key of the modified row, but only records the new values for updated columns. The before state in Debezium will be empty.

• DEFAULT: before block contains only Primary Key columns.
• FULL: before block contains the complete old values for every single column in the row prior to the update. Essential for audit logs, cache invalidations, and CDC stream joins.

---

4. Complete Production Setup: Docker Compose, Postgres & Debezium

A. PostgreSQL Configuration (postgresql.conf):

B. PostgreSQL User Permissions:

C. Debezium Connector Configuration (debezium-postgres-connector.json):

---

5. Summary & Operational Runbook

1. Monitor pg_replication_slots Continuously: Create Prometheus alerts querying SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn) FROM pg_replication_slots;. Alert if slot lag exceeds 5 GB.
2. Use Native pgoutput: Avoid legacy plugins like wal2json which deserialize JSON inside PostgreSQL backend processes and consume excessive CPU.
3. Drop Inactive Slots on Decommissioning: Always run SELECT pg_drop_replication_slot('slot_name'); when decommissioning a CDC connector.