PolarDB:Physical and logical replication: Read WAL logs from the WAL buffer

Background

In native PostgreSQL, replication is divided into physical replication and logical replication.

• Physical replication: synchronizes data between primary and standby nodes by transferring the content of WAL logs.

• Logical replication: synchronizes data between publisher and subscriber nodes by transferring logical changes decoded from WAL logs.

A running PostgreSQL instance can have multiple physical and logical replication links at the same time. Each link has a corresponding WAL sender process that reads WAL logs from storage, parses them, and sends them to a downstream system over the network. The replication progress of each link is independent.

Because the replication progress of each link is independent, each replication process must read, process, and send the full set of WAL logs. When there are many replication links, the read bandwidth for WAL logs can be several times higher than the write bandwidth. This consumes many input/output operations per second (IOPS). When an instance is under high I/O pressure, the large volume of WAL log read I/O can interfere with regular business read/write I/O. This interference slows down business operations and increases replication latency.

In native PostgreSQL, when WAL logs are generated, they are first written to the WAL buffer. Then, a background or regular process writes them to storage. The space in the WAL buffer is reused cyclically. Therefore, the WAL buffer holds the most recent WAL logs, which are the logs most likely to be used by replication processes.

PolarDB for PostgreSQL supports reading WAL logs directly from the WAL buffer, even if the logs have been written to disk but are still present in the buffer. This converts the read I/O from replication processes into memory copy operations. This optimization significantly reduces read I/O amplification from multiple replication processes and saves bandwidth for reading WAL logs. It also lowers the read latency of WAL logs, which in turn reduces replication latency.

Applicability

This feature is available for the following versions of PolarDB for PostgreSQL:

• PostgreSQL 18 (minor engine version 2.0.18.0.1.0 or later)

• PostgreSQL 17 (minor engine version 2.0.17.2.1.0 or later)

• PostgreSQL 16 (minor engine version 2.0.16.6.2.0 or later)

• PostgreSQL 15 (minor engine version 2.0.15.12.4.0 or later)

• PostgreSQL 14 (minor engine version 2.0.14.13.27.0 or later)

Usage

You can enable this feature for replication processes by setting the polar_enable_read_from_wal_buffers parameter to on. The default value is on. For more information about how to set cluster parameters, see Set cluster parameters.

You can use the polar_monitor extension to check the hit statistics for reading WAL logs from the WAL buffer.

1. Create the polar_monitor extension.

   CREATE EXTENSION IF NOT EXISTS polar_monitor;

2. Check the hit statistics for reading WAL logs from the WAL buffer.

   SELECT * FROM polar_stat_walsnd_xlog_read();

   The command returns a result similar to the following:

      pid   | hit  | hit_bytes | prefetched | prefetched_bytes | read | read_bytes
   ---------+------+-----------+------------+------------------+------+------------
    3865685 | 2175 | 251583064 |          0 |                0 |   82 |   10628128
    3865751 | 2173 | 251582792 |          0 |                0 |   82 |   10628400
   (2 rows)

The following table describes the returned fields.