Postgres Logical Replication Internals for Data Engineers

Postgres logical replication gives change data capture pipelines something that polling and trigger-based approaches cannot match: a complete, ordered, low-latency stream of committed row changes, read straight from the write-ahead log, with no added write cost to the application's own tables. The write-ahead log already exists to support crash recovery, so logical replication just rides on infrastructure Postgres keeps anyway, and it needs no second write path. Timestamp-based polling misses hard deletes, and it needs a watermark column that has to stay reliable under load; trigger-based CDC does catch deletes, but you pay for it with a schema object on every watched table and a write on every transaction. Logical replication avoids both costs, requiring only that wal_level be set to logical. In exchange, it asks a team to understand and correctly operate four separate primitives: WAL configuration, publications, replication slots, and replica identity. Each one has its own failure mode, and each will surface in production if it isn't handled deliberately. The sections that follow take them in turn.

The WAL as a change stream: what wal_level=logical changes

Every Postgres installation writes a write-ahead log, but the amount of detail it records depends on the wal_level setting, and that setting is the single prerequisite the rest of the CDC stack depends on. At the minimal level the WAL holds just enough to recover from a crash, which is not relevant to CDC work and can be set aside. At replica, the default since Postgres 10, the WAL carries enough information for physical standby replication: it records what changed at the page level, which is sufficient for a byte-for-byte copy of the database but not for reconstructing individual row values. Logical adds row-level column values and tuple images, so a decoding plugin can work out which rows changed and what their old and new values were.

That distinction matters because without it, there's no way for a downstream consumer to answer the basic question a CDC pipeline exists to answer. A page-level diff tells a physical standby how to reproduce the exact bytes on disk. It tells nothing to a consumer that needs to know a specific order row moved from "pending" to "shipped." Setting wal_level to logical is what makes that row-level reconstruction possible, and it comes with a real cost: changing it requires a full server restart. A reload isn't enough: this is one setting a team has to get right before building anything on top of it, not something adjusted later without downtime.

Once the WAL is recording at the logical level, a WAL sender process reads it, hands it to an output plugin, and streams the decoded row events to a consumer over the standard Postgres wire protocol via TCP. The default output plugin is pgoutput, which speaks a compact binary format and ships with Postgres itself, so native logical replication needs no extension. An alternative, wal2json, emits plain JSON instead, which is far easier to parse from most programming languages, but it's a separate extension that has to be installed on the server (most managed providers include it already). On managed Postgres, none of this is set by editing postgresql.conf directly. Amazon RDS exposes it through a parameter group, Azure Database for PostgreSQL through a server parameter, and Cloud SQL through a database flag, and each of those interfaces is where the restart requirement actually gets triggered.

Publications: how Postgres decides what enters the change stream

A publication is where a Postgres administrator declares what actually belongs in the change stream, and its defaults are narrower than most engineers expect walking in. A statement like CREATE PUBLICATION my_pub FOR TABLE orders; names a specific set of tables and the operations to capture on them, which can include INSERT, UPDATE, DELETE, and TRUNCATE. TRUNCATE is a relatively recent addition: it arrived in Postgres 11, so a publication running against an older server simply will not emit TRUNCATE events at all, regardless of how it's configured.

A publication's scope has firm edges. Unlogged tables are excluded entirely, because they bypass the WAL by design, so if there's no log entry to read, logical decoding has nothing to capture. Large objects and TOAST-stored values need care too: when an UPDATE touches only a TOAST column while the regular columns stay the same, the resulting event may not carry the values a consumer expects, since Postgres can choose not to re-log an unchanged TOAST value.

The exclusion that surprises engineers most is schema change. A publication carries row-level data manipulation only. Statements like ALTER TABLE or CREATE TABLE are filtered out by the logical decoding layer before a consumer ever sees them, so a pipeline built on the assumption that "the stream has everything" will be wrong the first time someone adds a column. A publication defines capture scope for data, not structure, and a replication slot (the subject of a later section) tracks how far a given consumer has read through that scope; that gap around schema change is covered later in this piece.

Replica identity: what UPDATE and DELETE events contain

Publications settle which tables and operations enter the stream; replica identity settles what's actually inside each UPDATE and DELETE event once it's there. Postgres needs some way to tell a consumer which row was changed, and replica identity is the per-table setting that decides what identifying information gets attached to that event. By default, Postgres uses the primary key: an UPDATE or DELETE event carries the old values of primary key columns only, nothing more. If a published table lacks both a primary key and a unique index, Postgres has no identifier to fall back on, and UPDATE and DELETE events fail outright with a blunt error on the publisher side.

For tables where downstream consumers need the full previous state of a row, not just its key, REPLICA IDENTITY FULL changes the picture: it logs the entire previous row image on every UPDATE and DELETE, so old values for every column are available to whatever reads the stream. That completeness comes at a cost. Picture a wide table, dozens of columns, under heavy update traffic: with the default setting, each change logs only the key columns needed to identify the row, but under FULL, Postgres logs the whole row every time, every column, every update. At high change rates, that difference in what gets written per row compounds directly into how much WAL the primary has to generate and store, which becomes a real I/O consideration long before anyone notices it in a dashboard. The practical guidance holds up well across most schemas: give tables primary keys wherever possible, and reserve REPLICA IDENTITY FULL for the genuine exceptions that lack a unique key. Both settings, default and FULL alike, feed directly into total WAL volume, and that volume determines how much pressure builds behind a replication slot that falls behind. That pressure is where the next primitive comes in.

Replication slots: the fourth primitive that is also the dominant failure mode

A replication slot exists to make one guarantee: Postgres will not recycle WAL that a consumer hasn't read yet. That guarantee is what makes logical replication reliable, since a consumer can disconnect, crash, or lag for a while and still pick up exactly where it left off. The same guarantee, inverted, is what makes a stalled slot dangerous. A slot that stops advancing doesn't just sit idle, it actively holds WAL in place, and Postgres keeps retaining that WAL for as long as the slot exists and no consumer confirms progress past it.

Each slot tracks two positions: restart_lsn, the oldest WAL position the slot still needs, and confirmed_flush_lsn, the most recent position the consumer has acknowledged processing. Postgres retains everything from restart_lsn forward until that consumer advances, and crucially, an inactive slot with nobody reading from it is not cleaned up on its own. This produces a blast-radius effect across a whole instance: each slot retains WAL independently, so if one slot among several falls behind, that single lagging slot sets the retention floor for the entire system. The other, healthy slots gain nothing, because WAL behind the laggard can't be freed regardless of how current they are.

In practice, this is how teams most often damage their own production Postgres instance when they set up CDC. A proof-of-concept consumer gets spun up, creates a slot, and later gets torn down without anyone dropping the slot it left behind. WAL accumulates quietly from that point forward, and days later, the disk is full. Treating slot health as an ongoing metric rather than a one-time setup check is the defense against this: a query such as SELECT slot_name, active, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal FROM pg_replication_slots; shows how much WAL each slot is pinning, and that figure belongs on a standing dashboard, not a setup checklist that gets checked off once and forgotten. On top of that, Postgres 13 introduced max_slot_wal_keep_size, and it caps how much WAL a lagging slot can pin. Once the gap between a slot's restart_lsn and the current WAL position exceeds that limit, Postgres invalidates the slot and reclaims the older segments. Invalidation forces a full re-snapshot on that consumer, which costs time and effort but is fully recoverable. Disk exhaustion is not. Managed providers each cap the number of slots available (parameter groups on RDS, server parameters on Azure, database flags on Cloud SQL), and those limits scale with instance size rather than following one fixed number, so they're worth checking against the specific provider before several CDC tools each try to claim their own slot. Confirming a change too early risks losing it if the consumer then fails, and confirming it too late pins WAL unnecessarily. Advancing confirmed_flush_lsn only after a change has been durably processed is the pattern that keeps both risks in check.

The initial snapshot boundary: where CDC pipelines most often misalign

Every CDC pipeline needs two things at startup: a snapshot of existing data and a stream of changes going forward. Running them separately is the easy part. They must share a precise, consistent boundary: it has to leave no gap where a row is missed and no overlap where both paths capture the same row.

A replication slot can only serve changes within the WAL range it retains, and it has no way to reconstruct data that existed before it was created. That means the initial full-table scan has to be bounded at exactly the same LSN where the slot begins streaming: the snapshot owns everything up to that point, and the slot owns everything after it. A native Postgres subscription handles this coordination automatically. It copies a consistent snapshot of the published tables, applies any changes that occurred while that copy was running, and then hands control over to the ongoing apply process for everything that happens afterward. A custom consumer built directly against START_REPLICATION has to set up that coordination itself. It has to create the slot before exporting the snapshot, so the slot's starting position is locked in ahead of the scan and no committed transaction can slip through the gap between the two steps.

Postgres 14 added transaction streaming, where a large in-progress transaction can be broken into multiple Stream Start and Stream Stop segments before a final Stream Commit or Stream Abort arrives. If a consumer applies each segment as it arrives, instead of waiting for confirmation that the transaction actually committed, it risks applying changes from a transaction that later aborts. You need to group the segments by transaction ID and hold them until commit is confirmed.

DDL exclusion and schema evolution: the gap that logical replication leaves open

Logical replication carries row-level data changes and nothing else. No CREATE TABLE, ALTER TABLE, DROP COLUMN, or type change ever flows through a publication, because the logical decoding layer strips all of it out before a consumer sees the stream. If the consuming side isn't prepared to handle a schema change on the source, it will break the pipeline outright, or worse, corrupt it silently by applying mismatched data to a changed structure.

Even Postgres's own native subscribers don't close this gap automatically. Adding a column to a published table on the publisher does not make that column appear on the subscriber. It has to be added on both sides by hand, and depending on the setup, the publication itself may need to be refreshed before replication picks up the change. Tools that move CDC data into a warehouse each handle DDL differently, and nothing about one tool's behavior can be assumed to carry over to another. The operations that most commonly break a running pipeline are familiar ones: dropping a column, changing a column's data type, adding a NOT NULL constraint, renaming a table. Each looks routine from the application side and each can stop a CDC pipeline cold.

Because the decoding layer itself offers no protection here, schema evolution has to be treated as a design requirement for any pipeline built on logical replication, not a problem to patch after the first outage. Either the tooling a team picks handles schema drift automatically, or the team has to build its own coordination layer to catch changes and apply them on both sides in lockstep. DDL handling varies so much from one tool to the next that a team evaluating options should treat a native Postgres subscriber's behavior and a third-party tool's behavior as two separate questions, never as interchangeable.

High-availability coupling: how failover disrupts a running CDC slot

A replication slot lives on the primary. A replication slot is not, by default, a property of the cluster as a whole. A failover that promotes a standby to primary can leave the CDC pipeline stranded unless someone has explicitly configured the slot to survive that transition. Physical WAL streaming keeps a standby's data pages in sync with the primary, but in older Postgres versions it does nothing to replicate the logical slot's own state, restart_lsn and confirmed_flush_lsn, to that standby. Without that synchronization, a promoted standby simply has no record of where the CDC consumer left off, and the consumer has no option but to start over with a full re-snapshot.

Postgres 17 addressed this directly with logical replication failover slots, which allow slot state to be synchronized to promotion candidates so a failover doesn't necessarily mean starting from zero. The safeguard built into that feature is specific: a standby only becomes eligible to carry the slot after the subscriber has actually advanced that slot at least once while the standby was receiving its metadata. That requirement exists to stop Postgres from promoting a node that has never actually observed real progress on the slot and would hand the subscriber an inconsistent view of the stream. In practice, this means that if a CDC client hasn't connected in several hours, it can leave a freshly added or recently restarted standby ineligible to take over the slot at exactly the moment a failover happens.

Setting this up takes configuration on both sides of the cluster. On the primary, the slot must be created with failover=true passed to pg_create_logical_replication_slot(), and synchronized_standby_slots must name the physical slot connecting the primary to its standby. On the standby, you need to set sync_replication_slots to on, and that starts a background worker that keeps the logical slot's state synchronized from the primary automatically. If you skip either side of that configuration, the guarantee a replication slot is supposed to provide, that a consumer can always resume where it left off, stops holding the moment a failover actually happens.