PostgreSQL change data capture in production: WAL, ordering, and consumer design

Your analytics warehouse is six hours behind. Search results still show a deleted product. A partner’s webhook fired twice for the same invoice update. In consulting work, these symptoms often trace to the same architectural shortcut: batch ETL or application-level “publish after commit” that never quite matches the database’s ground truth. Change data capture (CDC) reads the database’s own replication stream—typically the write-ahead log (WAL) via logical decoding—and turns committed row changes into an ordered, replayable event log for caches, search indexes, warehouses, and microservices.

This article explains how PostgreSQL CDC works under the hood, how it compares to the transactional outbox, and how to operate consumers that remain correct when schemas evolve, connectors restart, and messages are delivered more than once.

Why CDC matters when the database is the source of truth

Most production systems still treat the OLTP database as authoritative for business state. Everything else—Elasticsearch, Snowflake, Redis denormalizations, audit logs—is a projection. Projections drift when:

• A nightly job misses rows written during the export window.
• An app crashes after COMMIT but before kafka.send().
• Two services write related facts through different code paths with no shared transaction.

CDC inverts the integration model: the commit log drives downstream work, not individual HTTP handlers. That gives you:

• Durability aligned with the database — if a change is visible to other transactions, the WAL has it; CDC connectors read after commit.
• Decoupled consumers — search, analytics, and notifications subscribe without adding publish calls to every repository method.
• Replay — after fixing a buggy projector, you can rebuild from a retained log or a fresh snapshot plus stream (operational cost varies by stack).

CDC is not free. You add connector infrastructure, schema governance, and consumer idempotency. For teams moving from “scripts that poll updated_at” to event-driven projections, the trade-off is usually worth it once staleness or dual-write bugs show up in revenue paths.

PostgreSQL logical replication and the WAL

PostgreSQL physically appends every change to the WAL before it acknowledges a commit. Logical decoding exposes those changes as a stream of logical tuples (insert/update/delete) for tables you include in a publication.

At a high level:

flowchart LR
  subgraph pg [PostgreSQL]
    App[Application writes]
    WAL[(WAL)]
    Pub[Publication]
    App --> WAL
    WAL --> Pub
  end
  subgraph cdc [CDC platform]
    Slot[Replication slot]
    Conn[Connector e.g. Debezium]
    Bus[(Kafka / Pulsar)]
    Pub --> Slot
    Slot --> Conn
    Conn --> Bus
  end
  subgraph consumers [Consumers]
    Search[Search indexer]
    WH[Warehouse loader]
    Svc[Domain service]
    Bus --> Search
    Bus --> WH
    Bus --> Svc
  end

Key concepts:

Concept	Role
Publication	Named set of tables (or all tables) whose changes are streamed.
Replication slot	Cursor into the WAL; prevents the server from discarding WAL segments the connector has not consumed.
Plugin	pgoutput (native) or wal2json-style decoders; Debezium uses pgoutput on modern versions.
LSN	Log sequence number — monotonic position in the WAL; consumers checkpoint here.

REPLICA IDENTITY and what consumers actually see

For UPDATE and DELETE, logical replication must know how to identify the old row. PostgreSQL uses REPLICA IDENTITY:

• DEFAULT — uses the primary key; sufficient for most tables.
• FULL — sends all column values for the “before” image; higher WAL volume but required when no PK exists or you need old values for partial updates.
• USING INDEX — rare; uses a unique index.

If your consumer builds “before/after” diffs for partial updates, verify identity settings before going live. Missing old column values in the stream is a common surprise on wide tables with DEFAULT identity.

Slots, WAL retention, and disk risk

A replication slot that falls behind keeps WAL on disk. Monitor:

• Lag bytes / LSN distance between current WAL and slot confirmed flush.
• Disk usage on the database volume.

An abandoned connector with an active slot can fill the disk and take down the primary. Runbooks should include alerting, automated slot cleanup for decommissioned connectors, and max_slot_wal_keep_size (PostgreSQL 13+) where appropriate.

CDC versus the transactional outbox

Both patterns emit events from database commits; they differ in who owns the message shape and operational boundaries.

Dimension	Transactional outbox	WAL / CDC
Event definition	Application writes explicit domain events to outbox	Row-level change events (Debezium envelopes)
Coupling	Tied to app release and event schema	Tied to table DDL; all writers to the table appear
Latency	Poll interval or in-process relay	Often sub-second with Kafka Connect
Ops	Your relay + outbox table	Connector cluster + slots + schema registry
Deletes	You model them in event types	Native DELETE records (tombstones in Kafka)

Many mature systems use both: outbox for rich domain events consumed by bounded contexts, CDC for integration projections (search, warehouse, cache warming) where mirroring table shape is acceptable.

Choosing CDC alone for cross-service contracts is risky: users row updates are a poor substitute for UserEmailChanged when business meaning depends on application validation. Choosing outbox alone at very high write volume can stress the database with polling. Hybrid designs are normal in production.

Event shape, ordering, and partitioning

Debezium (and similar tools) wrap changes in an envelope with op (c/u/d/r), before, after, source metadata (table, schema, LSN, transaction id), and timestamps.

Per-table ordering

PostgreSQL guarantees transaction order in the WAL. Kafka producers typically map primary key to the message key so all changes for one row land in one partition and preserve order per key.

Global total order across all tables is usually unnecessary and expensive (single partition). Design consumers to need per-aggregate or per-table order only.

Transactions and multiple tables

A single database transaction touching orders and order_lines may produce multiple events. Consumers that must see a consistent snapshot across tables should either:

• Use transaction metadata (Debezium can emit transaction boundaries on supported versions), or
• Design idempotent per-table projectors and accept brief cross-table skew, or
• Drive cross-table consistency from domain events in an outbox instead of raw CDC.

There is no universal answer; product requirements for “invoice line must never appear without header” dictate the choice.

Tombstones and compacted topics

For Kafka compacted topics, a DELETE often becomes a tombstone (null value) so compaction can remove prior keys. Search indexers must interpret tombstones as document removal. Forgetting to handle null payloads after compaction is a frequent production bug.

Schema evolution without breaking the stream

DDL on published tables affects connectors and consumers.

Safe practices:

• Prefer additive changes first (ADD COLUMN NULL), backfill, then enforce NOT NULL in a later deploy.
• Use a schema registry (Avro/Protobuf/JSON Schema) when using Kafka; register compatible schemas before producers emit new fields.
• Configure Debezium schema history topic and understand that breaking changes (drop column, rename without migration plan) can stall or confuse consumers.

Renames are painful: CDC sees a drop + add unless you use migration tricks. Operational pattern: add new column, dual-write in app, backfill, switch readers, drop old column—same as zero-downtime migrations.

Consumer resilience:

• Ignore unknown fields in after payloads.
• Version projectors (v2 indexer) when semantics change materially.
• Keep dead-letter queues for poison messages that fail deserialization.

Practical example: publication, connector sketch, and idempotent consumer

The following is a realistic minimal setup: enable logical replication, create a publication, run a connector (configuration shown in Kafka Connect JSON style), and consume with an idempotent search indexer.

Database setup

-- postgresql.conf (managed clouds often expose this as a parameter group)
-- wal_level = logical

CREATE PUBLICATION app_cdc FOR TABLE orders, order_lines;

-- Verify replica identity on tables without PK (avoid if possible)
-- ALTER TABLE legacy_audit REPLICA IDENTITY FULL;

Create a dedicated replication user with REPLICATION privilege and SELECT on published tables (exact grants depend on your PostgreSQL version and connector docs).

Connector excerpt (Debezium PostgreSQL)

{
  "name": "orders-pg-connector",
  "config": {
    "connector.class": "io.debezium.connector.postgresql.PostgresConnector",
    "database.hostname": "db.internal",
    "database.port": "5432",
    "database.user": "cdc_replicator",
    "database.password": "${secrets:cdb_password}",
    "database.dbname": "app",
    "topic.prefix": "prod",
    "table.include.list": "public.orders,public.order_lines",
    "plugin.name": "pgoutput",
    "publication.name": "app_cdc",
    "slot.name": "debezium_orders",
    "tombstones.on.delete": "true",
    "decimal.handling.mode": "string",
    "heartbeat.interval.ms": "10000",
    "signal.data.collection": "public.debezium_signal"
  }
}

heartbeat and incremental snapshots (via signaling tables) matter for large backfills and confirming the connector is alive when traffic is quiet.

TypeScript consumer with idempotent upsert

type DebeziumEnvelope<T> = {
  op: "c" | "u" | "d" | "r";
  before: T | null;
  after: T | null;
  source: { table: string; lsn: number; txId: number };
};

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

async function handleOrderEvent(
  env: DebeziumEnvelope<OrderRow>,
  index: { upsert: (doc: OrderRow) => Promise<void>; delete: (id: string) => Promise<void> }
): Promise<void> {
  const op = env.op;

  if (op === "d") {
    const id = env.before?.id;
    if (!id) return;
    await index.delete(id);
    return;
  }

  const row = env.after;
  if (!row) return;

  // Idempotency: LSN or updated_at guards stale replays
  await index.upsert(row);
}

// Kafka consumer loop (pseudocode)
for await (const msg of consumer.eachMessage({ topic: "prod.public.orders" })) {
  const env = JSON.parse(msg.value?.toString() ?? "{}") as DebeziumEnvelope<OrderRow>;
  try {
    await handleOrderEvent(env, searchIndex);
    await consumer.commitOffsets(msg);
  } catch (err) {
    await deadLetter.publish(msg, err);
  }
}

Production code should also:

• Store last applied LSN (or updated_at + PK) in the projection store to drop stale updates on replay.
• Metric lag (consumer offset vs topic end, and connector LSN vs DB).
• Scale consumers with partition count aligned to key cardinality.

Operating CDC in production

Initial snapshot + stream. First deploy usually needs a consistent snapshot (connector snapshot mode) then continuous streaming. Plan disk and replication load during snapshot; throttle if the primary is small.

Failover. Managed PostgreSQL failovers may promote a replica; connectors must reconnect, and slot state must survive according to your provider’s logical replication story. Document whether slots are recreated and whether a full resnapshot is required.

Monitoring checklist:

• Replication slot lag (bytes and time)
• Connector task health and restart counts
• Consumer lag per partition
• DLQ depth and deserialization error rate
• Schema registry compatibility failures

Security. CDC credentials are powerful; restrict network paths, rotate passwords, and avoid publishing PII columns you do not need—column lists and transforms (SMTs) can mask or route sensitive fields.

When I help teams harden event-driven backends, CDC is often the bridge between a solid relational core and the read models product expects in near real time—without entangling every write path with message broker calls.

Common mistakes and pitfalls

Treating CDC events as domain contracts. Row shapes leak storage details (status enums, internal flags). External services should consume curated integration events or well-versioned contracts, not raw public.orders unless that boundary is deliberate.

No idempotency on consumers. Connectors restart; Kafka redelivers; you will process the same change twice. Every projector needs a dedupe key (PK + LSN, or monotonic version column).

Ignoring REPLICA IDENTITY. Partial UPDATE payloads without “before” images break diff-based indexers.

Unbounded replication slots. A forgotten staging connector stalled for a week can exhaust disk on the primary.

Synchronous expectations across tables. CDC does not give you free distributed transactions; design for per-table or explicit transaction boundaries.

Running CDC from read replicas without understanding vendor support. Some setups decode from primaries only; read replica CDC depends on provider capabilities and lag trade-offs.

Schema renames as renames. Prefer expand/contract migrations; otherwise consumers see discontinuities that look like delete+create storms.

Conclusion

PostgreSQL change data capture turns committed writes into a durable, ordered stream that downstream systems can trust more than ad hoc polling or dual writes. Logical replication slots, publications, and tools like Debezium are the production-grade path from WAL to Kafka (or similar), with explicit trade-offs around schema coupling, operational overhead, and consumer idempotency.

Key takeaways:

• CDC streams truth from the WAL; align projections with commits, not with best-effort publish calls
• Understand replica identity, slot lag, and per-key ordering before designing consumers
• Combine with outbox when you need rich domain events, not only table mirrors
• Operate for retries, schema evolution, and tombstones from day one

Teams building scalable, production-ready data pipelines use CDC to keep search, analytics, and integrations in step with OLTP without sacrificing correctness. If you are evaluating event-driven architecture, replication lag, or migration off batch ETL, the contact page is the best place to start a conversation.