I have an legacy application that has a number of very complex queries that take significant time and resource to execute. Rather than rewrite the application I am looking at the possibility of doing some form of load balancing.
I firstly looked at writing a script with pg_isready to determine if the database is able to respond, if not to potentially switch to another replica. However, this approach does not give an indication of the current load on the database.
I've read up on using haproxy to do some load balancing, but it seems as if it would suffer the same problem. My question is, has anyone faced a similar problem and found a neat solution. I would like to switch to a different replica is the database load is above a certain threshold. Is this at all possible ?
Best Answer
Is the complex query read-only ? The answer to that kinda determines the solution.
You could do this with haproxy maybe. See https://githubengineering.com/context-aware-mysql-pools-via-haproxy/ for inspiration on how to measure the load of your backend (this is mysql but same idea works on pgsql)
You could probably get some good config values from my repo here https://github.com/gplv2/haproxy-postgresql
it's made for master / standby cluster but you can add a standby pgsql backend pool to this config to the example of the guys at github with their mysql machines pool and the backup pool. sounds like that is what you want