Speed Up Bulk Inserts with COPY Instead of Row-by-Row

Replace thousands of single INSERT round trips with one COPY stream, measure the difference, and keep the loader resumable and idempotent.

~30 mindatabasepostgresperformance

Task

Convert a row-by-row import to a single COPY stream, verify the throughput gain, and preserve duplicate protection on reruns.

When to use this

A nightly CSV import of 900 thousand rows takes 40 minutes because it sends one INSERT per row and pays a round trip plus a parse for each.

Prerequisites

PostgreSQL with COPY protocol support, or a driver exposing it such as pg-copy-streams
The source data as CSV, NDJSON, or a stream
A staging table strategy for idempotent reruns

Steps

1. Measure the current row-by-row baseline.

time node import.js --rows 900000

Expected output: The baseline records 40 minutes and a network round trip per row, establishing what the rewrite must beat.

2. Stream the file into a staging table with COPY.

const { pipeline } = require("node:stream");
const copyFrom = require("pg-copy-streams").from;

const client = await pool.connect();
const stream = client.query(copyFrom("COPY staging_events (id, kind, payload, occurred_at) FROM STDIN WITH (FORMAT csv, HEADER true)"));
await pipeline(fs.createReadStream("events.csv"), stream);

Expected output: One protocol-level stream pushes the whole file in a single transaction with no per-row parsing overhead.

3. Upsert from staging into the target, idempotently.

INSERT INTO events (id, kind, payload, occurred_at)
SELECT id, kind, payload, occurred_at FROM staging_events
ON CONFLICT (id) DO UPDATE
SET payload = EXCLUDED.payload, occurred_at = EXCLUDED.occurred_at;

Expected output: Rerunning the import is safe: conflicting rows update, new rows insert, and a crash mid-run loses nothing already committed.

4. Time the new pipeline end to end.

time node import-copy.js --rows 900000

Expected output: The same 900 thousand rows load in roughly one minute, a throughput gain of about forty times.

5. Watch locks and replication during the load.

SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) FROM pg_stat_replication;

Expected output: A single large COPY still generates one WAL burst; if lag matters, chunk the file into multiple COPY transactions.

Troubleshooting

Problem

COPY fails on malformed rows

Solution

Load into staging with relaxed constraints, clean or quarantine bad rows there, then upsert the survivors; never let one bad line abort a 40-minute import.

Problem

Driver does not expose COPY

Solution

Use psql \copy from a file as the fallback, which uses the same protocol from the client side and needs no application code changes.

Problem

Rerun inserts duplicates

Solution

Always COPY into staging and upsert with ON CONFLICT; copying directly into the target makes reruns dangerous.

Limitations

COPY bypasses per-row application logic such as validation hooks; staging must absorb that responsibility.
One COPY runs in one transaction; extremely large loads should still be chunked for replication and lock hygiene.

A round trip per row is a tax per row

Inserting nine hundred thousand rows one statement at a time pays the network round trip, the parse, and the plan nine hundred thousand times. The COPY protocol streams the data as one continuous pipe with none of that per-row overhead, and the speedup is usually one to two orders of magnitude. The pattern that keeps it safe is unchanged: land in staging, then upsert idempotently, so reruns and bad rows are routine instead of catastrophic.

const stream = client.query(
  copyFrom("COPY staging_events (id, kind, payload, occurred_at) FROM STDIN WITH (FORMAT csv, HEADER true)")
);
await pipeline(fs.createReadStream("events.csv"), stream);

// Then upsert idempotently
INSERT INTO events ... SELECT ... FROM staging_events
ON CONFLICT (id) DO UPDATE SET payload = EXCLUDED.payload;

Staging keeps COPY restartable

A COPY that lands directly in the target table leaves you with a partial load after any failure. Copying into an empty staging table instead makes the operation atomic from the target point of view: truncate staging, stream, clean, upsert. The import can rerun as many times as the pipeline needs without ever double-applying.

Chunk when replication is watching

One giant COPY is one giant transaction: locks held until the end and a WAL burst replicas must chew through. Splitting the stream into several COPY transactions of a few hundred thousand rows each keeps every commit bounded while retaining nearly all of the throughput advantage.