PostgreSQL CDC with Debezium: Logical Replication, WAL Decoding, and Real Event-Driven Architecture

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:

-- 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.

-- 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:

{
 "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:

-- 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:

{
 "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.

-- 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:

-- 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.

# 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.