The Logical Replication Model

Logical replication takes the same write-ahead log that physical streaming replication ships byte-for-byte and instead decodes it into a stream of row-level changes: inserts, updates, and deletes on specific tables.

That single shift, from bytes to rows, is what lets logical replication do things physical replication structurally cannot, like replicating a subset of tables or moving data between different major versions of PostgreSQL.

This page builds the mental model behind publications, subscriptions, and logical decoding so the more procedural pages in this section make sense as applications of one underlying idea rather than a list of unrelated features.

Summary

• Core Idea: Logical replication decodes WAL into row-level changes and applies them on a subscriber, rather than replicating the database byte-for-byte.
• Why It Matters: It enables selective table sync, cross-version upgrades, and feeding downstream systems without cloning an entire cluster.
• Key Concepts: logical decoding, publication, subscription, replica identity, logical replication slot, apply worker.
• When to Use: Major version upgrades, replicating a subset of tables, feeding analytics or search systems, and multi-database consolidation.
• Limitations / Trade-offs: DDL does not replicate automatically, and the model adds decoding overhead absent from physical streaming.
• Related Topics: logical vs physical replication, publications and subscriptions, upgrading with logical replication.

Foundations

PostgreSQL's write-ahead log records changes at a low level, close to the physical page layout, which is exactly what physical replication wants but not directly usable as "row X changed to this value."

Logical decoding is the process that reads that low-level WAL and reconstructs it into a readable stream of logical changes, using a decoding plugin to translate physical WAL back into row-level operations.

A publication, created on the source database, names the set of tables whose changes should be exposed through this decoding process, acting as a filter over what the rest of the mechanism ever sees.

A subscription, created on the destination database, points at a publication, opens a replication connection, and continuously applies the changes it receives through a dedicated apply worker process.

Think of physical replication as photocopying every page of a ledger, while logical replication reads the ledger aloud and dictates only the transactions someone chose to listen for.

That dictation model is why logical replication can connect databases that are not identical, since the subscriber only needs matching tables and columns for what it subscribes to, not an identical cluster.

Getting a subscription running for the first time also requires an initial sync, a bulk copy of existing rows before ongoing changes start applying, so a new subscriber does not start from an empty table and only see changes from that point forward.

-- Publisher: expose two tables to any subscriber that connects
CREATE PUBLICATION app_pub FOR TABLE orders, order_items;

That one statement is the entire mental model in miniature: name the tables, and everything downstream is about who listens and how they catch up.

Mechanics & Interactions

The decoding step depends on replica identity, a per-table setting that tells PostgreSQL which columns to include in the WAL for updates and deletes so the subscriber can match the correct row.

By default, replica identity uses the table's primary key, which is enough information to identify a unique row on the subscriber side without shipping every column on every change.

A table with no primary key needs REPLICA IDENTITY FULL instead, which includes the entire old row in the WAL so deletes and updates can still be matched, at the cost of significantly larger WAL volume for that table.

Each active subscription is backed by a logical replication slot on the publisher, conceptually similar to the physical slots streaming replication uses, retaining WAL until the subscriber confirms it has applied a given point.

That retention has the same double edge as physical slots: it protects a temporarily disconnected subscriber from losing changes, and it can also grow unbounded WAL on the publisher if a subscriber disappears and nobody notices.

The sharpest edge of the entire model is that DDL does not replicate, meaning a CREATE TABLE, ALTER TABLE, or index change made on the publisher has no automatic counterpart on the subscriber.

This is a deliberate design choice rather than an oversight, since applying arbitrary schema changes automatically on a subscriber that might have a different schema, extensions, or downstream consumers would be far more dangerous than requiring an explicit step.

In practice, teams handle this with expand-contract migration discipline: apply compatible schema changes to both sides in a coordinated order, using tooling rather than relying on replication to propagate structure.

Conflicts are the other mechanic worth internalizing, since an apply worker that hits a constraint violation, such as a duplicate key from a row that already exists on the subscriber, simply stops applying changes until the conflict is resolved by an operator.

That fail-stop behavior is intentional: silently skipping or overwriting conflicting data would make logical replication unpredictable, so PostgreSQL instead surfaces the problem loudly and pauses.

Advanced Considerations & Applications

At scale, logical replication's row-level granularity becomes both its biggest asset and its biggest operational surface, since every table you add to a publication is a table whose replica identity, initial sync cost, and conflict behavior you now own.

Major version upgrades are the model's signature use case, since a logical subscriber can run a newer PostgreSQL major version than its publisher, which turns what used to require downtime into a blue-green cutover: stand up a subscriber on the new version, let it catch up, then redirect traffic.

Selective replication also enables architectural patterns physical replication cannot, such as consolidating several source databases into one analytics warehouse, or fanning a single source out to multiple downstream consumers with different table subsets.

Change data capture tools like Debezium occupy adjacent territory, reading the same logical decoding stream but transforming and routing it to non-PostgreSQL destinations like Kafka, which makes them a natural extension of this model rather than a competing one.

Sequences remain a persistent gap in the model, since sequence values are not part of row-level DML and historically required manual synchronization after a cutover, an area where recent PostgreSQL versions have added targeted support.

The pattern that holds up in production is treating logical replication as a precise, table-scoped sync primitive, reaching for physical streaming when the goal is a whole-cluster standby and reserving logical replication for when selectivity or version flexibility is the actual requirement.

Common Misconceptions

• "Logical replication is just a slower version of streaming replication." It solves a different problem entirely, trading whole-cluster fidelity for table-level selectivity and cross-version compatibility that physical streaming cannot provide.
• "Schema changes propagate automatically like data changes do." DDL is deliberately excluded from the model, and schema changes must be applied to publisher and subscriber through a coordinated, explicit process.
• "A subscription is a backup." It is a live sync mechanism vulnerable to the same accidental deletes and corruption as the source, and it does not replace point-in-time recovery from WAL archives.
• "Any table can be published without changes." A table with no primary key needs REPLICA IDENTITY FULL before updates and deletes will replicate correctly, and skipping this step silently breaks those operations.
• "Conflicts resolve themselves." An apply worker halts on a conflict rather than guessing at resolution, which means unmonitored subscriptions can silently stop applying changes for long periods.

FAQs

Related

• Logical Replication Basics - hands-on publication and subscription setup
• Publications & Subscriptions - filtering tables, rows, and columns
• Logical vs Physical - a practical decision guide between the two models
• Upgrade with Logical Replication - blue-green major version cutovers
• Streaming Replication Explained - the physical replication model this contrasts with

Stack versions: This page was written for PostgreSQL 18.4 (stable major 18, maintenance line 17 also supported).