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
