Scalable Data Ingestion: Using pgLoader for Advanced CSV-to-Postgres Pipelines

PostgreSQL's COPY command is the fastest way to push CSV data into a table — but it has one rigid behavior that makes it dangerous for production pipelines: a single malformed row aborts the entire transaction. When you're ingesting a million-row file at 3 AM and row 700,000 has a stray comma in an unquoted text field, you don't want the whole load to roll back. You want the good rows committed and the bad row quarantined for review.

pgLoader is the standard tool for that job in the Postgres ecosystem. It wraps the COPY protocol with error handling, schema discovery, data transformation, and a declarative command-file format that fits into version control. For the underlying COPY mechanics, see the COPY vs INSERT comparison; this article focuses on when pgLoader is the better choice and how to configure it for scale.

When to Reach for pgLoader

pgLoader is not a universal replacement for COPY. It is the right tool for a specific set of pipeline problems where rigid all-or-nothing loading is the bottleneck.

When to Stick With COPY

For internal exports of known shape — your nightly database-to-database dump, your QA seed file, a one-off migration of trusted data — COPY remains the right tool. It is faster, requires no installation, and produces a cleaner failure: if the data is wrong, you want to know immediately, not discover it in a reject log a day later. pgLoader's value appears only when you actually need its resilience features.

Installing pgLoader

Anatomy of a .load Command File

pgLoader uses a declarative DSL stored in a .load file. The file describes the source, the target, the parsing options, and any setup SQL to run before or after the load. The structure is intentionally readable — you write it once, commit it to Git, and team members can review changes the same way they review code.

A minimal command file looks like this:

LOAD CSV
  FROM 'data/sales_2023.csv' (id, product, price, sold_at)
  INTO postgresql://user:password@localhost/sales_db
  TARGET TABLE sales_data

  WITH truncate,
       skip header = 1,
       fields terminated by ',',
       fields optionally enclosed by '"'

  SET work_mem to '128MB',
      client_encoding to 'utf8';

Run it with a single command: pgloader import_sales.load. On completion, pgLoader prints a summary table showing rows read, rows imported, rows rejected, and the time spent in each phase (read, prepare, copy, index).

Error Resilience: The Reject Log Pattern

This is the single feature that justifies installing pgLoader for most teams. When a row fails to parse or fails a type cast, pgLoader writes the raw row to a .rejects file and the reason to a .errors file, then continues with the next row. The pipeline does not abort.

Combined with rows per transaction, this turns a fragile script into a resumable, observable pipeline. A typical configuration commits every 10,000 rows, quarantines bad rows, and produces a daily reject file that an on-call engineer reviews each morning. The database stays current, the load never wakes anyone up at 3 AM, and bad data has a defined remediation path.

On-the-Fly Transformation With CAST

The CAST clause lets you transform a column's value while it is streaming into Postgres, instead of loading raw text and running a cleanup pass afterward. This is faster (one pass instead of two), cleaner (no temporary "dirty" state in the target table), and easier to reason about (the transformation lives next to the load definition).

The transformation language is Common Lisp, which is unfamiliar to most teams. In practice you copy-paste from the pgLoader documentation, adjust the column name and the function, and move on. You rarely need to write Lisp from scratch.

Parallel Ingestion

pgLoader is multi-threaded by default. While one worker reads and parses the CSV, another applies casts, and a third streams the result to Postgres via the COPY protocol. On a multi-core machine with a fast SSD, this pipeline-style parallelism keeps the network connection saturated even when the source CSV is large or the transformations are non-trivial.

This is different from running multiple COPY commands in parallel, which is also possible but requires you to manually split the source file and manage concurrent connections. With pgLoader, the parallelism is automatic and configured per-run via workers and concurrency settings.

Production Tuning Tips

Decision Summary