Logical Replication

What is logical replication?

Logical replication is a method of copying data changes between databases as row-level operations – inserts, updates, and deletes decoded from the source's transaction log – instead of raw disk blocks. Because the stream describes rows, the target can run a different major version, hold a subset of tables, or be another system, making it the basis of log-based change data capture.

Postgres logical replication has two layers:

• Logical decoding – added in PostgreSQL 9.4, it converts write-ahead log (WAL) records, the append-only record of every change, into row changes through an output plugin such as pgoutput or wal2json.
• Publications and subscriptions – added in PostgreSQL 10, they stream selected tables from one Postgres server to another.

How logical replication works

Logical replication works by decoding committed transactions from the WAL and sending them in commit order to a consumer, which tracks its read position with a replication slot. The built-in mechanism:

1. Enable logical WAL. Setting wal_level = logical logs the extra detail needed to rebuild rows. On PostgreSQL 18 and earlier this requires a restart.
2. Create a publication. CREATE PUBLICATION orders_pub FOR TABLE orders; defines the published tables and operations. PostgreSQL 15 added row filters and column lists.
3. Create a subscription. CREATE SUBSCRIPTION on the subscriber creates a logical replication slot on the publisher by default. A slot records how far a consumer has read, and Postgres keeps all WAL past that point.
4. Copy existing rows. Table sync workers run COPY from a snapshot matching the slot's start, so no change falls between copy and stream.
5. Stream changes. A walsender process decodes WAL through pgoutput and sends each transaction after commit. The apply worker replays it.
6. Acknowledge progress. The subscriber reports the flushed log sequence number (LSN), a byte position in the WAL. The slot advances and older WAL can be recycled.

CDC tools such as Debezium replace the subscriber: they create a slot and read the decoded stream directly.

Logical replication vs physical replication

Physical replication streams raw WAL to a standby that replays it into a byte-identical copy of the cluster; logical replication streams decoded row changes for chosen tables.

When to use which: physical replication for failover and read replicas, where an exact copy is the point. Logical replication when the target differs from the source in version, scope, or engine.

Logical replication use cases

• Change data capture – CDC pipelines write the decoded stream to Snowflake, BigQuery, Redshift, or Kafka.
• Major version upgrades – a PostgreSQL 13 publisher can feed a PostgreSQL 17 subscriber, and cutover is a connection switch instead of an in-place pg_upgrade outage.
• Selective replication – one tenant's tables, or a column list that excludes PII.

Logical replication limitations

Logical replication carries row changes for published tables and little else. It does not carry:

• DDL – schema changes are applied to the subscriber separately. A column added on the publisher stops apply until the subscriber has it.
• Sequences – not replicated through PostgreSQL 18. PostgreSQL 19 adds on-demand sync with REFRESH SEQUENCES.

Other gaps:

• Replica identity is required – UPDATE and DELETE locate rows by primary key by default. A published table with neither a key nor a REPLICA IDENTITY setting rejects updates and deletes.
• Unchanged TOAST values are omitted – large out-of-line values an update did not touch are not sent unless the table uses REPLICA IDENTITY FULL.
• Conflicts halt apply – a unique violation on the subscriber stops replication until resolved.
• WAL retention – a stalled consumer pins WAL on the publisher. A slot can also lag when WAL grows from activity outside the publication.

Logical replication best practices

• Cap retained WAL. max_slot_wal_keep_size (PostgreSQL 13+) limits WAL per slot. Past it, the slot is invalidated and needs a full resync, which trades a resync for a full disk.
• Monitor lag in bytes. pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn) from pg_replication_slots gives retained WAL per slot. Alert on sustained growth, active = false, and wal_status of unreserved or lost.
• Drop unused slots. An abandoned slot retains WAL indefinitely. PostgreSQL 18 adds idle_replication_slot_timeout.
• Give every published table a primary key. REPLICA IDENTITY FULL works without one but logs the whole old row on every change.
• Apply additive DDL to the subscriber first so apply never sees an unknown column.
• Plan for failover. Before PostgreSQL 17, logical slots did not survive standby promotion. PostgreSQL 17 adds failover = true and sync_replication_slots.
• Keep idle slots moving. A periodic write to a published heartbeat table lets a slot advance on quiet databases. Artie reads through a logical slot, uses such a heartbeat table, and applies new source columns when the table's next change arrives, so a new column on a rarely written table appears downstream only after its next write.