Multi-Insert Overview: Bulk Data Loading Optimization - PolarDB

Multi-Insert Optimization speeds up bulk data loading in PolarDB for PostgreSQL by automatically replacing slow, row-by-row inserts with a single, efficient batch operation — reducing Write-Ahead Log (WAL) volume and I/O overhead for write-heavy workloads.

Version requirements

This feature requires one of the following minimum revision versions:

PolarDB for PostgreSQL 16: revision version 2.0.16.8.3.0 or later
PolarDB for PostgreSQL 15: revision version 2.0.15.12.4.0 or later
PolarDB for PostgreSQL 14: revision version 2.0.14.13.28.0 or later

To check your cluster's revision version, run SHOW polardb_version; in your cluster or use the PolarDB console. If the revision version does not meet the requirements, update it.

How it works

The problem with row-by-row inserts

Standard PostgreSQL's Table Access Method (AM) exposes two write interfaces: tuple_insert for single rows and multi_insert for batches. Only COPY FROM is fully optimized to use the multi_insert path. Other bulk-loading commands — such as CREATE TABLE ... AS SELECT — fall back to tuple_insert, repeating a full page-lock-write-unlock cycle for every single row. At scale, this repeated overhead limits throughput and inflates the WAL.

PolarDB's batch write approach

PolarDB extends the Table Access Method framework so that more SQL commands can use the high-performance multi_insert path. Instead of processing rows one at a time, PolarDB buffers a batch of rows in memory and writes them to a page in a single, consolidated operation. The underlying algorithm and WAL format within the multi_insert implementation are also further optimized for even greater efficiency.

Step	Standard method (row-by-row)	PolarDB Multi-Insert Optimization
1	Prepares metadata and calculates space for a single row.	Buffers a batch of rows in memory and prepares their metadata at once.
2	Finds a page, locks it, writes one row, and unlocks the page.	Finds a page and acquires a single lock for the entire batch.
3	Repeats this loop for every row until all rows are inserted.	Fills the page with as many rows from the buffer as possible, then releases the lock once. If rows remain, repeats from step 2 for the next batch.

WAL efficiency

Batch writes also consolidate WAL output. Row-by-row inserts generate one Heap/INSERT WAL record per row:

-[ 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 0

Multi-insert writes generate a single Heap2/MULTI_INSERT record for an entire batch written to the same page:

-[ 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

The ntuples: 8 field in the MULTI_INSERT record shows that a single WAL entry covers 8 rows. The INSERT record covers only one row (off: 8). For an 8-row insert:

Metric	Standard method (row-by-row)	PolarDB Multi-Insert Optimization
WAL records	8 separate records	1 single record
Total WAL size	~488 bytes (8 × 61 bytes)	~194 bytes

This represents approximately a 60% reduction in WAL volume, which directly lowers I/O overhead and speeds up command execution.

Supported SQL commands

Multi-Insert Optimization is applied automatically to the following commands:

Enable and configure

The feature is controlled by a single parameter and is enabled by default:

SHOW polar_enable_tableam_multi_insert;
 polar_enable_tableam_multi_insert
-----------------------------------
 on
(1 row)

Behavior by `wal_level`

The active write path depends on your cluster's wal_level setting:

`wal_level` value	Behavior
`logical`	Multi-Insert Optimization is active when `polar_enable_tableam_multi_insert` is `on`.
`replica` or `minimal`	PolarDB uses a different, specialized bulk import infrastructure instead. The `polar_enable_tableam_multi_insert` parameter has no effect.

What's next

Bulk import infrastructure — learn about PolarDB's specialized bulk write path for wal_level = replica or minimal.
Version management — check and update your cluster's revision version.