Batch insert data with JPA, Hibernate and PostgreSQL

by

So you coded the data layer of your application using JPA (with for instance the Hibernate implementation) and when the application reaches the staging environment, you hear it is too slow !
Too many queries, too long queries, etc.
What do you do ? You try to optimize your data layer !
One possible optimization is to batch insert/update data.
The following properties must be set in persistence.xml :

...
      <property name="hibernate.jdbc.batch_size" value="50" />
      <property name="hibernate.order_inserts">true</property>
      <property name="hibernate.order_updates">true</property>
...

Now the big question is : how do i know it works ?
Checking the Hibernate logs still shows that several insert queries where generated instead of 1 (version used is hibernate-core-4.3.6.Final.jar) :


02 nov. 2017 19:02:42,190 DEBUG SQL(109) -
insert
    into
        lambda
        (id_lambda, color, city)
    values
        (?, ?, ?)
02 nov. 2017 19:02:42,193 DEBUG AbstractBatchImpl(145) - Reusing batch statement
02 nov. 2017 19:02:42,193 DEBUG SQL(109) -
    insert
    into
        lambda
        (id_lambda, color, city)    
  values
        (?, ?, ?)
02 nov. 2017 19:02:42,196 DEBUG AbstractBatchImpl(145) - Reusing batch statement
02 nov. 2017 19:02:42,196 DEBUG SQL(109) -
    insert
    into
        lambda
        (id_lambda, color, city)    
    values
        (?, ?, ?)
02 nov. 2017 19:02:42,199 DEBUG AbstractBatchImpl(145) - Reusing batch statement
02 nov. 2017 19:02:42,199 DEBUG SQL(109) -
    insert
    into
        lambda
        (id_lambda, color, city)    
    values
        (?, ?, ?)
02 nov. 2017 19:02:42,201 DEBUG BatchingBatch(110) - Executing batch size: 4
02 nov. 2017 19:02:42,253 DEBUG Expectations(78) - Success of batch update unknown: 0
02 nov. 2017 19:02:42,254 DEBUG Expectations(78) - Success of batch update unknown: 1
02 nov. 2017 19:02:42,254 DEBUG Expectations(78) - Success of batch update unknown: 2
02 nov. 2017 19:02:42,254 DEBUG Expectations(78) - Success of batch update unknown: 3
02 nov. 2017 19:02:42,254 DEBUG SQL(109) -

The thing to remember and keep in mind is that the Hibernate logs are one thing and what ACTUALLY happens in the database is another thing.

The postgreSQL logs (C:\Program Files\PostgreSQL\9.4\data\pg_log\postgresql-2017-11-06_100916.log) show a batched insert statement :


2017-11-02 19:02:42 CET LOG:  execute <unnamed>: insert into lambda (id_lambda, color, city) values ($1, $2, $3),($4, $5, $6),($7, $8, $9),($10, $11, $12)
2017-11-02 19:02:42 CET DETAIL:  parameters : $1 = '1', $2 = 'blue', $3 = 'Paris', $4 = '2', $5 = 'red', $6 = 'Dakar', $7 = '3', $8 = 'red', $9 = 'Lisbon', $10 = '4', $11 = 'violet', $12 = 'Helsinki'

To make it work with postgreSQL it is necessary to use the latest JDBC postgreSQL driver :

...
 <dependency>
      <groupId>org.postgresql</groupId>
      <artifactId>postgresql</artifactId>
      <version>42.1.4.jre7</version>
  </dependency>
...

That version of the driver supports batched insert statements through the use of the reWriteBatchedInserts property :

...
<bean id="poolPostgresql" class="org.apache.tomcat.jdbc.pool.PoolProperties">
...
<property name="dbProperties">
     <value>reWriteBatchedInserts=true</value>
</property>
</bean>
...

More info on that property here :
https://github.com/pgjdbc/pgjdbc/blob/master/README.md