When you are working with a large-scale installation, it is sometimes quite likely that you have to deal with many concurrent open connections. Nobody will put up 10 servers to serve just two concurrent users—in many cases this makes simply no sense. A large installation will usually have to deal with hundreds or even thousands of concurrent connections. Introducing a connection pooler such as pgbouncer will help to squeeze more performance out of your systems.
Usually creating thousands and thousands of connections can be quite an overhead because every time a connection to PostgreSQL is created a fork() call is required. If a connection is only used for a short period of time, this can be expensive to do. This is exactly when pgbouncer should be used. Basically pgbouncer is not a replication-related tool—however, we have decided to include it in this book because it is often used in combination with replication to make it work more efficiently.
In this chapter we will take a deep look at pgbouncer and see how it can be installed and used to speed up your installation. It is not meant to be a comprehensive guide to pgbouncer and it can in no way replace the official documentation.
The following topics will be covered in this chapter:
- The purpose of pgbouncer
- Fundamental concepts of connection pooling
- Installing pgbouncer
- Configuring and administering pgbouncer
- Performance tuning
- Making pgbouncer work with Java
As stated before, the basic idea of pgbouncer is to save on connection-related costs. When a user creates a new database connection, it usually means burning a couple hundred kilobytes of memory. This consists of around 20 kb of shared memory and the amount of memory used by the process serving the connection itself. While the memory consumption itself might not be a problem, the actual creation process of the connection can be comparatively time consuming. What does time consuming mean? Well, if you create a connection and use it, you might not even notice the time PostgreSQL needs to fork a connection. But, let us take into account, what a typical website does. It opens a connection, fires a handful of simple statements, and disconnects—even if creating a connection can be barely noticed it is still a fair amount of work compared to all the rest. How long can looking up a handful of phone numbers or some other trivial information take after all? So, the less work a single connection has to do in its lifecycle, the more important the time to actually create the connection becomes.
pgbouncer solves this problem by placing itself between the actual database server and the heavily used application. To the application, pgbouncer looks just like a PostgreSQL server. Internally pgbouncer will simply keep an array of open connections and pool them. Whenever a connection is requested by the application pgbouncer will take the request and assign a pooled connection. In a way it will act like a proxy.
The main advantage here is that pgbouncer can quickly provide a connection to the application because the real database connection already exists behind the scenes. In addition to that a very low memory footprint can be observed. Users reported a footprint which is a little as 2 KB per connection. This makes pgbouncer ideal for very large connection pools.