PolarDB:WAL log reading from the WAL buffer

Background information

In native PostgreSQL, replications can be categorized into physical replication and logical replication.

• Physical replication: transfers WAL logs to synchronize data between the primary and secondary nodes.

• Logical replication: transfers the logical changes decoded from WAL logs to synchronize data between the publisher and subscribers.

A running PostgreSQL cluster allows multiple physical replication and logical replication links to exist at the same time. The WAL sender process on each link reads WAL logs that have been persisted to storage and parses and sends the WAL logs to downstream systems.

The replication links are independent of each other. Each replication process needs a full read of the WAL logs and independently handles and sends WAL logs. As the number of replication links increases, the bandwidth required for reading WAL logs becomes significantly greater than the bandwidth required for writing WAL logs. This results in a higher demand for IOPS. The frequent reading of WAL logs by multiple replication processes can interfere with the daily I/O operations of the system, which leads to increased latencies for I/O operations, slows down daily business activities, and increases the replication latency.

In native PostgreSQL, the WAL logs are first written to the WAL buffer before they are flushed to persistent storage by a background or common process. The space in the WAL buffer is reused in a cyclic manner. As a result, the WAL buffer contains the most recently generated WAL logs, which are also the ones most likely to be accessed by replication processes.

PolarDB for PostgreSQL (Compatible with Oracle) allows replication processes to directly read the retained WAL logs from the WAL buffer. This approach significantly reduces disk I/O workloads, saves bandwidth, and reduces WAL log reading latency.

Prerequisites

Your PolarDB for PostgreSQL (Compatible with Oracle) cluster runs PolarDB for PostgreSQL (Compatible with Oracle) 2.0 (revision version 2.0.14.13.27.0 or later).

Usage

Set the polar_enable_read_from_wal_buffers parameter to ON to enable replication processes to read WAL logs from the WAL buffer. Default value: ON. For information about how to configure cluster parameters in the PolarDB console, see Configure cluster parameters.

You can use the polar_monitor extension to view the hits of WAL log read operations in the WAL buffer.

1. Create the polar_monitor extension.

   CREATE EXTENSION IF NOT EXISTS polar_monitor;

2. View the hits of WAL log read operations in the WAL buffer.

   SELECT * FROM polar_stat_walsnd_xlog_read();

   Sample result:

      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.