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

Question

I need to programmatically insert tens of millions of records into a Postgres database. Presently I am executing thousands of insert statements in a single query.

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

Answer 1

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).

Answer 2

There is an alternative to using COPY, which is the multirow values syntax that Postgres supports. From the documentation:

INSERT INTO films (code, title, did, date_prod, kind) VALUES     ('B6717', 'Tampopo', 110, '1985-02-10', 'Comedy'),     ('HG120', 'The Dinner Game', 140, DEFAULT, 'Comedy'); 

The above code inserts two rows, but you can extend it arbitrarily, until you hit the maximum number of prepared statement tokens (it might be $999, but I'm not 100% sure about that). Sometimes one cannot use COPY, and this is a worthy replacement for those situations.