Why To Avoid PostgreSQL (BIG)SERIAL In Batching Inserts Via Hibernate
Description: In PostgreSQL, using GenerationType.IDENTITY will disable insert batching. The (BIG)SERIAL is acting "almost" like MySQL, AUTO_INCREMENT. In this application, we use the GenerationType.SEQUENCE which permits insert batching, and we optimize it via the hi/lo optimization algorithm.
Key points:
- use
GenerationType.SEQUENCEinstead ofGenerationType.IDENTITY - rely on the
hi/loalgorithm to fetch a hi value in a database roundtrip (the hi value is useful for generating a certain/given number of identifiers in-memory; until you haven't exhausted all in-memory identifiers there is no need to fetch another hi) - you can go even further and use the Hibernate
pooledandpooled-loidentifier generators (these are optimizations ofhi/lothat allows external services to use the database without causing duplication keys errors) - optimize batching via
spring.datasource.hikari.data-source-properties.reWriteBatchedInserts=true
Output example:
SERIAL%20and%20Batching%20Inserts.png)
| If you need a deep dive into the performance recipes exposed in this repository then I am sure that you will love my book "Spring Boot Persistence Best Practices" | If you need a hand of tips and illustrations of 100+ Java persistence performance issues then "Java Persistence Performance Illustrated Guide" is for you. |
|
|
|
SERIAL%20and%20Batching%20Inserts.png){kind=link}