deadpool-postgres with connections to multiple hosts

currently we use tokio_postgres order of connecting to hosts. This means we go in the order of the hosts. I would prefer to round robin over the hosts and allow connections to multiple hosts simultaniously. My usecase: I connect to cockroachdb using deadpool-postgres.

You can already configure multiple hosts using Config::hosts: https://docs.rs/deadpool-postgres/0.7.0/deadpool_postgres/config/struct.Config.html#structfield.hosts - I guess that's what you meant by "tokio_postgres order of connecting to hosts"?

I currently wonder what's the best way to implement such feature. The recycling happens on Pool::get and there is no background task to check the health or making sure that the load is evenly distributed. e.g. If you have two servers (A and B) and B goes offline for a brief moment of time the connections to B will switch to A when recycled. So you end up with a pool that only has connections to A and even if B goes online again no connection will ever be established to B again unless a connection to A is dropped.

I feel like the Manager implementation would need to be a lot smarter with some kind of health check to the endpoint servers. Upon recycling it would need to check if there are other healthy servers available which don't have enough connections and drop a possibly healthy connection if that's the case.

@godofdream I wonder if you have found a solution for your problem?

I keep coming back to this ticket from time to time wondering about how this could be implemented in a robust way without adding a lot of logic to deadpool inside some kind of background task. I was wondering if a combination of deadpool-postgres + pgbouncer could do the trick, but after digging into it a bit I found the following:

When using pgbouncer with pool_mode=session it can end up exactly like described earlier. Since deadpool-postgres holds onto the connection forever there is no way to tell pgbouncer that it's now okay to terminate a healthy connection to switch to a different server that just became available. It would be nice if there was a command that could be executed to tell pgbouncer: "This connection is being returned to an internal connection pool. Please disconnect if rebalancing is desired."

When using pgbouncer with pool_mode=transaction it should almost do the trick but this breaks the statement cache of deadpool-postgres so I wouldn't recommend this unless you're not using the prepare_cached or prepare_typed_cached methods.

Oh wait... pgbouncer doesn't even support load-balancing: https://www.pgbouncer.org/faq.html#how-to-load-balance-queries-between-several-servers :facepalm: