Hibernate not batching inserts

Question

I hope you can help me with this problem since I'm really clueless by now and none of the related questions could help me so far.

I have a collection of entities I want to batch insert into TimescaleDB (extension of Postgresql) via spring-data saveAll() and I thought I configured everything by the book, but the hibernate stats never reflect a batch insert:

1223268 nanoseconds spent acquiring 1 JDBC connections;

0 nanoseconds spent releasing 0 JDBC connections;

34076442411 nanoseconds spent executing 3408 JDBC statements;

0 nanoseconds spent executing 0 JDBC batches;

My Hibernate properties are configured via HibernatePropertiesCustomizer like this:

props.put("hibernate.generate_statistics", true);
props.put("hibernate.order_inserts", true);
props.put("hibernate.order_updates", true);
props.put("hibernate.jdbc.batch_size", "100");

I validated that a transaction context is indeed present during saveAll() by calling:

TransactionSynchronizationManager.isSynchronizationActive()

And the entity's ID looks like this. There are no @GeneratedValue or @SequenceGenerator annotations, since I use the data's timestamp (from a web API) as the ID.

@Id
@Column
private Instant time;

I even tried by adapting my connection String to control the rewrite behavior, but it did not help:

jdbc:postgresql://localhost:5432/db?reWriteBatchedInserts=true&currentSchema=finance-data

I tested this with both maven org.postgresql 42.2.9 and 42.2.13 drivers. The Spring Boot release train version is the 2.2.2.RELEASE which bundles hibernate-core 5.4.9.FINAL and spring-jdbc 5.2.2.RELEASE. The timescale docker version is 1.7.1-pg12.

Please let me know if you need any more additional information. Thanks in advance!!!

Ken Chan Ken Chan · Accepted Answer · 2020-06-07T13:09:15

From this, it mentions if the entity being batched inserted is manually assigned its ID , you have to add a @Version property. But for PostgreSQL , adding @Version is not required if using SEQUENCE generator to generate the ID.

I do not try if adding @Version can solve the problem if the ID is manually assigned but you could have a try. I personally use SEQUENCE generator to generate ID and it works without adding @Version in PostgreSQL.

And in order to generate ID effectively when using SEQUENCE generator , I also changed to use "pooled" or "pooled-lo" algorithm that is mentioned in this to reduce the database round trip to get the ID.

Here are what I do :

@Entity
public class Foo {


    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator="foo_sequence")
    @SequenceGenerator(name="foo_sequence", sequenceName = "foo_id_seq", allocationSize = 100)
    private Long id;

}

And the hibernate setting :

hibernate.order_inserts = true
hibernate.order_updates = true
hibernate.jdbc.batch_size = 50
hibernate.jdbc.batch_versioned_data = true

# For using "pool-lo" optimiser for generating ID when using JPA @SequenceGenerator
hibernate.id.optimizer.pooled.preferred = pooled-lo

And also need to make sure the sequence in PostreSQL is aligned with what is configured in @SequenceGenerator :

alter sequence foo_id_seq increment by 100;

For completeness , in case of PostgreSQL , also add reWriteBatchedInserts=true in the JDBC connection string which can provides 2-3x performance improvement which is said by the docs.

Hibernate not batching inserts

1 Answers