| From: | pg_fastload via PostgreSQL Announce <announce-noreply(at)postgresql(dot)org> |
|---|---|
| To: | PostgreSQL Announce <pgsql-announce(at)lists(dot)postgresql(dot)org> |
| Subject: | pg_fastload 1.0.0 released: parallel bulk loading for PostgreSQL |
| Date: | 2026-11-17 08:22:49 |
| Message-ID: | 179490376991.1269.4993182119967596082@wrigleys.postgresql.org |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-announce |
pg_fastload 1.0.0 is a parallel bulk loader for PostgreSQL, 1.58x faster than the best pg_bulkload mode and 6.07x faster than COPY.
It parallelises the loading pipeline across background workers while keeping the load inside a single transaction. It is a source-level fork of pg_bulkload 3.1.23, so control-file syntax, `TYPE` values, log format and `WRITER` modes are unchanged.
* Repository: [github.com/yuansong/pg_fastload](https://github.com/yuansong/pg_fastload)
* Release: [pg_fastload v1.0.0](https://github.com/yuansong/pg_fastload/releases/tag/v1.0.0)
* License: BSD 3-Clause, with the upstream pg_bulkload copyright notices 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 followed by a type input function per column.
## How it works
The load has four stages: read input, parse record and split fields, form the tuple, and 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 fall back automatically**, with one `INFO` line saying why: `FILTER` and `FORCE_NOT_NULL`, explicit `ENCODING`, `NOT NULL` or `CHECK` constraints, `PARSE_ERRORS` tolerance, `STDIN` input, `TUPLE` and `FUNCTION` input, and others.
* **The serial path itself was optimised.** Records without quotes or CRs take a `memchr` bulk split instead of the byte-at-a-time loop.
## Progress reporting and export
Every load and export publishes progress into a cluster-wide view, `pgfastload.pg_fastload_progress`: rows, bytes, rates and elapsed time. When a row total is known (a finite `LIMIT`, or a `TABLE=` export estimated from `pg_class.reltuples`), it also reports a percent and an ETA. Long loads are trackable from any session, or streamed as `NOTICE`s with `PROGRESS_NOTICE=ON`.
Export logs additionally record the MVCC snapshot fingerprint (`xmin`/`xmax`/`cid`), so every output file is auditable back to the exact snapshot that produced it. `pg_fastload_export()` writes a table or any `SELECT` to CSV, BINARY or FIXED output.
## 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.
configuration time vs serial vs COPY FROM
PARALLEL=OFF 5.81 s 1.00x 1.43x
PARALLEL_WORKERS=4 1.58 s 3.68x 5.27x
default (15 workers) 1.37 s 4.24x 6.07x
reference: upstream pg_bulkload 6.98 s
reference: COPY ... FROM 8.32 s
reference: COPY into UNLOGGED table 7.51 s
A full loader x writer-mode x format matrix (3 alternating rounds, medians):
CSV BINARY FIXED
upstream pg_bulkload BUFFERED 9.22 4.08 3.95
upstream pg_bulkload DIRECT 6.98 1.62 1.59
upstream pg_bulkload PARALLEL 6.63 1.39 1.42
pg_fastload PARALLEL=OFF 5.84 1.58 1.63
pg_fastload 8 workers 1.42 0.88 0.90
pg_fastload default 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, rather than the 2x+ that a `DIRECT`-only comparison suggests.
Export shows the same effect in the other direction: 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.
Method, fairness controls and the full result set (cold cache, fixed-length, export, historical tiers) are in `docs/BENCHMARK.md` in the repository, and all raw run logs ship with the release archive.
## Differences from upstream pg_bulkload 3.1.23
* Parallel CSV and parallel fixed-length loading, 4.7x and 1.6x over upstream's best mode. The serial path alone is 1.22x upstream with identical semantics.
* Load and export progress view with percent and 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, which recovers about half the load time on an indexed table.
* No spool busy-work on unindexed tables.
* Stage-level profiling build, and one branch for PostgreSQL 15 through 19.
## Compatibility and limits
* `PARALLEL=ON`, `PARALLEL_WORKERS` and `SKIP_INDEX` are additive; everything else is compatible with pg_bulkload.
* 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 about 1.9 s per GiB for one btree and does not parallelise. `SKIP_INDEX=ON` automates the drop-and-recreate workflow; constraint-backed indexes (PRIMARY KEY, 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.
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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Meob PostgreSQL Tools via PostgreSQL Announce | 2026-11-17 08:42:34 | PG_CVE 1.1.1 Released |
| Previous Message | Datasentinel via PostgreSQL Announce | 2026-11-15 06:03:55 | Datasentinel 2026.10 released with AI Connector |