Performance and tuning
David Sterling  

pg_fastload 1.0: Postgres Bulk Load on All Your Cores

A 1 GiB CSV load is not I/O bound, and it never was. Run COPY lineitem FROM '/tmp/lineitem.csv' on a 16-core machine and one backend pins a core at 100% while fifteen cores idle, because read, parse, tuple formation and insert all execute serially inside that single backend. The expensive stage is parsing: a quote-aware state machine for CSV, plus one type input function per column, per row.

As of 2026-11-17 there is a tool that moves that work onto the other cores: pg_fastload 1.0.0, a parallel bulk loader for PostgreSQL. It is a source-level fork of pg_bulkload 3.1.23 (BSD 3-Clause), so control-file keywords, TYPE values, WRITER modes and log format are unchanged — rename two identifiers and your existing .ctl files keep working.

The four stages, and which one parallelises

pg_bulkload splits a load into read input, parse record and split fields, form the tuple, and insert. pg_fastload pushes stages one through three onto N background workers and keeps stage four serial in the backend that issued the load. One reader scans the file once and hands ~512 KiB chunks to the workers over shm_mq; each worker parses and forms finished tuples; the leader inserts them.

Two design details matter more than the headline speedup. The file is read exactly once — workers never open it — so total read volume stays at 1x the file size and the gain does not invert once the data outgrows the page cache. And the whole load stays inside one transaction: no second connection, no early visibility of uncommitted rows, and a bad row rolls back the load instead of leaving a timing-dependent subset behind.

The benchmark numbers, read honestly

The published figures come from a 1 GiB TPC-H lineitem CSV, 7,375,042 rows, 16 vCPU, warm cache, no indexes, median of three runs:

  • COPY ... FROM: 8.32 s
  • PARALLEL=OFF (serial path): 5.81 s — already 1.43x COPY
  • PARALLEL_WORKERS=4: 1.58 s — 5.27x COPY
  • default (online CPUs − 1 = 15 workers): 1.37 s — 6.07x COPY

Against upstream pg_bulkload the honest headline is 1.58x, not 2x: its fastest mode, WRITER=PARALLEL, runs the same file in 6.63 s, and the release notes say so plainly — a good sign for the methodology in docs/BENCHMARK.md.

Now the part the numbers do not say out loud. Index maintenance costs about 1.9 s per GiB per btree, and it does not parallelise. Add three secondary indexes and your 1.37 s load becomes roughly 7 s, at which point parsing is no longer the bottleneck and the 6x evaporates. The leader’s serial insert is the floor: at 1.37 s/GiB the remaining cost is page fill plus WAL, not parsing. Parallel loading does not make your database write faster; it makes your CPUs read faster.

Install and first parallel load

You build it against your server’s pg_config; one branch serves PostgreSQL 15 through 19. shared_preload_libraries is not required, but max_worker_processes must be at least workers + 1 (N parsers plus one reader), and changing it needs a restart. Start at four to eight workers on a memory-tight box: the DSM queues cost roughly 144 MiB at fifteen.

make USE_PGXS=1 PG_CONFIG=/usr/pgsql-18/bin/pg_config
sudo make USE_PGXS=1 PG_CONFIG=/usr/pgsql-18/bin/pg_config install

psql -c "CREATE EXTENSION pg_fastload;"
psql -c "ALTER SYSTEM SET max_worker_processes = 32;"   -- then restart

The control file is pg_bulkload, plus two keywords

PARALLEL=ON and PARALLEL_WORKERS are additive; everything else is inherited. SKIP_INDEX=ON is the keyword I would reach for in any nightly reload: it drops the target’s secondary indexes inside the load transaction and prints the verbatim CREATE INDEX statements to rebuild them, recovering roughly half the load time on an indexed table. Constraint-backed indexes (primary key, unique, exclusion) are refused rather than silently dropped.

OUTPUT            = public.order_line
INPUT             = /data/ingest/order_line.csv     # server-side absolute path
TYPE              = CSV
WRITER            = DIRECT
DELIMITER         = ','
PARALLEL          = ON
PARALLEL_WORKERS  = 8
SKIP_INDEX        = ON
pg_fastload -h db-01 -d warehouse -U loader order_line.ctl
# one-off override, no file edit needed:
pg_fastload order_line.ctl -o "PARALLEL_WORKERS=4"

One trap door: the control file lives on the client, but INPUT is always a server-side path opened by the postgres OS user, so a client-local path fails with could not open file.

Watch a long load from a second session

Every load publishes progress to a cluster-wide view, so you can tell a working load from a hung one without guessing.

-- session 1
SELECT * FROM pgfastload.pg_fastload(ARRAY[
  'INPUT=/data/ingest/order_line.csv',
  'OUTPUT=public.order_line_stage',
  'TYPE=CSV','WRITER=DIRECT','PARALLEL=ON','PARALLEL_WORKERS=8']);

-- session 2
SELECT pid, relname, mode, state, elapsed_seconds,
       rows, round(percent::numeric,1) AS pct, eta_seconds
FROM pgfastload.pg_fastload_progress;

The same engine exports: pgfastload.pg_fastload_export() writes a table or any SELECT to CSV, BINARY or FIXED, and measured 9.33x server-side COPY TO. A parallel export does not preserve row order, and neither does a parallel load, so verify with count(*) and a checksum rather than diff.

When the parallel path quietly switches itself off

Thirteen conditions cannot be honoured in parallel and fall back to the serial path with a single INFO line rather than an error. The ones you will actually hit: a target with NOT NULL or CHECK constraints (constraint checking is serial-only), FILTER or FORCE_NOT_NULL, any PARSE_ERRORS tolerance, INPUT=STDIN, and WRITER=PARALLEL. If you enable PARALLEL=ON and see no improvement, grep the log for INFO before blaming the tool. Downgrading is a safety net, not a fault.

Also plan for the missing per-row tolerance: on the parallel path one bad row fails the statement. If a feed is messy and you want to keep everything before the error, run that one with PARALLEL=OFF.

Is your load actually parse-bound?

Measure before rewriting a pipeline, because the answer is often no. Run the same file with PARALLEL=OFF and PARALLEL=ON into an empty unindexed staging table; if the ratio is under about 1.5x, parsing was never your problem. Count the indexes on the target, because each btree costs roughly 1.9 s/GiB and ignores your worker count. Then check whether the load is waiting on WAL or a replication slot rather than CPU, which shows up as a serial insert floor no amount of parallelism will move.

When it is genuinely parse-bound — multi-gigabyte nightly reloads, warehouse staging, vendor extracts, test-data refreshes — the arithmetic is simple: 6x on the parse stage is a real 6x on the load window, which Houston teams running overnight claims, inventory or meter-read batches feel in the morning rather than in a benchmark table.

If your nightly ingest window is tight and you cannot tell whether the cost is parsing, indexes or WAL, that is a short conversation about Postgres bulk-load and ingest tuning for Houston data teams. For the plain COPY baseline this tool is measured against, our walkthrough of bulk loading data into PostgreSQL with COPY still covers the syntax and the staging-table pattern; and if the target table needs a random UUID primary key, that insert slowdown is a separate diagnosis worth settling first.

Leave A Comment