CODEWITHSUNDEEP
postgresqlamazon-web-servicesspring-bootspring-data-jpa
Isidora
Isidora

Reputation: 1

Is it possible for certain queries that are read-only to hit the read replicas in Spring Boot?

I'm trying to optimize query execution times for huge loads by hitting the read replicas instead of the master DB node for the read-only (SELECT) queries.

Is there an easy way to specify this in a Spring Boot service?

How could I check/test if the queries are hitting the right replica? In AWS logs?

I see here there is a way for MySQL, I guess it should work for RDS Postgres as well.

https://cloud.spring.io/spring-cloud-static/spring-cloud-aws/2.1.3.RELEASE/single/spring-cloud-aws.html#_read_replica_configuration

Upvotes: 0

Views: 459

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246788

There is no way to do that automatically, because you cannot tell if a statement is reading or writing just by looking at it (what about SELECT delete_customer(42);?).

Your application will have to use two data sources, one for reading and one for writing, and explicitly use the appropriate one. There is pgPool that tries to automatize this, but I would recommend not to use that.

Apart from that, there are some problems with using streaming replication for scaling that you have to cope with:

  • Unless you are using synchronous replication with synchronous_commit = remote_apply, it can happen that a read request won't see data that were written immediately before. But you cannot use that setting unless you set max_standby_streaming_delay = 0, otherwise the inevitable replication conflicts will delay your COMMITs unbearably.

  • Unless you set max_standby_streaming_delay very high, you will get queries canceled by the inevitable replication conflicts, so that you have to repeat them if necessary.

I don't mean to say that you cannot use PostgreSQL streaming replication for horizontal scaling, you just have to be able to cope with the implications. My article has some more detail about replication conflicts.

Upvotes: 1

Related Questions