Is there a way with Spring Boot and Hibernate to batch insert entities with UUID primary keys, using postgres?

user_144578 0
Viewed 826

So I've done my homework as far as I know. I know auto generated keys cannot be inserted. But how do I get around this for primary keys that are a UUID?

I have this primary key configuration

@Id
@GeneratedValue(generator = "uuid")
@GenericGenerator(name = "uuid", strategy = "uuid2")
private UUID id;

I was hoping this was not considered an 'AUTO' strategy but it seems it is, because my entities are still being inserted individually, wrecking my performance with 10k inserts. Why could this not be considered a sequence strategy? The UUIDs are guaranteed unique anyway so there shouldn't be anything technical in the way of just generating a number of UUIDs and batch inserting them.

I do have the appropriate settings in my application.properties:

spring.jpa.properties.hibernate.jdbc.batch_size=50
spring.jpa.properties.hibernate.order_inserts=true
spring.jpa.properties.hibernate.order_updates=true
spring.jpa.properties.hibernate.jdbc.batch_versioned_data=true

And I do use a PagingAndSortingRepository's method saveAll to save the entities.

Some logs:

03:12:01.122 |  INFO | o.h.e.i.StatisticalLoggingSessionEventListener | Session Metrics {
    35000 nanoseconds spent acquiring 1 JDBC connections;
    0 nanoseconds spent releasing 0 JDBC connections;
    1533100 nanoseconds spent preparing 8 JDBC statements;
    49660800 nanoseconds spent executing 5 JDBC statements;
    5137013700 nanoseconds spent executing 401 JDBC batches;
    0 nanoseconds spent performing 0 L2C puts;
    0 nanoseconds spent performing 0 L2C hits;
    0 nanoseconds spent performing 0 L2C misses;
    6933591600 nanoseconds spent executing 2 flushes (flushing a total of 60152 entities and 40294 collections);
    244354300 nanoseconds spent executing 5 partial-flushes (flushing a total of 60009 entities and 60009 collections)
}

Which is interesting because it does mention batches. However, with spring.jpa.show-sql=true enabled I see several thousands of log lines like the following:

Hibernate: insert into ingestion_id_mapping (derive_ingestion_id, ingestion_id, project_id, metric_id, subject_id) values (?, ?, ?, ?, ?)

Which doesn't seem to correlate with the number in the batching stats.

jpa postgresql hibernate spring java
2 Answers

Replicating your sample as follows, it actually seems to work as expected, assuming that the reWriteBatchedInserts jdbc connection property is set:

spring.datasource.url=jdbc:postgresql://localhost:5432/postgres?reWriteBatchedInserts=true

Using e.g. with spring.jpa.properties.hibernate.jdbc.batch_size=8, a corresponding postgres log would look like this one when inserting 8 newly created entities via repository.saveAll:

postgres_db_1  | 2021-06-02 15:22:54.327 UTC [386] LOG:  
  execute <unnamed>: /* insert org.demo.batchinsert.UuidEntity */
  insert into uuid_entity (id) values ($1),($2),($3),($4),($5),($6),($7),($8)
...
user_131561260

Well actually you can't really say if batching is working just by looking at hibernate logs.

You have to proxy the JDBC Driver or the Datasource with tool like P6Spy. Then you'll probably see that batching is actually working.

You can use this decorator for that : https://github.com/gavlyukovskiy/spring-boot-data-source-decorator.

Now if you want to speed up the batch processing on the database side too the property reWriteBatchedInserts might be used.

user_49117750
Related