pghealth Imagepghealth

Understanding Streaming and Logical Replication in PostgreSQL

A detailed look at Streaming and Logical Replication in PostgreSQL, their differences, use cases, and configuration tips.

Β·3 minutes reading
Cover Image for Understanding Streaming and Logical Replication in PostgreSQL

What is Streaming Replication?

Streaming Replication in PostgreSQL is a form of physical replication that works by continuously sending Write-Ahead Log (WAL) records from the primary server to one or more standby servers.
It replicates the entire database cluster at the binary level, ensuring an exact copy of the primary. This is commonly used for high availability, disaster recovery, and read scalability via Hot Standby.
Since it operates at the WAL level, it’s fast and efficient, but it cannot selectively replicate individual tables or schemas.

What is Logical Replication?

Logical Replication is a row/statement-level replication mechanism introduced in PostgreSQL 10.
It allows selective replication of specific tables (and even certain columns), rather than copying the entire database. Logical replication uses a publish/subscribe model and is ideal for data migration, cross-version replication, and integrating PostgreSQL with other systems.
Unlike physical replication, it can replicate between different major PostgreSQL versions, but it has limitations β€” for example, DDL changes are not replicated automatically, and performance can be affected for high-traffic workloads.

Streaming vs Logical Replication β€” Quick Comparison

Replication Level
πŸ“¦ Streaming: Physical (WAL-level) β€” entire database cluster
πŸ“„ Logical: Row/statement-level β€” specific tables only

Setup Complexity
⚑ Streaming: Simple, built-in since PostgreSQL 9.0
πŸ› οΈ Logical: More flexible, introduced in PostgreSQL 10

Data Granularity
πŸ“š Streaming: All databases in the cluster
🎯 Logical: Selective β€” specific tables or columns

Use Cases
βœ… Streaming: High availability, disaster recovery, read scaling
πŸ”„ Logical: Data migration, selective replication, cross-version upgrades

Failover Support
πŸ†— Streaming: Yes (manual or with tools)
❌ Logical: Not designed for failover

Cross-Version Support
❌ Streaming: Must be same major PostgreSQL version
βœ… Logical: Can replicate between different major versions

Downstream Read Queries
πŸ“– Streaming: Yes (Hot Standby)
⚠️ Logical: Not in the same way β€” subscribers are writable databases

Limitations
🚫 Streaming: No filtering by table/schema, schema changes require full sync
β›” Logical: No sequence replication before v14, no DDL replication, possible performance hit

In 2019, at Swiss PGDay, I gave a detailed presentation explaining what Physical and Logical Replication are in PostgreSQL.
I believe this talk clearly explains both Streaming Replication and Logical Replication with real-world use cases and demos.

Here’s the original agenda from my talk:

  • What is Replication?
  • Why do we need Replication?
  • How many replication layers do we have?
  • Understanding milestones of built-in Database Physical Replication.
  • What is the purpose of replication? and How to rescue system in case of failover?
  • What is Streaming Replication and what are its advantages? Async vs Sync, Hot standby, etc.
  • How to configure Master and Standby Servers? And what are the most important parameters? Example of topology.
  • What is Cascading Replication and how to configure it? Live demo on terminal.
  • Quorum Commit for Sync Replication, etc.
  • What is Logical Replication (introduced in PostgreSQL 10)? And what are its advantages?
  • What is the purpose of Logical Replication?
  • How to set up Logical Replication and what are its benefits?
  • Limitations of Logical Replication.
  • Logical Replication vs Physical Replication in detail.

πŸŽ₯ Watch the presentation on YouTube: Swiss PGDay 2019 β€” PostgreSQL Replication Talk

πŸ“‚ Download the slides: Swiss PGDay 2019 Replication Slides

Author: Fırat Güleç — Principal PostgreSQL DBA