Optimizing 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:
| [database] section | You can add databases, define their hosts, ports, assign alias names for the databases and specify a database user login and password (all clients connected to the specified alias name through PgBouncer will use the DB user login and password specified in this section). |
| [pgbouncer] section | Specify IP addresses which will be listened by PgBouncer daemon. Use the Listen_addr = * to listen to all available addresses. |
| auth_file | PgBouncer is not a part of the PostgreSQL, so you need to provide the list of users with encrypted passwords required to connect to the PgBouncer. Auth_file is a directory path to the file containing usernames and passwords. |
| auth_type | (md5 | crypt | plain | trust | any) Md5 is an argument for using passwords specified in the auth_file. Md5 is set by default. |
| max_client_conn | The maximum amount of clients that can connect to the pool (100, by default). |
| admin_users | Assigning privileges with admin console access. |
| default_pool_size | Assigning the max number of server connections per each user/database combination. |
| reserve_pool_size | The max number of additional connections allowed in case any trouble occurs. |
| pool_mode | Defines pooling mode: (session) PgBouncer assigns a server connection to the client. When the client application disconnects the connection is released back into the pool. (transaction) PgBouncer assigns a server connection to the client application until the transaction is finished. (statement) PgBouncer assigns a server connection for each statement of the client application. |
As you can see PgBouncer provides an easy solution for managing connection pools, especially when dealing with multi-threaded architecture. By using existing connections PgBouncer eliminates the necessity of creating a database connection for each client.
Our developers have thoroughly analyzed mapping schemes of PgBouncer and configured DataSunrise Database Security Suite to provide functioning to the full extent alongside with this connection pooler.
