Publications, Subscriptions, and Replica Identity
Configure selective replication and the replica identity needed for correct UPDATE and DELETE streaming.
Why Logical Replication for Performance
Physical (streaming) replication ships the entire WAL byte-for-byte to identical replicas. Logical replication instead decodes the WAL into row-level change events (INSERT/UPDATE/DELETE) and streams only the tables you choose.
- Selective: replicate a hot subset of tables, not the whole cluster.
- Cross-version & cross-schema: publisher and subscriber can differ in major version and have extra columns or indexes.
- Performance use cases: offload reporting queries to a read replica, build a slimmer OLAP copy, or shard write traffic.
The two building blocks are a PUBLICATION on the source and a SUBSCRIPTION on the target.
Enabling Logical Decoding
Logical replication requires the WAL to carry enough information to reconstruct rows. Set wal_level = logical on the publisher (a server restart is required).
max_wal_sendersmust allow one slot per subscription plus headroom.max_replication_slotsbounds the number of logical slots.
Each subscription consumes one replication slot, which pins WAL on the publisher until the subscriber confirms it. An inactive subscriber can therefore cause unbounded WAL growth.
SHOW wal_level;
ALTER SYSTEM SET wal_level = 'logical';
ALTER SYSTEM SET max_wal_senders = 10;
ALTER SYSTEM SET max_replication_slots = 10;
-- Restart PostgreSQL, then verify
SHOW wal_level;All lessons in this course
- Publications, Subscriptions, and Replica Identity
- Offloading Read and Analytic Workloads
- Near-Zero-Downtime Major Version Upgrades
- Monitoring Replication Lag and Slot Bloat