Bulk load performance in PostgreSQL

Test setup and table definition

Bulk loading into PostgreSQL can be done in several ways, and the choice between them shows up clearly in a simple benchmark. The table used here is narrow — two columns — but it carries a primary key index, which is the realistic case: dropping indexes and constraints before a load and recreating them afterwards can be slower when the table already holds data.

The test loads 10 million rows of the form (1, '1'), (2, '2') and up to 10000000, against PostgreSQL v16 with its default configuration. Defaults are not ideal for bulk loading, but the comparison between methods is what matters here. The runs were performed on a laptop with Fedora Linux 37, local domain socket connections and ext4 file systems on an NVMe disk.

The six loading methods

One INSERT per transaction

Each statement runs in its own transaction, and every transaction forces PostgreSQL to write WAL out to disk — 10 million I/O requests. This is not a sane bulk-load strategy, but it mirrors how transactional applications insert small amounts of data with no way to bundle requests. Two remedies exist for that case:

• Set synchronous_commit to off for a large speedup, at the risk of losing committed transactions after an operating system crash.
• Set commit_delay above 0 and tune commit_siblings to cut the number of I/O requests. The gain is smaller than with synchronous_commit, but no committed transaction can be lost.

All INSERTs in a single transaction

The same statements wrapped in one transaction across all 10 million rows — a substantial improvement over per-statement commits.

Prepared INSERT in a single transaction

Here the statement is prepared:

and executed repeatedly:

Because PostgreSQL reuses the execution plan instead of planning the INSERT 10 million times, short statements gain noticeably.

Multi-line INSERTs in a single transaction

Several rows go into one statement:

This test inserts 1000 rows per statement, giving 10000 statements in total. Fewer client-server round trips and fewer statements to plan and execute are the payoff.

Prepared multi-line INSERTs in a single transaction

The same approach with a prepared statement:

The intent is to combine both advantages.

COPY

COPY has the reputation of being the fastest route into PostgreSQL. All 10 million rows are loaded with a single COPY statement. Its drawback is that it is non-standard SQL: not every API supports it, and code intended to run against other database systems cannot use it. When the table is created or truncated in the same transaction, COPY (FREEZE) is available — it does not accelerate loading itself, but it avoids hint bit overhead on the rows from the first reader and anti-wraparound autovacuum on the table.

Results

The general picture matches expectations: separate transactions are unbearably slow, prepared statements help small INSERTs, a few large statements beat many small ones, and COPY stands alone at the top. One surprise is that preparing the multi-line statements makes them slower rather than faster. The exact cause is unclear, though overhead from the extended query protocol is a plausible explanation.

Reducing redundant writes

Little can be done to make writes themselves faster, but redundant ones can be avoided:

• max_wal_size is the most important setting, with a default of 1GB. Once more than that amount of WAL has been written since the last checkpoint, PostgreSQL triggers another checkpoint in order to keep crash recovery short. During a bulk load 1GB is quickly exhausted, and checkpoints can arrive every few seconds — overhead that is especially wasteful because the same index pages may be flushed to disk repeatedly.
• checkpoint_timeout should be raised for the same reason. Fewer checkpoints mean fewer redundant writes, and the WAL shrinks as well, since fewer full-page images are written.

In this particular test, the effect of those settings is small: the table only grows, the index values only increase, and B-tree indexes are optimized for exactly that pattern. With UUID primary keys the index modifications would not be localized, and cutting the number of full-page images would matter much more.

Takeaway

For bulk load, COPY is the way to go.