What is the fastest way to load a big CSV into Postgres?

COPY ... FROM STDIN loaded a 497 MB, 5,000,000-row CSV into Postgres 16 in 3.95 seconds — 1,265,000 rows a second, 120x the 10,498 rows a second a loop of single-row INSERTs managed, which would have taken 7 minutes 56 seconds for the same file. Six parallel COPY connections finished in 1.40 seconds (3,571,000 rows/sec, 340x). Everything else on the ladder — batching, prepared statements, UNLOGGED, synchronous_commit=off — is worth between nothing and 30x, and the ones people repeat most confidently are the weakest.

The short answer

• COPY FROM STDIN is the answer, and the margin is 120x. 5,000,000 rows: 3,952 ms median for COPY, against 476 seconds extrapolated for a single-row INSERT loop in autocommit. Binary COPY was a further 1.32x (2,993 ms, 1,670,000 rows/sec).
• If you cannot use COPY, batch the VALUES, and stop at 500 per statement. One row per INSERT inside a single transaction gave 26,156 rows/sec; 100 rows per statement gave 278,241; 500 gave 305,064; 10,000 gave 316,055. Everything past 500 is inside the noise.
• Dropping indexes and rebuilding them was worth 2.32x end to end — but only on a bulk load. Loading 5M rows into an empty table: 13,462 ms with three indexes present, 5,804 ms with them built afterwards. Adding 500,000 rows to a table that already had 5,000,000, the same trick was 1.66x slower.
• UNLOGGED bought 1.20x and synchronous_commit=off bought nothing. 3,877 ms → 3,228 ms for UNLOGGED (and 480 MB of WAL → zero); 3,973 ms for synchronous_commit=off, which is the baseline. A single big COPY commits once, so there is nothing to desynchronise.
• A foreign key checked during the load cost 4.5x — the single most expensive thing measured. 17,437 ms with the FK in place versus 3,877 ms without, and adding the same constraint after the load took 977 ms.

What was measured, and on what

Apple M3, 8 cores, 16 GB RAM, macOS 26.4.1. Postgres 16.15 (Debian, aarch64) in Docker 29.5.2, in a container limited to --cpus=6 --memory=6g --shm-size=1g, inside a Docker VM with 8 CPUs and 8.3 GB. Non-default server settings: shared_buffers=1GB, max_wal_size=8GB, checkpoint_timeout=30min, autovacuum=off so nothing moved under the measurements. fsync=on, full_page_writes=on, wal_compression=off, synchronous_commit=on except where stated.

These are indicative figures from a laptop in Docker, not a lab benchmark. The ratios are the point, not the absolute numbers. One caveat matters especially: an fsync on this machine is fast, so the naive autocommit loop looks better here than it will on a server with a network-attached disk. 120x is a floor, not a ceiling.

The data was generated inside Postgres, in one statement over generate_series(1, 5000000) (5.76 s), then written out with COPY ... TO (2.56 s):

CREATE UNLOGGED TABLE gen AS
SELECT g::bigint AS id,
       (1 + (hashtext(g::text) & 262143))::int AS user_id,
       (ARRAY['page_view','click','signup','purchase','logout',
              'error','search','share'])[1 + (g % 8)] AS event_type,
       (timestamptz '2024-01-01 00:00:00+00'
         + (g % 31536000) * interval '1 second') AS created_at,
       round(((hashtext('a'||g) & 1048575)::numeric / 100.0), 2) AS amount,
       'sess-' || md5(g::text) || '-v' || (g % 97) AS payload
FROM generate_series(1, 5000000) g;

That is 497,208,119 bytes (474 MiB) of CSV, 5,000,000 rows, six columns: bigint, integer, text, timestamptz, numeric(12,2), text. The binary dump of the same rows is 551,096,576 bytes. Every load ran at least three times; medians are quoted with the range.

How slow is a loop of INSERTs, really?

Slow enough that nobody should ship it, but not for the reason usually given. 20,000 single-row INSERTs in autocommit took 1,905 ms (1,874–1,946) — 10,498 rows a second. Extrapolated to 5,000,000 rows that is 476 seconds, just under eight minutes. I measured a subset and multiplied; the rate is linear in rows here, but it is an extrapolation and I am saying so.

Wrapping the same statements in one transaction, 200,000 rows took 7,628 ms (7,489–7,700) — 26,219 rows a second. That is 2.5x, not the order of magnitude the folklore promises. The commit is real but it is not the main cost. The main cost is that each row is a separate parse, plan, execute and network round trip, and no transaction boundary changes that.

Where does batching the VALUES stop helping?

At about 500 rows per statement. 500,000 rows, all inside one transaction, median of three:

The first factor of ten does 5.5x of the work. Going from 1,000 rows per statement to 10,000 buys 1.8%, and costs you a statement large enough to be awkward to log, retry or fit inside a parameter limit. If your ORM will only do batched INSERT, set the batch to a few hundred and stop tuning.

Note the ceiling: 312,000 rows a second is still a quarter of what COPY does. Batching gets you to within 4x, not to parity.

Do prepared statements help?

Barely. pgbench, one connection, same table, three protocols:

Parameter binding is worth 9.6% in autocommit and 20.1% batched. Postgres's parser is fast; re-planning a trivial single-table INSERT is not where the time goes. Prepared statements are worth having — they are free once your driver does them — but they will not rescue a row-at-a-time loader.

The whole ladder, in one table

5,000,000 rows. Rungs 1–3 were measured on a subset and extrapolated (marked); rungs 4–6 are wall clock for the full file.

Two footnotes. Server-side COPY events FROM '/path' was no faster than the client-side \copy (4,127 ms versus 3,952 ms median) — the file read is not the bottleneck, so use \copy and keep your loader off the database host. And COPY ... WITH (FREEZE), which the internet recommends freely, was slower: 4,673 ms median, and 485 MB of WAL against 480 MB. It saves a later vacuum, not the load.

Should you drop the indexes?

Only if you are adding more than about a quarter of the table. This is the measurement I expected to confirm the usual advice and did not.

Three indexes — primary key on id, btree on created_at, btree on user_id, 264 MB in total. Loading 5,000,000 rows into an empty table:

2.32x end to end, and half the WAL. Raising maintenance_work_mem from the 64 MB default to 1 GB cut the rebuild by 1.30x — real, but 11% of the end-to-end time, so do it and then stop thinking about it.

Now the part articles skip. Repeat the experiment as an incremental load, against a table that already holds 5,000,000 indexed rows:

The trap is that the COPY itself looks spectacular either way: 376 ms without indexes against 1,629 ms with them, a 4.3x improvement you can screenshot. Then the rebuild costs 2,296 ms, because it sorts all 5,500,000 rows, not the 500,000 you added. The rebuild is proportional to the whole table; the maintenance is proportional to the new rows. The crossover on this data sits between 20% and 40%. If you are appending a daily file to a large table, leave the indexes alone. See what each index costs for the steady-state side of that bill.

Does UNLOGGED help? Does synchronous_commit=off?

One is a modest win, one is nothing. Same 5,000,000-row COPY, one variable changed at a time:

UNLOGGED eliminates 480 MB of write-ahead log and returns 20%. The WAL was never the bottleneck — it is written sequentially and absorbed by the page cache, exactly as bind mount versus volume found for ordinary writes.

synchronous_commit=off does nothing for COPY because a single COPY is a single transaction with a single commit. It is not useless — it is just useless here. On the rungs where commits are frequent it is the largest single multiplier available:

Both of these are unsafe, and not in the same way. An UNLOGGED table is truncated — emptied, not corrupted — if Postgres crashes or is shut down uncleanly, and it is not replicated, so a load into one is only safe if you can rerun it from the source file. synchronous_commit=off risks losing transactions committed in the last wal_writer_delay window (200 ms by default) on a crash; the database stays consistent, but rows your loader was told were committed can be gone. Use UNLOGGED for staging tables you will INSERT ... SELECT out of, and turn synchronous_commit back on the moment the load ends.

The foreign key is the headline of that table. Enforcing it row by row during the load turned a 3.9-second load into a 17.4-second one, while validating the same constraint in bulk afterwards took 977 ms — 14x cheaper for an identical guarantee. Drop the FK, load, add it back.

How far does parallel COPY scale?

To four connections, then it stops. The file was split into 24 equal chunks with split -n l/24 and loaded into one unindexed table:

Two connections give 1.92x — nearly perfect. Four give 3.13x. Six give nothing more than four, and eight is worse than four. The container had six CPUs, and COPY is CPU-bound on parsing text into tuples, so the curve bends exactly where the cores run out. Past that, the backends contend for the same relation extension lock and the same WAL insert slots, and you pay coordination for no throughput. Set the parallelism to the number of cores the database has, not the number of chunks you can make.

What does ON CONFLICT cost?

COPY cannot do ON CONFLICT, so the real question is what the staging-table round trip costs. Loading 5,000,000 rows from an unindexed UNLOGGED staging table into a target with a primary key:

The conflict clause costs 1.7x even when nothing conflicts — Postgres has to take a speculative insertion token on every row whether or not it is used. A fully redundant DO NOTHING re-load is the cheapest operation here and writes literally zero WAL, which makes idempotent reloads very cheap. A fully redundant DO UPDATE is the most expensive: 3.4x a plain insert, because every row becomes a dead tuple plus a new one plus index entries, and then autovacuum has to clean up after you.

How much WAL does each approach write?

Measured as pg_current_wal_lsn() deltas around each load, per row:

COPY writes 33% less WAL per row than an INSERT of the same data, because it packs many tuples into one MULTI_INSERT record instead of one record each. Batch size makes no difference at all to WAL — 10 rows per statement and 10,000 rows per statement both produced 72,257,100 bytes for 500,000 rows, to within 200 bytes. If you are replicating, that 3.13x for loading with indexes in place is also 3.13x down your replication link.

Check it yourself

Runs the whole ladder at 500,000 rows in about a minute, then removes the container and its volumes. Save as csv-load-bench.sh.

#!/usr/bin/env bash
set -euo pipefail
ROWS=${ROWS:-500000}; NAME=csvbench; PORT=${PORT:-55652}

docker rm -f -v "$NAME" >/dev/null 2>&1 || true
docker run -d --name "$NAME" -e POSTGRES_PASSWORD=pw -e POSTGRES_DB=bench \
  -p "$PORT":5432 --shm-size=1g --cpus=6 --memory=6g postgres:16 \
  -c shared_buffers=1GB -c max_wal_size=8GB -c checkpoint_timeout=30min \
  -c autovacuum=off >/dev/null
until docker exec "$NAME" pg_isready -U postgres -q 2>/dev/null; do sleep 1; done
docker exec "$NAME" install -d -o postgres /csv

cat > /tmp/bench.sh <<'INNER'
#!/bin/bash
ROWS=$1
P="psql -U postgres -d bench -qtAX -v ON_ERROR_STOP=1"
COLS="id bigint NOT NULL, user_id integer NOT NULL, event_type text NOT NULL,
      created_at timestamptz NOT NULL, amount numeric(12,2) NOT NULL, payload text NOT NULL"
q(){ $P -c "$1"; }
tms(){ local s=$(date +%s%N); eval "$1" >/dev/null; local e=$(date +%s%N); echo $(( (e-s)/1000000 )); }

q "CREATE UNLOGGED TABLE gen AS SELECT g::bigint AS id,
     (1+(hashtext(g::text)&262143))::int AS user_id,
     (ARRAY['page_view','click','signup','purchase','logout','error','search','share'])[1+(g%8)] AS event_type,
     (timestamptz '2024-01-01 00:00:00+00' + (g%31536000)*interval '1 second') AS created_at,
     round(((hashtext('a'||g)&1048575)::numeric/100.0),2) AS amount,
     'sess-'||md5(g::text)||'-v'||(g%97) AS payload
   FROM generate_series(1,$ROWS) g" >/dev/null
q "COPY (SELECT * FROM gen ORDER BY id) TO '/csv/events.csv' WITH (FORMAT csv)" >/dev/null
q "COPY (SELECT * FROM gen ORDER BY id) TO '/csv/events.bin' WITH (FORMAT binary)" >/dev/null
echo "CSV: $(du -m /csv/events.csv | cut -f1) MB, $ROWS rows"; echo

mkins(){ $P -c "COPY (SELECT 'INSERT INTO events VALUES '||string_agg(v,',')||';'
   FROM (SELECT (id-1)/$1 AS grp,
                format('(%s,%s,%L,%L,%s,%L)',id,user_id,event_type,created_at,amount,payload) AS v
         FROM gen WHERE id<=$2) s GROUP BY grp ORDER BY grp) TO '/tmp/o.sql'" >/dev/null
  mv /tmp/o.sql "$3"; }
mkins 1 20000 /csv/one_20k.sql; mkins 1 $ROWS /csv/n1.sql; mkins 1000 $ROWS /csv/n1000.sql

bench(){   # label, setup DDL, load command, rows loaded
  q "DROP TABLE IF EXISTS events CASCADE" >/dev/null; q "$2" >/dev/null
  local l0=$(q "select pg_current_wal_lsn()")
  local ms=$(tms "$3")
  local w=$(q "select round(pg_wal_lsn_diff(pg_current_wal_lsn(),'$l0')/1048576.0)")
  printf '%-34s %8s ms %12s rows/sec  WAL %6s MB\n' "$1" "$ms" "$(( $4*1000/(ms>0?ms:1) ))" "$w"
}
CP="\$P -c \"\\\\copy events FROM '/csv/events.csv' WITH (FORMAT csv)\""
bench "1 INSERT/row, autocommit (20k)" "CREATE TABLE events ($COLS)" "\$P -f /csv/one_20k.sql" 20000
bench "1 INSERT/row, one transaction"  "CREATE TABLE events ($COLS)" "\$P --single-transaction -f /csv/n1.sql" $ROWS
bench "1000 rows/INSERT, one txn"      "CREATE TABLE events ($COLS)" "\$P --single-transaction -f /csv/n1000.sql" $ROWS
bench "COPY FROM STDIN (csv)"          "CREATE TABLE events ($COLS)" "$CP" $ROWS
bench "COPY FROM STDIN (binary)"       "CREATE TABLE events ($COLS)" "\$P -c \"\\\\copy events FROM '/csv/events.bin' WITH (FORMAT binary)\"" $ROWS
bench "COPY into UNLOGGED table"       "CREATE UNLOGGED TABLE events ($COLS)" "$CP" $ROWS
bench "COPY with 3 indexes present"    "CREATE TABLE events ($COLS, PRIMARY KEY(id)); CREATE INDEX ON events(created_at); CREATE INDEX ON events(user_id)" "$CP" $ROWS
echo
q "DROP TABLE IF EXISTS events CASCADE" >/dev/null; q "CREATE TABLE events ($COLS)" >/dev/null
c=$(tms "$CP")
i=$(tms "PGOPTIONS='-c maintenance_work_mem=1GB' \$P -c \"ALTER TABLE events ADD PRIMARY KEY (id);
        CREATE INDEX ON events(created_at); CREATE INDEX ON events(user_id);\"")
echo "COPY then build 3 indexes:  copy ${c} ms + index build ${i} ms = $((c+i)) ms end to end"
rm -f /csv/*.sql /csv/events.csv /csv/events.bin
INNER

docker cp /tmp/bench.sh "$NAME":/csv/bench.sh >/dev/null
docker exec -u postgres "$NAME" bash /csv/bench.sh "$ROWS"
rm -f /tmp/bench.sh
docker rm -f -v "$NAME" >/dev/null   # -v matters: anonymous volumes leak otherwise

At 500,000 rows on the machine described above it prints:

CSV: 45 MB, 500000 rows

NOTICE:  table "events" does not exist, skipping
1 INSERT/row, autocommit (20k)         1831 ms        10922 rows/sec  WAL      3 MB
1 INSERT/row, one transaction         18481 ms        27054 rows/sec  WAL     65 MB
1000 rows/INSERT, one txn              1615 ms       309597 rows/sec  WAL     65 MB
COPY FROM STDIN (csv)                   407 ms      1228501 rows/sec  WAL     43 MB
COPY FROM STDIN (binary)                293 ms      1706484 rows/sec  WAL     43 MB
COPY into UNLOGGED table                335 ms      1492537 rows/sec  WAL      0 MB
COPY with 3 indexes present            1449 ms       345065 rows/sec  WAL    142 MB

COPY then build 3 indexes:  copy 394 ms + index build 234 ms = 628 ms end to end

Every ratio in this article survives at a tenth of the row count. Once the file is in, the next question is usually how many rows you actually got — and COUNT(*) is not free either.