Psycopg2, Postgresql, Python: Fastest way to bulk-

问题:

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

回答1:

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.

回答2:

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.

回答3:

in my experience executemany is not any faster than running many inserts yourself, the fastest way is to format a single INSERT with many values yourself, maybe in the future executemany will improve but for now it is quite slow

i subclass a list and overload the append method ,so when a the list reaches a certain size i format the INSERT to run it

回答4:

You could use a new upsert library:

$ pip install upsert

(you may have to pip install decorator first)

conn = psycopg2.connect('dbname=mydatabase')
cur = conn.cursor()
upsert = Upsert(cur, 'mytable')
for (selector, setter) in myrecords:
    upsert.row(selector, setter)

Where selector is a dict object like {'name': 'Chris Smith'} and setter is a dict like { 'age': 28, 'state': 'WI' }

It's almost as fast as writing custom INSERT[/UPDATE] code and running it directly with psycopg2... and it won't blow up if the row already exists.

回答5:

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.

回答6:

After some testing, unnest often seems to be an extremely fast option, as I learned from @Clodoaldo Neto's answer to a similar question.

data = [(1, 100), (2, 200), ...]  # list of tuples

cur.execute("""CREATE TABLE table1 AS
               SELECT u.id, u.var1
               FROM unnest(%s) u(id INT, var1 INT)""", (data,))

However, it can be tricky with extremely large data.

回答7:

Anyone using SQLalchemy could try 1.2 version which added support of bulk insert to use psycopg2.extras.execute_batch() instead of executemany when you initialize your engine with use_batch_mode=True like:

engine = create_engine(
    "postgresql+psycopg2://scott:tiger@host/dbname",
    use_batch_mode=True)

http://docs.sqlalchemy.org/en/latest/changelog/migration_12.html#change-4109

Then someone would have to use SQLalchmey won't bother to try different combinations of sqla and psycopg2 and direct SQL together.

回答8:

A very related question: Bulk insert with SQLAlchemy ORM

---

All Roads Lead to Rome, but some of them crosses mountains, requires ferries but if you want to get there quickly just take the motorway.

---

In this case the motorway is to use the execute_batch() feature of psycopg2. The documentation says it the best:

The current implementation of executemany() is (using an extremely charitable understatement) not particularly performing. These functions can be used to speed up the repeated execution of a statement against a set of parameters. By reducing the number of server roundtrips the performance can be orders of magnitude better than using executemany().

In my own test execute_batch() is approximately twice as fast as executemany(), and gives the option to configure the page_size for further tweaking (if you want to squeeze the last 2-3% of performance out of the driver).

The same feature can easily be enabled if you are using SQLAlchemy by setting use_batch_mode=True as a parameter when you instantiate the engine with create_engine()