pg_fastload 1.0.0 released: parallel bulk loading for PostgreSQL

2026-11-17来源 PostgreSQL NewsEnglish

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.

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 NOTICEs 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

原文:https://www.a8q9.cn/en/article/pg-fastload-1-0-0-released-parallel-bulk-loading-for-postgresql-6874db.html

相关文章