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 ?

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

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.