Skip to content
Tutorials Center
My learning

Topics

Lesson 82 of 90 ยท Using PostgreSQL from Applications

Bulk Loading and COPY

Load millions of rows into PostgreSQL fast: COPY FROM and TO, psql \copy, INSERT batching, unlogged staging tables and index handling. Live staging demo.

Sooner or later every project has to load a lot of data at once: a CSV export from a partner, a nightly feed, a migration from another system. If you do it with one INSERT per row from application code, a million rows can take an hour. Done well, the same load takes under a minute. The difference is not magic. It comes from fewer round trips, fewer transactions, and less work per row for the database.

This lesson covers PostgreSQL's dedicated bulk tool, COPY, its client-side sibling \copy, how to batch ordinary INSERT statements when COPY is not an option, how staging tables keep bad data out of your real tables, and what to do about indexes and constraints during a load. If you use a driver, PostgreSQL from Python, Node.js, PHP and Java shows where COPY fits in each one.

What runs live. COPY, \copy, unlogged tables and server settings only exist on a PostgreSQL server, so those blocks are read-only. The live examples run in SQLite and demonstrate the two techniques that work everywhere: batched inserts and a staging table that validates rows before they reach the real table.

Why one row at a time is slow

Each INSERT sent by an application pays for several things: a network round trip, parsing and planning, a transaction commit (which waits for the write-ahead log to reach disk), and the per-row work of updating every index. With autocommit on, you pay the commit cost for every single row. A client in another data center adds a few milliseconds of latency per statement, and a million statements multiply that by a million.

Where a slow load spends time

Round trips One network wait per statement.
Commits Each commit waits for the WAL flush.
Index updates Every index changes on every row.
Constraint checks Foreign keys look up the parent per row.
1 / 2

COPY: the fast path

COPY streams rows between a table and a file (or a client connection) in a single command. It skips per-statement parsing and runs inside one transaction, so a failure rolls the whole load back. The basic forms are:

SQL
-- PostgreSQL (read-only)
-- Load from a file that lives on the DATABASE SERVER:
COPY books (title, price, published)
FROM '/var/lib/postgresql/import/books.csv'
WITH (FORMAT csv, HEADER true);

-- Load from the client's standard input (what drivers and \copy use):
COPY books (title, price, published) FROM STDIN WITH (FORMAT csv);

-- Export a whole table, or any query, as CSV:
COPY books TO STDOUT WITH (FORMAT csv, HEADER true);
COPY (SELECT title, price FROM books WHERE price > 30) TO STDOUT WITH (FORMAT csv);

Pay attention to where the file lives. COPY ... FROM '/path' reads the file on the server, as the server's operating-system user. That requires superuser rights or membership in the pg_read_server_files role (pg_write_server_files for TO), and on a managed cloud service you usually cannot put files on the server at all. For everyday work you use the client-side variants below.

The most useful options are:

Option What it does
FORMAT text (default, tab-separated), csv, or binary (fast, but tied to the exact column types)
HEADER Skip (or write) a first line with column names
DELIMITER, QUOTE, ESCAPE Adjust the CSV dialect
NULL 'xyz' The text that means SQL NULL (the CSV default is an empty unquoted field)
FORCE_NULL, FORCE_NOT_NULL Treat quoted empty strings as NULL, or not, for named columns
ON_ERROR ignore PostgreSQL 17 and later: skip rows whose values cannot be converted to the column type instead of aborting
REJECT_LIMIT n PostgreSQL 18: with ON_ERROR ignore, fail anyway after more than n skipped rows
FREEZE Mark rows frozen on load (needs the table to be created or truncated in the same transaction)

COPY FROM also accepts a WHERE clause (PostgreSQL 12 and later) to keep only matching rows. Note that ON_ERROR ignore only covers data-conversion errors such as 'abc' in a numeric column. A constraint violation, such as a NOT NULL or UNIQUE failure, still aborts the whole command. That limit is the main reason to load into a staging table, as shown later.

\copy: the same thing from the client

\copy is a psql meta-command (see psql: The Command-Line Client). It reads or writes a file on your machine and sends the data over the connection with COPY ... FROM STDIN underneath. It needs only normal table privileges, which makes it the right tool on managed services and from laptops:

Terminal
# PostgreSQL (read-only): run in a shell
psql "postgresql://[email protected]/shop" \
  -c "\copy books (title, price, published) FROM 'books.csv' WITH (FORMAT csv, HEADER true)"

# Export a query result to a local file
psql "postgresql://[email protected]/shop" \
  -c "\copy (SELECT * FROM books ORDER BY id) TO 'books_export.csv' WITH (FORMAT csv, HEADER true)"

Drivers expose the same protocol. In Python, psycopg offers cursor.copy("COPY ... FROM STDIN"); in Node.js, the pg-copy-streams package; in Java, the JDBC driver's CopyManager. The application writes rows to the stream and PostgreSQL ingests them at full speed, with no per-row statement.

When you cannot use COPY: batch your INSERTs

Sometimes COPY is not available: an ORM hides the connection, a proxy blocks the protocol, or each row needs logic on the way in. Then reduce the cost per row with three habits.

  1. Send many rows per statement. A multi-row INSERT INTO t (a, b) VALUES (...), (...), (...) pays one round trip for many rows. Batches of a few hundred to a few thousand rows are a good range. There is a hard ceiling: the wire protocol allows at most 65,535 bind parameters per statement, so a table with 10 columns tops out at 6,553 rows per parameterized statement.
  2. Wrap batches in a transaction. Commit once per batch (or per several batches) instead of once per row.
  3. Use INSERT ... SELECT when the data is already in the database. One statement can move millions of rows between tables.

The first live example shows the same idea in SQLite. It inserts three rows in one multi-row statement, then 1,000 rows in a single INSERT ... SELECT fed by a recursive CTE (see CTEs, Materialization and Recursive Queries), inside one transaction:

SQL Batched inserts: many rows, one statement, one transaction

Press Run (or Ctrl + Enter) to see the result here.

All 1,003 rows arrive through two statements and one commit. A loop that issued 1,003 separate INSERTs with autocommit would need 1,003 commits, which is exactly the pattern to avoid.

Staging tables: load first, judge later

Raw feeds are messy: empty titles, prices like abc, dates such as 2025-13-45. If you COPY straight into your real table and one row violates a constraint, the whole load fails, and you learn about only the first bad row. The standard fix is a staging table:

  1. Create a staging table with loose types (usually text) and no constraints or indexes.
  2. Bulk-load the raw file into it. Nothing can fail on bad values.
  3. Validate with plain SQL, copy the good rows into the real table with INSERT ... SELECT, and record the bad rows with a reason.
  4. Drop or truncate the staging table.

This also gives you an audit trail: you can tell the supplier exactly which line numbers were rejected and why. The next example does all four steps. Row 1 and row 5 are valid. Row 2 has an empty title, row 3 a non-numeric price, and row 4 an impossible date:

SQL A staging table that separates good rows from bad rows

Press Run (or Ctrl + Enter) to see the result here.

Two rows load and three are rejected, each with its line number and reason. In PostgreSQL you would write the same flow with COPY stage_books FROM STDIN in step 2 and a TEMP or UNLOGGED staging table, covered next. The validation SQL is the same except for the checks: price ~ '^[0-9]+(\.[0-9]+)?$' is a regular expression, and to_date or a cast inside a function can validate dates.

Unlogged staging tables

Every change to an ordinary table is first written to the write-ahead log (WAL), so a crash cannot lose it and replicas can replay it. For disposable staging data that guarantee costs time and disk. An UNLOGGED table skips the WAL:

SQL
-- PostgreSQL (read-only)
CREATE UNLOGGED TABLE stage_books (
  line_no   bigint GENERATED ALWAYS AS IDENTITY,
  title     text,
  price     text,
  published text
);

The tradeoffs are important and not negotiable:

  • After a crash or unclean shutdown, an unlogged table is emptied automatically. Never keep data there that you cannot reload.
  • It is not replicated to streaming replicas, and a replica cannot query it.
  • Writes are often noticeably faster than on a logged table, but not free: they still go through shared buffers and checkpoints.
  • ALTER TABLE ... SET LOGGED converts it later, but this rewrites the whole table and writes it to the WAL.

A TEMP table is the other common staging choice. It lives for the session, is invisible to other connections, and is never WAL-logged. It is a good fit for a single-script import, but it vanishes if the connection drops and does not work through a transaction-pooling proxy (see Connection Pooling).

Indexes and constraints during a load

Every index on a table is updated for every row you insert, and every foreign key checks that the parent row exists. For a large load into an empty or nearly empty table, it is often faster to do things in a different order:

SQL
-- PostgreSQL (read-only)
BEGIN;
DROP INDEX books_title_idx;                    -- or never create it yet

COPY books (title, price, published) FROM STDIN WITH (FORMAT csv);
-- ... data ...

SET LOCAL maintenance_work_mem = '1GB';        -- more memory for the index build
CREATE INDEX books_title_idx ON books (title); -- one sorted bulk build
COMMIT;

ANALYZE books;                                 -- fresh statistics for the planner

Building an index once, from sorted data, is far cheaper than updating it a million times. The guidance in short:

  • Create the table, load, then create indexes. Keep the primary key if other work depends on it, but add secondary indexes afterwards.
  • Keep constraints that protect you (NOT NULL, CHECK, UNIQUE) unless you have already validated the data in staging. Dropping a foreign key to load faster and re-adding it later works, but re-adding runs one full validation scan. With ADD CONSTRAINT ... NOT VALID followed by VALIDATE CONSTRAINT the lock is lighter (see Constraints in Depth).
  • Run ANALYZE after a big load. Autovacuum will eventually do it, but until then the planner thinks the table is empty and may choose terrible plans.
  • Watch the WAL. A giant load produces a lot of WAL and may trigger frequent checkpoints. Raising max_wal_size temporarily (see Configuration Tuning) avoids a checkpoint storm.
  • Parallelize by file. One COPY uses one process. Splitting the input into several files and running several COPY sessions at once can use more CPU and disk bandwidth, up to the point where the disk or the WAL becomes the bottleneck.

pg_bulkload and other shortcuts

pg_bulkload is a third-party extension that goes beyond COPY: it can write data pages directly and skip the WAL and shared buffers, which makes it faster for huge one-off loads. The price is real. Crash recovery works differently (a failed load may need a special recovery step), replicas do not see the data the normal way, it is not available on most managed services, and it needs an installation per server. Reach for it only after a well-tuned COPY into an index-free table is genuinely too slow, and test restoring and replicating after a load before trusting it.

The comparison, from simplest to fastest: single-row INSERT in a loop, then batched INSERT in transactions, then COPY, then COPY into an unlogged or index-free table, then specialized tools. Most workloads should stop at the third step.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. You run COPY books FROM '/data/books.csv' on a managed cloud database and get a permission error. What is the usual fix?

    COPY FROM 'path' reads a file on the database server and needs special privileges. \copy in psql (or COPY FROM STDIN from a driver) streams a local file over the connection and needs only normal table privileges.
  2. 2. A CSV has one row with the text abc in a numeric column and another row that violates a UNIQUE constraint. With ON_ERROR ignore (PostgreSQL 17+), what happens?

    ON_ERROR ignore only covers values that cannot be converted to the column type. Constraint violations still abort the COPY, which is why messy feeds are best loaded into a staging table first.
  3. 3. What happens to an UNLOGGED table after a server crash?

    Unlogged tables skip the write-ahead log, so PostgreSQL cannot recover them after a crash and empties them. They are also not replicated, which makes them suitable only for data you can reload.
  4. 4. For a very large load into an empty table, which order is usually fastest?

    Building an index in one pass over loaded data is much cheaper than updating it for every inserted row. ANALYZE afterwards gives the planner fresh statistics.

Summary

  • Slow loads are dominated by round trips, per-row commits, index maintenance and constraint checks.
  • COPY streams rows in one command and one transaction; \copy and COPY FROM STDIN read files from the client, COPY FROM 'path' reads the server's disk and needs extra privileges.
  • ON_ERROR ignore (17+) skips unconvertible values only, and REJECT_LIMIT (18) caps how many.
  • If you cannot use COPY, send many rows per INSERT (mind the 65,535-parameter limit) and commit per batch.
  • Load raw data into a constraint-free staging table, validate with SQL, and move good rows over while logging the bad ones.
  • Unlogged tables are fast but vanish after a crash and are not replicated; use them only for reloadable staging data.
  • Create secondary indexes after the load, then run ANALYZE; treat pg_bulkload as a last resort.

Next: Project: Booking System with Ranges and Constraints.