PolarDB for PostgreSQLClusters provide bulk import infrastructure.
Scope
The following versions of PolarDB for PostgreSQL are supported:
PostgreSQL 18(minor engine version 2.0.18.0.1.0 or later)
PostgreSQL 17(minor engine version 2.0.17.2.1.0 or later)
PostgreSQL 16(minor engine version 2.0.16.6.2.0 or later)
PostgreSQL 15(minor engine version 2.0.15.12.4.0 or later)
PostgreSQL 14(minor engine version 2.0.14.13.28.0 or later)
You can view the minor engine version number in the console or by running the
SHOW polardb_version;statement.If the minor engine version does not meet the requirement, use one of the following solutions for your database engine:
PostgreSQL 15 or later: upgrade the minor engine version.
PostgreSQL 14 or earlier: use create or refresh a materialized view to implement bulk-write materialization.
Background information
When data is bulk imported into a newly created object before the current transaction commits, transaction isolation keeps the object invisible to other processes. Based on the system characteristics of PolarDB for PostgreSQL, targeted optimizations are implemented at each layer of the bulk data import path.
PolarDB for PostgreSQL provides general-purpose bulk import infrastructure for objects that are visible only to the current process. It significantly improves bulk import performance by bypassing buffer management, constructing pages in batches, extending storage pages in batches, recording WAL logs in batches, and making the synchronous path asynchronous.
Usage notes
The following statements currently use this infrastructure:
CREATE INDEXREINDEXVACUUM FULLALTER TABLE SET TABLESPACECREATE MATERIALIZED VIEWREFRESH MATERIALIZED VIEW- Note
CREATE TABLE AS/SELECT INTO statements use the bulk import infrastructure depending on the value of the
wal_levelparameter. When the value is lower thanlogical, these statements use the bulk import infrastructure described above by default. When the value islogical, these statements use the bulk insert interface of Multi-insert Table AM infrastructure by default.
How it works
Bypass buffer management
Because imported data does not need to be visible to other processes before the transaction commits, pages can be processed directly in the private memory of the process during page construction instead of being allocated from the shared memory Buffer Pool. Bypassing buffer management effectively avoids lock resource competition and significantly improves page construction efficiency.
Extend pages in batches
Traditional bulk import mechanisms usually write pages one at a time: each time a page becomes full, the storage management layer interface extends storage by a single page. Because cloud storage has high latency, these page-by-page extensions introduce significant I/O wait overhead on the main path.PolarDB for PostgreSQL pre-aggregates data for multiple pages in the private memory of the process. After the accumulated data reaches a threshold, the storage management layer interface extends the pages in a batch, which effectively amortizes the latency of single-page extensions.
Record WAL logs in batches
In traditional bulk import mechanisms, both heap table and index import operations usually record data row by row, generating a separate WAL log record for each row. When a process constructs pages in private memory, it writes multiple rows into a single page and pre-aggregates multiple pages that wait to be persisted. Therefore, PolarDB for PostgreSQL uses XLOG_FPI-type WAL logs and records them in a batch after this group of pages is constructed. This type of WAL log records the entire page data instead of the incremental modifications on a page. Compared with traditional row-by-row recording, it is more compact and effectively saves WAL log metadata overhead.
Make the main path asynchronous
After the current transaction commits, the pages constructed in private memory must become visible to other processes. In theory, all constructed pages must be persisted before the transaction commits, but this significantly increases the latency of the main path. To address this, PolarDB for PostgreSQL immediately copies the pages to the Buffer Pool and marks them as dirty pages, which allows the commit to return. Background parallel dirty page flushing processes then write the pages back asynchronously, taking full advantage of the high-throughput dirty page flushing capability of PolarDB to run page construction and background dirty page flushing in parallel.
This mechanism also guarantees data consistency in system crash scenarios. Because FPI-type WAL logs are recorded in advance, committed data can be recovered.
Usage
The polar_bulk_write_maxpages parameter controls the maximum number of pages pre-aggregated in the private memory of the process. When the number of pre-aggregated pages reaches this value, batch page extension and batch WAL log recording are triggered, and the pages are copied to the Buffer Pool for dirty page flushing. The default value is already optimal and usually requires no attention.
SHOW polar_bulk_write_maxpages;
polar_bulk_write_maxpages
---------------------------
1MB
(1 row)