Databases # PostgreSQL CDC with Debezium: Logical Replication, WAL Decoding, and Real Event-Driven Architecture 2026-01-136 min read Lucio Durán Engineering Manager & AI Solutions Architect Also available in: [Español]() ## PostgreSQL WAL Decoding Internals PostgreSQL's Write-Ahead Log (WAL) records every change to the database at the physical level (page and tuple modifications). Logical replication adds a decoding layer that translates these physical changes into logical operations (INSERT, UPDATE, DELETE on specific tables). ``` Physical WAL Record (simplified): LSN: 0/16B3740 Resource: Heap Type: INSERT Relation: 16384/16399/16402 (dboid/tsoid/reloid) Block: 42 Offset: 3 Data: \x0001000200034A6F686E... (raw tuple bytes) Logical Decoding Output (pgoutput plugin): BEGIN txid=12345 INSERT INTO public.orders (id, customer_id, total, status) VALUES (9001, 42, 159.99, 'pending') COMMIT txid=12345 ``` The key components: ```sql -- 1. Enable logical replication in postgresql.conf -- wal_level = logical -- max_replication_slots = 10 -- max_wal_senders = 10 -- 2. Create a publication (what tables to capture) CREATE PUBLICATION my_publication FOR TABLE orders, order_items, customers WITH (publish = 'insert, update, delete'); -- 3. Create a replication slot (consumer cursor) SELECT pg_create_logical_replication_slot( 'debezium_slot', 'pgoutput' -- The output plugin ); -- 4. Peek at changes (without consuming them) SELECT * FROM pg_logical_slot_peek_changes( 'debezium_slot', NULL, NULL ); -- 5. Consume changes (advances the slot position) SELECT * FROM pg_logical_slot_get_changes( 'debezium_slot', NULL, NULL ); ``` The replication slot is the critical piece. It tells PostgreSQL: "keep all WAL segments from this LSN position until I confirm I've processed them." This guarantees no data loss but introduces the biggest operational risk: if the consumer stops, WAL accumulates. ```sql -- Monitor replication slot lag SELECT slot_name, confirmed_flush_lsn, pg_current_wal_lsn(), pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn) AS lag_bytes, pg_size_pretty(pg_wal_lsn_diff( pg_current_wal_lsn(), confirmed_flush_lsn )) AS lag_pretty FROM pg_replication_slots WHERE slot_type = 'logical'; -- Output: -- slot_name | confirmed_flush_lsn | pg_current_wal_lsn | lag_bytes | lag_pretty -- debezium_slot| 0/16B3740 | 0/16B4A20 | 4832 | 4832 bytes ``` ## Debezium Configuration for Production The following is a production-grade [Kafka Connect]() configuration using [Debezium](): ```json { "name": "pg-orders-connector", "config": { "connector.class": "io.debezium.connector.postgresql.PostgresConnector", "database.hostname": "pg-primary.internal", "database.port": "5432", "database.user": "debezium_replication", "database.password": "${env:DEBEZIUM_PG_PASSWORD}", "database.dbname": "orders_db", "topic.prefix": "orders", "schema.include.list": "public", "table.include.list": "public.orders,public.order_items,public.customers", "plugin.name": "pgoutput", "slot.name": "debezium_orders", "slot.drop.on.stop": "false", "publication.name": "dbz_orders_pub", "publication.autocreate.mode": "filtered", "snapshot.mode": "initial", "snapshot.lock.timeout.ms": "10000", "tombstones.on.delete": "true", "column.exclude.list": "public.customers.ssn,public.customers.credit_card", "transforms": "route,unwrap", "transforms.route.type": "io.debezium.transforms.ByLogicalTableRouter", "transforms.route.topic.regex": "orders\\.public\\.(.*)", "transforms.route.topic.replacement": "domain.events.$1", "transforms.unwrap.type": "io.debezium.transforms.ExtractNewRecordState", "transforms.unwrap.drop.tombstones": "false", "transforms.unwrap.delete.handling.mode": "rewrite", "heartbeat.interval.ms": "30000", "heartbeat.action.query": "INSERT INTO debezium_heartbeat (id, ts) VALUES (1, now()) ON CONFLICT (id) DO UPDATE SET ts = now()", "errors.tolerance": "all", "errors.deadletterqueue.topic.name": "dlq.pg-orders", "errors.deadletterqueue.context.headers.enable": "true" } } ``` Key decisions explained: - ` slot.drop.on.stop: false `, Never drop the slot on connector restart. Losing the slot means full re-snapshot. - ` heartbeat.interval.ms: 30000 `, Sends a heartbeat every 30s. Without this, if you're filtering tables and no changes occur on captured tables, the slot position never advances, causing WAL accumulation. - ` heartbeat.action.query `, Writes to a heartbeat table to force WAL progress even when filtered tables are quiet. - ` errors.tolerance: all `, Dead-letter queue instead of crashing the connector on bad records. ## The Transactional Outbox Pattern This pattern addresses the fundamental challenge of event-driven systems with relational databases. Instead of trying to atomically write to both Postgres and Kafka (impossible without distributed transactions), you write the event to an outbox table within the same database transaction: ```sql -- Create the outbox table CREATE TABLE outbox ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), aggregate_type VARCHAR(255) NOT NULL, aggregate_id VARCHAR(255) NOT NULL, event_type VARCHAR(255) NOT NULL, payload JSONB NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT now() ); -- Application code: atomic business logic + event BEGIN; -- Business logic UPDATE orders SET status = 'shipped', shipped_at = now() WHERE id = 9001; -- Publish event (same transaction!) INSERT INTO outbox (aggregate_type, aggregate_id, event_type, payload) VALUES ( 'Order', '9001', 'OrderShipped', '{"order_id": 9001, "shipped_at": "2026-02-28T10:30:00Z", "tracking_number": "1Z999AA10123456784"}'::jsonb ); COMMIT; -- Both succeed or both fail. No dual-write problem. ``` Debezium captures the outbox insert and routes it to the appropriate Kafka topic: ```json { "transforms": "outbox", "transforms.outbox.type": "io.debezium.transforms.outbox.EventRouter", "transforms.outbox.table.fields.additional.placement": "event_type:header:eventType", "transforms.outbox.table.field.event.id": "id", "transforms.outbox.table.field.event.key": "aggregate_id", "transforms.outbox.table.field.event.payload": "payload", "transforms.outbox.table.field.event.payload.id": "aggregate_id", "transforms.outbox.route.by.field": "aggregate_type", "transforms.outbox.route.topic.replacement": "domain.events.${routedByValue}" } ``` The event ends up in Kafka topic ` domain.events.Order ` with key ` 9001 ` and the JSON payload. The outbox table can be periodically truncated since Debezium has already captured the events. ## Production Gotchas ### 1. TOAST columns and UPDATE events PostgreSQL stores large column values in TOAST (The Oversized-Attribute Storage Technique) tables. When you UPDATE a row but don't modify a TOASTed column, the WAL record doesn't include the unchanged TOAST value. Debezium sees ` null ` for that column. ```sql -- Set REPLICA IDENTITY FULL to include all columns in WAL ALTER TABLE orders REPLICA IDENTITY FULL; -- Or, if that's too expensive (doubles WAL size for updates): -- Set a specific index as the identity ALTER TABLE orders REPLICA IDENTITY USING INDEX orders_pkey; ``` ### 2. Schema changes break everything Adding a column to a captured table can cause Debezium to throw schema mismatch errors. Always: ```sql -- 1. Add the column with a default ALTER TABLE orders ADD COLUMN priority INTEGER DEFAULT 0; -- 2. Wait for Debezium to process past the schema change LSN -- 3. Then start writing non-default values ``` ### 3. Slot bloat during maintenance During pg\_dump or vacuum full, no LSN advances. If your backup takes 4 hours and you have 10K writes/second, that's ~144M WAL records retained. ```bash # Monitor slot lag in bytes, alert at 1GB pg_replication_slots_lag_bytes{slot_name="debezium_orders"} > 1073741824 ``` ## The Final Architecture After six months of iteration in a representative production deployment: ``` ┌─────────────────┐ ┌──────────────┐ ┌───────────────┐ │ PostgreSQL │ │ Debezium │ │ Kafka │ │ │ WAL │ (Kafka │ │ │ │ orders ├────→│ Connect) ├────→│ domain.events │ │ outbox │ │ │ │ .Order │ │ order_items │ │ Transforms: │ │ .Customer │ │ │ │ - Outbox │ │ .OrderItem │ └─────────────────┘ │ - Route │ └───────┬───────┘ └──────────────┘ │ ┌──────┴──────┐ │ Consumers │ │ - Search │ │ - Analytics │ │ - Notify │ └─────────────┘ ``` The outbox pattern handles domain events. Direct CDC handles materialized view updates for search indexes and analytics. The two patterns complement each other, outbox for intentional events, CDC for data synchronization. Total end-to-end latency from Postgres commit to Kafka consumer: p50 = 45ms, p99 = 180ms. For an event-driven system built on top of a monolithic database, those numbers are more than acceptable. ## Frequently asked questions ### What is Change Data Capture (CDC) and why use it with PostgreSQL? CDC captures row-level changes (inserts, updates, deletes) from a database and streams them as events. With PostgreSQL, Debezium uses logical replication to read the Write-Ahead Log (WAL), which means zero impact on application queries and no polling overhead. It enables real-time event-driven architectures without modifying application code. ### What are PostgreSQL logical replication slots? Replication slots are server-side cursors that track a consumer's position in the WAL. They guarantee that PostgreSQL retains WAL segments until the consumer confirms processing, preventing data loss. The downside is that a stalled consumer can cause WAL accumulation and disk exhaustion. ### When should I use the transactional outbox pattern? Use the outbox when you need to atomically update your database and publish an event. Instead of dual-writing to both Postgres and Kafka, you write the event to an outbox table within the same transaction, and Debezium captures it via CDC. This guarantees exactly-once semantics between your database and message broker. ## Tips 1. **Tip:** Always set ` slot.drop.on.stop=false ` in production Debezium connectors. If you accidentally drop the replication slot, Postgres will recycle the WAL segments, and you'll need a full re-snapshot of every table. This is a lesson commonly learned the hard way during late-night incidents. 2. **Tip:** Monitor ` pg_replication_slots.confirmed_flush_lsn ` vs ` pg_current_wal_lsn() `. If the gap keeps growing, your consumer is falling behind and Postgres is retaining WAL segments, eventually you'll run out of disk. Set up an alert when the lag exceeds 1 GB. 3. **Tip:** The transactional outbox pattern with Debezium is the only reliable way to publish domain events from a PostgreSQL transaction. Dual-writes to Postgres and Kafka will *always* have a failure window where one succeeds and the other doesn't. The outbox eliminates this completely. 4. **Tip:** Use ` publication.autocreate.mode=filtered ` and explicitly list your tables. Default ` all_tables ` will capture every table including temp tables, audit logs, and that forgotten migration table, flooding your Kafka topics with noise. - postgresql - cdc - debezium - kafka - logical-replication - wal - event-driven - outbox-pattern [Back to Blog]()