Debezium on Postgres: Replication Slots, Snapshots, and Applying Changes Idempotently

The alert was disk usage 94% on the primary Postgres instance behind our order service. Nobody had deployed anything. Table sizes were normal. pg_wal was 380 GB and climbing.

The cause was a Debezium connector that had been in FAILED state for nine days. Its replication slot was still there, still registered, still telling Postgres that every WAL segment since the failure was needed. Postgres did exactly what it was told and refused to recycle any of it. We were about two hours from a database that could not accept writes, on a Sunday, because of a connector that had died quietly on a Friday.

That is the defining characteristic of Postgres CDC: it is a feature of your production database, not a read-only observer standing safely off to the side. A stalled consumer is a database availability problem. Every other part of Debezium — snapshots, transforms, sink behavior — is ordinary engineering. The slot is the part that will page you.

This guide covers a Postgres to Kafka CDC pipeline as I actually run it: the Postgres-side configuration, snapshot strategies for tables too large to snapshot naively, the operational metrics that matter, and how to apply the resulting change stream to a warehouse without duplicating or reordering anything. Connect itself is assumed; if it is new, start with Kafka Connect in production.

How Postgres logical decoding works

Postgres writes every change to the write-ahead log for durability and physical replication. Logical decoding reads that same WAL and turns it into a stream of logical row changes through an output plugin — pgoutput since Postgres 10, which is built in and the one to use.

A replication slot is a server-side bookmark. It records the oldest LSN (log sequence number) the consumer has not yet confirmed, and Postgres guarantees those WAL segments stay on disk until it does. That guarantee is what makes CDC durable across a connector restart, and it is exactly what fills your disk when the connector never comes back.

A publication defines which tables the slot streams. Scoping it is not just tidiness: an unscoped publication means every write to every table becomes WAL that Debezium decodes and discards, which is CPU on the primary for nothing.

-- postgresql.conf: requires a restart.
--   wal_level = logical
--   max_replication_slots = 10
--   max_wal_senders = 10

-- A dedicated role. REPLICATION is required; superuser is not.
CREATE ROLE debezium WITH LOGIN REPLICATION PASSWORD '...';
GRANT USAGE ON SCHEMA public TO debezium;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO debezium;

-- Scope the publication to the tables you actually stream.
CREATE PUBLICATION dbz_orders FOR TABLE
    public.orders,
    public.order_items,
    public.customers;

-- Deletes and before-images only carry non-key columns with FULL.
-- Cost: every UPDATE writes the full old row into the WAL.
ALTER TABLE public.orders       REPLICA IDENTITY FULL;
ALTER TABLE public.order_items  REPLICA IDENTITY DEFAULT;  -- PK is enough here
ALTER TABLE public.customers    REPLICA IDENTITY FULL;

-- The safety net you will be glad you have at 2 a.m.
--   ALTER SYSTEM SET max_slot_wal_keep_size = '64GB';

REPLICA IDENTITY deserves a decision per table rather than a blanket setting. With DEFAULT, a delete event carries only the primary key and an update carries no before-image. With FULL, both carry the complete old row, at the cost of significantly more WAL on update-heavy tables. I use FULL where downstream logic needs to know what changed (audit trails, slowly changing dimensions) and DEFAULT where the sink only ever upserts on the key.

max_slot_wal_keep_size is the setting I now put on every instance. It caps how much WAL a slot may pin; past that, Postgres invalidates the slot. You lose the CDC position and have to re-snapshot, which is a bad day. Losing the database is a worse one.

flowchart LR
  app["Application writes"] --> pg["Postgres primary"]
  pg --> wal["WAL"]
  wal --> slot["Replication slot"]
  slot --> dbz["Debezium connector"]
  dbz --> topics["Topics per table"]
  topics --> sink["Sink connector"]
  sink --> wh["Warehouse"]
  dbz -->|"stalled"| grow["WAL cannot recycle"]
  grow --> disk["Primary disk fills"]

The feedback edge on the bottom is why CDC lag is a database alarm, not just a pipeline alarm.

The connector configuration I start from

{
  "name": "pg-orders-cdc",
  "config": {
    "connector.class": "io.debezium.connector.postgresql.PostgresConnector",
    "tasks.max": "1",

    "database.hostname": "orders-db.internal",
    "database.port": "5432",
    "database.user": "debezium",
    "database.password": "${file:/opt/connect/secrets.properties:pg_password}",
    "database.dbname": "orders",
    "topic.prefix": "cdc.orders",

    "plugin.name": "pgoutput",
    "slot.name": "dbz_orders_slot",
    "publication.name": "dbz_orders",
    "publication.autocreate.mode": "disabled",
    "table.include.list": "public.orders,public.order_items,public.customers",

    "snapshot.mode": "initial",
    "signal.data.collection": "public.dbz_signal",
    "incremental.snapshot.chunk.size": "8192",

    "heartbeat.interval.ms": "10000",
    "heartbeat.action.query":
      "INSERT INTO public.dbz_heartbeat (ts) VALUES (now()) ON CONFLICT (id) DO UPDATE SET ts = now()",

    "decimal.handling.mode": "string",
    "time.precision.mode": "connect",
    "tombstones.on.delete": "true",

    "key.converter": "io.confluent.connect.avro.AvroConverter",
    "key.converter.schema.registry.url": "http://schema-registry:8081",
    "value.converter": "io.confluent.connect.avro.AvroConverter",
    "value.converter.schema.registry.url": "http://schema-registry:8081",

    "errors.tolerance": "all",
    "errors.deadletterqueue.topic.name": "dlq.pg-orders-cdc",
    "errors.deadletterqueue.context.headers.enable": "true"
  }
}

Four of those lines are the ones I argue about in reviews.

tasks.max: 1 is not a typo or a conservative default. One connector owns one replication slot, which produces one ordered stream. Setting it higher changes nothing. To parallelize, split tables across multiple connectors with separate slots and publications — and accept that cross-connector ordering is then undefined, which matters if two tables participate in one transaction you care about.

heartbeat.interval.ms with heartbeat.action.query solves a specific and very common trap. Debezium only advances the slot’s confirmed LSN when it commits an offset, and it only commits when it has processed a change. If your publication covers a low-traffic table in a busy database, the WAL advances rapidly while Debezium sees nothing to emit, so the slot’s confirmed position stays frozen and WAL accumulates. The heartbeat query writes to a dummy table inside the publication, which manufactures a change, which advances the slot. Every quiet-database WAL growth incident I have investigated came down to a missing heartbeat.

decimal.handling.mode: string avoids the base64-encoded bytes representation that precise produces, which nearly every downstream consumer gets wrong at least once. Strings are unambiguous and parse cleanly in the warehouse.

publication.autocreate.mode: disabled forces the publication to be managed as DDL you reviewed rather than something the connector invents. The default (all_tables) will happily create a publication covering every table in the database.

Snapshots without locking the source

snapshot.mode: initial reads every included table in full, emits those rows as r (read) events, and then switches to streaming. For a few million rows this is fine and takes minutes. For a 500 GB orders table it is a long-running transaction with a REPEATABLE READ snapshot held open, blocking vacuum on those tables for the duration.

The modern answer is the incremental snapshot, which chunks the table by primary key, interleaves chunks with the live change stream, and can be started, paused, and resumed by writing a row to a signal table.

-- One-time setup, and it must be in the publication so the
-- connector actually observes the signal row.
CREATE TABLE public.dbz_signal (
    id   text PRIMARY KEY,
    type text NOT NULL,
    data text
);
ALTER PUBLICATION dbz_orders ADD TABLE public.dbz_signal;

-- Backfill one table with zero downtime and no long transaction.
INSERT INTO public.dbz_signal (id, type, data) VALUES (
    'backfill-orders-2026-05-17',
    'execute-snapshot',
    '{"data-collections": ["public.orders"], "type": "INCREMENTAL"}'
);

-- Backfill only part of a table, when you know what is missing.
INSERT INTO public.dbz_signal (id, type, data) VALUES (
    'repair-may-orders',
    'execute-snapshot',
    '{"data-collections": ["public.orders"],
      "type": "INCREMENTAL",
      "additional-conditions": [
        {"data-collection": "public.orders",
         "filter": "created_at >= ''2026-05-01''"}
      ]}'
);

This is the mechanism that makes CDC operationally sane. Adding a table to an existing pipeline no longer requires a full re-snapshot of everything. Repairing a gap after an incident is a scoped signal row rather than a rebuild. The chunks arrive interleaved with live changes, and because chunk reads emit r events for whatever the row looks like at read time, a row that changed mid-snapshot produces both an r and a u — which your sink must tolerate. It does, if you apply changes the way the next section describes.

My rule: snapshot.mode: initial for tables under roughly 50 million rows, snapshot.mode: no_data plus an incremental snapshot signal for anything bigger.

Applying changes idempotently

Debezium’s output is at-least-once. A connector restart replays from the last committed offset, an incremental snapshot overlaps with streaming, and a topic replay after a sink bug re-delivers everything. Ordering is guaranteed per key within a partition and nowhere else.

That means the apply logic must be idempotent and order-independent. The pattern that satisfies both is a MERGE keyed on the primary key with an LSN-based watermark:

-- Snowflake. cdc_stage holds the flattened Debezium envelope:
--   op: 'c' create, 'u' update, 'd' delete, 'r' snapshot read
--   lsn: source.lsn, monotonic per Postgres instance
MERGE INTO analytics.raw.orders AS t
USING (
    SELECT
        order_id,
        customer_id,
        status,
        amount_cents,
        updated_at,
        op,
        lsn
    FROM analytics.stage.orders_cdc
    -- Collapse the batch to the newest change per key first, or the
    -- MERGE will fail with a duplicate-target-row error.
    QUALIFY ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY lsn DESC) = 1
) AS s
ON t.order_id = s.order_id

-- Soft delete. Hard DELETE loses the tombstone for downstream readers.
WHEN MATCHED AND s.op = 'd' AND s.lsn > t._lsn THEN UPDATE SET
    t._deleted = TRUE,
    t._lsn     = s.lsn

-- The lsn guard makes a replay of older events a no-op.
WHEN MATCHED AND s.op IN ('c', 'u', 'r') AND s.lsn > t._lsn THEN UPDATE SET
    t.customer_id  = s.customer_id,
    t.status       = s.status,
    t.amount_cents = s.amount_cents,
    t.updated_at   = s.updated_at,
    t._deleted     = FALSE,
    t._lsn         = s.lsn

WHEN NOT MATCHED AND s.op <> 'd' THEN INSERT
    (order_id, customer_id, status, amount_cents, updated_at, _deleted, _lsn)
VALUES
    (s.order_id, s.customer_id, s.status, s.amount_cents, s.updated_at, FALSE, s.lsn);

Three details carry the correctness. The QUALIFY collapses multiple changes to the same key within one batch, without which the MERGE throws on a duplicate target row. The s.lsn > t._lsn guard makes every branch order-independent, so a replayed older event cannot overwrite newer state. And the delete is a soft delete, preserving the fact that the row existed — hard deletes make downstream incremental models silently wrong, which is the same problem discussed in dbt incremental models.

The ExtractNewRecordState SMT flattens the Debezium envelope so the stage table looks like the query above rather than a nested before/after structure:

{
  "transforms": "unwrap",
  "transforms.unwrap.type": "io.debezium.transforms.ExtractNewRecordState",
  "transforms.unwrap.drop.tombstones": "false",
  "transforms.unwrap.delete.handling.mode": "rewrite",
  "transforms.unwrap.add.fields": "op,source.lsn:lsn,source.ts_ms:source_ts_ms"
}

delete.handling.mode: rewrite turns a delete into a row with a __deleted flag instead of a null value, which is what lets the sink see deletes at all. Keep tombstones if any consumer relies on log compaction; drop them if your sink chokes on null values.

What to monitor, in priority order

Slot retained WAL. The metric that prevents the outage in the opening paragraphs. Alert well before the disk does.

SELECT
    slot_name,
    active,
    pg_size_pretty(
        pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)
    ) AS retained_wal,
    pg_size_pretty(safe_wal_size) AS headroom_before_invalidation
FROM pg_replication_slots
ORDER BY pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn) DESC;

I page at 10 GB retained and at active = false for more than two minutes on any slot. An inactive slot is a connector that died, and the clock is running.

Connector task state. Debezium fails the task, not the worker. Poll /connectors?expand=status and treat anything other than RUNNING as an incident.

MilliSecondsBehindSource. The Debezium JMX metric for end-to-end lag from the source commit. Watch the trend, not the instantaneous value, because a batch job on the source produces a legitimate spike.

Snapshot progress. RowsScanned and SnapshotCompleted on the snapshot MBean, so you know whether a six-hour backfill is progressing or wedged.

flowchart TD
  slot["pg_replication_slots"] --> a1{"retained WAL > 10GB?"}
  a1 -->|yes| page["Page the pipeline owner"]
  slot --> a2{"active = false?"}
  a2 -->|yes| page
  task["Connect task status"] --> a3{"state != RUNNING?"}
  a3 -->|yes| page
  lag["MilliSecondsBehindSource"] --> a4{"trending up 30 min?"}
  a4 -->|yes| warn["Warn, then investigate sink"]

Two of the four alarms are on the database, not the pipeline. That is the right split for CDC.

Pitfalls

A slot left behind by a deleted connector. Deleting a Debezium connector does not drop its slot. The orphan keeps pinning WAL forever. Add SELECT pg_drop_replication_slot('...') to your teardown runbook and audit pg_replication_slots monthly.

No heartbeat on a low-traffic publication. The slot’s confirmed LSN freezes while the database’s WAL races ahead. Configure heartbeat.action.query against a table inside the publication.

Failing over the primary without planning for slots. Physical replicas do not inherit logical slots before Postgres 17, and even with slot synchronization the details matter. Know what your managed provider does on failover before it happens, not during.

REPLICA IDENTITY FULL everywhere. It multiplies WAL volume on update-heavy tables, which increases both replication load and the amount of data a stalled slot pins. Set it per table, with a reason.

Assuming cross-table transaction ordering. Each table is its own topic with its own partitions. An order and its items arrive independently. If you need the transaction boundary, use Debezium’s transaction metadata topic and buffer on transaction.id, or accept eventual consistency in the sink.

Hard-deleting in the sink. A DELETE in the warehouse breaks incremental models that scan for changed rows and erases the audit trail. Soft delete with a flag and a timestamp.

Snapshotting a huge table with initial. The long transaction blocks vacuum for its duration, and on a busy table that means bloat you will be cleaning up for days. Use incremental snapshots past a few tens of millions of rows.

FAQ

Can I run Debezium against a read replica?

Yes on Postgres 16 and later, where logical decoding on standbys is supported, and it is genuinely attractive for keeping decoding CPU off the primary. Below that, no. Either way, understand what happens to the slot during a failover before you rely on it.

How do I add a table to an existing connector?

Add it to table.include.list and to the publication, then send an incremental snapshot signal for that table. The connector picks up new changes immediately; the signal backfills the history. No re-snapshot of the other tables required.

What happens if the connector is down for a week?

The slot retains a week of WAL. Whether that is survivable depends entirely on your write volume and disk headroom, which is why max_slot_wal_keep_size exists as a circuit breaker. If the slot gets invalidated, you re-snapshot; plan for that path rather than assuming it will not happen.

Should I use Debezium or a managed CDC tool?

Managed tools remove slot operations and charge per row, which gets expensive at volume and cheap at low volume. Debezium gives you control and costs engineering time. I run Debezium where volume is high or the destination is unusual, and buy managed CDC for a handful of small, boring tables.

How do I handle schema changes in the source table?

Debezium emits a new schema version and, with Avro, registers it. An additive column flows through if the subject’s compatibility mode allows it, which is the argument for BACKWARD in the schema registry post. A dropped column breaks downstream models regardless of what the registry says, so treat source DDL as a contract change.

Is CDC a replacement for batch extracts?

For tables where you need low latency and true deletes, yes. For a reference table that changes twice a year, a nightly full extract is simpler and has no slot to babysit. I run CDC on the handful of high-value transactional tables and leave the rest on batch.

What this means for your pipelines

Postgres CDC is the highest-fidelity extraction method available: you get every change including deletes, in commit order, without polling a updated_at column that some code path forgets to set. That fidelity is why it is worth the operational weight. But the weight is real, and it lands on the database team as much as the data team.

Before the first connector goes live, get four things in place: max_slot_wal_keep_size as a circuit breaker, a heartbeat query so quiet publications still advance the slot, an alert on retained WAL and on active = false, and a runbook entry that drops the slot when a connector is decommissioned. Those four cost an afternoon and prevent the only Debezium failure mode that can take down production.

On the pipeline side, build the apply logic assuming duplicates and out-of-order delivery from day one. The MERGE with an LSN watermark and a soft delete is not defensive over-engineering; it is the minimum that survives a connector restart, an incremental snapshot, and the day someone replays a topic to fix a transformation bug. Get that right and CDC becomes what it should be: a change stream you can rebuild from at any time, rather than a delicate pipe you are afraid to restart.