postgres and insert performance with grails

recently I spend a lot of time tuning a postgres database and an algorithm (based on grails) to try to insert 80M chemical compounds into a database with reference checks and assurance for there uniqueness.


The main issue is that the inserts get slower and slower over time and end up taking to long. To improve the performance we started with the following approach:

• more memory
• database indexes (obvious)
• flush the hibernate session all 100 inserts
• flush the grails cache all 100 inserts
• tune several postgres parameters
• re index the database all 10k inserts
• enable vacuum on a 10k row basis
• enable analyze on a 10k row basis
• set auto vacuum to check all 10 minutes

But after all this work we are still stuck with a problem, the inserts get gradually slower over time, which can be related to the index building time.

Graph over 1M inserts: TODO, it's still in the calculation...