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 ?

  • Wouldn't it be wiser to reduce the complexity of your queries and increase their performance, rather than to increase the complexity of your environment? – Gerard H. Pille Mar 13 '20 at 12:17
  • Yes indeed that is what was done in the end ... – avrono Mar 13 '20 at 12:26
  • Sorry about that, I hadn't noticed this was asked two years ago. But congrats for choosing that way. – Gerard H. Pille Mar 13 '20 at 12:30

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

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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