Logical Replication Fundamentals
Logical replication decodes the write-ahead log into a stream of logical changes — inserts, updates, deletes, and truncates on specific tables — using the pgoutput plugin built into PostgreSQL since version 10, rather than shipping raw physical WAL blocks. Because it operates at the level of individual rows and table names instead of disk pages, it can replicate a subset of tables, work between databases running different (but compatible) major PostgreSQL versions, and even feed non-PostgreSQL consumers via custom decoding plugins, none of which physical streaming replication can do.
Cricket analogy: It is like a scorer's ball-by-ball text commentary feed that describes 'Kohli hits a four through covers' rather than broadcasting the raw video signal — the same event, translated into a portable, selective format anyone can subscribe to.
Publications and Subscriptions
On the source (publisher) database, CREATE PUBLICATION defines which tables (or all tables) to expose, optionally filtered to specific DML operations via WITH (publish = 'insert, update'). On the target (subscriber) database, CREATE SUBSCRIPTION connects to the publisher, performs an initial data copy of the published tables via COPY, and then continues streaming ongoing changes. Each subscription runs its own logical replication worker process, and multiple subscriptions can pull from the same or different publications, enabling fan-out or consolidation topologies that physical replication cannot express.
Cricket analogy: It is like a broadcaster setting up a dedicated regional feed (the publication) covering only certain matches, which a local cable operator (the subscription) then taps into, first replaying the full match archive before joining the live feed.
-- On the publisher database
CREATE PUBLICATION orders_pub
FOR TABLE orders, order_items
WITH (publish = 'insert, update, delete');
-- On the subscriber database
CREATE SUBSCRIPTION orders_sub
CONNECTION 'host=publisher.db.internal dbname=shop user=repl_user password=...'
PUBLICATION orders_pub
WITH (copy_data = true, create_slot = true, slot_name = 'orders_sub_slot');
-- Check status
SELECT subname, received_lsn, latest_end_lsn FROM pg_stat_subscription;Use Cases: Selective Replication and Upgrades
Because logical replication is table-selective and version-flexible, it is the standard technique for performing near-zero-downtime major version upgrades: you stand up a new cluster on the target PostgreSQL version, subscribe it to the old cluster's publications, let it catch up, then cut application traffic over once lag reaches zero. It's equally useful for feeding a subset of tables (say, just the tables a reporting team needs) into a separate analytics database without exposing the full production schema, or for consolidating changes from several regional databases into a single central warehouse.
Cricket analogy: It is like a franchise relocating its entire operations to a new stadium mid-season by first mirroring only the fixtures and squad data (not the old stadium's seating charts) to the new venue's systems, cutting over once fully synced.
Logical replication does not automatically propagate DDL (schema changes like ALTER TABLE or CREATE INDEX). You must apply schema changes manually on the subscriber, typically before the corresponding change is made on the publisher, or use tooling that coordinates this for you. Forgetting this is one of the most common causes of a broken subscription.
Conflict Handling and Limitations
Logical replication does not replicate sequences, large objects, or most DDL, and by default a subscriber applies changes without any real conflict detection — if a subscriber-side row was modified independently of the publisher, an incoming UPDATE or DELETE can fail or silently diverge depending on the situation, and PostgreSQL 16+ adds conflict logging to at least surface these. For UPDATE and DELETE to work correctly, the publisher table needs a REPLICA IDENTITY (by default the primary key); tables without a primary key must be set to REPLICA IDENTITY FULL, which replicates the entire old row for matching, at a real performance cost.
Cricket analogy: It is like two scorers independently correcting the same over's tally after a DRS overturn — without a clear protocol for whose correction wins, the two scorebooks can quietly disagree, which is why the official scorer (replica identity) is designated as the tiebreaker.
REPLICA IDENTITY FULL is required for any published table without a primary key (or a suitable unique index), but it forces PostgreSQL to include the entire old row image in every UPDATE/DELETE WAL record and requires the subscriber to do a full table scan to find matching rows if no usable index exists — this can severely degrade write throughput on wide tables. Prefer adding a primary key over relying on REPLICA IDENTITY FULL wherever possible.
- Logical replication decodes WAL into row-level insert/update/delete/truncate events via the pgoutput plugin, unlike physical streaming replication.
- It supports table-selective replication and cross-major-version replication, making it the standard tool for near-zero-downtime upgrades.
- CREATE PUBLICATION on the source and CREATE SUBSCRIPTION on the target establish the replication link, with an automatic initial data copy.
- DDL is not replicated automatically — schema changes must be applied manually and coordinated with care.
- Sequences and large objects are not replicated by logical replication and need separate handling.
- REPLICA IDENTITY (default: primary key) determines how updated/deleted rows are matched on the subscriber; tables without a primary key need REPLICA IDENTITY FULL at a performance cost.
- Conflict handling is limited by default; PostgreSQL 16+ improves conflict visibility but applications should still avoid concurrent independent writes to subscriber tables.
Practice what you learned
1. What does PostgreSQL logical replication decode WAL into?
2. Which SQL command is used on the source database to define which tables are exposed for logical replication?
3. What is a key capability logical replication has that physical streaming replication does not?
4. Why must a table without a primary key use REPLICA IDENTITY FULL for logical replication of updates and deletes?
5. What must you do when making a schema change (DDL) on a logically replicated table?
Was this page helpful?
You May Also Like
Streaming Replication Explained
Learn how PostgreSQL ships write-ahead log records from a primary to standby servers in real time, and how to configure, secure, and monitor a physical replication topology.
Partitioning Large Tables
Learn how PostgreSQL's declarative partitioning splits large tables into manageable pieces for faster queries, easier maintenance, and efficient data lifecycle management.
PostgreSQL High Availability Patterns
Learn how to design a highly available PostgreSQL deployment combining replication, automated failover, connection routing, and backups to meet real RTO/RPO targets.