PolarDB:Read-ahead and pre-extension

Heap table read-ahead

When two or more pages are needed, read-ahead is triggered automatically. The implementation proceeds in four steps:

1. Allocate N buffers from the buffer pool.

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

3. Use PFS to read N × page size of data from the heap table into p in a single I/O.

4. Copy the N pages from p into the N buffers allocated in step 1.

Subsequent read operations hit the buffer pool directly. The following diagram shows the data flow:

Heap table pre-extension

Pre-extension separates buffer allocation from file system extension. The implementation proceeds in three steps:

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

2. Use the PFS file write interface to extend 4 MB of pages in a single batch write of all-zero pages.

3. Initialize each page individually, record available space, and mark the pre-extension complete.

Index creation pre-extension

Index creation pre-extension follows the same batching approach but does not require buffer allocation:

1. Use the PFS file write interface to extend 4 MB of index pages in a single batch write of all-zero pages.

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

Heap table read-ahead

The polar_bulk_read_size parameter controls the read-ahead batch size.

To disable heap table read-ahead:

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

To enable heap table read-ahead at the default 128 KB:

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

This setting is most impactful during sequential scans and vacuum operations on large tables.

Heap table pre-extension

The polar_heap_bulk_extend_size parameter controls the pre-extension batch size. In PostgreSQL 11, 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 enable heap table pre-extension at the default 4 MB:

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

This setting is most impactful during bulk data loading, for example, when running COPY on large tables.

Index creation pre-extension

The polar_index_bulk_extend_size parameter controls the index pre-extension batch size. In PostgreSQL 11, the equivalent parameter is polar_index_create_bulk_extend_size.

In PostgreSQL 11, index creation pre-extension supports only B-tree indexes, regardless of the parameter value. Setting the parameter to a non-zero value on PostgreSQL 11 has no effect on GIN, GiST, SP-GiST, or Bloom indexes.

To disable index creation pre-extension:

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

To enable index creation pre-extension at the default 4 MB:

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

This setting is most impactful during CREATE INDEX on large tables.

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 128 KB produces no significant additional 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 4 MB produces no significant additional improvement.

Index creation pre-extension

Performance comparison for creating indexes on a 400 GB table:

Conclusions:

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

• Increasing polar_index_bulk_extend_size beyond 4 MB produces no significant additional improvement.