Logical replication is a form of database replication that transmits data changes as row-level operations, such as insert, update and delete on specific tables, instead of copying the physical storage blocks of the whole database. The receiving server applies those operations through its own SQL engine, so it can run a different major version, a different storage layout or even a different schema.
Logical replication is the counterpart of physical or streaming replication, which ships write-ahead log records byte for byte and produces an identical copy of the entire cluster. The logical approach is selective and flexible, which makes it the usual tool for upgrades, migrations, data distribution and change data capture.
How logical replication works
The source database still records every change in its transaction log, the write-ahead log in PostgreSQL or the binary log in MySQL. For logical replication, a decoding step reads that log and turns low-level records into a stream of committed row changes with table names, column values and transaction boundaries.
In PostgreSQL the model uses publications and subscriptions. A publication on the source names the tables and operations to share; a subscription on the target connects to it, performs an optional initial data copy and then streams changes from a replication slot. The slot remembers how far the subscriber has confirmed, so the source keeps the log segments the subscriber still needs.
The subscriber applies changes in commit order, one transaction at a time by default, and identifies the rows to update or delete through the table’s replica identity, normally its primary key. The table below lists the main building blocks in PostgreSQL and MySQL terms.
| Component | Role | PostgreSQL | MySQL |
|---|---|---|---|
| Change source | Ordered record of committed changes | Write-ahead log with wal_level logical | Binary log in ROW format |
| Decoding | Turns log records into row changes | Logical decoding output plugin | Row events in the binary log |
| Selection | Chooses what to replicate | Publication | Replication filters |
| Progress tracking | Remembers the subscriber position | Replication slot | GTID or binary log position |
| Apply process | Executes changes on the target | Subscription apply worker | Replica SQL threads |
Logical versus physical replication
The two approaches answer different needs. Physical replication creates an exact standby suitable for failover; logical replication creates a selective, writable copy of chosen data that is not bound to the source’s physical format.
| Aspect | Logical replication | Physical replication |
|---|---|---|
| Unit of transfer | Row changes per table | Log records for data blocks |
| Scope | Selected tables or rows | Entire database cluster |
| Version requirements | Cross-version possible | Same major version and platform |
| Target writability | Target accepts its own writes | Standby is read-only |
| Schema changes | Not replicated automatically in PostgreSQL | Replicated as part of the log |
| Sequences and large objects | Limited or no support | Fully replicated |
| Typical use | Upgrades, integration, CDC | High availability, read replicas |
Logical replication is usually asynchronous, so the subscriber trails the source by a measurable replication lag. PostgreSQL can make a logical subscriber synchronous, but that is rarely used because the apply path is slower than physical replay.
Typical uses of logical replication
Because it decouples source and target, logical replication solves problems that physical copies cannot. The most valuable is the near-zero-downtime major version upgrade: a new server is built on the target version, synchronized through logical replication and promoted when lag reaches zero.
| Scenario | How it is used | Benefit |
|---|---|---|
| Major version upgrade | Replicate old version to new, then switch | Downtime of seconds to minutes |
| Cloud or platform migration | Replicate into the new environment | Cut over without a long freeze |
| Data consolidation | Several sources publish into one reporting database | Central analytics without batch exports |
| Selective distribution | Share only some tables with another team | Smaller, purpose-built copies |
| Change data capture | Decoded stream feeds message queues or warehouses | Event-driven integration |
Since PostgreSQL 15, publications can also filter rows with a WHERE clause and limit column lists, which helps keep sensitive data out of downstream copies.
Operational limitations and pitfalls
Logical replication demands more care than physical replication, because more depends on schema and data discipline. The table below lists frequent problems.
| Problem | Cause | Remedy |
|---|---|---|
| Disk fills on the source | Inactive replication slot retains the log | Monitor slots, drop abandoned ones, cap retained size |
| Updates fail on subscriber | Table lacks a primary key or replica identity | Add a key or set a suitable identity |
| Apply stops on conflict | Duplicate key or missing row on target | Resolve the row, then resume or skip |
| Schema drift | DDL applied only on the source | Apply schema changes on both sides first |
| Sequences out of step after cutover | Sequence values not replicated | Reset sequences before switching |
| Growing lag on large tables | Single-threaded apply or huge transactions | Use parallel or streaming apply options |
Writes on both sides are possible, but multi-primary setups built on logical replication need explicit conflict handling and careful key design. Without it, two servers editing the same row produce divergence that no tool resolves automatically.
Monitoring and tuning logical replication
Healthy logical replication depends on watching two positions: how far the source has written and how far each subscriber has confirmed. In PostgreSQL this information appears in views of replication slots and subscription statistics, and the difference in bytes or seconds is the lag to alert on.
- Alert on retained log size per slot, not only on lag, because a stopped subscriber silently grows the backlog.
- Keep transactions short: one transaction that updates millions of rows reaches the subscriber only after commit and can stall it.
- Size the initial copy carefully; synchronizing large tables can take hours and holds its own slot.
- Test failover: logical slots historically did not survive promotion of a physical standby, although recent PostgreSQL releases add slot synchronization.
In simple terms
Logical replication copies what changed, not how it is stored. Instead of photocopying every page of a ledger, the source sends short notes such as add this customer or change that price. The receiving database follows those notes in order.
Because the notes are simple, the receiver can be a newer system or keep only some tables. That makes it ideal for moving to a new database without stopping the business.
A practical example
An online retailer runs a 2 TB PostgreSQL database on an old major version and needs to upgrade without hours of downtime. The team builds a new server on the current version, restores the schema, and creates a publication for all tables on the old server and a subscription on the new one.
The initial copy takes about a day, after which changes stream continuously with lag under a second. On the night of the switch, the team stops application writes, waits for lag to reach zero, resets the sequences on the new server and points the application to it. Total write downtime is about five minutes, compared with the many hours a dump and restore would have required.