I am working with a backend service (Spring Boot 2.2.13.RELEASE + Hikari + JOOQ) that uses an AWS Aurora PostgreSQL DB cluster configured with a Writer (primary) node and a Reader (Read Replica) node. The reader node has just been sitting there idle/warm waiting to be promoted to primary in case of fail-over.
Recently, we've decided to start serving queries exclusively from the Reader Node for some of our GET endpoints. To achieve this we used a "flavor" of RoutingDataSource so that whenever a service is annotated with @Transactional(readOnly=true) the queries are performed against the reader datasource.
Until here everything was going smooth. However after applying this solution I've noticed a latency increase up to 3x when compared with the primary datasource.
After drilling down on this I found out that each transaction was doing a couple of extra round trips to the db to SET SESSION CHARACTERISTICS:
SET SESSION CHARACTERISTICS READ ONLY ACTUAL QUERY/QUERIES SET SESSION CHARACTERISTICS READ WRITE
To improve this I tried to play with the readOnlyMode setting that was introduced in pg-jdbc pg-jdbc 42.2.10. This setting allows to control the behavior when a connection is set to read only (readOnly=true).
https://jdbc.postgresql.org/documentation/head/connect.html
In my first attempt I used readOnly=true and readOnlyMode=always. Even though I stooped seeing the SET SESSION CHARACTERISTICS statements, the latency remained unchanged. Finally I tried to use readOnly=false and readOnlyMode=ignore. This last option caused the latency to decrease however it is still worse than it was before.
Has someone else experience with this kind of setup? What is the optimal configuration? I don't have a need to flag the transaction as read only (besides to tell the routing datasource to use the read replica instead) so I would like to figure out if it's possible to do anything else so that the latency remains the same between the Writer an Reader Nodes.
Note: At current moment the reader node is just service 1% of all the traffic (+- 20req/s).
