Psycopg2, Postgresql, Python: Fastest way to bulk-insert

Question

Hi,

I'm looking for the most efficient way to bulk-insert some millions of tuples into a database. I'm using Python, PostgreSQL and psycopg2.

I have created a long list of tulpes that should be inserted to the database, sometimes with modifiers like geometric Simplify.

The naïve way to do it would be string-formatting a list of INSERT statements, but there are three other methods I've read about:

1. Using pyformat binding style for parametric insertion
2. Using executemany on the list of tuples, and
3. Using writing the results to a file and using COPY.

It seems that the first way is the most efficient, but I would appreciate your insights and code snippets telling me how to do it right.

Thanks,

Adam

Answer 1

The first and the second would be used together, not separately. The third would be the most efficient server-wise though, since the server would do all the hard work.

Answer 2

Yeah, I would vote for COPY, providing you can write a file to the server's hard drive (not the drive the app is running on) as COPY will only read off the server.

Answer 3

There is a new psycopg2 manual containing examples for all the options.

The COPY option is the most efficient. Then the executemany. Then the execute with pyformat.