How do you bulk-load data efficiently with COPY?

Question

Accepted Answer

**`COPY`** is PostgreSQL's high-performance command for **bulk loading and exporting** data — moving large amounts of data between a file (or stream) and a table far faster than individual `INSERT` statements. It's the standard tool for importing/exporting big datasets.

## COPY for fast bulk loading

```sql
-- load data from a CSV file into a table (server-side; needs file access + privileges)
COPY users (name, email, age) FROM '/path/data.csv' WITH (FORMAT csv, HEADER true);

-- export a table to a CSV file
COPY users TO '/path/export.csv' WITH (FORMAT csv, HEADER true);

-- from psql (client-side — works without server file access): use \copy
\copy users FROM 'data.csv' WITH (FORMAT csv, HEADER true)
```

`COPY` reads/writes an entire file in one operation — dramatically faster than running thousands of individual INSERTs.

## Why COPY is so much faster than INSERT

```text
❌ thousands of individual INSERTs:
   each is a separate statement → per-statement overhead, possibly per-row transactions,
   network round-trips → SLOW for large data (can be 10-100x slower)

✅ COPY:
   one bulk operation, minimal overhead, optimized data path → FAST
→ For loading large datasets, COPY is the go-to; INSERTs are for small/individual rows.
```

## COPY vs \copy (server-side vs client-side)

```text
COPY (SQL command) → reads/writes files on the SERVER, needs superuser/file privileges
\copy (psql command) → reads/writes files on the CLIENT, no special privileges
→ Use \copy when running from your machine against a remote database.
```

## Tips for bulk loading large datasets

```text
✓ Use COPY (not INSERTs) for large data
✓ Consider dropping/disabling indexes and constraints during a huge load, then
  recreating them after (building an index once is faster than maintaining it per-row)
✓ Load within a single transaction; increase maintenance_work_mem for index rebuilds
✓ For programmatic bulk inserts, use multi-row INSERT or the driver's COPY support
```

## Why it matters

`COPY` is the essential PostgreSQL tool for **efficient bulk data loading and exporting**, and understanding it is valuable for any scenario involving large datasets — data migrations, ETL, imports/exports, seeding, and analytics, which are common real-world needs.

The key value is **performance**: loading large amounts of data with individual `INSERT` statements is dramatically slow (per-statement overhead, round-trips — potentially 10-100x slower), while `COPY` performs the load as one optimized bulk operation, making it the go-to for importing or exporting big datasets (thousands to millions of rows).

Understanding *why* COPY is so much faster (bulk operation vs per-row overhead) clarifies when to reach for it (large data) versus INSERTs (small/individual rows).

Knowing the practical distinction between **`COPY`** (server-side SQL command, needs file access/privileges) and **`\copy`** (psql client-side command, no special privileges — works against remote databases from your machine) is important for actually using it in different contexts.

The **bulk-loading tips** (using COPY over INSERTs, dropping/recreating indexes around huge loads since building an index once beats maintaining it per-row, loading in a transaction) are valuable optimization knowledge for large imports.

Since bulk data operations (migrations, imports, exports, ETL) are common, and since COPY is the standard, high-performance way to handle them in PostgreSQL (vastly outperforming INSERTs for large data), understanding COPY — its performance benefit, the COPY vs \copy distinction, and bulk-loading best practices — is valuable, practically-relevant knowledge for efficiently moving large datasets in and out of Postgres, a frequent operational need where using the right tool (COPY) makes a major difference.