← All notes

Short answer

Use COPY instead of many INSERTs. It moves data between a table and a file (or STDIN/STDOUT) in one command and has far less overhead. For a new table: create it, COPY the data, then build indexes. [S1][S2]

COPY items (id, name) FROM STDIN WITH (FORMAT csv, HEADER true);

Tips

• COPY FROM appends to whatever is already in the table. [S1]
• COPY is fastest in the same transaction as the CREATE TABLE or TRUNCATE. [S2]
• Building an index on loaded data is quicker than updating it row by row. [S2]

Claims

• id: C1 claim: COPY moves data between tables and file-system files. source: S1 evidence: "COPY moves data between PostgreSQL tables and standard file-system files."
• id: C2 claim: COPY FROM appends data to what is already in the table. source: S1 evidence: "appending the data to whatever is in the table already"
• id: C3 claim: Loading with COPY is almost always faster than INSERT. source: S2 evidence: "loading a large number of rows using COPY is almost always faster than using INSERT"
• id: C4 claim: COPY is fastest in the same transaction as CREATE TABLE or TRUNCATE. source: S2 evidence: "COPY is fastest when used within the same transaction as an earlier CREATE TABLE"

Sources

• [S1] PostgreSQL: Documentation: 18: COPY. PostgreSQL Global Development Group. https://www.postgresql.org/docs/current/sql-copy.html . License: PostgreSQL License. Accessed 2026-11-04.
• [S2] PostgreSQL: Documentation: 18: 14.4. Populating a Database. PostgreSQL Global Development Group. https://www.postgresql.org/docs/current/populate.html . License: PostgreSQL License. Accessed 2026-11-04.

License: this note is CC BY 4.0 (https://creativecommons.org/licenses/by/4.0/). Summary by iinventedlight (AI-assisted).

@misc{iil_note_x9x8akrr,
  author = {{iinventedlight}},
  title = {{Fast bulk loading in PostgreSQL with COPY}},
  howpublished = {iinventedlight Library},
  year = {2026},
  date = {2026-11-04},
  url = {https://iinventedlight.com/library/notes/postgres-copy-bulk-load/},
  note = {Permanent ID iil:note:x9x8akrr. License: CC-BY-4.0.},
}

Markdown

Spotted a mistake? Use the MCP feedback tool or email hello@iinventedlight.com.

For language models and agents: start at /llms.txt. This page in Markdown: /library/notes/postgres-copy-bulk-load.md.