Spring Data JPA: How to load balance read queries between RDS Aurora PostgreSQL instances?

Viewed 1718

I have created an Aurora PostgreSQL cluster in AWS:

+ mydb (Role: Regional), (Engine: Aurora PostgreSQL), (Region and AZ: ap-southeast-2)
|
+- foo (Role: Writer),   (Engine: Aurora PostgreSQL), (Region and AZ: ap-southeast-2a), Multi-AZ (2 Zones)
|
+- bar (Role: Reader),   (Engine: Aurora PostgreSQL), (Region and AZ: ap-southeast-2b), Multi-AZ (2 Zones)

There's an endpoint for the Writer instance and an endpoint for the Reader instance:

| Endpoint name                                         | Type   |
|-------------------------------------------------------|--------|
| mydb.cluster-1234.ap-southeast-2.rds.amazonaws.com    | Writer |
| mydb.cluster-ro-1234.ap-southeast-2.rds.amazonaws.com | Reader |

In my app, I have defined a data source as usual:

spring:
  datasource:
    url: jdbc:postgresql://mydb.cluster-1234.ap-southeast-2.rds.amazonaws.com:5432/someDb?currentSchema=someSchema
    username: someUser
    password: somePass

Again, there are some ordinary entities and repositories defined in the code:

public interface ThingRepository extends JpaRepository<Thing, Long> {
}

@Entity
public class Thing {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Basic
    private String name;

    // etc.
}

Similarly, there are some ordinary READ/WRITE operations:

public void doSomething() {
    Thing thing = thingRepository.findById(123L);
    // ...
}

public void doSomethingElse() {
    Thing thing = new Thing();
    thingRepository.save(thing);
    // ...
}

The problem is all these queries are being sent to Aurora's Writer instance and my Reader instance remains unused.

But I want method calls such as findById, existsById, findAll, etc. to be load balanced between all the instances in my cluster and only operations such as save, saveAll, deleteById, deleteAll to be sent to the Writer instance.

Is there a way to do this?

2 Answers

What you could do is setup two datasources and create different repositories using the different datasources.

With JPA there is a problem though with this approach: If you read an entity and manipulate it it will get saved which probably won't work and is at least undesired when the original read went to the read instance.

So what you'd have to do to prevent this would be to detach entities after loading them, which in turn will lead to a LazyLoadingException if you don't make sure to fully load the entities before detaching.

Related