Definition
- Physical (streaming) replication – ships the write-ahead log at the byte level; the replica is an exact binary copy requiring the same engine and major version.
- Logical replication – decodes WAL into row-level change events (INSERT/UPDATE/DELETE per table); supports selective replication, cross-version, and cross-engine pipelines.
- Hot standby – a physical replica that can serve read-only queries while continuously receiving WAL.
- Change-data-capture (CDC) – a pattern that uses logical replication to stream row changes into external systems like Kafka.
- Debezium – a popular CDC tool that reads the logical replication stream and publishes to Kafka topics.
Physical replication is like photocopying every page of National Bank of India's ledger exactly, so the copy is byte-for-byte identical, but both books must be the same edition. Logical replication is like reading out each change ("credit 50000 to SAV2001") and writing it into another book that could be a different edition or layout, and you can choose to copy only the accounts ledger, not the whole library.
Understanding the Concepts
What is physical (streaming) replication?
Physical replication continuously sends WAL bytes from the primary to a standby that replays them, producing an identical copy of every database, table, and index.
-- PostgreSQL: set up streaming replication
-- On primary (postgresql.conf):
-- wal_level = replica
-- max_wal_senders = 5
-- On standby, create a standby.signal file and configure:
-- primary_conninfo = 'host=primary port=5432 user=replicator'
-- The standby continuously receives and replays WAL
-- It is an exact binary copy of the primary
Physical Replication Flow: Primary Standby ┌──────────────┐ ┌──────────────┐ │ accounts │──WAL bytes──► │ accounts │ │ transactions │ │ transactions │ │ indexes │ │ indexes │ │ sequences │ │ sequences │ │ ALL objects │ │ ALL objects │ └──────────────┘ └──────────────┘ Same engine, same version, exact binary copy
What is logical replication?
Logical replication decodes the WAL into a stream of row-level changes: INSERT, UPDATE, DELETE events per table. A subscriber applies those events, which means you can replicate selectively.
-- PostgreSQL: set up logical replication
-- On publisher (postgresql.conf):
-- wal_level = logical
-- Create a publication for specific tables
CREATE PUBLICATION bank_accounts_pub
FOR TABLE accounts, transactions;
-- On subscriber:
CREATE SUBSCRIPTION bank_accounts_sub
CONNECTION 'host=primary port=5432 dbname=bankdb user=replicator'
PUBLICATION bank_accounts_pub;
-- Only accounts and transactions tables are replicated
-- Internal audit tables are deliberately excluded
How do physical and logical replication compare?
| Aspect | Physical | Logical |
|---|---|---|
| Data copied | Entire cluster (all databases, tables, indexes) | Selected tables only |
| Format | Byte-level WAL | Row-level change events |
| Engine requirement | Same engine + major version | Can cross versions and engines |
| Replica writability | Read-only | Can be writable |
| DDL replication | Automatic (part of WAL) | Not automatic (must apply manually) |
| Use case | Hot standby, DR, read replicas | CDC, selective feeds, upgrades |
| Performance | Very efficient | Higher cost on large transactions |
package com.bank.service;
import java.util.logging.Logger;
/**
* Rajesh configures both replication modes for different purposes.
*/
public class ReplicationConfig {
private static final Logger LOG = Logger.getLogger(
ReplicationConfig.class.getName());
public void describeSetup() {
// Physical: exact DR standby
LOG.info("Physical standby: exact copy for disaster recovery");
LOG.info(" - Same PostgreSQL 16.x on both primary and standby");
LOG.info(" - Automatic failover with pg_auto_failover");
// Logical: selective CDC into Kafka
LOG.info("Logical replication: CDC pipeline");
LOG.info(" - Publishes only accounts + transactions");
LOG.info(" - Debezium reads the logical stream");
LOG.info(" - Publishes to Kafka topics");
LOG.info(" - Analytics warehouse consumes from Kafka");
}
}
When do you use physical replication?
Use physical replication for high-availability standbys and read scaling where you want an exact clone. The standby can be promoted to primary on failover with zero data divergence.
-- Monitor physical replication lag
SELECT client_addr, state,
pg_wal_lsn_diff(sent_lsn, replay_lsn) AS lag_bytes
FROM pg_stat_replication;
-- Promote standby to primary (during failover)
-- pg_ctl promote -D /var/lib/postgresql/data
When do you use logical replication?
Use logical replication for selective table feeds, zero-downtime major-version upgrades, cross-engine pipelines, and change-data-capture into Kafka or data warehouses.
-- Zero-downtime PostgreSQL upgrade: 15 → 16
-- 1. Set up logical replication from PG15 to PG16
-- 2. Let PG16 catch up on all changes
-- 3. Stop writes to PG15
-- 4. Wait for PG16 to be fully caught up
-- 5. Switch application to PG16
-- 6. Drop PG15 subscription
-- Debezium CDC pipeline
-- Debezium reads PostgreSQL logical replication slot
-- and publishes to Kafka topics:
-- bank.public.accounts
-- bank.public.transactions
What are the trade-offs of logical replication?
Logical replication does not automatically replicate DDL (schema changes must be applied manually on both sides). Sequences need care. Large transactions cost more because the entire transaction must be decoded. Initial table snapshots can be expensive.
Logical Replication Trade-offs: Advantage Trade-off ─────────────────────── ──────────────────────────── Selective table replication DDL not auto-replicated Cross-version / cross-engine Sequences need manual sync Zero-downtime upgrades Large txns are expensive CDC into Kafka / warehouses Initial snapshot can be slow Writable subscriber Conflict resolution needed
How do you combine both in practice?
Most production setups use both. Physical replication maintains the DR standby and read replicas. Logical replication feeds the CDC pipeline.
National Bank of India Production Setup:
┌──────────────┐
│ Primary │
│ PostgreSQL │
└──────┬───────┘
│
┌────┴────────────────┐
│ │
▼ ▼
Physical Logical
Replication Replication
│ │
▼ ▼
┌──────────┐ ┌──────────┐
│ DR │ │ Debezium │
│ Standby │ │ CDC │
│ (exact) │ │ (select) │
└──────────┘ └────┬─────┘
│
▼
┌──────────┐
│ Kafka │
│ Topics │
└────┬─────┘
│
▼
┌──────────┐
│ Analytics│
│ Warehouse│
└──────────┘
What should a senior engineer know?
A senior should know that logical replication slots, if not consumed, cause WAL to accumulate on the primary and can fill the disk. Monitoring slot lag is critical. They should also know that logical replication from a physical standby (cascading logical from a physical) was added in PostgreSQL 16.
-- Monitor logical replication slots
SELECT slot_name, active,
pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)
AS lag_bytes
FROM pg_replication_slots
WHERE slot_type = 'logical';
-- Danger: inactive slot causes WAL accumulation
-- Drop unused slots to prevent disk fill
SELECT pg_drop_replication_slot('unused_slot');
Visual Overview
┌──────────────────────────────────────────────────────┐ │ Physical vs Logical Replication │ ├──────────────────────────────────────────────────────┤ │ │ │ PHYSICAL (Streaming): │ │ Primary ──WAL bytes──► Standby │ │ [All databases, tables, indexes] │ │ Same engine + version required │ │ Replica is read-only, exact binary copy │ │ │ │ LOGICAL: │ │ Primary ──Row events──► Subscriber │ │ [Selected tables only] │ │ Cross-version, cross-engine possible │ │ INSERT/UPDATE/DELETE per table │ │ │ │ Combined Production Setup: │ │ │ │ Primary ──physical──► DR Standby (exact copy) │ │ ──logical───► Debezium ──► Kafka ──► DW │ └──────────────────────────────────────────────────────┘
Key Takeaway
Physical, or streaming, replication ships the write-ahead log at the byte level, so the standby replays those bytes and becomes an exact binary copy of the entire cluster. It is efficient and is the standard way to run a hot standby and read replicas, but the replica is read-only, must match the primary's major version and architecture, and copies everything with no way to select a subset.
Logical replication decodes the WAL into row-level change events published per table, which a subscriber applies. This enables selective replication (only certain tables), zero-downtime major-version upgrades, and cross-engine streaming through CDC tools like Debezium.
At the National Bank of India, Pankaj uses both. Physical streaming replication maintains an exact hot standby in the disaster-recovery site for fast failover. Logical replication publishes only the accounts and transactions tables into a Kafka CDC pipeline that feeds the analytics warehouse, deliberately leaving internal audit tables behind. The team monitors both replication lag and logical slot lag, because an unconsumed logical slot causes WAL to accumulate on the primary.
The trade-offs are that logical replication does not carry DDL automatically, needs care with sequences, and costs more on very large transactions, while physical replication is cheaper and exact but locks you to the same engine and version.
Remember one sentence: physical is an exact binary copy of the whole cluster on the same engine, while logical is selective row-level change events that can cross versions and engines.
---