Batch insert execute too much select nextval ('sequence')

Hibernate ORM
1

Dear Developer Team of Hibernate,

Now I’m trying to enhance performance of my web application, I use spring JPA - Hibernate 5.4.15.Final, Postgres 12 and manage transaction by Hibernate. 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 data 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.SEQUENCE:
_ 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, 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.

Hope to see your response soon.
Thank you