Lorixen.
EN

Loading big data imports into PostgreSQL fast

Short answer: Inserting rows one at a time, each in its own transaction, is the slowest way to load data into PostgreSQL. Use COPY (or psql's \copy from your own machine) to load whole files in one command, load into a plain staging table first, then validate and merge into your real tables with SQL. For very large loads into empty or new tables, create indexes and foreign keys after loading, and run ANALYZE when you're done so the database plans queries well on the new data.

Why row-by-row is slow

PostgreSQL's documentation on populating a database starts with the basics: if each insert commits separately, PostgreSQL does a lot of work for every row. Wrapping many inserts in one transaction helps, and has a bonus: if one row fails, the whole load rolls back, so you aren't left with half-imported data.

But the bigger step is COPY. The documentation says COPY is optimised for loading large numbers of rows and has significantly less overhead than INSERT, and that loading with COPY is almost always faster than inserts, even prepared and batched in one transaction.

COPY or \copy?

COPY ... FROM 'file' reads a file on the database server. The COPY reference explains that reading server files is restricted to superusers or users with specific roles, since it can read any file the server can access. Managed databases rarely allow it.

\copy in psql is the practical alternative. The reference explains that it runs COPY FROM STDIN and reads the file on the client side, so you can load a file from your laptop or app server with normal permissions:

\copy staging_products FROM 'feed.csv' WITH (FORMAT csv, HEADER true)

Most database drivers also expose COPY from application code. Look for "copy from" or "copy expert" in your driver's documentation.

Load into a staging table first

Real-world files are messy: extra columns, inconsistent dates, duplicates, rows for products you don't sell. A reliable pattern:

  1. Create a staging table whose columns are mostly text, matching the file.
  2. COPY the file into it as-is.
  3. Use SQL to inspect and clean: find bad dates, missing references, duplicates.
  4. Insert or update into the real tables with one INSERT ... SELECT that converts types, ideally with an upsert for existing rows.
  5. Drop or truncate the staging table.

Loading as text means the file loads even when values are bad, and you fix problems with queries rather than editing the file.

Skipping bad rows

Newer PostgreSQL versions add an ON_ERROR option to COPY. The reference explains that ignore discards rows whose values can't be converted to the column type and continues, reporting how many rows were skipped, and REJECT_LIMIT makes the command fail if there are more errors than you allow. It's useful for typed loads, but a staging table still gives you more control over what was wrong and why.

Indexes, foreign keys and memory

PostgreSQL's guidance for big loads:

  • New tables: create the table, load with COPY, then create indexes. Building an index on existing data is quicker than updating it row by row.
  • Large loads into existing tables: dropping indexes, loading and recreating them can be faster, but the docs warn that other users' queries suffer meanwhile, and dropping a unique index removes its duplicate checking.
  • Foreign keys: checking them in bulk after loading is more efficient. The docs note that loading many millions of rows into a table with foreign keys can even exhaust memory with pending checks, so for very large loads it may be necessary to drop and re-add them, or to split the load into smaller transactions.
  • Settings: temporarily raising maintenance_work_mem speeds up index and foreign key creation, and raising max_wal_size can help large loads.

For everyday imports of a few hundred thousand rows into a staging table, you usually don't need to touch any of this.

Finish with ANALYZE

The documentation recommends running ANALYZE after loading large amounts of data, so the query planner has up-to-date statistics. Skipping it can leave queries on the new data slow until autovacuum catches up.

Check the file before loading

A few quick checks catch most problems before they reach the database: confirm the file's character encoding and delimiter, compare the header row against the columns you expect, and count the lines. If the supplier changes their format without warning, a failed header check with a clear message is far better than a load that quietly puts prices in the description column.

Make imports repeatable

  • Script the whole process, from download to merge, and run it the same way each time.
  • Log row counts at each stage: rows in the file, rows loaded, rows rejected, rows inserted or updated.
  • Keep the original file for a while, so you can investigate questions later.
  • Run large imports outside busy hours, or against a staging copy first.

A worked example

An illustrative Nelson outdoor gear wholesaler imported a nightly supplier feed of hundreds of thousands of products. The old script inserted each row with its own commit and ran for hours, sometimes still going when staff arrived. The developer switched to \copy into a text staging table, cleaned and validated with SQL, upserted into the products table in one transaction, logged counts, and ran ANALYZE afterwards. The import now finishes in a small fraction of the time and fails cleanly, with nothing half-loaded, when the supplier sends a broken file.

FAQ

Can I use COPY with JSON files?

COPY reads text and CSV formats; load JSON lines into a single column and parse with SQL, or convert first.

Will COPY fire triggers?

Yes, row triggers fire for COPY FROM, which can slow loads; staging tables usually have none.

How big can the file be?

Very large files load fine; split them only if you want smaller transactions.

Is this safe on a live database?

Loading into a staging table is; schedule the merge step for a quiet time.

If your data imports are slow or fragile, send us a sample file and your current process and we'll help you build a faster, safer load.

Sources

WhatsAppBook a call