Speed up insert table in database using PostgreSQL

Question

I am using python for coding and psql for save my data. My problem is that when I write into database it takes about 2-3 minutes. Size of the data is about 1,200,000 (lines) and 3 columns.

Insertion function:

def store_data(cur,table_name,data):
    cur.executemany(
    "INSERT INTO"+" "+table_name+" "+"(name, date,id) VALUES (%s, %s, %s)",
    [(data[i][0], data[i][1], data[i][2]) for i in xrange(0,len(data))]
    )

    cur.connection.commit()

How to speed-up function?

W.Mann · Accepted Answer · 2017-09-02 13:34:57Z

4

Use the COPY command. Postgres Documentation. Also check out the psycopg documentation on COPY.

Some numbers: Separate INSERT for 3 million rows: 3 hours. Using COPY: 7 seconds.

edited Sep 2, 2017 at 13:34

answered Sep 2, 2017 at 13:30

W.Mann

9235 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user8058941 Over a year ago

It's more faster?

W.Mann Over a year ago

Added some numbers. They originate from the Database Tuning class that I tought.

user8058941 Over a year ago

I save my data into array list.

W.Mann Over a year ago

Convert it to some CSV format that COPY likes, e.g. using StringIO (python 2) or io.StringIO (python 3)

Janning Vygen · Accepted Answer · 2017-09-02 15:46:12Z

1

There is a detailed chapter in the excellent PostgreSQL Docs about "Populating a Database"

In addition to use COPY as W.Mann suggests you can do more if you have further performance requirements:

Remove Indexes temporary
Remove Foreign Key and Check Constraints temporary
Increase maintenance_work_mem
Increase max_wal_size
Disable WAL Archival and Streaming Replication
Run ANALYZE Afterwards

If you use pg_restore you can try to use the -j option on a multi processor system to run multiple jobs parallel. And check out the other options given in the documentation linked above.

answered Sep 2, 2017 at 15:46

Janning Vygen

9,30211 gold badges81 silver badges114 bronze badges

Comments

aschoerk · Accepted Answer · 2017-09-02 17:10:34Z

0

Looking at the documentation of executemany:

Warning
In its current implementation this method is not faster than 
executing execute() in a loop. For better performance you can use 
the functions described in Fast execution helpers.

At the same location a link can be found to: http://initd.org/psycopg/docs/extras.html#fast-exec they recommend:

 psycopg2.extras.execute_batch

answered Sep 2, 2017 at 17:10

aschoerk

3,6632 gold badges17 silver badges32 bronze badges

Collectives™ on Stack Overflow

Speed up insert table in database using PostgreSQL

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related