Postgres Logical Replication Explained

Logical replication in PostgreSQL streams row-level changes — inserts, updates, deletes — from a source database to one or more targets, decoded from the write-ahead log into a portable format rather than copied as raw disk blocks. That makes it possible to replicate a subset of tables, replicate into a database running a different major Postgres version, or feed changes into an entirely different system, none of which physical replication can do.

Logical vs. physical replication

Postgres has supported physical (streaming) replication for years: a replica applies byte-for-byte copies of WAL segments, producing an exact binary copy of the primary. It’s simple and fast, but rigid — the replica must run the same major version, and you replicate the entire cluster or nothing.

Logical replication works one layer up. A background process reads the WAL, decodes each change into a stream of logical operations (INSERT INTO orders VALUES (...), effectively), and applies those operations on the target. Because the unit of replication is a row change rather than a disk block, the two sides don’t need identical binary layouts.

	Physical replication	Logical replication
Unit replicated	WAL bytes / disk blocks	Row-level changes
Granularity	Entire cluster	Selected tables/publications
Cross-version	Same major version only	Can differ across versions
Target can differ from source	No (must be Postgres, same layout)	Can write to a different schema or system
Typical use	High-availability standby	Selective sync, CDC, zero-downtime upgrades
Failover-ready replica	Yes	Not automatically

Publications and subscriptions

Logical replication is built from two objects. A publication, created on the source with CREATE PUBLICATION, names which tables’ changes should be exposed. A subscription, created on the target with CREATE SUBSCRIPTION, connects to that publication and starts applying its changes. One publication can feed multiple subscribers, and a single database can subscribe to publications from multiple sources — useful for consolidating data from several services into one analytical database.

Because replication is scoped to specific tables rather than the whole cluster, you can, for example, replicate just an orders table out of a much larger production database into a reporting instance, without shipping every other table along with it.

What it enables

• Selective replication. Send only the tables a consumer actually needs, reducing storage and exposure on the target.
• Cross-version and cross-platform upgrades. Because logical replication doesn’t require matching binary layouts, it’s the standard technique for near-zero-downtime major-version upgrades: stand up a new-version target, let it catch up via logical replication, then cut traffic over.
• Change data capture. Downstream consumers — a search index, a cache, an event stream — can subscribe to row-level changes as they happen rather than polling. This is the same problem change data capture tools generally solve; Postgres’s logical decoding is one of the mechanisms CDC pipelines build on.
• Multi-directional and selective merging. Because the source of truth for a subscription is a publication, not a raw binary stream, it’s feasible (with care around conflict handling) to replicate between peers rather than only from a single primary.

The limits

Logical replication doesn’t replicate everything by default. DDL changes (ALTER TABLE, new indexes) aren’t replicated automatically — schema changes generally need to be applied on both sides. Sequences aren’t replicated either, which matters if the target ever needs to take over writes. And a table normally needs a primary key (or a configured replica identity) so that updates and deletes can be matched to the correct row on the target.

Replication lag is also a real concern, just as it is with physical replication — a subscriber that falls behind on applying changes will serve stale data until it catches up. See what causes replication lag for the general mechanics, and synchronous vs. asynchronous replication for how the consistency guarantees differ; Postgres logical replication is asynchronous by default, with synchronous behavior available as an option.

There’s also an operational cost to keep in mind: each publication relies on a replication slot on the source, which retains WAL segments until every subscriber has confirmed it has consumed them. A subscriber that goes offline for an extended period doesn’t just fall behind — it can cause WAL to accumulate on the source until disk space becomes a problem, so monitoring slot lag is as important as monitoring the subscription itself.

Logical replication in a migration

A common pattern for reducing downtime during a major-version upgrade or a cross-provider migration:

1. Stand up the target database on the new version or provider.
2. Create a publication on the source covering the tables to migrate, and a subscription on the target.
3. Let the subscription perform its initial data copy and then continue applying live changes.
4. Once the target is caught up, briefly pause writes on the source, confirm the subscriber has applied everything, and cut application traffic to the target.

This shrinks the downtime window from “however long a full dump-and-restore takes” to “however long it takes to confirm the last few changes have replicated” — often seconds. It’s a different tool than the schema evolution handled by database migrations, which change structure rather than move data between instances, and different again from sharding, which splits data across instances rather than duplicating it.

The takeaway

Physical replication copies bytes; logical replication copies meaning. By decoding the write-ahead log into row-level changes instead of streaming raw blocks, Postgres logical replication enables selective table sync, cross-version upgrades, and change-data-capture pipelines that physical replication can’t touch — at the cost of needing to manage schema changes, sequences, and replica identity yourself.