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 ?