Logical vs physical replication differences

Physical replication ships opaque, block-level Write-Ahead Log (WAL) to a byte-for-byte standby, while logical replication decodes that same WAL into row-level change events that a consumer can filter, transform, and route — this guide maps the two models parameter by parameter as a companion to the publication/subscription model on PostgreSQL 14 through 17. Choosing wrong is a production-grade mistake: pick physical for a change-data-capture (CDC) sink and you inherit strict version parity with zero row filtering, while picking logical for a hot-standby failover target saddles you with decoding CPU, slot-retention risk, and asynchronous, at-least-once delivery.

Commit & Behavior Semantics #

Both models read the same physical WAL, but they diverge the moment a record leaves the walsender. Physical replication requires only wal_level = replica (the default since PostgreSQL 10) and streams binary WAL segments directly to standbys. It bypasses SQL parsing entirely, so serialization overhead on the primary is near zero — the trade is strict schema parity, identical major versions, and identical collation between primary and standby. Logical replication mandates wal_level = logical, which instructs the logical decoding subsystem to log the extra relation and old-tuple metadata needed to reconstruct row images. A background walsender then runs the pgoutput plugin over the reorder buffer and emits structured messages — BEGIN, INSERT, UPDATE, DELETE, TRUNCATE, COMMIT — the same shape a downstream parser reads when parsing the pgoutput format with psycopg2. The mechanics of that decode step are covered in depth under WAL stream mechanics.

The durability and latency consequences of that split are exact, not qualitative:

The single most consequential row is the durability marker. On a physical slot, restart_lsn advances as the standby replays segments; on a logical slot, WAL is pinned until the subscriber returns a confirmed_flush_lsn, and a stalled consumer therefore holds WAL on the primary indefinitely. That retention risk is the price of the routing flexibility in the right-hand column.

Diagnostic Patterns #

Distinguishing the two models on a running cluster — and catching the failure mode unique to logical slots — comes down to three catalog views. Start by confirming which decode mode is actually live, not just staged:

-- logical decoding is only active when the RUNNING server reports 'logical'.
SELECT name, setting, pending_restart
FROM pg_settings
WHERE name IN ('wal_level', 'max_wal_senders', 'max_replication_slots');
-- pending_restart = true means the change is staged but NOT yet in effect.

Inspect every slot’s type and its retained WAL. On a logical slot, restart_lsn lagging far behind the write head is the leading indicator of unbounded pg_wal growth:

-- Per-slot retention. Alert when retained_bytes crosses ~50% of max_slot_wal_keep_size,
-- and page immediately if it exceeds it (PG 13+ will mark the slot 'lost' and break it).
SELECT slot_name,
       slot_type,                                   -- 'physical' vs 'logical'
       active,
       pg_size_pretty(
         pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)
       ) AS retained_bytes,
       pg_size_pretty(
         pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)
       ) AS unconfirmed_bytes,                       -- NULL for physical slots
       wal_status                                    -- reserved | extended | unreserved | lost
FROM pg_replication_slots
ORDER BY slot_type, retained_bytes DESC;

Two thresholds matter operationally. Treat active = false on a logical slot for longer than 300 s as an incident — an idle consumer is silently accumulating WAL. Treat wal_status = 'lost' as terminal: the segments the slot needed have been recycled and the only recovery is to drop the slot and reseed downstream. For the acknowledgement side, compare send and apply positions on the live stream:

-- Live lag per connected consumer. write/flush/replay lag are intervals (PG 10+).
SELECT application_name,
       client_addr,
       state,                                        -- streaming | catchup | backup
       pg_size_pretty(
         pg_wal_lsn_diff(sent_lsn, replay_lsn)
       ) AS apply_lag_bytes,
       replay_lag
FROM pg_stat_replication;

For sizing max_replication_slots before you provision consumers, the safe-headroom math is worked through under configuring max_replication_slots safely.

Safe Deployment Sequence #

Moving a physical-only primary to also feed a logical CDC stream is a zero-downtime change with one hard constraint: raising wal_level from replica to logical requires a full restart and cannot be hot-reloaded. Sequence it so the restart is the only disruptive step and every prior step is reversible.

1. Stage the reload-safe GUCs first. These take effect immediately and do not disturb existing physical standbys.

   ALTER SYSTEM SET max_wal_senders = 10;         -- one per stream + failover headroom
   ALTER SYSTEM SET max_replication_slots = 10;
   ALTER SYSTEM SET logical_decoding_work_mem = '128MB';
   ALTER SYSTEM SET max_slot_wal_keep_size = '40GB';  -- PG 13+: hard cap on stalled-slot WAL
   SELECT pg_reload_conf();
   

2. Stage wal_level and confirm it is pending, not live. Nothing changes until the bounce.

   ALTER SYSTEM SET wal_level = 'logical';
   SELECT setting, pending_restart FROM pg_settings WHERE name = 'wal_level';
   -- expect: setting='replica', pending_restart='t'
   

3. Restart during a maintenance window. Physical standbys reconnect automatically; budget the extra 10-30% WAL volume that logical produces on update-heavy workloads. Verify SHOW wal_level; returns logical on the running primary before proceeding.

4. Create the slot and publication without a consumer yet. Pre-creating the slot lets you validate replica identity before any WAL is pinned in earnest.

   SELECT pg_create_logical_replication_slot('analytics_cdc', 'pgoutput');
   CREATE PUBLICATION analytics_cdc_pub FOR TABLE orders, customers;
   

5. Attach the subscriber and complete the initial subscription sync. Only now does the slot begin advancing confirmed_flush_lsn.

Revert procedure. To back the change out cleanly, drop the consumer-facing objects before lowering wal_level, so no orphaned slot pins WAL through the restart:

DROP SUBSCRIPTION analytics_cdc_sub;               -- on the subscriber
DROP PUBLICATION analytics_cdc_pub;                -- on the publisher
SELECT pg_drop_replication_slot('analytics_cdc');  -- releases pinned WAL immediately
ALTER SYSTEM SET wal_level = 'replica';            -- takes effect on the next restart

If you lower wal_level while a logical slot still exists, the slot becomes unusable after the restart and continues to pin WAL until dropped — always release slots first.

Pipeline Integration #

A physical standby needs no application code; a logical consumer is an application, and the semantics table above dictates its shape. Because logical delivery is at-least-once, every sink write must be idempotent. Key downstream rows on the source primary key and upsert:

# Idempotent apply. A redelivered change after a reconnect is a no-op, not a duplicate.
UPSERT = """
    INSERT INTO warehouse.orders (id, status, total, src_lsn)
    VALUES (%(id)s, %(status)s, %(total)s, %(lsn)s)
    ON CONFLICT (id) DO UPDATE
       SET status = EXCLUDED.status,
           total  = EXCLUDED.total,
           src_lsn = EXCLUDED.src_lsn
     WHERE warehouse.orders.src_lsn < EXCLUDED.src_lsn;  -- ignore stale replays
"""

Advance confirmed_flush_lsn only after the sink write is durably committed, and send a periodic heartbeat during idle windows so the slot’s restart_lsn keeps moving even when the source is quiet — otherwise an idle publisher plus a well-behaved consumer can still accumulate WAL:

import time

def stream(cur, apply_change, feedback):
    last_status = time.monotonic()
    for msg in cur:                         # psycopg2 logical replication cursor
        apply_change(msg)                   # durable sink write (upsert above)
        # Only confirm what is safely persisted downstream.
        msg.cursor.send_feedback(flush_lsn=msg.data_start)
        # Heartbeat at least every 10 s so restart_lsn advances while idle.
        if time.monotonic() - last_status > 10:
            msg.cursor.send_feedback(reply=True)
            last_status = time.monotonic()

Wrap reconnects in bounded exponential backoff (for example 1 s doubling to a 60 s ceiling) and resume from the last confirmed LSN — the slot remembers the position, so a restarted consumer replays only unconfirmed changes. Export retained_bytes, apply_lag_bytes, and slot active state from the diagnostic queries above to your metrics backend so retention breaches alert before they page. For a managed connector that implements this loop, snapshotting, and schema handling out of the box, see Debezium connector configuration.

Failover handling. This is where logical diverges most sharply from physical. A physical standby promotes with pg_ctl promote and needs no slot migration. A logical slot does not follow a promotion (before PG 16’s failover slots), so after the primary is lost you must recreate the slot on the new primary and skip already-applied transactions to prevent duplicate delivery:

-- On the promoted standby, after recreating the slot:
SELECT pg_replication_origin_advance('pg_analytics_cdc_sub', '0/1A2B3C4D');
-- advance the origin to the last LSN the sink durably applied, then resume.

If slot state cannot be recovered, fall back to a bounded snapshot — COPY (SELECT ... WHERE updated_at > :last_sync) TO STDOUT — to bridge the gap, then re-attach the stream. Role scoping, hostssl rules, and the REPLICATION privilege these consumers connect with are detailed under security boundaries & permissions.

Authoritative references #

• PostgreSQL manual — Logical Replication and the lower-level Logical Decoding chapter.
• wal_level configuration parameter — the replica vs logical semantics.
• pg_replication_slots system view — restart_lsn, confirmed_flush_lsn, and wal_status field definitions.
• Streaming Replication / High Availability — the physical-standby model.

Related #

• Publication/subscription models — the parent guide to the declarative object model this comparison sits under.
• WAL stream mechanics — how the shared WAL is generated, retained, and acknowledged.
• How WAL decoding works in PostgreSQL 16 — the decode step that turns blocks into row events.
• Configuring max_replication_slots safely — sizing slot headroom before you add logical consumers.