Horizontal scaling of cs:postgresql, looking to load-balance queries between cs:postgresql applications

juju
1

Question for @stub perhaps?

I’m looking to set up a large database cluster with queries load-balanced across nodes.

Via https://dba.stackexchange.com/q/56559:

What’s the appropriate way to do this via the cs:postgresql and cs:pgbouncer charms? Can they interoperate behind e.g. cs:haproxy?

Here’s one attempt of a hypothetical model (please excuse the syntax!)

[web]
 -> [haproxy]
       -> [pgbouncer-1]
            -> [pg-1/0 ⋯> pg-1/1] ~~.
       -> [pgbouncer-2]             |
            -> [pg-2/0 ⋯> pg-2/1] <~|
       ⋮                           ⋮                                       
       -> [pgbouncer-n]             |
            -> [pg-n/0 ⋯> pg-2/1] <~'

Legend
  []  application
  ->  TCP traffic
  ⋯>  peer relation (hot standbys replication)
  ~>  pgreplication relation (streaming replication)
2

Did you find a solution for load balancing the requests?