Should a connection pooler be run on my database server or my app server?
I'm getting ready to start using PGBouncer, but I'm unsure of whether it should be used on my database server or on the app servers. If it's on the app servers, there will necessarily be multiple pools of connections, vs one central pool of connections for the app servers to share, but then the TCP connections have to be recreated for each new query instead of also being pooled, presumably. Which is the "proper" way to use a connection looker like PGBouncer, and are the points I make about each even valid?
Note:
For those who stumble into this question, see PgBouncer FAQ (the last question).
Personally, I'd put it on the app server. Here's why.
PGBouncer basically implements connection pooling, which is a useful (although sometimes annoyingly troublesome) thing that reduces overall application latency by removing the cost of setting up a new connection to the database. In many cases, this is done by the database connection driver itself - see the ADO.NET MSSQL driver on Windows, PDO on PHP, etc. etc. The principal aim being to minimize the time cost between the code saying "I need to talk to the database" and then being able to actually start throwing SQL commands around.
The drivers mentioned above implement the connection pooling, so the code has to do very little before it gets to the point that the database can receive SQL commands.
PGBouncer is an odd case, because you've still got to open a connection to the PGBouncer daemon, wherever it may be. Because you're trying to minimize connection time, it therefore makes sense to put the daemon as close to the application code as possible. Ideally, you'd connect over a socket on the same box, as then you don't have to go through the TCP rigmarole just to get to the connection pooler.
As with everything though, YMMV. Test, test, and then test some more.