What are read-ahead and pre-extension and how to use them - PolarDB

This topic describes the overview, principles, and usage of heap table read-ahead, heap table pre-extension, and index creation pre-extension.

Prerequisites

The supported PolarDB for PostgreSQL versions are as follows:

PostgreSQL 18(kernel minor version 2.0.18.0.1.0 and later)
PostgreSQL 17(kernel minor version 2.0.17.2.1.0 and later)
PostgreSQL 16(kernel minor version 2.0.16.6.2.0 and later)
PostgreSQL 15(kernel minor version 2.0.15.12.4.0 and later)
PostgreSQL 14(kernel minor version 2.0.14.5.1.0 and later)
PostgreSQL 11(kernel minor version 2.0.11.2.0.0 and later)

Note

You can view the kernel minor version in the console, or use the SHOW polardb_version; statement to view it. If the kernel minor version requirement is not met, upgrade the kernel minor version.

Background information

PolarDB for PostgreSQL uses PolarFS (hereinafter referred to as PFS) as the file system at the underlying layer. Unlike standalone file systems such as ext4, PFS has large metadata update overhead during the page extension process. In addition, the minimum page extension granularity of PFS is 4 MB. The 8 KB page extension granularity of PostgreSQL is not suitable for PFS and causes performance degradation when writing tables or creating indexes. Meanwhile, PFS has higher I/O efficiency when reading large blocks of pages.

To adapt to the preceding characteristics, PolarDB for PostgreSQL designs the features of heap table read-ahead, heap table pre-extension, and index creation pre-extension, so that PolarDB for PostgreSQL running on PFS can achieve better performance.

Overview

Heap table read-ahead

When PostgreSQL reads heap tables, pages are read from the file system into the memory buffer pool (Buffer Pool) in units of 8 KB pages. PFS is not very efficient for such small-volume I/O operations. Therefore, PolarDB for PostgreSQL designs bulk read-ahead for heap tables to adapt to PFS.

When the number of pages to read is greater than 1, bulk read-ahead is triggered, and 128 KB of data is read into the buffer pool in a single I/O. Read-ahead can roughly double the performance in the Sequential Scan and Vacuum scenarios, and can bring an 18% performance improvement in the index creation scenario.

Note
PostgreSQL 17 does not currently support bulk reading of heap tables.
Heap table pre-extension

In PostgreSQL, during the extension of a tablespace, 8 KB pages are requested and extended one by one. Even with the bulk page extension supported by PostgreSQL, a single N-page extension involves N I/O operations. This kind of page extension does not conform to the characteristic of PFS that the minimum page extension granularity is 4 MB. Therefore, PolarDB for PostgreSQL designs bulk pre-extension for heap tables.

During the extension of heap tables, 4 MB pages are extended in a single I/O. In scenarios with frequent table writes (such as data loading), this can double the performance.
Index creation pre-extension

Index creation pre-extension is similar to heap table pre-extension. Index creation pre-extension is specially designed to optimize the index creation process for PFS. During the page extension of index creation, 4 MB pages are extended in a single I/O. This design can bring a 30% performance improvement during index creation.

Index creation pre-extension currently supports the following index types:
- B-Tree
- GIN
- GiST
- SP-Gist
- Bloom
Note
PostgreSQL 11 Index creation pre-extension supports only B-Tree indexes.

Principles

Heap table read-ahead
The implementation steps of heap table read-ahead are mainly divided into the following four steps:
1. Request N buffers in the Buffer Pool.
2. Use palloc to request a memory space with a size of N*page_size in the memory. The space is referred to as p.
3. Use PFS to bulk-read data of N * page_size from the heap table and copy it to p.
4. Copy the contents of the N pages in p one by one to the N buffers requested from the Buffer Pool.
Subsequent read operations directly hit the buffers. The data flow diagram is as follows:
Heap table pre-extension
The implementation steps of pre-extension are mainly divided into the following three steps:
1. Request N buffers from the Buffer Pool without triggering the page extension of the file system.
2. Perform bulk page extension through the file writing interface of PFS, and write all-zero pages.
3. Initialize the requested pages one by one, mark the available space of the pages, and complete the pre-extension.
Index creation pre-extension
The implementation steps of index creation pre-extension are similar to pre-extension, but do not involve requesting buffers. The steps are as follows:
1. When writing index pages, perform bulk page extension through the file writing interface of PFS, and write all-zero pages.
2. Write the index pages that have been built in the Buffer Pool into the file system.

Usage guide

Heap table read-ahead
The parameter name of heap table read-ahead is polar_bulk_read_size. This feature is enabled by default, and the default size is 128 KB.

Note
We do not recommend that you modify this parameter yourself. 128 KB is the optimal value for PFS, and adjusting it yourself does not bring performance improvements.
- Disable the heap table read-ahead feature.
```
ALTER SYSTEM SET polar_bulk_read_size = 0;
SELECT pg_reload_conf();
```
- Enable the heap table read-ahead feature and set the read-ahead size to 128 KB.
```
ALTER SYSTEM SET polar_bulk_read_size = '128 KB';
SELECT pg_reload_conf();
```
Heap table pre-extension
The parameter name of heap table pre-extension is polar_heap_bulk_extend_size. This feature is enabled by default, and the pre-extension size is 4 MB by default.
Note
- We do not recommend that you modify this parameter value yourself. 4 MB is the optimal value for PFS.
- In PostgreSQL 11, the parameter name is polar_bulk_extend_size.
- Disable the heap table pre-extension feature.
```
ALTER SYSTEM SET polar_heap_bulk_extend_size = 0;
SELECT pg_reload_conf();
```
- Enable the heap table pre-extension feature and set the pre-extension size to 4 MB.
```
ALTER SYSTEM SET polar_heap_bulk_extend_size = '4 MB';
SELECT pg_reload_conf();
```
Index creation pre-extension
The parameter name of index creation pre-extension is polar_index_bulk_extend_size. This feature is enabled by default. The index creation pre-extension size is 4 MB by default.
Note
- We do not recommend that you modify this parameter value yourself. 4 MB is the optimal value for PFS.
- In PostgreSQL 11, the parameter name is polar_index_create_bulk_extend_size.
- Disable the index creation pre-extension feature.
```
ALTER SYSTEM SET polar_index_bulk_extend_size = 0;
SELECT pg_reload_conf();
```
- Enable the index creation pre-extension feature and set the pre-extension size to 4 MB.
```
ALTER SYSTEM SET polar_index_bulk_extend_size = '4 MB';
SELECT pg_reload_conf();
```

Performance comparison

To demonstrate the performance improvements of heap table read-ahead, heap table pre-extension, and index creation pre-extension, tests were performed on PolarDB for PostgreSQL clusters of PostgreSQL 14.

Specifications: 8 cores and 32 GB of memory.
Test scenario: 400 GB pgbench test.

Heap table read-ahead
- The Vacuum performance comparison of a 400 GB table is as follows:
- The SeqScan performance comparison of a 400 GB table is as follows:
Conclusions:
- Heap table read-ahead improves the performance by 1 to 2 times in the Vacuum and SeqScan scenarios.
- Increasing the heap table read-ahead size beyond the default value of 128 KB does not significantly improve the performance.
Heap table pre-extension

The data loading performance comparison of a 400 GB table is as follows:
Conclusions:
- Heap table pre-extension doubles the performance in the data loading scenario.
- Increasing the heap table pre-extension size beyond the default value of 4 MB does not significantly improve the performance.
Index creation pre-extension

The index creation performance comparison of a 400 GB table is as follows:
Conclusions:
- Index creation pre-extension can bring a 30% performance improvement in the index creation scenario.
- Increasing the index creation pre-extension size beyond the default value of 4 MB does not significantly improve the performance.