Mapping PostgreSQL serial type with Hibernate annotations I am using Hibernate 3.3 and PostgreSQL 8.x and would like to use Hibernate annotations to map. PostgreSQL - AUTO INCREMENT. The type name serial creates an integer columns. The type name bigserial creates a bigint column. Bigserial should be used if you anticipate the use of more than 231 identifiers over the lifetime of the table. The type name smallserial creates a smallint column.
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.SEQUENCE
instead ofGenerationType.IDENTITY
- rely on the
hi/lo
algorithm 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
pooled
andpooled-lo
identifier generators (these are optimizations ofhi/lo
that 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:
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. |