Blocking every 20 sec while mass copying.

[Benjamin Dugast <bdugast@xxxxxxxxxxx>]

Hello,

I'm working on Postgres 9.3.4 for a project.

We are using Scala, Akka and JDBC to insert data in the database, we have around 25M insert to do which are basically lines from 5000 files. We issue a DELETE according to the file (mandatory) and then a COPY each 1000 lines of that file.

DELETE request : DELETE FROM table WHERE field1 = ? AND field2 = ?;

COPY request : COPY table FROM STDIN WITH CSV

We have indexes on our database that we can't delete to insert our data.

When we insert the data there is some kind of freezes on the databases between requests. Freezes occur about every 20 seconds.

Here is a screenshot from yourkit.

We tried different solutions:

• 1 table to 5 tables to reduces lock contention
• fillfactor on indexes
• commit delay
• fsync to off (that helped but we can't do this)

We mainly want to know why this is happening because it slowing the insert too much for us.