Databases & Storage

pg_fastload 1.0.0 brings parallel bulk loading to PostgreSQL, 6x faster than COPY

Key Takeaway

pg_fastload parallelizes CSV and fixed-length loading across background workers while preserving single-transaction semantics, achieving 6.07x speedup over standard COPY on a 16-core system.

Summary

pg_fastload 1.0.0, a BSD 3-Clause fork of pg_bulkload 3.1.23, introduces parallel bulk loading for PostgreSQL by distributing read, parse, and tuple-formation stages across background workers while keeping the final insert stage serial in the issuing backend. The tool maintains full compatibility with pg_bulkload's control-file syntax, TYPE values, log format, and WRITER modes, allowing existing users to migrate without configuration changes. On a 16-vCPU system with warm cache loading 1 GiB of TPC-H lineitem CSV data, the default 15-worker configuration completed in 1.37 seconds compared to 8.32 seconds for COPY and 6.98 seconds for upstream pg_bulkload's fastest mode.

The design preserves transactional integrity: the load remains a single transaction with no early visibility of uncommitted rows, and failed rows abort the entire load rather than leaving partial results. A single sequential reader hands chunks to workers via shm_mq, ensuring the input file is read exactly once and preventing performance inversion when data exceeds page cache. Thirteen conditions automatically trigger fallback to the optimized serial path, including FILTER, explicit ENCODING, NOT NULL or CHECK constraints, PARSE_ERRORS tolerance, STDIN input, and TUPLE/FUNCTION input formats.

Parallel pipeline architecture and performance characteristics

  • Four-stage pipeline with worker distribution: Stages 1-3 (read input, parse records and split fields, form tuples) execute in background workers; stage 4 (insert) remains serial in the leader backend, with one reader worker feeding N parser workers via shm_mq shared-memory message queues.
  • Single-transaction semantics preserved: Unlike multi-connection loading approaches, pg_fastload maintains one transaction boundary with no early row visibility; a bad row aborts the complete load rather than leaving timing-dependent subsets committed.
  • Serial path optimizations: When parallel mode is unavailable, records without quotes or CR characters use memchr bulk splitting instead of byte-at-a-time state machines, yielding 1.22x speedup over upstream pg_bulkload with identical semantics.
  • Progress visibility and export features: Loads and exports publish to pgfastload.pg_fastload_progress with rows, bytes, rates, elapsed time, percent, and ETA; NOTICE streaming via PROGRESS_NOTICE=ON enables real-time monitoring, and exports record MVCC snapshot fingerprints (xmin/xmax/cid) for auditability.
  • SKIP_INDEX automation: SKIP_INDEX=ON drops secondary indexes inside the load transaction and emits verbatim CREATE INDEX statements for recreation, recovering approximately half the load time on indexed tables while refusing to drop constraint-backed indexes (PRIMARY KEY, UNIQUE, EXCLUSION).

Why it matters

Database administrators running large PostgreSQL data migrations, ETL pipelines, or analytical data loads gain a production-ready tool that exploits modern multi-core servers without sacrificing transactional safety or operational visibility. The pg_bulkload compatibility and single-branch support for PostgreSQL 15 through 19 reduce adoption friction across supported versions, while the automatic fallback to serial execution for unsupported conditions prevents silent correctness failures.

Original reference: www.postgresql.org →