The Complete Change Data Capture Guide for PostgreSQL

PostgreSQL change data capture (CDC) is the practice of capturing changes to your Postgres tables (inserts, updates, and deletes) and making them available to other systems. The most reliable method is log-based CDC, built on logical replication, which reads the write-ahead log (WAL) and streams every change as it commits, with minimal load on the source and without re-querying tables on a schedule.

This guide covers the three CDC methods, a step-by-step logical replication setup, and the production issues that actually break Postgres CDC in the real world: slot bloat, TOAST, schema changes, and failover. It's written to be useful whether you're standing up your first pipeline or hardening one that's already in production. The early sections assume no prior CDC knowledge, while the later sections go deep enough for a DBA or senior data engineer. 

If you want the general theory of CDC across all databases, start with our Change Data Capture pillar guide and come back here for the Postgres specifics.

What PostgreSQL CDC is and how it differs from streaming replication

Change data capture is a technique for recording the changes committed to a database (inserts, updates, and deletes) and delivering them to other systems, so they can react to those changes or keep a copy in sync. In Postgres specifically, "CDC" almost always means log-based CDC: reading changes from the write-ahead log rather than polling tables.

Two terms get used interchangeably here, and they shouldn't be.

• Physical (streaming) replication ships the WAL byte-for-byte to a standby server that replays it to become an identical copy of the primary. It exists to provide high availability and read replicas. You can't point it at a data warehouse, filter it to specific tables, or transform it; it's an all-or-nothing binary copy of the whole cluster.
• Logical replication decodes that same WAL into a stream of row-level change events: "row X in table orders was updated, here are the new values." Because the output is logical (rows and columns, not disk blocks), you can send it anywhere: another Postgres instance, Kafka, a warehouse like Snowflake, or a CDC platform. Log-based CDC is built on this mechanism.

So when someone asks whether to use Postgres CDC or streaming replication, the two solve different problems. Use physical replication for database failover and read scaling. Use logical-replication-based CDC when you need those changes outside Postgres: in analytics, search indexes, caches, audit logs, or downstream microservices. The rest of this guide is about the second case.

Why stream changes out of PostgreSQL?

PostgreSQL is a transactional database optimized for the reads and writes of a live application. That's why you usually don't want to run heavy analytical work against it directly: long-running queries and frequent SELECT * scans compete with production transactions, and at the wrong moment they slow down or time out the requests your users are waiting on.

The common pattern is to keep Postgres as the system of record and replicate its data into a store built for analytics (Snowflake, BigQuery, ClickHouse, or an Iceberg lakehouse), where heavy queries run without touching production. CDC is what makes that replication both low-impact and current: instead of repeatedly scanning tables, it follows the WAL and forwards each change as it commits.

That continuous approach also captures history a batch job can't. A nightly snapshot only sees the state of each row at the moment it runs. CDC sees every state in between: rows that changed several times during the day, or that were created and deleted between snapshots. Three use cases lean on that directly:

1. Real-time analytics, where dashboards and models need data that's seconds old, not hours.
2. Audit and history, where you need a complete record of every change, not just the latest value.
3. Low-impact replication of business-critical databases that can't absorb the load of repeated bulk reads.

How PostgreSQL CDC works

Log-based CDC rests on five Postgres building blocks. Most CDC failures in production trace back to one of them, so it's worth understanding each.

1. The write-ahead log (WAL). Before Postgres applies a transaction, it writes the change to the WAL, an append-only log that guarantees durability and crash recovery. Every committed change lands here first, which is why the WAL is the ideal source for CDC: it's complete and ordered.
2. Logical decoding. By default the WAL is a low-level physical format. Setting wal_level = logical tells Postgres to include enough information to decode the WAL into logical row changes. Logical decoding is the engine that performs that translation.
3. The output plugin. The decoder hands raw changes to an output plugin that formats them. pgoutput is the built-in plugin that ships with Postgres (since version 10) and is the standard choice for modern tools. wal2json is a popular third-party plugin that emits JSON, and decoderbufs emits Protocol Buffers; you'll see these named in older tutorials, but most current connectors default to pgoutput.
4. The replication slot. A slot is a bookmark that tracks how far a given consumer has read in the WAL. Postgres will not delete WAL that a slot still needs. It's what makes CDC reliable. It's also, as the production section shows, the feature most likely to take your database down if you neglect it.
5. The publication. A publication is the named set of tables (and optionally which operations) whose changes are marked for logical replication. It's how you tell Postgres "replicate orders and customers, not everything."

Put together: a change is committed and written to the WAL; logical decoding reads it; the output plugin (pgoutput) formats it; and an external consumer such as Debezium, Estuary, Fivetran's HVR, or a script using pg_recvlogical connects to a replication slot and receives the decoded change stream, advancing the slot as it goes.

A single decoded change carries everything a downstream system needs to apply it: the operation (insert, update, or delete), the table, the new row values, and, for updates and deletes, enough of the old row to identify it (controlled by REPLICA IDENTITY, covered below). An update to an orders row, for example, arrives as an update event naming the table, the key that identifies the row, and the changed values, in the same commit order Postgres applied it. That ordering guarantee is what lets a consumer rebuild an exact copy of the source rather than an approximate one.

The three CDC methods in PostgreSQL

Not all CDC is log-based. Three approaches show up in practice, and the trade-offs between them matter more than any single feature comparison.

Query-based CDC

Query-based CDC polls tables on a schedule, using an updated_at timestamp or an incrementing version column to find rows that changed since the last run. It's the simplest method to build and requires no special database configuration. The problems are structural, not incidental: it can't see deletes (a deleted row simply stops appearing, with no event to mark its removal), it misses intermediate states (if a row changes three times between polls, you capture only the final value), and the repeated scans add load to the production database that grows with table size. Query-based CDC is a reasonable choice for a simple periodic sync of an append-mostly table, and a poor one for anything that needs accuracy or low latency.

Trigger-based CDC

Trigger-based CDC attaches triggers to your tables that write a record into a separate change-log table on every insert, update, and delete. It captures all operation types, including deletes, and it works on Postgres versions and managed services that don't expose logical replication. The cost is paid on the write path: every transaction now does extra work, which adds latency and write amplification to your most performance-sensitive operations. You also inherit the operational burden of maintaining the trigger logic and pruning the change-log table as schemas evolve. Trigger-based CDC suits custom audit requirements where you control the schema and the write volume is moderate.

Log-based CDC (logical replication)

Log-based CDC reads committed changes directly from the WAL through logical replication. Because it reads a log the database is already writing for durability, its impact on the source is low: there's no polling and no per-row trigger overhead. It captures every operation type in commit order and delivers changes within seconds, which is what makes real-time use cases possible. The trade-off is setup and operational care: you have to configure wal_level, slots, and publications correctly, and you have to monitor the slot so it doesn't retain WAL without bound. For most production CDC into analytics systems or other databases, log-based CDC is the right method, and it's the one the rest of this guide focuses on.

PostgreSQL CDC methods compared

Setting up logical replication: a step-by-step walkthrough

This walkthrough is conceptual: it explains each step and shows the shape of the SQL, using placeholder object names you'll replace with your own. If you'd rather work in a runnable environment end to end, follow our PostgreSQL CDC to Snowflake tutorial, which stands up a real Postgres instance and a working pipeline with Docker.

Step 1: Set wal_level to logical

Logical decoding is off by default. Enable it and confirm there's headroom for slots and WAL senders:

sql
ALTER SYSTEM SET wal_level = logical;

ALTER SYSTEM SET max_replication_slots = 10;

ALTER SYSTEM SET max_wal_senders = 10;

Changing wal_level requires a database restart to take effect. On managed Postgres this is a parameter-group change rather than an ALTER SYSTEM you run yourself. Amazon RDS, for example, uses the parameter rds.logical_replication=1 followed by a reboot, and Google Cloud SQL uses the cloudsql.logical_decoding flag. See the managed-Postgres notes in the production section below.

Step 2: Create a replication role with least privilege

CDC needs a dedicated role with the REPLICATION attribute and read access to the tables you're capturing. Don't reuse your application's superuser:

sql
CREATE ROLE flow_capture WITH REPLICATION LOGIN PASSWORD '<password>';

GRANT pg_read_all_data TO flow_capture;   -- PostgreSQL 14+

The built-in pg_read_all_data role (Postgres 14 and later) is the cleanest way to grant read access. On earlier versions, grant SELECT on the specific schemas and tables instead. Managed services add their own grant. On RDS, for instance, you instead run GRANT rds_replication TO flow_capture;.

Step 3: Make sure each table can be identified

For Postgres to emit enough information to replay an UPDATE or DELETE downstream, it has to be able to identify which row changed. That's governed by a table's REPLICA IDENTITY:

• With the default setting, Postgres uses the table's primary key. If a table has a primary key, you're done.
• A table with no primary key will not replicate updates or deletes at all unless you set REPLICA IDENTITY FULL, which logs every column of the old row.

sql
ALTER TABLE my_table REPLICA IDENTITY FULL;

REPLICA IDENTITY FULL works, but it amplifies WAL volume (every update now logs all columns), and the ALTER TABLE that changes replica identity takes a brief ACCESS EXCLUSIVE lock. Prefer a primary key where you can; reserve FULL for keyless tables you can't change.

Step 4: Create a publication

A publication defines the set of tables whose changes are published:

sql
CREATE PUBLICATION flow_publication FOR TABLE orders, customers;

You can publish all tables with FOR ALL TABLES, but be deliberate about it: any table in the publication that lacks a primary key won't be able to process updates or deletes, so a blanket publication can silently drop changes for keyless tables. If you replicate partitioned tables, set publish_via_partition_root = true so changes are published against the root table.

Step 5: Perform logical decoding through a slot

A replication slot binds a consumer to the WAL using an output plugin. Most modern tooling uses pgoutput, the built-in plugin, and creates and manages the slot for you, so you rarely create it by hand. Conceptually, this is the step where a slot is created against pgoutput and a consumer begins reading decoded changes from it. (If you're building your own pipeline, this is also where you'd choose a plugin: pgoutput for the native binary protocol, or wal2json if you specifically want JSON output.)

Step 6: Route the change stream to a consumer

This step is sometimes framed as "set up a message queue or event bus." That's one option, not a requirement. What you actually need is something that reads the slot and delivers changes to your destination. If you're assembling your own pipeline, that often means Debezium publishing into Kafka, with all the operational surface that implies. If you use a managed CDC platform, the platform connects to the slot directly and lands changes in your destination without Kafka. Either way, the slot is the contract, and what consumes it is your choice.

Step 7: Monitor and maintain the pipeline

Logical replication is not fire-and-forget. At minimum, watch replication-slot lag and WAL disk usage, set a safety net on how much WAL a slot can pin, and have a plan for schema changes and failover. Each of those is a real failure mode with a known fix, and the next section covers them.

PostgreSQL CDC setup checklist

• wal_level = logical (and a restart applied)
• max_replication_slots and max_wal_senders sized for your number of consumers
• max_slot_wal_keep_size set as a safety net (more on this below)
• A dedicated replication role with REPLICATION + least-privilege read access
• Every captured table has a primary key, or REPLICA IDENTITY FULL is set
• A publication scoped to exactly the tables you want
• Output plugin is pgoutput (the default for most tools; managed connectors that create the slot select it for you)
• Slot lag and WAL growth monitored with alerting
• Managed-Postgres prerequisites met for your provider (RDS, Aurora, Cloud SQL, Azure, Supabase, or Neon)

Common PostgreSQL CDC issues and how to avoid them

Each issue below is a real failure mode. Each starts with a short plain-English explanation, then the mechanics and the fix.

Replication slot bloat and slots that won't advance

A slot is a bookmark, and Postgres keeps every WAL segment after that bookmark until the consumer acknowledges it has read past them. If the consumer stalls, disconnects, or is reading too slowly to keep up, WAL accumulates on disk. If the disk fills, the whole database stops accepting writes.

Monitor the lag between the current WAL position and each slot's confirmed position:

sql
SELECT

 slot_name,
 
 active,
 
 pg_size_pretty(
 
   pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)
   
 ) AS retained_wal
 
FROM pg_replication_slots;

pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) returns the bytes of WAL the slot is forcing Postgres to retain. The most common silent cause of bloat is an orphaned slot: one created by a tool or experiment, then forgotten, that pins WAL forever. Drop slots you no longer use.

There's a subtler case worth knowing about. If every table you're capturing is idle while other tables on the same server are busy, the slot can't advance: nothing in your publication moves it forward, even though WAL keeps growing from the other tables. The fix is to make sure at least one captured table changes regularly, or to add a small dedicated heartbeat table that's updated every few minutes and included in the capture.

Finally, set max_slot_wal_keep_size as a safety net (a value around 50 GB is enough for many databases). It bounds how much WAL a slot can pin. Understand the trade-off, though: if a slot exceeds that limit, Postgres invalidates the slot to protect the disk, and the capture using it will fail and require a re-backfill. The safety net protects the database at the cost of the pipeline. That's the right priority, but it makes the limit a backstop rather than a substitute for monitoring.

The xmin horizon and vacuum interaction

An active replication slot, and any long-running transaction, tells Postgres not to clean up data it might still need. That's correct behavior, but it means VACUUM can stall on dead tuples it would otherwise reclaim, and the bloat that follows often shows up as "the database got slower after we turned on CDC."

A logical slot holds back catalog_xmin, which pins vacuum on the system catalogs specifically. Long-running transactions on the source are a related, broader failure mode: an open transaction holds back the xmin horizon for user tables too, blocking vacuum more widely, and it can also delay slot advancement until it commits. The two symptoms overlap and are easy to mistake for each other, so when you see unexplained bloat after enabling CDC, check both the slot health and pg_stat_activity for old transactions.

REPLICA IDENTITY and tables without a primary key

Postgres needs a way to identify which row changed. Without it, updates and deletes can't be replicated.

This is the operational side of Step 3. A table with a primary key works under the default replica identity. A table without one will fail to replicate updates and deletes until you set REPLICA IDENTITY FULL, at the cost of logging every column on every update. The trap is that this fails quietly: inserts replicate fine, so the pipeline looks healthy until someone notices that updates to a keyless table never reached the destination. Audit your captured tables for primary keys before you trust the stream.

TOAST values and the unchanged-TOAST problem

Postgres stores oversized column values (large text, JSON, arrays) out-of-line, in a mechanism called TOAST. To save space, if a large value doesn't change during an update, Postgres leaves it out of the WAL record entirely, so a naive consumer sees an empty placeholder instead of the real value.

There are two fixes. The blunt one is REPLICA IDENTITY FULL, which forces the WAL to record all values regardless of size. The better one is a consumer that recognizes the unchanged-TOAST marker and carries forward the previous value. Mature CDC tools handle this for you. Debezium does, and Estuary's connector applies merge reductions to fill in the last known value when an update omits a TOASTed column. If you're building your own consumer, this is a case you have to handle explicitly.

DDL and schema changes

Logical replication replicates data, not structure. An ALTER TABLE on the source is not carried downstream, so a column added on the source won't appear at the destination on its own, and a column dropped or retyped can break the stream.

A related source of confusion: Postgres 15 added row filters and column lists to publications, which look like schema-management features but aren't. They control which rows and columns are published, not how schema changes propagate. The durable pattern is to treat the destination schema as a contract: use a schema registry or a tool that detects source schema changes and evolves the destination accordingly, rather than assuming DDL flows through the WAL.

Failover and slot recreation

A logical replication slot lives on one primary server. If that primary fails over to a standby, the slot doesn't come with it, and your consumer loses its place in the WAL.

How you handle this depends on your Postgres version. Before Postgres 17, you needed the pg_failover_slots extension or a manual process to recreate the slot and re-snapshot after a failover. Postgres 17 added native failover slots: create the slot with pg_create_logical_replication_slot(slot_name, plugin, ..., failover => true), and pg_replication_slots gains failover and synced columns so you can confirm a slot is being synchronized to standbys (Postgres 18, the current GA release, carries this forward.).

Amazon Aurora behaves differently: its shared-storage architecture means the slot lifecycle doesn't follow the WAL-streaming-to-a-standby model at all, so the failover question there is best answered against Aurora's own documentation.

Backfill impact on the production database

Before a CDC pipeline can stream changes, it usually has to copy the existing rows first. That initial copy is the backfill, and a naive full-table read of a large, busy table can compete with production traffic.

The mitigations are straightforward once you know to apply them: read the backfill in chunks rather than one long-running query; run it against a read replica if you have one; schedule it for off-peak hours; and for very large tables, skip the historical backfill and start the stream from "now." Good CDC tools expose these controls (chunk size, per-table skip lists, and capturing from a standby) because backfill is where source impact concentrates.

Permissions and least privilege

CDC needs real privileges: the REPLICATION attribute plus read access. Handing it a superuser is the easy path, and a security problem that won't pass review.

Create a dedicated role scoped to exactly what CDC requires: the REPLICATION attribute, and read access via pg_read_all_data (Postgres 14+) or explicit SELECT grants on the captured tables. Managed providers layer their own grants on top, such as rds_replication on RDS. Scoping the role narrowly also makes the pipeline easier to audit later, when someone asks what that account can actually see.

PostgreSQL CDC on managed services (RDS, Aurora, Cloud SQL, Azure, Supabase, Neon)

Most Postgres in production today runs on a managed service, and each one gates logical replication a little differently. The common thread: on a fully managed instance you can't run ALTER SYSTEM to set wal_level, so you enable logical replication through the provider's parameter or console, almost always followed by a restart. From there the setup matches self-hosted Postgres. Here's what changes per provider.

• Amazon RDS for PostgreSQL. Set rds.logical_replication=1 in a custom parameter group and reboot, then grant the replication role with GRANT rds_replication TO <role>;.
• Amazon Aurora PostgreSQL. The same rds.logical_replication parameter, set on the cluster parameter group. Keep the failover caveat from the previous section in mind: Aurora's shared-storage architecture changes how replication slots behave across failover compared with standard streaming replication.
• Google Cloud SQL for PostgreSQL. Set the cloudsql.logical_decoding flag to on (the instance restarts), and create the replication user with the REPLICATION attribute in the cloudsqlsuperuser role. 
• Azure Database for PostgreSQL. Enable logical replication through the server parameters (set wal_level to logical) and restart. Confirm the exact parameter names against your specific Azure deployment's documentation, since they differ across Azure's offerings.
• Supabase. Logical replication is available, but the connection pooler doesn't support the replication protocol—you have to connect directly. In the Supabase dashboard, switch off the pooled connection string and use the direct host.
• Neon. Enable logical replication from the console (Project settings → Beta → Enable), which restarts all computes and drops active connections. As with Supabase, Neon requires a direct, non-pooled connection: the pooler host (the one with -pooler in it) won't carry the replication protocol. Note too that Neon automatically removes inactive replication slots when other active slots exist, so a long-paused capture can lose its slot.

A few things generalize across providers. First, your CDC tool has to be able to reach the instance, which usually means either a public endpoint with the tool's IP addresses allow-listed or a private connection through an SSH tunnel or peered network. That holds for RDS, Aurora, Cloud SQL, and Azure alike, not just one of them. Second, connection poolers and logical replication don't mix: transaction-mode poolers like PgBouncer can't carry the replication protocol, so always point CDC at a direct connection, whatever the provider. Third, a locked-down managed instance may not expose logical replication at all. When that happens, your options narrow to query- or trigger-based CDC, or a tool that offers a batch fallback for this case.

Choosing a PostgreSQL CDC tool

Once you've decided on log-based CDC, the next question is whether to build or buy, and which tool fits. The comparison comes down to four dimensions that data engineers feel in production: latency, how the tool handles schema changes, the operational burden, and the cost model.

This is a guide-level overview, not an exhaustive bench test; the right choice depends on your latency requirements, whether you want to operate streaming infrastructure yourself, and how your data volume maps to each pricing model. If you want a deeper head-to-head, Estuary maintains comparison pages such as Estuary vs. Fivetran, Airbyte vs. Estuary, and Debezium + Kafka vs. Estuary.

How Estuary handles these production issues

The production section above is tool-neutral on purpose, because those issues exist regardless of what you use. It's still worth being concrete about how a managed platform maps onto them, because "fully managed" only means something if you can point to the specific problem each capability solves. Here's how Estuary lines up against the gotchas, including where it inherits Postgres's own limitations instead of hiding them.

Setup and permissions. Estuary's PostgreSQL connector supports Postgres 10 and later, across self-hosted instances and the major managed providers. It can create the replication slot and publication for you when the role has sufficient privileges, though wal_level = logical still has to be enabled manually in most environments because it requires a restart. It runs as a dedicated replication role scoped to what CDC needs: read access to the captured tables, plus write access to a small watermarks table the connector uses to coordinate each backfill. It can also run in a read-only mode that skips those watermark writes.

Slot bloat and the idle-table case. Estuary documents the same safety net this guide recommends: setting max_slot_wal_keep_size (around 50 GB for many databases) so a stalled slot can't fill the disk. For the idle-table case, the watermarks table the connector writes to doubles as a heartbeat: those regular writes keep the slot advancing even when your captured tables are quiet, so in the default mode you don't need a separate heartbeat table. (In read-only mode, where the connector isn't writing watermarks, a dedicated heartbeat table is the recommended fallback.) The platform doesn't exempt you from how logical replication works; it makes the right configuration the default and surfaces slot health.

Destination outages. Estuary captures land in collections, which are durable, replayable streams backed by cloud storage, and materializations to your destinations read from those collections. Because slot advancement is tied to the capture and not to whether a destination is healthy, a warehouse outage or a slow load doesn't cause WAL to pile up on your primary. The same collection also lets you fan out to multiple destinations without re-reading the source, so you extract from Postgres once.

TOAST and schema changes. The connector applies merge reductions to carry forward unchanged TOASTed values automatically, so you don't have to reach for REPLICA IDENTITY FULL just to handle large columns. For DDL, Estuary infers schemas and supports automatic schema evolution: with AutoDiscover enabled it adds newly discovered fields and tables, and its evolutions feature re-versions a collection when a change would otherwise break a downstream materialization.

Backfill and source impact. Backfills run in chunks (the default fetch size is 4,096 rows per query), and you can skip the backfill for specific tables. To take CDC load off a busy primary entirely, the connector supports capturing from a read-only standby on Postgres 16+ (with hot_standby_feedback enabled).

Slot loss. If a replication slot is dropped or invalidated, the capture "will fail and require manual recovery." There's a documented recovery path: set a Minimum Backfill XID and run an incremental backfill so destination tables aren't dropped and rebuilt. This is a limitation Estuary shares with logical replication itself, rather than one it claims to have engineered away. What it does reduce is the chance of reaching that point: because slot advancement is tied to the capture rather than the destination (the collections architecture above), WAL is far less likely to build up to the size limit at which many Postgres instances drop the slot. Once a slot is gone, though, the WAL it protected can be recycled, and no tool can recover changes Postgres has already discarded.

Managed Postgres that can't do logical replication. Some managed instances don't expose logical replication at all. For those, Estuary offers a PostgreSQL batch connector that polls on a schedule. It has higher latency than CDC, but it's a working fallback when the WAL isn't available to you.

On guarantees and economics: Estuary provides exactly-once delivery to transactional destinations, sub-100 ms latency in streaming mode (or scheduled batch from the same pipeline, your choice), and usage-based pricing at $0.50/GB plus a per-connector fee. Logistics platform Shippit, for instance, reported a 45% cost reduction and moved from 15-minute syncs to real-time CDC after consolidating onto Estuary. It's SOC 2 and HIPAA compliant, with private and bring-your-own-cloud deployment options for regulated workloads.

If you want to see the full flow end to end, the PostgreSQL CDC to Snowflake tutorial walks through a working pipeline, or you can start free on up to 10 GB/month and two connectors.

Related resources

• Change Data Capture: a complete pillar guide
• CDC done correctly: best practices
• Achieving data consistency with CDC
• CDC guides for other databases: MySQL, SQL Server, MongoDB, Oracle
• Estuary's PostgreSQL connector documentation
• External: PostgreSQL logical replication docs · Debezium documentation