PostgreSQL Load balancing - connection to Master/Slave based on SQL query type
I have came across with some interesting idea for scaling.
With JDBC driver v9.4-1200 onward is possible to set up a Tomcat server in a way it uses multiple jdbc URLs. Each of which is possible to mark as "any", "master", "slave", "secondary", "preferSlave" and "preferSecondary". Driver then makes a distinction which queries are write and read and sends them to appropriate DB (i.e INSERTS goes to DB on which the user has permissions to write, SELECTS goes to read only DB etc..). That would effectively divide whole load between several DB. Of coarse this only make sense on HA configuration, there needs to be a Master/Slave and replication needs to be set up properly.
This also only applies to TomCat config only. Since Tomcat is used for different Persistence Providers, config.xml is almost in default. and Persistence configurations are made in platformSettings.json.
Would be possible to set up such an environment with platformSettings only?
Allows opening connections to only servers with required state, the allowed values areany, master, slave and preferSlave. The master/slave distinction is currently done by observing if the server allows writes. The value preferSlave tries to connect to slaves if any are available, otherwise allows falls back to connecting also to master.
Version 42.2.0 (2018-01-17)
slave and preferSlave values for the targetServerType connection property have been deprecated in favour of secondary and preferSecondary respectively.
targetServerType = String
Allows opening connections to only servers with required state, the allowed values areany, master, slave, secondary, preferSlave and preferSecondary. The master/slave distinction is currently done by observing if the server allows writes. The value preferSecondary tries to connect to secondary if any are available, otherwise allows falls back to connecting also to master.