Physical and logical replication: Read WAL logs from the WAL buffer-PolarDB(PolarDB)-阿里云帮助中心

PolarDB for PostgreSQLphysical and logical replication can read WAL logs directly from the WAL buffer instead of reading them back from storage. This turns WAL read I/O for replication processes into memory copies, which reduces replication lag when several replication processes read the same WAL.

Background

In native PostgreSQL, replication is either physical replication or logical replication.

Physical replication synchronizes a primary node and a standby node by transferring the contents of WAL logs.
Logical replication synchronizes a publisher node and a subscriber node by transferring logical changes decoded from WAL logs.
A running PostgreSQL instance supports multiple physical and logical replication links at the same time. A dedicated WAL sender process serves each link: it reads the WAL logs that have been persisted to storage, parses them, and sends them downstream over the network. The links are independent of one another, and their replication progress does not affect each other.

Because progress is independent, every replication process reads the full WAL stream and then processes and sends it separately. When an instance has many replication links, WAL read bandwidth becomes a multiple of WAL write bandwidth and consumes a large amount of IOPS. Under heavy I/O load, WAL read I/O and the read and write I/O of your workload compete and increase each other's latency: your workload slows down and replication lag grows.

In native PostgreSQL, WAL logs are first written to the WAL buffer and then written to storage by a background process or a regular process. WAL buffer space is reused in a cycle, so the WAL buffer holds all recently generated WAL logs. That is also the part of the WAL a replication process is most likely to need.

PolarDB for PostgreSQLcan consume WAL logs that have already been flushed to storage but still remain in the WAL buffer. A replication process therefore copies those WAL logs from memory rather than issuing read I/O against storage.

Supported versions

The following versions of PolarDB for PostgreSQL are supported:

PostgreSQL 18: minor version 2.0.18.0.1.0 or later
PostgreSQL 17: minor version 2.0.17.2.1.0 or later
PostgreSQL 16: minor version 2.0.16.6.2.0 or later
PostgreSQL 15: minor version 2.0.15.12.4.0 or later
PostgreSQL 14: minor version 2.0.14.13.27.0 or later
You can view the minor engine version in the console or by running the SHOW polardb_version; statement. If the minor engine version does not meet the requirements, upgrade the minor engine version。

Usage

The polar_enable_read_from_wal_buffers parameter controls this feature, and its default value is on. If the parameter has been set to off, set it back to on so that it takes effect for replication processes. For instructions on how to set cluster parameters in the console, see Configure cluster parameters.

You can use the polar_monitor extension to check how often WAL logs are read from the WAL buffer.

Create the polar_monitor extension.

CREATE EXTENSION IF NOT EXISTS polar_monitor;

Query the WAL buffer hit statistics.

SELECT * FROM polar_stat_walsnd_xlog_read();

The following output is returned:

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.

Column	Data type	Description
pid	INTEGER	The PID of the replication process.
hit	BIGINT	The number of read I/O operations that hit the WAL buffer directly.
hit_bytes	BIGINT	The number of bytes read by I/O operations that hit the WAL buffer directly.
prefetched	BIGINT	The number of read I/O operations that hit the in-memory cache after WAL logs were prefetched from storage in batches.
prefetched_bytes	BIGINT	The number of bytes read by I/O operations that hit the in-memory cache after WAL logs were prefetched from storage in batches.
read	BIGINT	The number of read I/O operations that read WAL logs from storage.
read_bytes	BIGINT	The number of bytes read by I/O operations that read WAL logs from storage.

A high hit or prefetched count relative to read means replication processes are copying WAL from memory. A high read count means the WAL those processes need is no longer in the WAL buffer, so it is read back from storage.