We have a setup running Node.Js connecting Azure load-balancer then distribute workloads to pgbouncer and finally to PostgreSQL database. While we checking pgbouncer stats we have found that LB is not evenly distributing the loads to pgbouncer.
For a while I didn't know there was another layer of pooling happening at app layer using pg.pool because I work as a DBA. From my I understanding when pg.pool initialises connection with LB let'say 10, there can be evenly distribution but after that LB is not really doing distribution but just a pass through since the pool behind LB is still connected so does the connection to pgbouncer.
Now at the event one of pgbouncer goes down, all connections may go to 2nd one. But after that even if bring first one I believe it won't get connections until a new connection is established in pg.pool. Is this setup we having is suboptimal? Is there any parameter to control idle timeout for the connection between Node.Js and LB?