Postgres CDC: Logical Replication, Debezium, and Change Data Capture Options

What Is Postgres CDC?

Postgres CDC (change data capture) is the practice of capturing every insert, update and delete committed to a PostgreSQL database and delivering those changes, in commit order, to another system. The standard mechanism is logical decoding: Postgres reads its own write-ahead log, converts the changes into row-level events through an output plugin, and streams them to a consumer through a replication slot.

Change data capture turns a database from something you query into something that tells you what changed. Downstream consumers include search indexes, caches, data warehouses, lakehouses, event-driven microservices, audit stores, and other Postgres clusters.

The reason CDC matters is freshness without load. Re-querying a source table to find what changed costs the source CPU and I/O on every poll, and still misses deletes. Reading the write-ahead log costs very little extra, because Postgres is already writing that log for durability.

Three Ways to Do CDC in Postgres

Trigger-based audit tables. A row-level trigger fires on every INSERT, UPDATE and DELETE and writes the change into a changelog table. Consumers read and delete from that table.

CREATE TABLE change_log (
    id          bigserial PRIMARY KEY,
    table_name  text        NOT NULL,
    op          char(1)     NOT NULL,          -- I, U, D
    row_data    jsonb,
    old_data    jsonb,
    changed_at  timestamptz NOT NULL DEFAULT now()
);

CREATE OR REPLACE FUNCTION capture_change() RETURNS trigger AS $$
BEGIN
    INSERT INTO change_log (table_name, op, row_data, old_data)
    VALUES (
        TG_TABLE_NAME,
        left(TG_OP, 1),
        CASE WHEN TG_OP <> 'DELETE' THEN to_jsonb(NEW) END,
        CASE WHEN TG_OP <> 'INSERT' THEN to_jsonb(OLD) END
    );
    RETURN NULL;  -- AFTER trigger, return value ignored
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER orders_cdc
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW EXECUTE FUNCTION capture_change();

Triggers work everywhere, including managed services that lock down replication settings, and they capture deletes and full old rows. The cost is on the write path: every write to orders now does a second write to change_log inside the same transaction, which adds latency, WAL volume and vacuum pressure on a table that churns constantly. It is a reasonable choice for low-volume audit trails and a poor one for high-write tables.

Polling on updated_at. A job runs SELECT * FROM orders WHERE updated_at > :last_seen on an interval.

SELECT *
FROM orders
WHERE updated_at > $1
ORDER BY updated_at
LIMIT 10000;

Polling is simple and needs no special privileges, but it has three structural problems. It cannot see hard deletes, because a deleted row no longer matches any query. It misses intermediate states when a row changes twice between polls. And it can skip rows outright: updated_at is set when the statement runs, not when the transaction commits, so a long transaction that started earlier can commit a row with a timestamp older than your watermark after you’ve already moved past it. Teams work around this by re-reading an overlap window, which trades missed rows for duplicates. Many ELT tools still offer this as “incremental sync” or “cursor-based” replication for sources where logical decoding is not available.

Logical decoding. Postgres decodes committed changes from the write-ahead log (WAL) and streams them to a consumer. There’s no extra write on the source table, deletes and old values are captured, and changes arrive in commit order. Every serious Postgres CDC tool, including Debezium, Flink CDC, AWS DMS and the managed ELT services, is built on it. The rest of this guide is about this approach.

How Logical Decoding Works

Every change in Postgres is written to the WAL before it is applied to data files. Physical replication ships those WAL bytes to a standby. Logical decoding goes one step further: it interprets the WAL and turns it into row-level change events a non-Postgres consumer can use. Four pieces make it work.

wal_level = logical. The default wal_level is replica, which doesn’t write enough information to reconstruct row changes. logical adds that information. Changing it requires a server restart.

Replication slots. A replication slot is a named, crash-safe cursor into the WAL. It records how far its consumer has confirmed, and Postgres will not remove any WAL the slot still needs. This is what makes CDC reliable: if the consumer disconnects for an hour, it resumes exactly where it left off. It is also what makes CDC dangerous, covered below. Only one consumer can read from a slot at a time, and each slot belongs to one database.

Output plugins. An output plugin converts decoded changes into a wire format. pgoutput is built into Postgres 10+ and is what native logical replication uses; Debezium and most modern tools use it too. wal2json is a third-party plugin that emits JSON and is preinstalled on RDS and Azure. test_decoding ships with Postgres as an example plugin and still shows up in older tooling. decoderbufs emits Protobuf and is maintained by the Debezium community.

Publications. A publication defines which tables, and optionally which rows and columns, pgoutput sends. CREATE PUBLICATION ... FOR TABLE names specific tables; FOR ALL TABLES covers everything including tables created later; FOR TABLES IN SCHEMA covers a schema. Since Postgres 15, publications support row filters (WHERE) and column lists.

Two consequences of this design matter for everything downstream. First, a transaction’s changes are emitted when it commits, so a large transaction arrives as one burst at commit time, not gradually as it runs (subscriptions can opt into streaming in-progress transactions, and Postgres 18 made streaming = parallel the default for new subscriptions). Second, logical decoding does not capture DDL. An ALTER TABLE on the source doesn’t appear in the stream, so schema changes need their own process.

To watch all four pieces work together in a few minutes of SQL, see the Postgres CDC example after the setup steps below.

How to Enable CDC in PostgreSQL

The switch differs by platform, but the checklist is the same: turn on logical WAL, make sure there are enough slots and WAL senders, restart, and give the CDC user the REPLICATION attribute (or the platform’s equivalent role) plus SELECT on the tables for the initial snapshot.

Self-hosted PostgreSQL. Set these in postgresql.conf, then restart:

wal_level = logical
max_replication_slots = 10        # default 10; one per CDC consumer, plus headroom
max_wal_senders = 10              # default 10; one per active consumer, plus physical standbys
max_slot_wal_keep_size = 50GB     # default -1 (unlimited); see the risks section

Then create a dedicated user and a publication:

CREATE ROLE cdc_user WITH LOGIN REPLICATION PASSWORD 'change-me';
GRANT USAGE ON SCHEMA public TO cdc_user;
GRANT SELECT ON orders, payments, customers TO cdc_user;

CREATE PUBLICATION cdc_pub FOR TABLE orders, payments, customers;

Add a host replication or database line for cdc_user in pg_hba.conf if your tool connects over the replication protocol from a new host. Parameter defaults and restart requirements are in the replication configuration docs.

Amazon RDS for PostgreSQL. You can’t set wal_level directly. Set the static parameter rds.logical_replication = 1 in a custom DB parameter group, which sets wal_level to logical for you, then reboot the instance. The CDC user needs the rds_replication role:

GRANT rds_replication TO cdc_user;

(AWS docs). Default parameter groups can’t be edited, so if the instance is still on one, attach a custom group first.

Amazon Aurora PostgreSQL. Same parameter, rds.logical_replication = 1, but it lives in the custom DB cluster parameter group, not the instance group. Reboot the writer instance, then confirm with SHOW wal_level; (AWS docs). AWS’s own guidance is to size max_replication_slots for every publication, subscription and DMS task you plan to run, and to drop inactive slots, since they also block vacuum.

Google Cloud SQL for PostgreSQL. Set the cloudsql.logical_decoding flag to on. Changing it restarts the instance. Create the CDC user with replication rights:

CREATE USER cdc_user WITH REPLICATION IN ROLE cloudsqlsuperuser LOGIN PASSWORD 'change-me';

(Google Cloud docs).

Azure Database for PostgreSQL Flexible Server. Set the server parameter wal_level to logical in the portal or CLI, raise max_worker_processes to at least 16, save and restart, then ALTER ROLE cdc_user WITH REPLICATION; (Microsoft docs). Azure will also drop an unused logical slot automatically if storage crosses its threshold, and the server goes read-only at 95% storage, so alerting matters here too.

Supabase. Logical replication is on by default. Connect CDC tools with the direct connection string, not the pooler: logical replication can’t run through the transaction or session pooler. Direct connections are IPv6 unless you enable the paid IPv4 add-on (Supabase docs).

On every platform, verify before pointing a tool at it:

SHOW wal_level;                                   -- logical
SELECT rolname, rolreplication FROM pg_roles WHERE rolname = 'cdc_user';
SELECT * FROM pg_publication_tables WHERE pubname = 'cdc_pub';

REPLICA IDENTITY: What Updates and Deletes Carry

An insert event always carries the whole new row. What an update or delete carries about the old row is controlled per table by REPLICA IDENTITY:

ALTER TABLE payments REPLICA IDENTITY FULL;

-- Check what each table is set to: d = default, i = index, f = full, n = nothing
SELECT relname, relreplident FROM pg_class
WHERE relname IN ('orders', 'payments', 'customers');

Two gotchas. A table in a publication that publishes updates or deletes must have a replica identity, or Postgres rejects the UPDATE or DELETE on the source with an error: a table with no primary key and DEFAULT identity will break writes the moment you add it to the publication. And FULL increases WAL volume, since every update and delete now logs the entire old row. Use it where consumers need the before image, not everywhere.

In Debezium terms, DEFAULT means the before field of an update event contains only the key columns, and FULL means it contains the full previous row (Debezium docs). Even with FULL, unchanged values stored out of line in TOAST can arrive as a placeholder on some tools, so check your connector’s documentation for large text and jsonb columns.

Postgres CDC Example

This walkthrough runs end to end in psql on any Postgres where wal_level = logical and your role has the REPLICATION attribute (or the platform’s replication role). It uses test_decoding, which ships with Postgres and prints changes as readable text, so you can see exactly what a CDC consumer receives.

-- 1. A source table, with the before image turned on
CREATE TABLE accounts (id int PRIMARY KEY, balance numeric);
ALTER TABLE accounts REPLICA IDENTITY FULL;

-- 2. A publication: what pgoutput consumers (Debezium, native subscriptions) read
CREATE PUBLICATION demo_pub FOR TABLE accounts;

-- 3. A logical replication slot
SELECT * FROM pg_create_logical_replication_slot('demo_slot', 'test_decoding');

-- 4. Make some changes
INSERT INTO accounts VALUES (1, 100), (2, 250);
UPDATE accounts SET balance = 175 WHERE id = 1;
DELETE FROM accounts WHERE id = 2;

-- 5. Read (and consume) the change stream
SELECT lsn, xid, data FROM pg_logical_slot_get_changes('demo_slot', NULL, NULL);

Each statement comes back as its own transaction, wrapped in BEGIN and COMMIT rows and tagged with its log sequence number (LSN). The change rows in the data column look like this:

table public.accounts: INSERT: id[integer]:1 balance[numeric]:100
table public.accounts: INSERT: id[integer]:2 balance[numeric]:250
table public.accounts: UPDATE: old-key: id[integer]:1 balance[numeric]:100 new-tuple: id[integer]:1 balance[numeric]:175
table public.accounts: DELETE: id[integer]:2 balance[numeric]:250

The old values on the update and the full row on the delete are there because of REPLICA IDENTITY FULL; with the default setting the delete carries only the primary key. Run step 5 again and it returns nothing, because get_changes advances the slot. Use pg_logical_slot_peek_changes to read without consuming.

test_decoding ignores publications. A production consumer creates its slot with pgoutput instead and names the publication when it starts streaming, which is what Debezium’s publication.name setting does. To inspect that binary stream from SQL, create a second slot with pgoutput and call pg_logical_slot_peek_binary_changes(’slot’, NULL, NULL, ’proto_version’, ’1’, ’publication_names’, ’demo_pub’). Signatures are in the replication functions reference and the output format in the logical decoding examples.

-- 6. Clean up. An abandoned slot retains WAL until the disk fills.
SELECT pg_drop_replication_slot('demo_slot');
DROP PUBLICATION demo_pub;
DROP TABLE accounts;

The Operational Risks: Replication Slots and WAL

The most common way Postgres CDC hurts a production database is a slot nobody is reading.

A slot keeps every WAL segment from its last confirmed position forward. If the consumer stops (a connector crashes, a Kafka Connect cluster is torn down, a proof-of-concept is abandoned) the slot stays, WAL accumulates in pg_wal, and the disk fills. The Postgres docs are blunt about it: slots “will prevent removal of required resources even when there is no connection using them,” and an old slot’s catalog horizon also holds back vacuum, which in the worst case can push the database toward transaction ID wraparound shutdown.

Monitor retained WAL per slot. Run this, graph it, and alert on it:

SELECT
    slot_name,
    plugin,
    active,
    wal_status,
    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 consumer_lag
FROM pg_replication_slots
WHERE slot_type = 'logical';

active = false with growing retained_wal is the alert. wal_status moves from reserved to extended to unreserved to lost as a slot approaches and passes the retention cap.

Cap retention with max_slot_wal_keep_size. The default is -1, unlimited. Setting a cap, sized to your disk and to how long an outage you want CDC to survive, means a dead slot is invalidated instead of taking the primary down. The trade is explicit: an invalidated slot can’t resume, and the consumer has to re-snapshot. That’s almost always better than a full disk.

Postgres 18: idle_replication_slot_timeout. Postgres 18 added idle_replication_slot_timeout, which invalidates slots that have been inactive longer than a set duration. It defaults to 0 (disabled) and does not apply to slots being synced on a standby. It’s a useful second guard for slots left behind by abandoned tools.

Quiet databases need heartbeats. A slot’s position only advances when the consumer confirms something. On a database where the captured tables rarely change but other tables do, WAL grows while the slot sits still. Debezium’s heartbeat.interval.ms (and similar settings in other tools) periodically confirms progress to keep retained WAL bounded.

Failover. Before Postgres 17, logical slots existed only on the primary. After a failover to a physical standby, the slot was gone and every CDC consumer had to re-snapshot, or the team ran the pg_failover_slots extension. Postgres 17 added failover slots: create the slot with the failover option (the fifth argument of pg_create_logical_replication_slot, or failover = true on CREATE SUBSCRIPTION), and set these on the standby:

# on the physical standby
sync_replication_slots = on
hot_standby_feedback = on
primary_slot_name = 'standby1_slot'
# primary_conninfo must include dbname=...

On the primary, list the standby’s physical slot in synchronized_standby_slots so logical consumers never get ahead of the standby that would take over. That adds a little latency to CDC delivery, and it’s the correct trade. Managed services handle this differently: Azure documents native slot sync for Postgres 17+ and the pg_failover_slots extension for 16 and earlier, so check your provider’s docs before assuming CDC survives a failover. Debezium exposes a slot.failover option for creating failover-enabled slots.

Other failure modes worth a runbook. DDL isn’t replicated, so adding a column on the source and not the target breaks native subscriptions until the target catches up. Before Postgres 19, sequences aren’t replicated by native logical replication (19 adds FOR ALL SEQUENCES publications and ALTER SUBSCRIPTION ... REFRESH SEQUENCES). Large transactions arrive in one burst at commit. And the initial snapshot of a large table can take hours, during which the slot is already retaining WAL.

Postgres CDC Tools Compared

Native logical replication (Postgres to Postgres). CREATE PUBLICATION on the source and CREATE SUBSCRIPTION on the target, with no other infrastructure:

-- on the target
CREATE SUBSCRIPTION cdc_sub
CONNECTION 'host=source.internal dbname=app user=cdc_user password=change-me'
PUBLICATION cdc_pub
WITH (failover = true);   -- Postgres 17+

It copies existing rows, then streams changes. It’s the right answer for migrations, major-version upgrades, consolidating databases and feeding a reporting replica with a subset of tables. The target must be Postgres (or speak its replication protocol), and DDL (plus sequences, before Postgres 19) needs manual handling.

Debezium with Kafka Connect. The common answer to “postgres cdc kafka.” Debezium runs as a Kafka Connect source connector, snapshots the tables, then streams changes from a slot into one Kafka topic per table, with a consistent event envelope (before, after, op, source). A minimal connector config:

{
  "name": "orders-cdc",
  "config": {
    "connector.class": "io.debezium.connector.postgresql.PostgresConnector",
    "database.hostname": "source.internal",
    "database.port": "5432",
    "database.user": "cdc_user",
    "database.password": "change-me",
    "database.dbname": "app",
    "topic.prefix": "app",
    "plugin.name": "pgoutput",
    "slot.name": "debezium_orders",
    "publication.name": "cdc_pub",
    "publication.autocreate.mode": "disabled",
    "table.include.list": "public.orders,public.payments,public.customers",
    "snapshot.mode": "initial",
    "heartbeat.interval.ms": "10000"
  }
}

Creating the publication yourself and setting publication.autocreate.mode to disabled keeps the CDC user’s privileges narrow. Debezium also runs without Kafka as Debezium Server (sinks such as Kinesis, Pub/Sub and Redis streams) or embedded in a JVM application. The cost is operating Kafka and Kafka Connect, plus schema-evolution handling downstream.

Flink CDC. Apache Flink CDC embeds the Debezium engine inside a Flink source, so changes flow straight into a Flink job without a Kafka hop. Its incremental snapshot reads large tables in parallel chunks without a global lock and hands off to streaming with exactly-once semantics. Use it when you’re already transforming, joining or aggregating in Flink. Note that its default decoding plugin is decoderbufs, so set decoding.plugin.name = pgoutput unless you’ve installed that extension. For how Flink and Kafka split the work, see Apache Kafka vs Apache Flink.

AWS DMS. The default “postgres cdc aws” answer for moving data into other AWS targets (S3, Redshift, Kinesis, another RDS). DMS uses pglogical if the extension is installed, otherwise test_decoding, and supports CDC for tables with primary keys; tables without one need REPLICA IDENTITY FULL. It captures DDL through an event-trigger audit table it asks you to create. It’s a migration-first service, and teams running it as a long-lived pipeline should budget for task monitoring.

Managed ELT and CDC services. Fivetran and Airbyte replicate Postgres into warehouses and lakes on a sync schedule, using logical decoding when available and cursor polling when not. Estuary and Striim stream changes continuously to a range of destinations. Sequin is a Postgres-specific CDC service that streams to Kafka, SQS, Kinesis, Redis and webhooks. All of them still create a slot on your database, so the monitoring above still applies.

Where CDC Data Lands, and the Freshness Math

CDC moves changes. It doesn’t decide how fresh anything downstream is. The number a service actually experiences is the sum of every hop between the commit and the read:

1. 1.Commit to decode. Changes are decoded after commit. Usually milliseconds, except for large transactions, which arrive in one burst.
2. 2.Connector batching. Debezium polls and batches; Kafka producers wait for linger.ms and batch size.
3. 3.Broker and consumer. Replication across brokers, then the consumer’s poll interval.
4. 4.Sink write. Warehouse and lake sinks buffer into files or micro-batches, often on a schedule of seconds to minutes.
5. 5.Derived state. The raw rows arrive, but the thing a service reads is usually derived: a customer’s 24-hour spend, an account’s exposure, a session’s cart value. If that aggregate is a Postgres materialized view refreshed every five minutes, the freshness of the answer is five minutes, no matter how fast CDC was.

Step 5 is where most CDC projects quietly lose their gains. A team spends a quarter getting change events into Kafka in under a second, then lands them in a warehouse that refreshes hourly, or a matview that rebuilds the full result on a cron. Incremental materialized views are the structural fix for that hop: update the aggregate from each change instead of recomputing it.

The second problem is consistency across destinations. When CDC fans out to a cache, a search index and a feature store, each consumer applies changes at its own pace. A service that reads a balance from one and a transaction count from another sees two different moments in time. Every system is correct on its own, and together they describe a state that never existed. For many analytics workloads this doesn’t matter. For decisions like approving a payment, releasing a hold or letting an agent act, it’s how you approve what you would have blocked if you’d seen current state.

Where Tacnode Fits

CDC is how source data from the systems a team already runs reaches decision context. The Tacnode Context Lake™ holds that decision context: the source rows, the signals derived from them, and the decisions made on them, with the decisions written back and read as context for the next one.

For Postgres, Tacnode ingests changes through logical replication, using the same wal_level = logical, REPLICATION user and slot setup described above, and alongside MySQL, MongoDB and Kafka sources. Your transactions keep committing to your Postgres exactly as they do today. What changes is the last two hops of the freshness math:

• Derived state stays current sub-second behind each change Tacnode ingests. Aggregates such as spend velocity, exposure and session totals are defined in SQL and maintained incrementally as change events arrive, or computed on demand against the ingested rows when that’s cheaper.
• One coherent snapshot. The raw rows and the signals derived from them are read together, against the same set of ingested events, so a decision never mixes a fresh balance with a stale count.
• Postgres on the read side too. Tacnode presents the PostgreSQL wire protocol, so services query it with the drivers and SQL they already use. Tacnode can also emit its own changes as CDC to downstream consumers.

If CDC is feeding a decision, rather than a dashboard, that’s the workload Tacnode is built for. If you’re weighing the transport layer itself, our guide to Kafka alternatives covers brokers and where each fits.

FAQ