What's the fastest way to do a bulk insert into Postgres?

postgresql bulkinsert
Ash picture Ash · Apr 17, 2009 · Viewed 306.4k times · Source

I need to programmatically insert 10's of millions of records into a postgres database. Presently I am executing 1000's of insert statements in a single "query".

Is there a better way to do this, some bulk insert statement I dont know about?

Answer

Dan Lew picture Dan Lew · Apr 17, 2009

PostgreSQL has a guide on how to best populate a database initially, and they suggest using the COPY command for bulk loading rows. The guide has some other good tips on how to speed up the process, like removing indexes and foreign keys before loading the data (and adding them back afterwards).

Related questions

How to speed up insertion performance in PostgreSQL

I am testing Postgres insertion performance. I have a table with one column with number as its data type. There is an index on it as well. I filled the database up using this query: insert into aNumber (id) values (564),(43536),(34560) ... …

Read More
How do I temporarily disable triggers in PostgreSQL?

I'm bulk loading data and can re-calculate all trigger modifications much more cheaply after the fact than on a row-by-row basis. How can I temporarily disable all triggers in PostgreSQL?

Read More
Use binary COPY table FROM with psycopg2

I have tens of millions of rows to transfer from multidimensional array files into a PostgreSQL database. My tools are Python and psycopg2. The most efficient way to bulk instert data is using copy_from. However, my data are mostly 32…

Read More