Physical & Logical Replication

Learning outcomes

• Configure primary-replica streaming replication
• Architect zero-data-loss failover clusters with Patroni

Mental model

Replication streams database changes from a Primary node to Replica nodes to enable read scaling, disaster recovery, and high availability.

Theory

• Physical Streaming Replication: Streams byte-for-byte WAL records. The replica is an exact read-only copy of the primary.
• Logical Replication: Decodes WAL records into logical data change events (INSERT/UPDATE/DELETE). Allows selective table replication across different PostgreSQL versions or target schemas.

Automatic high availability (HA) frameworks like Patroni use a Distributed Consensus Store (DCS like etcd or Consul) to elect a new primary node when health checks fail.

-- Check replication lag on Primary node
SELECT client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS lag_bytes
FROM pg_stat_replication;

-- Create a logical publication for specific tables
CREATE PUBLICATION app_events_pub FOR TABLE events, audit_logs;

Alternatives and trade-offs

• Physical Replication: Simple configuration, zero logical translation overhead; replica must be read-only and identical version.
• Logical Replication: Supports cross-version upgrades and partial table subsets; higher CPU decoding overhead on primary.

Failure modes and misconceptions

1. Split-Brain Scenario: Network partitioning causing two nodes to act as Primary simultaneously. Prevent split-brain by requiring quorum consensus via DCS (etcd/Consul).
2. Replication Slot Disk Exhaustion: A disconnected standby holding an active physical replication slot causes the Primary to retain all WAL files indefinitely until disk fills up.

Decision scenario

Deploy Patroni with etcd consensus for automated primary failover in mission-critical PostgreSQL database clusters requiring 99.99% availability.

Learning outcomes

• Compare Physical Streaming Replication with Logical Replication mechanics.
• Monitor replication lag using LSN byte diffs (pg_wal_lsn_diff).
• Prevent split-brain scenarios using distributed consensus managers.

Trade-offs

Replication provides high availability and read scaling, but asynchronous replication introduces small replication lag windows where read replicas may reflect slightly stale data.

Primary sources

• PostgreSQL 16 Architecture, MVCC & Query Optimization Manual — PostgreSQL Global Development Group, verified 2026-07-22