5

Many application servers have connection pools integrated, and even standalone applications can be configured for use one like HikariCP, Apache DBCP, etc.

So what is the advantage of using PgBouncer when the application already have a connection pool?

The most close answer I found is What are advantages of using transaction pooling with pgbouncer? which do no mention the use of another connection pool and mentions that the advantage is the idle sessions usage.

I primary use WildFly configured with min pool size, max pool size, idle timeout... so it essentially removes idle connections when they are not in use (if that it's the main advantage).

This makes me think that PgBouncer don't fit in this scenario and I just should keep using my application server connection pool only.

BTW, in transaction pooling mode, PgBouncer can't use named prepared statements which don't look like a performance wise choice.

If there is any advantage, do it plays well with the wildfly connection pool?

Community
  • 1
  • 1
JorSol
  • 450
  • 1
  • 4
  • 14
  • 1
    pgBouncer is typically used when you also want to do load-balancing between a master and slave servers. If you only connect to a single Postgres server, then you a connection pool in the app-server is enough –  May 11 '17 at 04:32

1 Answers1

5

If your application server has a connection pool included, and there is only one application server connecting to the database, it is better to use the integrated connection pool.

In such a scenario, pgBouncer would just be an extra component that makes the architecture more complicated, and you'd have the extra overhead of all the connections between the application server and pgBouncer.

If there are several application servers that connect to the same database, the question is not so simple any more. If there are just two or three application servers, you might live fine without pgBouncer.

The more application servers connect to the database server, the more database connections you will have, and this puts the database at risk: if too many of these connections become busy at the same time, your database performance and response time will drop, since the database is overloaded. In such a case pgBouncer will help by limiting the number of active connections to the database.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Yes, but this don't answer my question, let suppose I have 2 or 3 application servers connecting to the database (it's more easy to cluster the app that the db), I still don't see the advantage of using PgBouncer as you said it's just an extra component that makes the architecture more complex. – JorSol May 11 '17 at 15:21
  • You didn't say that you have multiple application servers. I'll edit the answer to comment on that. – Laurenz Albe May 12 '17 at 06:55
  • Thanks for the edit Laurenz, but still it don't fully get the advantage, I can limit the maximum number of connections in the app-server connection pool, so I don't see how this puts the database at risk, maybe if I dynamically start applications servers based on load? And again, the best feature is "Transaction pooling", which should be tested carefully since it can break many things related to session, and it loses named prepared statements. – JorSol May 12 '17 at 20:34
  • 4
    Look here, if you have 20 application servers, each with a connection pool of 50 connections, all going to the same database, you might end up with 1000 connections on the database, all of which could decide to run statements at the same time. This would bring down the database server. With pgBouncer, you can reduce the number of connections to the database, the database would stay functional, and some application threads would have to wait until a connection is available. This "throttling" increases throughput and availibility of your system. – Laurenz Albe May 13 '17 at 08:00