[PGSQL] Connection for read and for writes

[chibenwa]

Why?

Native application for native interactions with row replica is a warm recommendation from the PG community.

Knowing we have a read to write ratio of 10/1 leveraging read replica effectively will be critical.

And better do this early.

How?

Within PGSQL postgres.properties add an option for read-replica.strategy (Along side read-replica.url):

• NONE all goes to the master: read and writes
• FULLY_ENABLED: We open 2 connection: one to the master for all writes. One to the slaves for all reads. We accept to be impacted by PG asynchronous replication.
• COHERENT-READ-REPLICA: Same that FULLY_ENABLED except that reads on mutable data (for us mailbox + mailbox_message, subscriptions) is done against the master. Reads on immutable data can be safely done on the slave (blobs ? message?). This enables leveraging at least partly read replica without giving up on consistency...

Propse a nice API for this in PG executor.

[vttranlina]

I think it would be better if It is done by Load Balancer. Ex:https://www.pgpool.net/docs/latest/en/html/runtime-config-load-balancing.html

While using Streaming replication and Hot Standby, it is important to determine which query can be sent to the primary or the standby, and which one should not be sent to the standby. Pgpool-II's Streaming Replication mode carefully takes care of this.

We distinguish which query should be sent to which node by looking at the query itself.

These queries should be sent to the primary node only

INSERT, UPDATE, DELETE, COPY FROM, TRUNCATE, CREATE, DROP, ALTER, COMMENT

SELECT ... FOR SHARE | UPDATE

SELECT in transaction isolation level SERIALIZABLE

LOCK command more strict than ROW EXCLUSIVE MODE

DECLARE, FETCH, CLOSE
...

[chibenwa]

Quote from PG boy: yes you can cheat, but always easier if done in the app, more perf, simpler setup, etc.

(Not invented by me - it's actually the only big recommendation they did to me...)

[quantranhong1999]

r2dbc-postgresql lib seems could support that out of the box: https://github.com/pgjdbc/r2dbc-postgresql?tab=readme-ov-file#connection-fail-over with loadBalanceHosts=true:

[vttranlina]

r2dbc-postgresql lib seems could support that out of the box:

it looks used for Read, I don't think "load balancer" is available for write (just my guess)

[Arsnael]

it looks used for Read, I don't think "load balancer" is available for write (just my guess)

Well I believe that's what we want :)