Physical vs Logical Replication in PostgreSQL: Guide

Physical and logical replication in PostgreSQL are two distinct methods for copying data from one server to another, and this guide explains the core differences, use cases, and operational trade-offs of each. Physical replication copies the exact byte-level changes (WAL records) to create an identical standby server, while logical replication decodes those changes into a stream of row-level operations that can be filtered, transformed, and sent to different PostgreSQL versions or even other systems. You choose between them based on whether you need a real-time, byte-for-byte copy for high availability or a flexible, row-based data distribution for reporting, upgrades, or selective sharing.

Background: How PostgreSQL Replication Evolved

PostgreSQL has supported physical replication since version 9.0 (2010), which replaced the older, less reliable trigger-based replication tools. Physical replication works by streaming the Write-Ahead Log (WAL) — the same log used for crash recovery — from a primary server to one or more standby servers. Each standby replays those WAL records to reproduce an exact copy of the primary’s data files. This is the foundation of high availability (HA) setups, where a standby can be promoted if the primary fails.

Logical replication was introduced in PostgreSQL 10 (2017) and matured significantly in later versions. Instead of copying raw WAL data, logical replication uses a decoding plugin (typically pgoutput) to interpret the WAL and extract logical changes: INSERT, UPDATE, DELETE, and TRUNCATE operations on individual tables. These changes are then sent to a subscriber that applies them to its own copy of those tables. Because the changes are logical, the subscriber doesn’t need to be a byte-for-byte clone — it can have different indexes, a different schema (within limits), or even run a different PostgreSQL version.

The two methods are not interchangeable; they solve different problems. Physical replication is about keeping a hot standby for failover and read scaling. Logical replication is about data distribution, consolidation, and migration.

How Physical Replication Works

Physical replication operates at the storage level. The primary server continuously sends WAL segments to the standby, which applies them using a process called restore (in archive mode) or streaming (via a walsender process). The standby runs in hot standby mode, allowing read-only queries while it replays WAL.

Key characteristics:

• Whole-cluster replication: You replicate the entire database cluster — all databases, all tables, all schemas, all roles, and even system catalogs.
• No cross-version support: The standby must run the same major PostgreSQL version as the primary (and ideally the same minor version, though minor upgrades are usually compatible).
• No filtering: You cannot choose to replicate only certain tables or rows. Everything is copied.
• Real-time consistency: The standby is always a consistent snapshot of the primary at some recent point in time, but there is always a small delay (typically milliseconds).
• Promotion: In a failover, the standby is promoted to primary; it becomes writable and any remaining WAL is applied.

Physical replication is the standard method for achieving high availability and read scaling. Tools like pg_basebackup and repmgr automate the setup and failover process.

How Logical Replication Works

Logical replication works at the row level. On the primary (publisher), a logical decoding plugin reads WAL and emits changes for subscribed tables. The changes are sent to the subscriber via a replication slot, and the subscriber applies them using the same row-level operations.

Key characteristics:

• Table-level granularity: You can subscribe to specific tables, not the whole cluster.
• Cross-version compatibility: A publisher running PostgreSQL 12 can send changes to a subscriber on PostgreSQL 15 (as long as the publisher’s version is <= the subscriber's version).
• Data transformation: The subscriber can have a different table structure (e.g., different column order, extra columns with defaults, different indexes). However, the subscribed columns must match by name.
• Filtering: You can use the WHERE clause in the publication to replicate only rows that match a condition (e.g., only active users).
• Conflict handling: If the subscriber also allows writes, conflicts can occur. The default is to skip conflicting changes and log an error.
• DDL not replicated: Schema changes (ALTER TABLE, CREATE INDEX) are not automatically replicated. You must apply them manually to the subscriber.

Logical replication is ideal for:

• Upgrading PostgreSQL with minimal downtime (logical replication from old to new version).
• Consolidating data from multiple databases into a single reporting database.
• Selective data sharing with external systems (e.g., a data warehouse).
• Running different PostgreSQL versions side by side.

Key Differences at a Glance

The table below summarizes the main contrasts (you can also find this in the official documentation):

• Scope: Physical replicates the entire cluster; logical replicates selected tables.
• Version compatibility: Physical requires same major version; logical allows newer subscriber.
• Filtering: Physical has none; logical supports table and row filtering.
• DDL replication: Physical replicates all DDL (since it’s just WAL); logical does not.
• Data types: Physical preserves everything; logical may fail on unsupported types (e.g., some user-defined types).
• Performance: Physical is generally lower overhead; logical has more CPU/memory cost due to decoding.
• Conflict handling: Physical has no conflicts (standby is read-only); logical can have conflicts if subscriber is writable.
• Use case: Physical for HA and read scaling; logical for data distribution and upgrades.

How to Choose Between Them

Choosing the right method depends on your primary goal. Here’s a decision guide:

1. If you need high availability (automatic failover) and zero data loss: Use physical replication. It’s the only method that guarantees a byte-identical copy of the entire cluster, which is required for seamless failover.
2. If you need to scale reads: Physical replication is the standard approach. You can add multiple hot standbys and route read-only queries to them. Logical replication can also be used for read scaling, but it’s more complex and doesn’t provide a full copy of the database (only subscribed tables).
3. If you need to upgrade PostgreSQL with minimal downtime: Use logical replication. Set up a publisher on the old version and a subscriber on the new version, then switch over when caught up.
4. If you need to share a subset of data with another system: Use logical replication. You can publish only the relevant tables and even filter rows.
5. If you need to consolidate data from multiple sources: Logical replication allows multiple publishers to send to one subscriber, merging data into a single database.
6. If you need to replicate the entire database for disaster recovery: Physical replication is simpler and more reliable for full copies. Logical replication would require you to subscribe to every table individually and doesn’t handle sequences, roles, or other cluster-wide objects.

In many production environments, both methods are used together: physical replication for HA, and logical replication for reporting or migration.

Common Problems and Misconceptions

Several issues trip up new users:

• Misconception: Logical replication is a drop-in replacement for physical replication. It is not. Logical replication does not replicate sequences (you must sync them manually), does not replicate large objects, and does not handle DDL. If you need a full copy, use physical.
• Problem: Replication slot bloat. Both methods use replication slots to track WAL. If a subscriber is down for a long time, the primary’s WAL accumulates, filling disk. Set max_slot_wal_keep_size and monitor slot lag.
• Problem: Data type mismatches. Logical replication requires that the column types on the subscriber are compatible. For example, if you change a column from integer to bigint on the publisher, the subscriber must already have that change applied, or replication will fail.
• Problem: No automatic conflict resolution. If the subscriber allows writes, conflicts can cause replication to stop. You must monitor logs and resolve conflicts manually (e.g., by deleting the conflicting row or altering the subscription to skip it).
• Misconception: Logical replication is always slower. It is slower than physical for initial sync, but for steady-state changes, the overhead is often acceptable. However, on high-write systems, decoding can add significant CPU load.

Operational Considerations and Costs

Both methods require careful planning:

• Network bandwidth: Physical replication sends raw WAL, which can be more compact than logical changes (especially for UPDATEs that only change one column). Logical replication sends full row images for UPDATEs unless you use the REPLICA IDENTITY FULL setting, which makes it even larger.
• Disk space: Replication slots on the primary can cause WAL to accumulate. Monitor pg_replication_slots and set appropriate limits.
• CPU and memory: Logical decoding consumes CPU on the publisher. On a busy server, this can impact performance. Test under load.
• Monitoring: Track replication lag using pg_stat_replication for physical and pg_stat_subscription for logical. Lag can be measured in bytes or time.
• Backup: Both methods are not backups. A logical replication subscriber can be used for backups, but it only has the subscribed tables. Physical standby is not a backup either — it’s a live copy that can be corrupted if the primary is corrupted.

Frequently Asked Questions

Can I use logical replication for high availability?

Technically, you can, but it’s not recommended. Logical replication does not replicate the entire cluster (sequences, roles, etc.), and it does not handle DDL. In a failover scenario, you would have to manually sync sequences and apply any schema changes. Physical replication is the standard for HA because it provides a complete, consistent copy that can be promoted with minimal effort.

Does logical replication work across different PostgreSQL versions?

Yes, as long as the publisher’s major version is equal to or older than the subscriber’s. For example, PostgreSQL 12 can publish to PostgreSQL 15, but not the reverse. This is what makes it ideal for upgrades. Always check the official compatibility matrix for the exact versions you plan to use.

What is the difference between replication slots and WAL archiving?

Replication slots are used to ensure the primary retains WAL segments that a standby or subscriber needs, even if it’s disconnected. WAL archiving copies WAL segments to a different location (e.g., S3) for point-in-time recovery. They are complementary: you can use both. Archiving is for backups, slots are for replication.

How do I monitor replication lag?

For physical replication, query pg_stat_replication and look at the replay_lsn and replay_lag columns. For logical replication, query pg_stat_subscription and check the latest_end_lsn and last_msg_send_time. You can also use tools like pg_stat_replication views in monitoring dashboards.

Can I replicate only some tables with physical replication?

No. Physical replication copies the entire cluster. If you need selective table replication, you must use logical replication. There are third-party tools that can do trigger-based replication, but they are not part of core PostgreSQL.

What happens if the subscriber is down for a long time?

If the subscriber is down, the publisher keeps WAL for the replication slot. If the slot is not removed, WAL will accumulate and can fill the disk. To avoid this, set max_slot_wal_keep_size (in PostgreSQL 13+) or use a slot that is dropped when the subscriber disconnects (but that would lose data). For long outages, you may need to reinitialize the subscriber from a backup.

Related

• AI-Generated Database Queries: Read-Only Access
• AI Hyperscalers’ Storytelling Problem Explained
• LangGraph Agents with Shared Persistent Python Memory