pg_fastload 1.0.0 - parallel bulk loading for PostgreSQL

From: yuansong <yyuansong(at)126(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: pg_fastload 1.0.0 - parallel bulk loading for PostgreSQL
Date: 2026-11-10 23:43:15
Message-ID: 6d239238.115.1a1c7d82cb7.Coremail.yyuansong@126.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi all,

I would like to announce pg_fastload 1.0.0, a bulk loader for PostgreSQL that
parallelises the loading pipeline across background workers while keeping the
load inside a single transaction.

Repository: https://github.com/yuansong/pg_fastload
Release: https://github.com/yuansong/pg_fastload/releases/tag/v1.0.0
License: BSD 3-Clause (it is a source-level fork of pg_bulkload 3.1.23,
whose copyright notices are retained)

The problem
-----------

pg_bulkload and COPY both run the whole load in one backend: read, parse,
form the tuple, write. On a 16-core machine that leaves 15 cores idle, and
the parse step is usually the expensive one -- for CSV it is a byte-at-a-time
state machine plus a type input function per column.

What pg_fastload does
---------------------

The load has four stages: (1) read input, (2) parse record and split fields,
(3) form the tuple, (4) insert. Stages 1-3 move to background workers; stage 4
stays serial in the backend that issued the load:

one reader worker -> N parser workers -> leader writes

Design points that matter in practice:

* The input file is read exactly once. A single reader scans sequentially
and hands chunks to the workers over shm_mq; the workers never open the
file. Total read volume stays at 1x the file size, so the speedup does not
invert once the data is larger than the page cache.
* It is still one transaction. No second connection, no early visibility of
uncommitted rows, and a bad row aborts the whole load rather than leaving a
worker-timing-dependent subset behind.
* 13 conditions the parallel path cannot honour (FILTER / FORCE_NOT_NULL,
explicit ENCODING, NOT NULL or CHECK constraints, PARSE_ERRORS tolerance,
STDIN input, TUPLE/FUNCTION input, ...) fall back to the serial path
automatically, with one INFO line saying why.
* The serial path itself was optimised: records without quotes or CRs take a
memchr bulk split instead of the byte-at-a-time loop.
* Every load and export publishes progress into a cluster-wide view
(pgfastload.pg_fastload_progress): rows, bytes, rates, elapsed -- and, when
a row total is known (a finite LIMIT, or a TABLE= export estimated from
pg_class.reltuples), a percent and an ETA. Long loads on big files are
trackable from any session, or streamed as NOTICEs with PROGRESS_NOTICE=ON.
Export logs also record the MVCC snapshot fingerprint (xmin/xmax/cid), so
every output file is auditable back to the exact snapshot that produced it.

Results
-------

1 GiB TPC-H lineitem CSV, 7,375,042 rows, 16 vCPU, warm cache, WRITER=DIRECT,
no indexes, median of 3 runs. Same file and same empty table for every row.

PARALLEL=OFF 5.81 s 1.00x serial 1.43x COPY FROM
PARALLEL_WORKERS=4 1.58 s 3.68x 5.27x
default (15 workers) 1.37 s 4.24x 6.07x

reference: upstream pg_bulkload 3.1.23 6.98 s
reference: COPY ... FROM 8.32 s
reference: COPY into an UNLOGGED table 7.51 s

Where the time goes (profile build, shares not absolutes): on the serial path
68% is type conversion (the same per-field work COPY does), 15% field split,
19% writing; on the parallel path the leader's serial insert (~1.1 s/GiB: page
fill + WAL) is the floor, which is why more than ~8 workers stops helping.

Fixed-length input (100-byte records) is also parallel: 1 GiB serial 1.62 s,
0.88 s with 8 workers. That mode has no COPY counterpart, because COPY cannot
read an unframed fixed-length record. A full loader x writer-mode x format
matrix (3 alternating rounds, medians) puts every mode side by side:

CSV BINARY FIXED
upstream BUFFERED 9.22 4.08 3.95
upstream DIRECT 6.98 1.62 1.59
upstream PARALLEL 6.63 1.39 1.42 <- upstream's fastest mode
pg_fastload OFF 5.84 1.58 1.63
pg_fastload w8 1.42 0.88 0.90
pg_fastload auto 1.40 0.90 0.93
COPY ... FROM 8.34 (n/a) (n/a)

Upstream's own fastest writer mode is PARALLEL (about 13% ahead of its DIRECT),
so the honest headline is 1.58x over the best upstream mode and 4.4-4.6x over
its slowest -- not the 2x+ that a DIRECT-only comparison suggests.

Export goes the other way with the same engine: pg_fastload_export() writes a
table or any SELECT to CSV/BINARY/FIXED; 7.4M rows (1.13 GiB) take 4.08 s
serial and 0.42 s with 15 workers, i.e. 9.33x server-side COPY TO. The export
scan runs under one MVCC snapshot (serialized to the parallel workers), so an
export under concurrent writes is consistent and reproducible.

Against upstream pg_bulkload 3.1.23, the full list of differences:

* parallel CSV and parallel fixed-length loading (4.7x / 1.6x vs upstream's
best mode; serial path alone is 1.22x upstream with identical semantics);
* load and export progress view with percent/ETA, plus NOTICE streaming;
* SKIP_INDEX=ON: drops the target's secondary indexes inside the load
transaction and prints the verbatim CREATE INDEX statements to re-create
them -- on an indexed table that is about half the load time back, without
hand-editing control files;
* no spool busy-work on unindexed tables (measurable but small);
* stage-level profiling build; same control-file keywords, one branch for
PG 15-19.

Compatibility and limits
------------------------

* Control-file keywords, TYPE values, log format and WRITER modes are
unchanged from pg_bulkload; PARALLEL=ON / PARALLEL_WORKERS / SKIP_INDEX
are additive.
* PostgreSQL 15, 16, 17, 18 and 19 from one branch; differences are resolved
at compile time with PG_VERSION_NUM.
* Index maintenance during a load costs ~1.9 s per GiB for one btree and
does not parallelise. SKIP_INDEX=ON now automates the drop-and-recreate
workflow; constraint-backed indexes (PK/UNIQUE/EXCLUSION) are refused
rather than silently removed.
* The leader's serial insert is the floor: at 1.37 s/GiB the remaining
bottleneck is page fill plus WAL, not parsing, which is why more workers do
not help beyond ~8.

Method, fairness controls and the full result set (cold cache, fixed length,
export, historical tiers) are in docs/BENCHMARK.md in the repository. All the
raw run logs ship with the release archive.

Feedback is very welcome -- especially counter-examples on other hardware,
larger-than-RAM datasets, or workloads where the parallel path falls back more
often than expected.

Regards,
Yuansong

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2026-11-10 23:45:59 Re: pg_fastload 1.0.0 - parallel bulk loading for PostgreSQL
Previous Message Laurenz Albe 2026-11-09 14:42:03 Re: Request for Official Technical Clarification on PostgreSQL statement_timeout Error