Replication and Logical Decoding

Postgres replicates via the write-ahead log (WAL). Two main flavors: physical (bit-for-bit, fast, all-or-nothing) and logical (decoded into per-row events, selective, slower).

Physical (streaming) replication

Primary writes WAL records; standby reads them and replays. The standby is a binary copy of the primary — same files, same byte layout. Standby is read-only unless promoted.

primary -- WAL stream --> standby (replay)

• Sync: primary waits for ≥1 standby to confirm before commit. Highest durability; latency cost.
• Async: primary commits immediately; standby catches up later. Lower latency; replication lag risk.
• Cascading: standby can replicate to another standby (chain).

This is what RDS Multi-AZ, Aurora replicas, Patroni, and stock Postgres streaming_replication use.

Failover: promote a standby with pg_promote(). Brief downtime; clients reconnect to the new primary. Tools: Patroni, repmgr, pg_auto_failover.

Sync replication setup (server-side)

# postgresql.conf on primary
wal_level = replica
max_wal_senders = 10
synchronous_commit = remote_apply       # or on, remote_write
synchronous_standby_names = 'standby1, standby2'

synchronous_commit values:

• off — fire-and-forget; commits return before WAL fsync. Fastest, can lose data.
• local — wait for local fsync.
• remote_write — wait for standby to receive (not fsync).
• on — wait for standby fsync.
• remote_apply — wait for standby replay. Slowest but read-after-write on replica.

For read-after-write consistency on a replica, you need remote_apply + the replica in your sync set. Most apps live with async + tolerate brief lag.

Replication lag

-- On primary
SELECT client_addr, state,
       pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn)   AS sent_lag,
       pg_wal_lsn_diff(pg_current_wal_lsn(), write_lsn)  AS write_lag,
       pg_wal_lsn_diff(pg_current_wal_lsn(), flush_lsn)  AS flush_lag,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_lag
FROM pg_stat_replication;

Bytes behind primary at each phase. replay_lag is what matters for replica reads.

-- On standby — how stale am I?
SELECT NOW() - pg_last_xact_replay_timestamp() AS lag;

Causes of lag:

• Network bandwidth between primary and standby.
• Standby IO (slower disk than primary’s writes).
• Long queries on standby (block apply when they hold relation locks — hot_standby_feedback).
• Single-threaded replay (Postgres replays WAL serially per replica).

Logical replication

Decodes WAL into row-level events (INSERT/UPDATE/DELETE with old+new values) and streams them. Subscribers can be other Postgres, or any consumer via output plugins (Debezium → Kafka → anywhere).

# postgresql.conf
wal_level = logical

Postgres → Postgres logical replication:

-- Publisher
CREATE PUBLICATION orders_pub FOR TABLE orders, users;

-- Subscriber
CREATE SUBSCRIPTION orders_sub
    CONNECTION 'host=primary dbname=app user=repl password=...'
    PUBLICATION orders_pub;

The subscriber can run a different Postgres major version, different schema, even different table definitions (with caveats). Used for:

• Zero-downtime major-version upgrades — replicate from 14 to 16, switch.
• Selective replication — only certain tables/databases.
• Heterogeneous architectures — primary OLTP + an analytics replica with extra indexes.

Debezium and CDC

For “Postgres → Kafka / Elasticsearch / data lake”, Debezium plugs into Postgres’ logical decoding via the pgoutput or wal2json plugin. Every row change becomes a Kafka message; downstream consumers project to their stores.

This is the canonical CDC pattern that replaces transactional outbox in many architectures.

Replication slots

A slot reserves WAL on the primary until the subscriber catches up — prevents the primary from cleaning up WAL the subscriber still needs.

SELECT * FROM pg_replication_slots;

Inactive slot = disaster waiting. WAL piles up on disk forever, fills, primary stops accepting writes. Always monitor slot lag:

SELECT slot_name, active,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS wal_retained
FROM pg_replication_slots;

If a subscriber goes away permanently, drop the slot.

Logical decoding gotchas

• Schema changes (ALTER TABLE) are NOT replicated by stock logical replication. Apply them on the subscriber separately. Debezium can emit DDL events but the consumer applies them.
• TRUNCATE — replicated if your publication includes it (WITH (publish = 'insert, update, delete, truncate')).
• Initial snapshot — created at subscription start; large tables can take hours to copy.
• No replication of DDL, sequences (until 16+), unlogged tables.
• Primary key required (or REPLICA IDENTITY set) for UPDATE/DELETE to be decodable — without it, you can’t identify which row changed.

Logical vs physical: trade-offs

Promotion / failover semantics

• Physical replica promotion — quick (pg_promote()), brief failover; sequences advance correctly.
• Logical subscriber promotion — depends on the tool. There’s no built-in “promote logical sub to primary” path.

For HA: use physical streaming replication (or Aurora, which abstracts it). For ETL: logical.

Aurora is different

Aurora doesn’t use Postgres’ streaming replication. The storage layer is shared between writer and readers; readers see writes within milliseconds because there’s no apply step. Aurora calls its WAL the “log records” but the model is simpler from outside.

Aurora Global Database (cross-region) is closer to physical replication conceptually but operates at the storage layer.

Common production patterns

• Hot standby with sync rep + async on extra replicas. Primary + 1 sync standby in another AZ (durability), + N async replicas for read scaling.
• Patroni / pg_auto_failover for HA orchestration. Or just use RDS / Aurora and skip the orchestration.
• Debezium → Kafka for event-streaming to all downstream consumers — replaces dual-write / transactional outbox in some architectures.
• pg_dump + pg_restore for one-time migrations, not replication. Logical replication for ongoing sync.
• pglogical extension — older logical replication implementation, predates built-in. Largely obsolete now.

Interview angle

• “Physical vs logical replication — when each?” — physical: identical bit-for-bit copy, fast, used for HA + read replicas. Logical: per-row events, slower, flexible, used for cross-version upgrades and CDC (e.g., Debezium).
• “How does synchronous replication affect commit latency?” — primary waits for ≥1 standby to confirm at the level you set (remote_write, on, remote_apply). Adds network round-trip per commit. Durability vs latency trade-off.
• “What’s a replication slot and why does it matter?” — reserves WAL on the primary so a subscriber that’s behind can catch up. Critical risk: an abandoned slot retains WAL forever; primary disk fills. Monitor pg_replication_slots.
• “What’s CDC?” — Change Data Capture. Postgres’ logical decoding emits a stream of row changes from the WAL. Tools like Debezium consume this and publish to Kafka. Used as a robust alternative to transactional outbox.
• “What goes wrong with read-after-write on a Postgres replica?” — async replication = replica lags by up to seconds. A user writes, immediately reads, sees stale data. Mitigations: route the read to the primary for a brief window after writes, use sync replication for the critical reads (synchronous_commit = remote_apply), or accept eventual consistency.
• “How does Aurora differ from RDS Multi-AZ?” — Aurora has a shared distributed storage layer; readers see writes in milliseconds because there’s no per-replica apply step. RDS Multi-AZ uses Postgres’ streaming replication (or MySQL equivalent); standby is invisible and failover takes 60-120s. Aurora failover is ~30s.