12. Logical Replication

Streaming Replication significantly improved the availability of PostgreSQL databases. However, it still had several limitations:

  • Streaming Replication replicates an entire database cluster and cannot be used to replicate only a specific database or selected tables.
  • A standby in Streaming Replication cannot perform write operations.
  • Streaming Replication requires compatible PostgreSQL versions.

These limitations arise from the nature of Streaming Replication. It continuously transfers WAL data from the primary to the standbys, which then replay the changes recorded in the WAL. To provide greater flexibility, PostgreSQL needed to replicate logical data changes rather than physical WAL-level changes.

PostgreSQL introduced native logical replication in version 10 (2017). In logical replication, changes recorded in WAL are decoded into logical operations and sent from a Publisher to a Subscriber. Unlike Streaming Replication, logical replication can target individual databases or specific tables, and a Subscriber can perform local write operations while receiving replicated data. See Figure 12.1.

In Streaming Replication, the participating nodes are referred to as the Primary and the Standby. In logical replication, they are referred to as the Publisher and Subscriber.
Figure 12.1. Comparison of data flow and node roles in streaming and logical replication.

In Streaming Replication, the participating nodes are referred to as the Primary and the Standby. In logical replication, they are referred to as the Publisher and Subscriber.

Table 12.1 summarizes the characteristics of logical replication through a detailed comparison with streaming replication:

Table 12.1: Logical Replication vs. Streaming Replication.
Logical Replication Streaming Replication
WAL Sending Timing After transaction commit *1 Whenever WAL is generated
Sending Processes walsender walsender
Receiving Processes
  • Logical replication launcher
  • apply worker
  • walreceiver
  • startup
Replication Unit Table, Database Database cluster
Unsent Operations DDL (See Official Documentation for details.) N/A
Unsent Objects Non-Table objects, e.g., views, sequences*2, indexes, large objects, etc.
(See Official Documentation for details.) 		UNLOGGED tables
Supports Different Versions YES NO
Supports Different Operating Systems YES NO
DML on Subscriber/Standby INSERT, UPDATE, DELETE, SELECT, TRUNCATE*3 SELECT
Triggers on Subscriber/Standby Enabled Disabled

  • *1: Since the introduction of streaming (Version 14), logical changes for large transactions may be sent in segments prior to the commit; see Sections 12.1.2 and 12.7 for details.

  • *2: Sequences have been supported since version 19 (2026).

  • *3: TRUNCATE has been supported since version 11 (2018).

Logical replication also provides an important secondary benefit. Because it replicates logical data changes rather than physical WAL records, it can not only replicate data between different PostgreSQL versions but also migrate data between different database systems. This capability forms the basis of many Database Migration Services (DMS) and Change Data Capture (CDC) systems.

This chapter provides a technical analysis of logical replication through the following sections:

  • Section 12.1: Presents a high-level overview, covering fundamental operations and core concepts of the logical replication framework.
  • Section 12.2: Details the internal startup process of logical replication.
  • Sections 12.3 - 12.4: Examine the architecture and functionality of the ReorderBuffer, a central component in managing logical replication.
  • Section 12.5: Explains pgoutput, the default pluggable plugin used to decode WAL data and populate the ReorderBuffer.
  • Section 12.6: Describes the mechanisms for constructing replication messages from the ReorderBuffer to send to the subscriber.
  • Section 12.7: Analyzes the internal operations and message-handling logic of the subscriber’s apply worker.
  • Section 12.8: Concludes the chapter with an explanation of subscriber restart sequences and crash recovery mechanisms.
Chapter Contents