PolarDB:Read-ahead and pre-extension

Heap table read-ahead

PostgreSQL normally loads pages into the buffer pool one 8 KB page at a time. On PFS, this small-read pattern is inefficient. When two or more pages need to be read, PolarDB batches them into a single 128 KB read, reducing the total number of I/O operations.

The process runs in four steps:

1. Allocate N buffers from the buffer pool.

2. Allocate a contiguous memory region of N × page size using palloc, referred to as p.

3. Read N × page size of data from the heap table into p using PFS.

4. Copy the N pages from p into the N buffers.

Subsequent reads for those pages hit the buffer pool directly, eliminating further disk I/O.

The following diagram shows the data flow:

Heap table pre-extension

In standard PostgreSQL, extending a tablespace applies and extends 8 KB pages one by one. Even with batch extension, N pages still require N I/O operations. On PFS, where the minimum extension size is 4 MB, this results in excessive I/O overhead.

Heap table pre-extension resolves this by extending 4 MB at a time using PFS's bulk write interface. The process runs in three steps:

1. Allocate N buffers from the buffer pool without triggering any file system extension.

2. Extend the file by writing all-zero pages in a single batch using the PFS file write interface.

3. Initialize the pages one by one, identify available space, and end the pre-extension.

Index creation pre-extension

Index creation pre-extension follows the same approach as heap table pre-extension, but skips the buffer allocation step because indexes are built in the buffer pool before being flushed to disk.

The process runs in two steps:

1. Extend the file by writing all-zero pages in a single batch using the PFS file write interface.

2. Write the index pages built in the buffer pool to the file system.

Supported index types: B-tree, GIN, GiST, SP-GiST, and Bloom.

In older versions of PolarDB for PostgreSQL (Compatible with Oracle), index creation pre-extension supports only B-tree indexes.

Heap table read-ahead

The polar_bulk_read_size parameter controls heap table read-ahead. The default value is 128 KB, which is the optimal value for PFS. Increasing beyond 128 KB produces no further improvement.

To disable heap table read-ahead:

ALTER SYSTEM SET polar_bulk_read_size = 0;
SELECT pg_reload_conf();

To re-enable heap table read-ahead at the default size:

ALTER SYSTEM SET polar_bulk_read_size = '128 KB';
SELECT pg_reload_conf();

To verify the current value:

SHOW polar_bulk_read_size;

Heap table pre-extension

The polar_heap_bulk_extend_size parameter controls heap table pre-extension. The default value is 4 MB, which is the optimal value for PFS. Increasing beyond 4 MB produces no further improvement.

In older versions of PolarDB for PostgreSQL (Compatible with Oracle), the equivalent parameter is polar_bulk_extend_size.

To disable heap table pre-extension:

ALTER SYSTEM SET polar_heap_bulk_extend_size = 0;
SELECT pg_reload_conf();

To re-enable heap table pre-extension at the default size:

ALTER SYSTEM SET polar_heap_bulk_extend_size = '4 MB';
SELECT pg_reload_conf();

To verify the current value:

SHOW polar_heap_bulk_extend_size;

Index creation pre-extension

The polar_index_bulk_extend_size parameter controls index creation pre-extension. The default value is 4 MB, which is the optimal value for PFS. Increasing beyond 4 MB produces no further improvement.

In older versions of PolarDB for PostgreSQL (Compatible with Oracle), the equivalent parameter is polar_index_create_bulk_extend_size. Only B-tree indexes were supported in those versions.

To disable index creation pre-extension:

ALTER SYSTEM SET polar_index_bulk_extend_size = 0;
SELECT pg_reload_conf();

To re-enable index creation pre-extension at the default size:

ALTER SYSTEM SET polar_index_bulk_extend_size = '4 MB';
SELECT pg_reload_conf();

To verify the current value:

SHOW polar_index_bulk_extend_size;

Heap table read-ahead

Performance comparison for vacuum on a 400 GB table:

Performance comparison for sequential scan on a 400 GB table:

Conclusions:

• Heap table read-ahead doubles or triples vacuum and sequential scan performance.

• Increasing polar_bulk_read_size beyond the default 128 KB produces no further improvement.

Heap table pre-extension

Performance comparison for data loading on a 400 GB table:

Conclusions:

• Heap table pre-extension doubles data loading performance.

• Increasing polar_heap_bulk_extend_size beyond the default 4 MB produces no further improvement.

Index creation pre-extension

Performance comparison for creating indexes on a 400 GB table:

Conclusions:

• Index creation pre-extension increases index creation performance by 30%.

• Increasing polar_index_bulk_extend_size beyond the default 4 MB produces no further improvement.