Intermediate LevelPostgreSQL
Compare Physical Streaming Replication versus Logical Replication in PostgreSQL.
❓ Interview Prompt: Compare Physical Streaming Replication (WAL byte stream, exact replica copy, read-only) vs Logical Replication (publication/subscription, row changes, cross-version).
1. Physical Streaming Replication:
•
Streams raw binary WAL bytes to standby nodes.
•
Produces an exact byte-for-byte read-only replica.
•
Fast, minimal CPU overhead; required for High Availability failover.
2. Logical Replication (Postgres 10+):
•
Uses Publication / Subscription model to stream decoded row-level changes.
•
Allows selective table replication, multi-version upgrades, and writing to target subscriber databases!
Code Implementation Example
sql
-- On Publisher Database: Create publication for specific tables
CREATE PUBLICATION app_pub FOR TABLE users, orders;
-- On Subscriber Database: Subscribe to publisher
CREATE SUBSCRIPTION app_sub
CONNECTION 'host=primary_host dbname=appdb user=rep_user password=secret'
PUBLICATION app_pub;💡Setting up Logical Replication publication and subscription.
🎯 Interview Key Takeaway
Use Physical Replication for HA failover replicas, and Logical Replication for selective data streaming and major version upgrades.
#Replication#Streaming Replication#Logical Replication#Publication#Intermediate
Back to all PostgreSQL Questions→