3 reasons to use PgBouncer.
3 reasons to use PgBouncer.For those who do not know, PgBouncer a lightweight connections pooler for PostgreSQL. And below there are 3 reasons why it is be used.
- It reduces PostgreSQL resource consumption (memory, backends, fork).
- It supports online restart/upgrade without dropping client connections.
- It allows allows PostgreSQL restart/upgrade without dropping client connections.
PostgreSQL forks a separate process for each client connection. At the same time applications do not always use connection to the database permanently, connections are often idle. Thus PgBouncer creates a pool of database connections and provides these connections to clients when the connection is required. Thus, if you have a lot of client connections to the database, PgBouncer can reduce the number of PostgreSQL backends processes. The response time between database and a client also decreases, because no need to fork a new backend process.
See details on slides 18, 19, 20 of our presentation at RIT fest.
It supports online restart/upgrade without dropping client connections.
Considering that PgBouncer become a frontend for the database, it is important to keep the database available. So if you want to update Pgbouncer configuration or upgrade PgBouncer package, PgBouncer support online restart/upgrade. This function allows you to restart PgBouncer without dropping client connections. Thus, maintenance can be performed transparently for applications and clients. Note that the unix_socket_dir option must be enabled in the configuration file pgbouncer.ini.
It allows allows PostgreSQL restart/upgrade without dropping client connections.
But most interesting feature is that using PgBouncer we can temporarily pause and resume client connections. This is very useful when you need to restart PostgreSQL service while PostgreSQL configuration update or minor upgrade. To do this, use the PgBouncer's management interface and issue PAUSE command. All connections will be paused as long as you do not make RESUME command. For an application it looks like a delay. It is definetly better than all client connections drop with an error.
This reasons make PgBouncer very useful in your PostgreSQL environments.
And finally, a short video how to restart PostgreSQL behind the PgBouncer without losing client connections.