Connection Pooling with PgBouncer

The majority of web applications use databases to store their data. Client applications can retrieve the information from the database by establishing a connection to it. Every query consumes resources including memory, port allocation and CPU time. A transaction can take milliseconds but establishing a connection can take up to several seconds.

In order to simplify the process of connecting to PostgreSQL, especially when a high volume of client connections is expected, you can use PgBouncer to effectively manage client-database connections. It is a popular connection pooler that connects to the database once and uses this connection many times for different clients. It is not the only advantage of using PgBouncer.

Let’s say you have 100 clients that need to connect to your database and you don’t want to share the database user password among these clients. In this case, you can use PgBouncer. You can set individual logins and passwords to connect so that PgBouncer and clients will not use the database user passwords. Also, you can assign an alias name for the database, so clients will not see the real name of the database.

Instead of creating a database connection, connection with the PgBouncer is created which uses already existing database connections.

DataSunrise Database Security Suite alongside with many other features supports pooling with PgBouncer. Working effectively with the clients-database mapping it maintains comprehensive database security when PgBouncer is running.

Advantages of using PgBouncer connection pools:

  • reducing processing time
  • reducing the consumption of server resources for maintaining a large number of server connections to one or several databases
  • support of online restart or upgrade without dropping client connections
  • hiding the real name of the database
  • the ability to avoid sharing a database user password with all client applications

Configuring PgBouncer

After the installation, PgBouncer functions as a service on Windows and as a daemon on Linux (named as pgbouncer in both occasions).

To change configuration settings, find the pgbouncer.ini file in the following directory: pgbouncer/share. It contains the following parameters: