Debezium PostgreSQL CDC: Slots, Snapshots and Safe Consumers

How Debezium reads PostgreSQL: logical replication, slots and the pipeline

Debezium's PostgreSQL connector is a logical replication client. It does not poll tables or rely on triggers. It connects to the primary over the replication protocol and receives a decoded stream of row-level changes from the write-ahead log (WAL), the same log PostgreSQL uses for crash recovery and physical replicas.

Three server-side pieces make this work:

• wal_level=logical makes PostgreSQL write enough information into the WAL to reconstruct row changes, not just physical page modifications.
• The pgoutput plugin, which ships with PostgreSQL, decodes WAL records into logical messages: begin, insert, update, delete and commit. A publication controls which tables pgoutput emits.
• A replication slot stores the position of the oldest WAL the consumer still needs and the LSN (log sequence number) the consumer has confirmed. PostgreSQL will not remove WAL past that position.

The pipeline runs in this order:

1. A transaction commits on the primary.
2. pgoutput decodes it through the slot.
3. The Debezium connector, running as a Kafka Connect task, turns each row change into an event.
4. Kafka Connect writes the event to a per-table topic.
5. Downstream services consume that topic.

Debezium records its own offsets in Kafka. It then confirms the corresponding LSN back to the slot, which is what lets PostgreSQL discard older WAL.

The slot is a resource you operate, separate from the connector. It persists when the connector is stopped, crashed or deleted. While the slot is inactive, the primary keeps every WAL segment generated after the slot's position. Disk usage on your production database grows until someone consumes from the slot or drops it.

Copy+- PostgreSQL primary (wal_level=logical) ---------------------------------+
|  +-------------+    +-----------------+    +--------------------------+  |
|  | transaction |--->| WAL records     |--->| pgoutput plugin          |  |
|  | commits     |    | in pg_wal       |    | tables in PUBLICATION    |  |
|  +-------------+    +--------+--------+    +-------------+------------+  |
|                              ^ pins WAL from             |               |
|                              | restart_lsn               v               |
|                   +----------+---------------------------+------------+  |
|                   | replication slot: restart_lsn, confirmed_flush_lsn|  |
|                   | inactive slot => WAL retained, primary disk grows |  |
|                   | persists if connector stops, crashes or is deleted|  |
|                   +-------------------+-----------+-------------------+  |
+---------------------------------------+-----------+----------------------+
                          change stream |           ^ confirm LSN
                                        v           |
+---------------------------------------+-----------+----------------------+
| Kafka Connect worker                                                     |
|   Debezium PostgreSQL connector task: row change -> change event         |
|   offsets stored in Kafka, then LSN confirmed to slot                    |
+------------+-----------------------+-----------------------+-------------+
             v                       v                       v
   +---------+--------+    +---------+--------+    +---------+--------+
   | topic: users     |    | topic: orders    |    | topic: payments  |
   +---------+--------+    +---------+--------+    +---------+--------+
             v                       v                       v
   +---------+--------+    +---------+--------+    +---------+--------+
   | consumer svc A   |    | consumer svc B   |    | consumer svc C   |
   +------------------+    +------------------+    +------------------+

Committed changes flow from the WAL through pgoutput and the replication slot to the Debezium connector in Kafka Connect, then to per-table topics and their consumers; the connector confirms LSNs back to the slot, and the slot pins WAL on the primary until it does.

When to use CDC, who it is for, and when to avoid it

That operational cost is worth paying when the problem CDC solves is real. The core problem is the dual write: a service updates PostgreSQL and then publishes an event or updates Elasticsearch, Redis or a warehouse. If the process crashes between the two steps, the systems diverge, and nothing reconciles them. CDC removes the second write. The database commit is the single source of truth, and every downstream system derives its state from the WAL in commit order.

Typical uses:

• Search indexes that must reflect inserts, updates and deletes without nightly rebuilds.
• Caches that are refreshed or evicted from actual row changes rather than application code paths that someone forgets to update.
• Analytics pipelines that need a row-level change history rather than periodic full-table dumps.
• Service integration, where other teams react to state changes without calling your API or reading your database.

The payoff grows with the number of consumers. A platform team running Kafka for several backend teams pays the infrastructure cost once and adds consumers cheaply.

Avoid it when there is one consumer, or when you only need cache invalidation. Running Kafka, Kafka Connect and a schema registry for that is disproportionate. PostgreSQL's LISTEN/NOTIFY, or a periodic poll on an updated_at column, is often enough.

Be wary of streaming domain tables directly. Every column becomes part of a contract other teams depend on, so renaming a field or splitting a table turns into a cross-team migration. The usual mitigation is the outbox pattern. The service writes an explicit event row to an outbox table in the same transaction as its domain change, and Debezium captures only that table. Consumers then depend on a deliberately designed event schema rather than your internal tables.

Preparing PostgreSQL and registering the connector

Prepare PostgreSQL

Set wal_level = logical so the WAL carries enough detail to decode row changes. This setting requires a server restart, so plan it into a maintenance window. Set max_replication_slots and max_wal_senders with headroom above current use. Each connector holds one slot and one sender connection, and physical replicas and backup tools draw from the same pools. Running out stops a connector from starting.

Create a dedicated login role with the REPLICATION attribute. Grant it SELECT only on the tables you intend to capture, because the initial snapshot reads them with ordinary queries. A narrow role limits what a leaked credential exposes.

Create the publication yourself with CREATE PUBLICATION ... FOR TABLE and list only the required tables, such as the outbox. Then set publication.autocreate.mode to disabled. This stops Debezium from creating a publication covering every table when the role happens to hold enough privilege to do so.

Register the connector

The connector class is io.debezium.connector.postgresql.PostgresConnector. The essential properties are:

• Connection: database.hostname, database.port, database.user, database.password and database.dbname.
• plugin.name=pgoutput: uses PostgreSQL's built-in decoder, so no server extension is needed.
• slot.name and publication.name: must be unique per connector.
• topic.prefix: namespaces the topics. Changing it later means new topic names.
• table.include.list: matching the publication.

POST the JSON to Kafka Connect's /connectors endpoint with curl. Then query /connectors/<name>/status. Both the connector and its task should report RUNNING. A FAILED task includes a stack trace in that response.

Copy#!/usr/bin/env bash
# Register a Debezium PostgreSQL connector and check its status.
# Assumes the publication 'app_outbox_pub' already exists and the
# 'debezium_cdc' role has REPLICATION plus SELECT on public.outbox.
set -euo pipefail

CONNECT_URL=""

curl -sS -X POST "$CONNECT_URL/connectors" \
  -H 'Content-Type: application/json' \
  -d @- <<EOF
{
  "name": "orders-outbox-cdc",
  "config": {
    "connector.class": "io.debezium.connector.postgresql.PostgresConnector",
    "database.hostname": "pg-primary.internal",
    "database.port": "5432",
    "database.user": "debezium_cdc",
    "database.password": "${PG_CDC_PASSWORD}",
    "database.dbname": "orders",
    "plugin.name": "pgoutput",
    "slot.name": "orders_outbox_slot",
    "publication.name": "app_outbox_pub",
    "publication.autocreate.mode": "disabled",
    "topic.prefix": "orders",
    "table.include.list": "public.outbox"
  }
}
EOF

# Give the task a moment to start, then confirm connector and task state.
# Expect "RUNNING" for both; a FAILED task includes a "trace" field.
sleep 5
curl -sS "$CONNECT_URL/connectors/orders-outbox-cdc/status"

A curl-based registration of a pgoutput Debezium connector bound to a pre-created publication and a dedicated slot, followed by a status check of the connector and its task.

Initial snapshots, change events and REPLICA IDENTITY

Once the task is RUNNING, the connector creates its replication slot before it reads any rows. Creating the slot first pins the WAL from that LSN onward, so no change can fall into the gap between the snapshot and streaming. The connector then reads each captured table with SELECT inside a single repeatable-read transaction and emits every row as an event with op: "r". When the snapshot finishes, it streams from the slot's starting LSN.

With the default initial mode, a large database means hours of sequential scans on the primary and WAL piling up behind the unconsumed slot. initial_only snapshots and stops, with no streaming. Incremental snapshots avoid one long transaction. You configure a signaling table and insert an execute-snapshot signal. The connector then reads tables in primary-key chunks interleaved with streaming, and you can trigger the same mechanism ad hoc to backfill a newly added table. The price is extra configuration: the signal table, permissions, and consumers that tolerate r and u events for the same key arriving close together.

Each event's value holds before, after, op (c, u, d, r) and source. source includes the lsn and txId, which are useful for deduplication. A create has a null before. A delete has a null after. A delete is followed by a tombstone, which has the same key and a null value, so Kafka log compaction can eventually drop the key.

Use FULL only on tables whose consumers need diffs.

Copy-- Signaling table for incremental / ad hoc snapshots.
-- The connector property signal.data.collection must name it: public.debezium_signal
CREATE TABLE public.debezium_signal (
  id   VARCHAR(42) PRIMARY KEY,  -- arbitrary unique signal id
  type VARCHAR(32) NOT NULL,     -- e.g. 'execute-snapshot'
  data VARCHAR(2048)             -- JSON payload for the signal
);

-- The connector user must be able to read the signal table.
GRANT SELECT ON public.debezium_signal TO debezium_user;

-- Backfill a newly captured table in primary-key chunks,
-- interleaved with streaming, instead of one long repeatable-read scan.
INSERT INTO public.debezium_signal (id, type, data)
VALUES (
  'backfill-orders-001',
  'execute-snapshot',
  '{"data-collections": ["public.orders"], "type": "incremental"}'
);

-- Opt in to full before-images only where consumers need them;
-- every UPDATE and DELETE on this table now logs the whole old row to WAL.
ALTER TABLE public.orders REPLICA IDENTITY FULL;

-- Check the current setting: d = DEFAULT, f = FULL, i = INDEX, n = NOTHING.
SELECT relname, relreplident
FROM pg_class
WHERE oid = 'public.orders'::regclass;

-- Revert if the extra WAL volume is not worth it.
-- ALTER TABLE public.orders REPLICA IDENTITY DEFAULT;

Creates a Debezium signaling table, triggers an incremental snapshot of one table, and switches that table to REPLICA IDENTITY FULL while showing how to verify or revert the setting.

Topic keys, ordering and consumers that survive at-least-once delivery

By default Debezium writes each table to its own topic, named from the configured topic prefix, schema and table. The message key holds the row's primary-key columns, so every change to a given row hashes to the same partition. Kafka preserves order within a partition, which means you get per-row ordering only. Changes to two different rows, rows in two tables, or all the statements in one transaction can arrive interleaved or out of commit order. If a consumer needs transactional grouping, it has to reassemble it, for example from the transaction metadata Debezium can emit.

Tables without a primary key have no natural key. Their events lose per-row ordering, and log compaction cannot collapse them. Add a key, or declare substitute key columns in the connector configuration.

Consumers that tolerate replays

Delivery is at-least-once, so after a connector restart you will see duplicates and replayed history. Write each event as an upsert keyed on the primary key. Store the event's source.lsn, or an application version column, alongside the row, and skip any event whose value is not newer than the stored one. Treat a delete event as a delete, or as a soft-delete marker if late updates might still arrive. Treat the null-value tombstone that follows a delete as a signal for compaction, not as business data.

Schema changes

Logical replication does not stream DDL. A new column shows up only when the next row change carries it. Sequence migrations accordingly:

• Deploy consumers that ignore unknown fields.
• Add nullable columns.
• Backfill the new columns.
• Only after every consumer has stopped reading a column, drop or rename it.

Copyimport json

# Assumes the JSON converter with schemas disabled, so each value is the bare
# Debezium envelope: {"before", "after", "source", "op", ...}.
UPSERT = """
INSERT INTO customers_replica (id, email, name, deleted, source_lsn)
VALUES (%(id)s, %(email)s, %(name)s, %(deleted)s, %(lsn)s)
ON CONFLICT (id) DO UPDATE
  SET email = EXCLUDED.email, name = EXCLUDED.name,
      deleted = EXCLUDED.deleted, source_lsn = EXCLUDED.source_lsn
  -- Stale or replayed event: only apply if strictly newer than what we stored.
  WHERE customers_replica.source_lsn < EXCLUDED.source_lsn
"""

def handle(cur, raw_key, raw_value):
    # Tombstone after a delete: exists for log compaction, not business data.
    if raw_value is None:
        return

    event = json.loads(raw_value)
    lsn = event["source"]["lsn"]

    if event["op"] == "d":
        # Soft delete keeps the row and its LSN, so a late, older update
        # replayed after a restart cannot resurrect it.
        pk = json.loads(raw_key)["id"]
        row = {"id": pk, "email": None, "name": None, "deleted": True, "lsn": lsn}
    else:
        # c = insert, u = update, r = snapshot read: all become upserts.
        after = event["after"]
        row = {"id": after["id"],
               "email": after.get("email"),
               "name": after.get("name"),  # unknown new fields are ignored
               "deleted": False, "lsn": lsn}

    cur.execute(UPSERT, row)
    # Commit the DB transaction before committing the Kafka offset, so a crash
    # in between yields a harmless replay rather than a lost change.

An idempotent Python consumer that upserts Debezium change events keyed on the primary key, discards stale or replayed events by comparing source.lsn, turns deletes into soft deletes, and ignores tombstones.

Operating the pipeline: slot lag, WAL retention, failover and a runbook

A stalled connector does not just delay events. It keeps its replication slot pinned, and PostgreSQL will retain every WAL segment from the slot's restart_lsn onward until the disk fills and the primary stops accepting writes. Alert on retained WAL, not only on consumer lag.

What to watch

• Retained WAL per slot: compute pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) from pg_replication_slots. Alert when this number keeps growing, and set the threshold well below your free disk.
• Slot activity: check the active and active_pid columns. An inactive slot that is still holding WAL means no one is reading it.
• Retention guardrail: consider setting max_slot_wal_keep_size. PostgreSQL will then invalidate a slot that falls too far behind instead of filling the disk. The trade-off is that you will need to resnapshot afterward.
• Task status: call GET /connectors/<name>/status on the Kafka Connect REST API. A connector can report RUNNING while one of its tasks is FAILED, so check each task state.

Failover

Logical slots exist only on the primary unless you configure slot synchronization to standbys. Recent PostgreSQL releases support this. Without it, a promoted standby has no slot, and changes made around the promotion can be lost. Test a promotion before you rely on the pipeline.

Runbook

1. Failed task: read the stack trace in the status response, fix the cause, then POST /connectors/<name>/tasks/<id>/restart.
2. Resnapshot: for selected tables, trigger an incremental snapshot through the Debezium signaling table. If the slot was lost, recreate the connector with a fresh slot and run a full snapshot.
3. Abandoned slot: confirm the slot is active = false. Delete the connector first so it cannot recreate the slot. Then run pg_drop_replication_slot('<slot>') and drop the publication if nothing else uses it.

Alternatives and impact on cost and engineering time

Debezium is not the only way to move PostgreSQL changes into other services, and for some workloads it is more machinery than the problem needs.

Dual writes cannot be fixed by retries, because no single transaction covers both systems. That is the main reason to adopt CDC or an outbox. If you already run Kafka, the extra cost of Debezium is mostly Kafka Connect, a schema registry, and the slot and WAL monitoring covered above. If you do not run Kafka, a polled outbox is often the cheaper way to get the same correctness.

Illustrative estimate (assumed numbers, not a benchmark)

Net hours per month = (sync-job upkeep + dual-write incident time) − (pipeline operations + idempotency upkeep)

Assumed inputs:

• Sync-job upkeep: 3 jobs × 6 h = 18 h
• Dual-write incident time: 2 incidents × 8 h = 16 h
• Pipeline operations: 12 h
• Idempotency upkeep: 6 h

Result: 34 h − 18 h = 16 h saved per month. This excludes the one-time cost of setting up the pipeline.

How OneEnv can help

Disclosure: OneEnv is the product behind this blog.

OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.

Applied to debezium postgresql cdc, this is where it helps:

Saves time

• Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
• One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
• Reviewers see the exact diff and the list of affected services up front, so approval is a decision rather than an investigation.

Saves cost

• Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
• Less duplicated configuration to maintain: one shared definition inherited by many services.
• Per-service approve/reject means a bad shared value can be refused where it would break something, before it is live.
• OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.

These are qualitative benefits, not measured results — how much you save depends on how many services share the value.

Want to try it? Start at oneenv.io.