Stream a Large CSV into a Database Without Loading It All

Parse a multi-gigabyte CSV with a streaming parser, batch the rows into prepared inserts, and report progress so the import never holds the whole file in memory.

~35 mincsvstreamingpostgres

Task

Import a 4 GB CSV into Postgres without loading the file into memory or blocking the database with one giant transaction.

When to use this

A nightly export arrives as a 4 GB CSV. The previous import read it into memory and crashed the node process at 2 GB. The team needs an import that streams and reports progress.

Prerequisites

Node.js 20 LTS with a streaming CSV parser
A Postgres connection that supports prepared statements
A way to resume from the last committed batch on failure

Steps

1. Create a streaming parser that emits one row at a time and never buffers the whole file.

import { parse } from "csv-parse";
fs.createReadStream("export.csv").pipe(parse({ columns: true }))

Expected output: The parser emits row events with constant memory, regardless of the file size.

2. Batch the rows into prepared inserts of 1000 and commit each batch separately.

const stmt = await client.prepare("INSERT INTO records VALUES ($1, $2)");
for await (const batch of batches(rows, 1000)) {
  await client.query("BEGIN");
  for (const r of batch) await stmt.execute([r.id, r.value]);
  await client.query("COMMIT");
}

Expected output: Each batch commits in well under a second, and the import resumes from the last committed batch if it crashes.

3. Report progress by tracking the byte offset of the stream and the row count.

node tools/import-csv.mjs export.csv --report-every 10000

Expected output: The script logs a progress line every 10000 rows with bytes read, rows inserted, and elapsed time.

Troubleshooting

Problem

A single bad row aborts the whole import

Solution

Log the row and its line number, skip it, and continue; write the skipped rows to a reject file for later review.

Problem

The database connection drops mid-batch

Solution

Use a connection pool with retry, and make each batch idempotent by keying on the primary key with ON CONFLICT DO NOTHING.

Limitations

Streaming keeps memory flat but the import still takes time proportional to the file size; schedule it off peak.
A batch that fails and retries must be idempotent, or the resume will create duplicate rows.

Parse, do not load

A streaming parser emits one row at a time and keeps memory flat. Pipe the file through the parser and never hold more than one batch in memory at a time.

import { parse } from 'csv-parse';
fs.createReadStream('export.csv').pipe(parse({ columns: true }))

Batch and commit

Group rows into prepared inserts of 1000 and commit each batch separately. If the script crashes, it resumes from the last committed batch. Make each batch idempotent with ON CONFLICT DO NOTHING so a retry does not duplicate rows.

for await (const batch of batches(rows, 1000)) {
  await client.query('BEGIN');
  for (const r of batch) await stmt.execute([r.id, r.value]);
  await client.query('COMMIT');
}

Expected outcome

The import runs with constant memory, commits a batch every fraction of a second, and reports progress by byte offset and row count. A crash resumes from the last commit, and a bad row is logged and skipped rather than aborting the run.