Spring JPA - Hibernate: Batch insert execute too much select nextval (‘sequence’)

Question

Now I'm trying to enhance performance of my web application, I use spring JPA 2.3.0- Hibernate 5.4.15.Final, Postgres 12 and manage transaction by @Transaction. The web app is deployed on aws beanstalk, run multiple instances at the same time, but the database instance is not scalable. And I use bigSerial type for ID of tables.

For instance, I have a STUDENTS table, ID is bigSerial and some other columns. I got a problems when using

@GeneratedValue(strategy = GenerationType.IDENTITY)

, Hibernate couldn't batch insert when saving a list of entities. And I try to use

@GeneratedValue(strategy = GenerationType.AUTO, generator = "students_id_seq") 
@SequenceGenerator(name = "students_id_seq", sequenceName = "students_id_seq")

hibernate.id.new_generator_mappings=false
hibernate.jdbc.batch_size=10 
hibernate.order_inserts=true 
hibernate.order_updates=true
hibernate.batch_versioned_data=true

It seem Hibernate could batch insert, but the problem is Hibernate execute select nextval ('students_id_seq') multiple times. If an entity list has 30 items, Hibernate execute select nextval 30 times, and 3 times for batch insert query.

Some statistics:

If using GenerationType.IDENTITY
- save(entity):
  - insert into ... : execute once
- saveAll(n entities)
  - insert into ... : execute n times
If using GenerationType.SEQUENCE/ GenerationType.AUTO
- save(entity):
  - select nextval ('students_id_seq'): execute once
  - insert into ... : execute once
- saveAll(n entities):
  - select nextval ('students_id_seq'): execute n times
  - insert into ... : execute n/batch_size times

In conclusion, If using GenerationType.AUTO or GenerationType.SEQUENCE with allocationSize = 1:

when insert one entity, application increases 100% times to execute queries ( from one insert query only increase to 2 queries: select nextval, and insert query )
when batch insert, application increase more than 10% if batch_size = 10

My question is, is there anyway to batch insert but not execute to many select nextval query ? Something likes GenerationType.IDENTITY, not execute select nextval, just batch insert and IDs will be handled by sequence in the database.

When I test with GenerationType.SEQUENCE and allocationSize=1 (GenerationType.AUTO), the application executes too much select nextval query, I think It is even worse than the IDENTITY strategy. And for some reasons, I don't want to use allocationSize, it may lead to duplicate primary key error when run insert query manual or when migrate data or some other cases.

After some research, I found a way to get a value list of a sequence:

select nextval ('students_id_seq') from generate_series(1,10);

We can replace 10 by entityList.size() or number of entities doesn't have ID in the entityList when batch insert, just get enough to use, don't create too much gap between IDs, but I'm not sure whether or not Hibernate supported, if yes, please share me the documentation to reference.

Thank you

https://discourse.hibernate.org/t/batch-insert-execute-too-much-select-nextval-sequence/4232

Jens_Schauder_66686 · Answer

What you are looking for is the HiLo algorithm for id generation.

For every id generated from a sequence it generates a number of ids on the client without accessing the database.

You configure it on your entity as this:

    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "hilo_sequence_generator")
    @GenericGenerator(
            name = "hilo_sequence_generator",
            strategy = "org.hibernate.id.enhanced.SequenceStyleGenerator",
            parameters = {
                    @Parameter(name = "sequence_name", value = "hilo_seqeunce"),
                    @Parameter(name = "initial_value", value = "1"),
                    @Parameter(name = "increment_size", value = "3"),
                    @Parameter(name = "optimizer", value = "hilo")
            })
    @Id
    private Long id;

Spring JPA - Hibernate: Batch insert execute too much select nextval (‘sequence’)

1 Answers