PolarDB for PostgreSQL The interface definitions of the Table AM of a cluster include a single-row insert interface and a bulk insert interface. When you need to insert a large amount of tuple data, you can enable the bulk insert interface to improve data insertion efficiency.
Scope of application
The supported versions of PolarDB for PostgreSQL are as follows:
PostgreSQL 17 (minor engine version 2.0.17.2.2.1 or later)
PostgreSQL 16 (minor engine version 2.0.16.8.3.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 in the console or by running the SHOW polardb_version; statement. If the minor engine version does not meet the requirements, upgrade the minor engine version。
Background
An Access Method (AM) is responsible for defining and implementing how tables and indexes are stored in PostgreSQL. The executor only needs to pass data rows or index rows to Table AM and Index AM respectively, without concerning how the AM is implemented internally.
PostgreSQL 12 and later versions introduced an extensible Table AM. Currently, only Heap AM is supported as a built-in implementation. Heap AM supports the following two interface definitions:
Bulk insert interface: processes multiple rows of data at a time and supports only the
COPY FROMsyntax.Single-row insert interface: processes one row of data at a time and applies to other syntaxes that insert data into a table, except for the
COPY FROMsyntax.
However, when a large number of tuples need to be inserted, the algorithm efficiency of single-row insertion is far lower than that of bulk insertion. In addition, bulk data insertion involves staging tuples in batches and requires complete lifecycle control.
To address the preceding issues, PolarDB for PostgreSQL extends a new optional Table AM interface and implements the interface for Heap AM. The new Table AM interface achieves the following optimizations:
Implements complete lifecycle control for bulk insertion.
Uses a more efficient insertion algorithm and WAL log format within Heap AM.
As a result, other syntaxes that may insert multiple rows of data can also use the bulk insert capability through this set of Table AM interfaces. The single-row insert interface and the bulk insert interface of Heap AM also support logical replication of heap tables.
Usage notes
The following syntaxes currently support the bulk insert Table AM:
Whether the preceding syntaxes use the bulk insert interface is related to the value of the wal_level parameter. When the value is logical, the preceding syntaxes use the bulk insert interface by default. When the parameter is set to another value, the bulk import infrastructure is used to optimize bulk import operations.
How it works
Buffer layer algorithm
Single-row insert algorithm
The procedure is as follows:
Fill in the tuple information.
Calculate the free space required for the tuple.
Find a page that can accommodate the currently inserted tuple.
Lock the page and place the tuple on the page.
Mark the page as dirty.
Generate a WAL record for the current page modification.
Release the page lock.
Bulk insert algorithm
The procedure is as follows:
Stage a batch of tuples in memory.
Fill in the tuple information for the batch.
Find a page that can accommodate the currently inserted tuples.
Lock the page and place the tuples on the page.
If the page has enough space, continue placing tuples until the page runs out of space or the staged tuples are exhausted.
Mark the page as dirty.
Generate a WAL record for the current page modification.
Release the page lock.
WAL log format
Single-row insertion records a log of the Heap INSERT type:
-[ RECORD 1 ]----+-------------------------------------------- start_lsn | 0/40BE24E0 end_lsn | 0/40BE2520 prev_lsn | 0/40BE24B8 xid | 792 resource_manager | Heap record_type | INSERT record_length | 61 main_data_length | 3 fpi_length | 0 description | off: 8, flags: 0x00 block_ref | blkref #0: rel 1663/5/16412 fork main blk 0Bulk insertion records a log of the Heap2 MULTI_INSERT type. This log type can record multiple rows of data inserted on a page at once:
-[ RECORD 1 ]----+------------------------------------------------------------------ start_lsn | 0/40BE2548 end_lsn | 0/40BE2610 prev_lsn | 0/40BE2520 xid | 793 resource_manager | Heap2 record_type | MULTI_INSERT record_length | 194 main_data_length | 20 fpi_length | 0 description | ntuples: 8, flags: 0x02, offsets: [9, 10, 11, 12, 13, 14, 15, 16] block_ref | blkref #0: rel 1663/5/16412 fork main blk 0
Difference analysis
The bulk insert algorithm can significantly reduce the frequency of page locking, reduce the number of WAL logs generated, and improve the compactness of log content.
Usage
The polar_enable_tableam_multi_insert parameter controls whether to enable the bulk insert Table AM interface. It is enabled by default:
SHOW polar_enable_tableam_multi_insert;
polar_enable_tableam_multi_insert
-----------------------------------
on
(1 row)