PostgreSQL change data capture in production: logical decoding, connectors, and operational guardrails

A product team adds a search index, an analytics warehouse, and a fraud rules engine. Every feature needs the same truth: what changed in PostgreSQL, in order, without re-implementing bespoke updated_at polling in each service. The first attempt—cron jobs that SELECT * WHERE updated_at > $cursor—works until backfills overlap with bursts of writes, clocks skew, and rows that change twice between polls. The second attempt—NOTIFY from triggers—drops events during deploys and cannot replay history. The third attempt—an outbox table with a relay—is correct but still adds application code on every write path.

Change data capture (CDC) reads the database write-ahead log (WAL) and emits row-level change events to Kafka, Pub/Sub, or another bus. Postgres has supported this for years through logical decoding; managed connectors (Debezium, PeerDB, native logical replication) package the hard parts. In consulting work, CDC is the pattern I reach for when multiple downstream systems must stay eventually consistent with OLTP, when replay matters after a bug, or when the organization is ready to own replication slots and schema contracts as first-class infrastructure.

This article explains how PostgreSQL CDC works under the hood, how it compares to outbox and polling, what to configure in production, and where teams get burned.

What CDC is—and what it is not

CDC answers: “What rows changed, with what before/after values, in commit order?” The database already records every committed change in the WAL for crash recovery. Logical decoding exposes a subset of that stream—filtered by table, transformed into a serialization format—without the application inserting duplicate “event” rows beside business data.

CDC is not:

• A replacement for domain events with rich business meaning. WAL events are row mutations (INSERT/UPDATE/DELETE). You still map them to OrderPaid in a consumer or use an outbox when the event shape must be intentional.
• Exactly-once end-to-end by itself. Connectors and consumers deliver at-least-once; idempotency and deduplication live downstream.
• Free of operational cost. Replication slots retain WAL until consumed; a stalled consumer can fill disks.

CDC is excellent when many subscribers need the same changelog, when you want minimal intrusion on write paths, or when you must backfill from a snapshot plus a stream.

Logical decoding: publications, slots, and formats

PostgreSQL logical replication is built from three concepts:

Publication

A publication names which tables (or all tables) emit changes. You create it once per pipeline or per consumer family:

CREATE PUBLICATION app_core FOR TABLE
  orders,
  order_lines,
  customers;

Only tables in the publication appear in the decoded stream. Adding a table later requires ALTER PUBLICATION ... ADD TABLE and often a resnapshot for consumers that need a full initial copy.

Replication slot

A logical replication slot tracks how far the consumer has read. Postgres retains WAL for that slot until the consumer acknowledges progress. If the consumer stops, WAL accumulates—this is the primary disk-risk of CDC.

SELECT * FROM pg_create_logical_replication_slot('debezium_app', 'pgoutput');

The plugin (pgoutput is standard in modern Postgres; wal2json exists for simpler JSON payloads) determines wire format. Managed connectors usually own slot creation; when you operate raw logical replication, you monitor pg_replication_slots.

Output formats

pgoutput emits a binary protocol efficient for Debezium and native logical replicas. JSON plugins trade CPU for debuggability. Pick based on connector support, not human readability in production.

Architecture: connector, bus, consumers

A typical production pipeline:

flowchart LR
  subgraph pg [PostgreSQL]
    WAL[WAL]
    PUB[Publication]
    SLOT[Replication slot]
    WAL --> PUB --> SLOT
  end
  subgraph connect [Connector]
    DEB[Debezium / managed CDC]
    SLOT --> DEB
  end
  subgraph bus [Event bus]
    K[Kafka / Pub/Sub]
    DEB --> K
  end
  subgraph consumers [Consumers]
    SRCH[Search indexer]
    WH[Warehouse sync]
    CACHE[Cache projector]
    K --> SRCH
    K --> WH
    K --> CACHE
  end

The connector:

1. Takes an initial snapshot (consistent read of published tables) unless you only want changes from “now.”
2. Streams change events with table metadata, primary keys, and old/new row images depending on REPLICA IDENTITY.
3. Commits offsets to Kafka (or the slot) after publish.

Consumers must treat each message as idempotent: the same primary key and offset may arrive twice after crashes.

CDC vs transactional outbox vs polling

Approach	Write-path cost	Replay	Event shape	Ops surface
updated_at polling	Low	Manual backfill	Whatever you query	Cron + missed updates
Transactional outbox	Extra insert per event	Replay outbox rows	Application-defined	Relay + outbox table
CDC (WAL)	None on app path	Snapshot + stream	Row-level	Slots, connectors, schema

Use outbox when events are the source of truth for integration (explicit versioning, aggregate boundaries, “we never emit without a business rule”). Use CDC when the database row is the truth and multiple projections need the same firehose. Many mature systems use both: outbox for integration events, CDC for analytics and search where row images suffice.

Replica identity and why updates look empty

UPDATE and DELETE events include a before image only if Postgres can identify the old row. That is controlled per table:

ALTER TABLE orders REPLICA IDENTITY FULL;
-- or REPLICA IDENTITY USING INDEX orders_pkey;

Default is DEFAULT (primary key columns only in the “old” key). If you need the previous values of non-key columns in the stream, use FULL (wider WAL, more bandwidth) or index-based identity when a unique index captures what you need.

Teams that skip this step wonder why Debezium shows null for “before” on wide rows—then they bolt on extra queries in consumers, defeating the purpose of CDC.

Schema evolution without breaking every consumer

WAL events carry column names and types at decode time. Breaking changes—rename without bridge, drop column consumers still read, change type incompatibly—will fail or silently corrupt projections.

Practices that work in production:

1. Expand–contract at the database layer (add column → dual-write or backfill → switch readers → drop old), the same discipline as zero-downtime API evolution.
2. Versioned Avro/Protobuf in the bus with a schema registry; consumers pin compatibility modes (BACKWARD, FULL).
3. Single writer for destructive DDL during migration windows; pause or snapshot consumers if needed.
4. Document which tables are in the publication; ad-hoc ALTER on unpublished tables does not affect CDC, but published tables are a contract.

In freelance engagements, the expensive mistakes are not connector YAML—they are unowned schema contracts between the DBA path and five Python consumers nobody owns.

Ordering, partitioning, and multiple consumers

Commit order in Postgres is total on one primary. Kafka partitions should key by primary key (or tenant_id) so per-entity order is preserved. Global order across all tables is rarely required and is expensive.

Multiple independent consumer groups can read the same topic; each maintains its own offset. Multiple connectors on the same tables need separate slots—each slot retains WAL independently. Duplicating slots without need doubles disk pressure.

Operational guardrails

Slot lag and disk

Monitor:

SELECT slot_name,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots
WHERE slot_type = 'logical';

Alert when retained WAL exceeds a threshold (gigabytes or hours of production write rate). During incidents, stalled consumers are a database availability risk, not only a pipeline delay.

Upgrades and failovers

Logical slots do not automatically survive all failover modes. On Amazon RDS/Aurora, Google Cloud SQL, or self-managed Patroni clusters, verify whether slots are preserved, recreated, or require snapshot restart. Runbooks should name: who drops a slot, who resnapshots, and how downstream idempotency handles a full replay.

Permissions

CDC readers need REPLICATION privilege and SELECT on published tables (for snapshots). Least-privilege roles; never reuse the application superuser.

Heartbeats and idle tables

Some connectors emit heartbeat events when the database is quiet so offsets advance and WAL can be recycled. Without heartbeats on low-traffic databases, slots may hold WAL longer than necessary depending on configuration.

Practical example: publication, Debezium-shaped event, and idempotent consumer

The following is a minimal end-to-end sketch: SQL to prepare Postgres, an example change event shape (as Debezium commonly emits), and a TypeScript consumer that upserts a search document idempotently.

1. Prepare Postgres

-- Primary keys required on published tables
ALTER TABLE orders REPLICA IDENTITY FULL;

CREATE PUBLICATION orders_pub FOR TABLE orders;

-- Slot often created by the connector; shown here for clarity
SELECT pg_create_logical_replication_slot('orders_slot', 'pgoutput');

2. Example event (simplified Debezium envelope)

{
  "op": "u",
  "source": { "table": "orders", "lsn": 987654321 },
  "before": { "id": "ord_1", "status": "pending", "total_cents": 1000 },
  "after": { "id": "ord_1", "status": "paid", "total_cents": 1000 }
}

3. Idempotent projector (Node.js / TypeScript)

type OrderRow = {
  id: string;
  status: string;
  total_cents: number;
};

type DebeziumOrderEvent = {
  op: "c" | "u" | "d" | "r";
  before: OrderRow | null;
  after: OrderRow | null;
  source: { table: string; lsn: number };
};

export async function projectOrderToSearch(
  event: DebeziumOrderEvent,
  searchIndex: {
    upsert: (doc: { id: string; status: string; totalCents: number }) => Promise<void>;
    delete: (id: string) => Promise<void>;
  },
): Promise<void> {
  const row = event.op === "d" ? event.before : event.after;
  if (!row) return;

  if (event.op === "d") {
    await searchIndex.delete(row.id);
    return;
  }

  await searchIndex.upsert({
    id: row.id,
    status: row.status,
    totalCents: row.total_cents,
  });

  // Optional: store last applied LSN per id to drop true duplicates
  // if (event.source.lsn <= await getLastLsn(row.id)) return;
}

Production code adds: deserialization, retry with backoff, dead-letter topics, metrics on lag (now - event_timestamp), and schema validation. The idempotent upsert by primary key is the non-negotiable core—duplicates are normal.

4. Connector configuration (illustrative fragment)

Debezium’s PostgreSQL connector wires publication, slot, and Kafka topic routing. Values vary by host and version; treat this as structural, not copy-paste:

{
  "connector.class": "io.debezium.connector.postgresql.PostgresConnector",
  "database.hostname": "db.internal",
  "database.dbname": "app",
  "slot.name": "orders_slot",
  "publication.name": "orders_pub",
  "table.include.list": "public.orders",
  "topic.prefix": "cdc",
  "snapshot.mode": "initial",
  "heartbeat.interval.ms": 10000
}

For teams without Kafka, native logical replication to another Postgres instance, or managed “CDC to warehouse” products, follows the same mental model: slot + publication + consumer offset.

When CDC is the wrong tool

• Low-volume, one consumer, simple invalidation — LISTEN/NOTIFY or a short-TTL cache with version keys may be cheaper (see the dedicated article on NOTIFY in this blog).
• Events must reflect business rules not row diffs — outbox or domain event store.
• You cannot operate slots — a managed platform with CDC as a checkbox may still be right, but “we’ll monitor WAL retention later” is how outages start.
• Cross-database choreography — CDC is per database; sagas still need explicit coordination.

Common mistakes and pitfalls

Creating multiple slots for the same pipeline “just in case.” Each slot retains WAL. Consolidate consumers via the bus, not duplicate slots.

Publishing wide tables with REPLICA IDENTITY FULL by default. Bandwidth and decode cost scale with column count. Prefer primary-key identity plus selective FULL where needed.

Assuming schema renames are invisible. ALTER RENAME COLUMN is a breaking change for typed consumers; use expand–contract or dual columns.

No snapshot strategy for new consumers. Adding a search index requires baseline data; snapshot.mode=initial or a one-off bulk export plus stream offset must be planned.

Treating DELETE as rare. Hard deletes propagate; soft deletes (deleted_at) appear as updates—consumers must handle both models explicitly.

Ignoring tombstones in compacted topics. If you compact Kafka topics by key, you need tombstone events on delete or orphaned keys resurrect.

Running CDC from read replicas incorrectly. Logical decoding runs on the primary (or vendor-specific replica promotion rules). Architecture diagrams that decode from a hot standby without checking vendor docs fail in production.

Conclusion

PostgreSQL change data capture turns the WAL into a durable, replayable stream of row changes—freeing write paths from N different polling queries while giving search, analytics, and cache projectors a shared source of truth. The cost moves to operations: replication slots, schema contracts, idempotent consumers, and failover runbooks.

Key takeaways:

• Use publications to define scope and slots to track progress; monitor retained WAL as a database health metric.
• Set replica identity deliberately so updates carry the data your consumers need.
• Pair CDC with schema evolution discipline and idempotent, key-partitioned consumers.
• Choose CDC for multi-subscriber row replication; keep outbox when integration events are intentionally modeled in application code.

When teams graduate from “it works on my laptop” polling to production-ready, multi-service data planes, getting CDC right early avoids expensive rewrites later. For architecture reviews, pipeline design, or hardening Postgres-backed systems under real load, the contact page is the simplest way to start a conversation.