34.1. Overview #
Regular INSERT operations are sufficient for populating tables in most scenarios. However, when you need to insert huge volumes of data into your database, Postgres Pro offers more efficient solutions.
At high ingestion rates, regular INSERT operations into the same table can suffer from higher latency even though they do not fully utilize CPU and disk resources. Increasing the number of backend processes does not scale the performance linearly and may even further increase the latency.
Data insertion performance can be constrained by the following factors:
page-level locks
a high rate of WAL writes
contention for shared structures, such as the free space map (FSM)
Postgres Pro provides two features for bulk data insertion that are designed for different scenarios:
Append-optimized tables (AOTs) are designed for high-volume continuous flow of data, such as logs or metrics.
Multi-segment insertion is designed for one-off bulk data insertion into the same table in multiple threads simultaneously, e.g. data migration or restoration from backups.
Warning
Multi-segment insertion is currently experimental and is not recommended for using in production.
The table below describes the advantages of both bulk insertion methods.
Table 34.1. Comparison of bulk insertion solutions
| Features | Append-Optimized Tables | Multi-Segment Insertion |
|---|---|---|
Table locks during | Not locked | Locked |
Supported DML operations | All DML operations are processed, only | Only |
| WAL load optimization | INSERT operations generate fewer WAL records and result in lower WAL contention | Multi-segment insertion generates a single WAL record per page (full-page write) |
| Memory requirements | AOT buffer size depends on the size of inserted rows and requires up to 8 MB of work_mem memory for each table | Data is written in 32-page blocks, which requires 256 KB of work_mem memory per segment |
| Optimization method | Buffering incoming | Multiple processes can insert data into their own segments without blocking each other |
You can use multi-segment insertion with append-optimized tables. For more information, refer to Section 34.4.