Postgres Publication and Replication Identity Configuration

Postgres logical replication for change data capture is built entirely on the write-ahead log, the same durability mechanism Postgres already uses for crash recovery. Configuring it correctly comes down to two decisions: what a publication exposes, and what replica identity preserves. If you get either one wrong, UPDATE and DELETE events silently break, WAL bloats past what the disk can hold, or replication slots strand themselves during a failover.

What Postgres logical replication does with the WAL

Every Postgres database writes a write-ahead log before it commits a change to disk, so it can recover cleanly after a crash. Physical replication takes that log and copies its raw bytes to a standby, producing a byte-identical copy of the cluster. Logical replication takes a different path: it decodes those same WAL bytes into row-level insert, update, and delete events, and lets a consumer filter them by table. That decoded layer, not the raw WAL stream, is what any CDC tool actually reads.

A complete logical replication setup rests on four pieces working together: the publication, which sets the capture scope; the log sequence number (LSN), which marks the position of each change in the stream; the replication slot, which saves how far a consumer has read; and replica identity, which determines what old-row values an UPDATE or DELETE can carry. The rest of this piece works through each of these in turn, starting with the publication, since it is the first and most basic scoping decision a team makes.

What a publication controls

A publication is an explicit, named declaration of which tables Postgres will decode changes for. It draws the line between what a CDC pipeline can see and what stays invisible to it, and getting that scope wrong produces one of two failures: tables silently missing from the stream, or tables the downstream system was never meant to receive, adding load for no benefit.

Postgres gives two basic patterns for defining one. FOR ALL TABLES captures every current and future table in the database, which is simple to set up but tends to raise WAL volume and exposes tables that a downstream consumer has no business receiving. The alternative is an explicit list, written as FOR TABLE public.orders, public.customers, which scopes capture to exactly the tables a pipeline needs. Later versions of Postgres added a third option, FOR TABLES IN SCHEMA public, and it captures an entire schema without naming each table one by one.

Production systems rarely define a publication once and leave it alone; tables get added as the schema grows, and Postgres supports that directly through ALTER PUBLICATION mypub ADD TABLE new_table. That single statement is the standard way to extend an existing publication without tearing down and recreating the whole capture configuration.

The publication's name has to match what the CDC tool expects. A mismatch doesn't fail quietly: Postgres raises an explicit error such as "publication does not exist," and the connector stops or retries on it. Publications also have to be created on the primary server. Even when a CDC tool reads from a read replica, the publication must exist upstream on the primary, and the replica picks it up automatically from there. Polytomic's setup documentation flags this as a common trap: operators create the publication on the replica they're reading from, find nothing happens, and lose time tracing back to the primary.

Permissions split in a way you should know before you set up a dedicated CDC role. A user must own a table to add it to a publication. After that, any user holding the REPLICATION property can consume the publication's stream, regardless of whether they own the underlying tables.

What a publication does not do is control how much of each changed row makes it into the decoded event. Scoping a table into a publication only makes its changes visible. What those change events actually contain, and whether you can even use them, comes down to replica identity.

Why replica identity determines whether UPDATE and DELETE events are usable

Replica identity sets which columns Postgres writes into the "old tuple" portion of the WAL record whenever a row is updated or deleted. Scoping a table into a publication correctly is necessary, but not sufficient: if replica identity is set wrong, the events coming out of that table can be structurally incomplete, often without any error to flag it, in ways a downstream consumer has no way to repair after the fact.

Postgres offers four modes. DEFAULT logs only the primary key columns in the old tuple, which is enough for most consumers that use the primary key to locate and apply the change on the receiving end, and it carries the lowest WAL overhead of the four. INDEX uses a specific unique index in place of the primary key, which helps when a table has a natural unique key but it isn't declared as the primary key. FULL logs every column value of the old row on every update and delete, which is the only workable option for tables that have no primary key or usable unique index, but it comes at a real cost: on a wide table, logging the entire old row on every change can double or triple the WAL volume that same table would generate under DEFAULT. FULL is the correct tradeoff for keyless tables, and the cost should be weighed against table width and write volume. NOTHING logs no old-row information at all, so INSERT events still work, but UPDATE and DELETE events carry nothing you can use to identify which row changed, so you can't apply them downstream.

The failure mode for a table with no primary key goes further than just producing incomplete events. Postgres will outright block UPDATE and DELETE operations on a table added to a publication that replicates those operations, if that table's replica identity is NOTHING, or DEFAULT without an actual primary key, or INDEX pointing at an index that's since been dropped. That turns a configuration oversight into an operational emergency: writes to the table stop working entirely, not just the replication of those writes.

The fix depends on the table. Teams facing a table without a primary key have three paths: set REPLICA IDENTITY FULL, create a unique index and set REPLICA IDENTITY INDEX against it, or add a proper primary key. Which one makes sense depends on how wide the table is and how heavily it's written to.

A separate trap involves TOAST storage, which Postgres uses to hold oversized values like large text fields or JSONB columns outside the main row. When a TOASTed column is unchanged in an UPDATE and isn't part of the replica identity, it drops out of the event. A consumer can't safely go fetch that value after the fact either, because the row may have changed again in the time between the event firing and the fetch running.

Checking a table's current setting is a single query: SELECT relreplident FROM pg_class WHERE oid = 'mytable'::regclass. The result comes back as one of four letters, d, n, f, or i, mapping to DEFAULT, NOTHING, FULL, and INDEX respectively. Running that query against every table in a publication before go-live catches the kind of silent misconfiguration that otherwise becomes a production incident.

Replication slots and consumer progress

A proof-of-concept CDC consumer gets torn down after a demo, its replication slot gets forgotten, and the disk fills up over the following days until Postgres refuses to accept writes. That sequence is the single most common way teams break their own database while setting up CDC, and it follows directly from what a replication slot guarantees.

A slot's job is to tell Postgres: don't recycle WAL past this point, because a consumer still needs it. That guarantee is unconditional. Postgres has no built-in sense of whether a slot is being actively read or has been abandoned for a week; it keeps retaining WAL either way, without limit, until the disk runs out.

The retained WAL per slot is visible with one query: SELECT slot_name, active, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal FROM pg_replication_slots;. That query belongs running on a schedule as a standing operational metric, not as a one-time check during initial setup.

Two server parameters bound the risk. Set max_slot_wal_keep_size above 4 GB to start, then tune it from there against replication lag, the database's actual write rate, and available disk space: it caps how much WAL a single slot can force Postgres to retain, but if its consumer falls too far behind, you invalidate that slot. max_replication_slots sets a hard ceiling on the total number of slots the server will allow. If each consuming tool claims its own slot, you can quietly run into that ceiling across several tools, and managed Postgres providers typically ship the same default limit of 10 as self-hosted instances, though some impose further restrictions on how adjustable that limit is.

Two more gaps apply regardless of how carefully slots are managed. The replication connection needs a persistent, session-pinned connection, so routing it through a transaction pooler breaks the slot's position tracking, since the pooler rotates connections underneath it. And unlogged tables bypass WAL entirely, so they stay invisible to CDC no matter how the slots are configured, the same way TOAST-only changes on a table with the wrong replica identity never make it into the decoded stream.

Logical replication slots and failover

Before PostgreSQL 17, failover and logical replication did not coexist well. If the primary failed and a standby got promoted, the new primary had no record of the logical replication slot that had only ever existed on the old one. Rebuilding the CDC pipeline meant reconstructing that slot and its consumer state by hand, often while the pipeline sat dark.

PostgreSQL 17 closed that gap with native failover slot synchronization. A slot marked with the failover option, combined with sync_replication_slots = on on each standby and synchronized_standby_slots on the primary, causes a slotsync worker on the standby to periodically pull slot metadata from the primary and keep a mirrored copy ready. The parameter logical_slot_sync_timeout, 300 seconds by default, bounds how long a failover will wait for that sync to finish. A CDC client that sleeps for long stretches between reads matters here: if the timeout expires before the standby's copy catches up, failover can proceed anyway, and the slot is lost in the process.

None of this exists on PostgreSQL versions before 17. Teams running older versions need either a manual runbook for reconstructing slots after a promotion, or a managed CDC service built to handle that reconstruction for them. And even on 17 and later, enabling the failover flag is not a complete guarantee by itself: the standby has to have actually synced the slot's current position before the failover happens, or the same loss scenario plays out despite the feature being turned on.

DDL changes and schema evolution are outside what publications and replica identity handle

Neither of the two configuration decisions covered so far touches schema changes. Native Postgres logical replication does not replicate DDL at all: adding a column to a table that's already in a publication does not flow to any subscriber automatically, because the publication mechanism has no built-in way to announce that the schema underneath it has changed.

For native Postgres-to-Postgres subscribers, the usual workaround is to add the column on both sides and refresh the publication. That workaround does not carry over cleanly into CDC tool setups, where how a given tool detects and handles DDL changes is specific to that tool and has to be checked against its own documented behavior.

This is the ongoing risk in a production CDC deployment, more than the initial configuration itself. Setting up a publication and replica identity correctly is a one-time exercise. Schema drift between what the source database looks like today and what the downstream system still expects is continuous, and every new migration on the source side reopens the question.

Exactly-once delivery guarantees at the CDC layer don't make this problem go away. An event can arrive exactly once, correctly ordered, with no duplication, and still carry a row shape the downstream table doesn't expect, either corrupting what lands there or getting silently discarded depending on how the consumer handles the mismatch. Delivery guarantees and schema correctness are separate problems, and solving one says nothing about the other.

What production-grade CDC requires beyond Postgres configuration

A correctly scoped publication paired with the right replica identity on every table gets changes into the WAL in a form that's structurally usable. That is the foundation, not the finish line. Whether those changes actually reach their destination accurately, continuously, and over the long run depends on everything downstream of that foundation: slot lifecycle, consumer reliability, schema evolution, and failover continuity.

Slot lifecycle needs an owner. Someone has to be responsible for watching retained WAL per slot, cleaning up slots left behind by decommissioned consumers, and keeping the total slot count under the configured ceiling. In most organizations that runs into, that responsibility belongs to nobody in particular, which is exactly how disk-filling incidents happen in the first place: not from a single bad configuration choice, but from a slot nobody was watching.

Schema evolution needs a plan that exists independently of the publication and replica identity settings, because neither of those mechanisms can see DDL. Failover continuity, since PostgreSQL 17, has a native answer in the form of slot synchronization, but it depends on configuration being applied correctly across the primary and every standby, and on sync completing before a failover happens, not just on the feature being turned on. None of these requirements appear in a CREATE PUBLICATION statement or a relreplident check. These requirements become visible only in production, over months, where a proof-of-concept CDC setup and a reliable one stop looking the same.