34.3. Multi-Segment Insertion #
Warning
Multi-segment insertion is currently experimental and is not recommended for using in production. For information about its limitations, refer to Section 34.3.1.
Postgres Pro allows you to bulk insert data into the same table in multiple threads simultaneously, bypassing the Postgres Pro buffer manager. This feature is designed for the following scenarios:
data migration from a consistent database
restoring data from pg_dump
loading data from a consistent backup
Important
Multi-segment insertion is designed for bulk data insertion into a single table, for example during migration. UPDATE, DELETE, and all other operations on the specified table, except read-only ones, are blocked for the entire duration of multi-segment insertion and will wait for its completion — i.e., until the table locks are released.
Since multi-segment insertion completely blocks all operations on the specified table except read-only queries, this functionality is not intended for continuous use in a production environment.
Using multi-segment insertion requires no special preparation or custom parameters when connecting a new client. Any client backend process can start inserting data in multi-segment mode.
Before writing data to a table, specify the name of the table in the multi_segment_relname parameter. To perform multi-segment insertion into several tables, specify a comma-separated list of table names. For example:
CREATE TABLE table_1 (num NUMERIC); CREATE TABLE table_2 (name TEXT); set multi_segment_relname = table_1, table_2;
You can set this parameter multiple times in a session so that a single client backend can perform multi-segment insertion into several tables, switching between its dedicated table segments.
The operation sequentially validates the specified table names and aborts at the first invalid name, reporting it in the error message.
Tip
Multi-segment insertion performs better if wal_compression is enabled due to full page image (FPI) compression.
To avoid lock conflicts between the worker processes that simultaneously perform insertions into a single table, each backend creates a dedicated table segment for inserting data. The backends that are not performing multi-segment insertion cannot access this segment and the corresponding table, even for reading. The backends that are currently using the multi-segment mechanism can read data from the dedicated table segments that do not belong to them, but they can write data only to their own segments. When a segment is full, the backend immediately creates another segment and continues writing data to the new segment.
Before performing the first INSERT, a backend acquires a session-level exclusive lock on the table specified in the multi_segment_relname parameter. This lock blocks access to the table for all processes, including autovacuum, except the backends that perform multi-segment insertion to this table. No operations other than multi-segment insertion are available.
Important
The table is unlocked when all backends that performed multi-segment insertion to this table disconnect from the database.
Data pages that have been inserted via the multi-segment mechanism act like regular Postgres Pro data pages: they are synced to disk during the COMMIT or CHECKPOINT operations for a higher level of safety.
34.3.1. Limitations of Multi-Segment Insertion #
Multi-segment insertion cannot be used with the following relations:
relations with unique indexes
relations with row-level triggers
catalog relations
TOAST relations
temporary relations
foreign relations