PostgreSQL interview question · Most asked
Streaming (physical) replication or logical replication: what is the difference?
Summary
Streaming replication ships WAL to standbys, which are byte-for-byte copies of the whole cluster (read-only hot standbys, used for HA and read scaling). Logical replication publishes row changes for chosen tables to subscribers, which can be different major versions, have different indexes, or receive only some tables.
Longer Answer
Physical: simple, complete, same major version and architecture; the standby cannot have its own writable tables.
Logical: publications and subscriptions; used for zero-downtime major-version upgrades, consolidating or splitting databases, and feeding other systems. It does not replicate DDL, sequences (until handled manually) or large objects, and needs primary keys or replica identity on tables.
Both use replication slots to retain WAL until consumers have it; an abandoned slot retains WAL forever and can fill the disk. Monitor pg_replication_slots.
Key Points
- Physical = whole cluster via WAL.
- Logical = selected tables' row changes.
- Logical for upgrades and integration.
- Monitor replication slots.
Deep dive: Scaling Databases & Data Consistency
More most-asked PostgreSQL questions
- How does MVCC work in PostgreSQL?
- JSON or JSONB in PostgreSQL, and how do you index JSONB?
- What does VACUUM do, and how does autovacuum work?
- How do you read EXPLAIN ANALYZE output in PostgreSQL?
- What isolation levels does PostgreSQL provide, and how do they behave?
- What index types does PostgreSQL support, and when do you use each?
- Which schema changes take heavy locks in PostgreSQL, and how do you avoid outages during migrations?
- Why do PostgreSQL deployments need connection pooling, and how do PgBouncer's modes differ?