Pgbouncer - Can I use pgbouncer for load balancing the request in postgres cluster?

Question

Currently, I am using pgbouncer for connection pooling in the postgresql cluster. I just want to make sure, Whether it is possible to load balance request between the nodes in the postgresql cluster using pgbouncer.

Answer 1

From the PgBouncer FAQ

How to load-balance queries between several servers?

PgBouncer does not have internal multi-host configuration. It is possible via some external tools:

1. DNS round-robin. Use several IPs behind one DNS name. PgBouncer does not look up DNS each time new connection is launched. Instead it caches all IPs and does round-robin internally. Note: if there is more than 8 IPs behind one name, the DNS backend must support EDNS0 protocol. See README for details.

2. Use a TCP connection load-balancer. Either LVS or HAProxy seem to be good choices. On PgBouncer side it may be good idea to make server_lifetime smaller and also turn server_round_robin on - by default idle connections are reused by LIFO algorithm which may work not so well when load-balancing is needed

Answer 2

Now there's pgbouncer-rr-patch(pgbouncer fork by AWS) that can do load balancing:

Routing: intelligently send queries to different database servers from one client connection; use it to partition or load balance across multiple servers/clusters.