Can using GenerationType.SEQUENCE cause issues with Kubernetes and load balancing across multiple instances of a Spring Boot app?

Question

I'm using Spring Boot with Hibernate and PostgreSQL as my database. I'm generating primary keys for my entities using the GenerationType.SEQUENCE strategy. I'm also deploying my Spring Boot app to a Kubernetes cluster, which runs multiple instances of my app behind a load balancer.

My question is whether using GenerationType.SEQUENCE could cause any issues in this setup, specifically when running multiple instances of my app.

Here's an example of my entity:

@Entity
public class MyEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "my_sequence")
    @SequenceGenerator(name = "my_sequence", sequenceName = "my_sequence", allocationSize = 1)
    private Long id;

    // ... other properties and methods

}

Can using GenerationType.SEQUENCE cause any issues in this setup? For example, could there be conflicts when two instances of my app try to insert a new row into the database at the same time, since they may both be trying to generate the next value in the sequence? If so, how can I avoid these conflicts?

Answer 1

It will not have any conflicts because the application will call the Postgres to get the value of the next ID by calling select nextval('my_sequence'). The sequence is generated by Postgres and it will take care to ensure every request will get distinct ID even though many requests are calling it concurrently.

But of course , it is assumed that you only have one DB instance for handling the non-query requests which should be true in most cases. Otherwise, you would need some kind of distributed ID generator to generate ID rather than relying on Postgres.

By the way, if you configure allocationSize of the sequence to be 1 , why don't simply use auto-increment ID ? One of the main reason to use sequence rather than auto-increment Id in JPA as it allows for batch inserting many records by prefetching a batch of ID to the application without many network roundtrips to get the auto-increment ID one by one. But setting the allocationSize to 1 just defeat this purpose.