Logical Decoding and CDC

Change data capture is one of the most reliable integration patterns in distributed systems: instead of polling tables with timestamps (fragile, high-latency, misses deletes) or publishing events from application code (dual-write problem), you read the database's own authoritative record of every mutation. Postgres has supported this since version 9.4 via logical decoding—a mechanism that transforms internal WAL records into a stream of row-level changes consumable by external systems.

What Logical Decoding Is

The WAL (Write-Ahead Log) records every change Postgres makes before applying it to data pages. Its native format is physical—byte offsets, page layouts, tuple headers—and is not meant for consumption outside Postgres. Logical decoding sits on top of WAL and uses an output plugin to translate physical WAL records into logical change events: INSERT, UPDATE, DELETE, and TRUNCATE for specific relations.

Two things are required for logical decoding:

1. wal_level = logical in postgresql.conf (a higher level than the default replica; requires restart)
2. A replication slot that tracks the consumer's position in the WAL stream

Output Plugins

Postgres ships pgoutput as the built-in output plugin (used by logical replication subscriptions). Third-party plugins include wal2json (produces JSON events, easier for debugging) and decoderbufs (protobuf, used by Debezium for efficiency).

-- Check available plugins
SELECT name FROM pg_available_extensions WHERE name LIKE '%wal%' OR name LIKE '%decoder%';
 
-- Create a logical replication slot with pgoutput
SELECT pg_create_logical_replication_slot('my_cdc_slot', 'pgoutput');
 
-- Create with wal2json (if installed)
SELECT pg_create_logical_replication_slot('my_json_slot', 'wal2json');
 
-- List all slots
SELECT slot_name, plugin, slot_type, active, restart_lsn, confirmed_flush_lsn
FROM pg_replication_slots;

Reading Changes Directly

For development and debugging, you can consume changes directly from a slot without an external system:

-- Peek at changes (does NOT advance the slot position)
SELECT * FROM pg_logical_slot_peek_changes(
  'my_json_slot',
  NULL,  -- upto_lsn
  NULL,  -- upto_nchanges
  'pretty-print', '1'
);
 
-- Get and advance (moves confirmed_flush_lsn forward)
SELECT * FROM pg_logical_slot_get_changes(
  'my_json_slot',
  NULL,
  NULL,
  'pretty-print', '1'
);

The slot's confirmed_flush_lsn advances only when the consumer explicitly acknowledges changes. Until acknowledged, Postgres retains all WAL from restart_lsn onward. This durability guarantee is what makes CDC reliable—but it is also what causes disk exhaustion when consumers stall.

Replica Identity

For UPDATE and DELETE events, Postgres needs to emit the old row values so consumers can identify which row changed. The REPLICA IDENTITY setting on each table controls this:

-- DEFAULT: log old values only for primary key columns (most efficient)
ALTER TABLE orders REPLICA IDENTITY DEFAULT;
 
-- FULL: log all old column values before the change
ALTER TABLE orders REPLICA IDENTITY FULL;
 
-- NOTHING: no old values emitted - UPDATE/DELETE consumers get no identity
-- (only useful if you never need to process updates/deletes in CDC)
ALTER TABLE orders REPLICA IDENTITY NOTHING;
 
-- USING INDEX: use a unique non-nullable index as the identity
ALTER TABLE orders REPLICA IDENTITY USING INDEX idx_orders_order_number;

If a table has no primary key and REPLICA IDENTITY DEFAULT, UPDATE and DELETE operations cannot be reliably replicated—you'll see a WARNING: table without primary key in Debezium and may get incomplete events. Fix this before setting up CDC on such tables.

Debezium Integration

Debezium is a Kafka Connect source connector that consumes Postgres logical decoding output and publishes structured change events to Kafka topics. It uses a replication slot per connector instance.

{
  "name": "orders-cdc",
  "config": {
    "connector.class": "io.debezium.connector.postgresql.PostgresConnector",
    "database.hostname": "postgres-primary",
    "database.port": "5432",
    "database.user": "debezium",
    "database.password": "secret",
    "database.dbname": "app",
    "database.server.name": "app-db",
    "table.include.list": "public.orders,public.order_items",
    "plugin.name": "pgoutput",
    "slot.name": "debezium_slot",
    "publication.name": "dbz_publication",
    "snapshot.mode": "initial",
    "heartbeat.interval.ms": "10000"
  }
}

Debezium emits events in an envelope format with before, after, and source fields:

{
  "before": {"id": 1, "status": "pending", "amount": 99.00},
  "after": {"id": 1, "status": "shipped", "amount": 99.00},
  "op": "u",
  "ts_ms": 1720000000000,
  "source": {
    "lsn": "0/1A2B3C4D",
    "txId": 12345,
    "ts_ms": 1720000000000
  }
}

The op field values: c (create/insert), u (update), d (delete), r (read/snapshot), t (truncate).

Publication Management

A publication defines which tables and operations are included in the logical replication stream:

-- Publish specific tables and all operation types
CREATE PUBLICATION dbz_publication
  FOR TABLE orders, order_items, products;
 
-- Publish only inserts and updates (exclude deletes for append-only view)
CREATE PUBLICATION inserts_only_pub
  FOR TABLE events
  WITH (publish = 'insert, update');
 
-- Add a table to an existing publication
ALTER PUBLICATION dbz_publication ADD TABLE shipments;
 
-- Check what's in a publication
SELECT * FROM pg_publication_tables WHERE pubname = 'dbz_publication';

Monitoring Slot Lag

-- The critical monitoring query for logical decoding health
SELECT
  slot_name,
  plugin,
  active,
  pg_size_pretty(
    pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)
  ) AS retained_wal,
  pg_size_pretty(
    pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)
  ) AS unacknowledged_lag,
  now() - COALESCE(
    (SELECT max(backend_start) FROM pg_stat_activity WHERE backend_type = 'walsender'),
    now() - interval '1 hour'
  ) AS approx_consumer_age
FROM pg_replication_slots
WHERE slot_type = 'logical';

Alert when retained_wal exceeds a threshold (e.g., 5 GB). A stalled consumer with an active slot will grow retained_wal without bound until the disk fills. The safety valve is max_slot_wal_keep_size (Postgres 13+), which invalidates a slot that grows too large—better to lose the slot than fill the disk.

-- In postgresql.conf: cap retained WAL per slot
-- max_slot_wal_keep_size = '10GB'
 
-- Drop a stale slot immediately if consumer is confirmed dead
SELECT pg_drop_replication_slot('stale_slot');

Key Takeaways

• Logical decoding translates physical WAL into row-level change events; wal_level = logical is required and needs a Postgres restart to activate.
• Replication slots provide durability—the primary retains WAL until the consumer acknowledges—but stalled slots cause unbounded WAL accumulation; set max_slot_wal_keep_size and monitor retained_wal aggressively.
• REPLICA IDENTITY FULL is required for reliable UPDATE/DELETE CDC on tables without a primary key; tables with a primary key should use DEFAULT for efficiency.
• pgoutput is the built-in plugin and the right choice for most setups; wal2json is easier for debugging but adds an extension dependency.
• Debezium's envelope format (before, after, op, source) provides everything downstream consumers need to reconstruct state changes with transactional ordering.
• Publications let you scope CDC to specific tables and operation types—exclude tables with sensitive columns or high-churn audit trails from the publication to reduce load on the replication slot.