Using PgBouncer

This article introduces the basics of using PgBouncer on the Instaclustr managed platform. It assumes that you have already created a PostgreSQL cluster with PgBouncer enabled, as demonstrated in the getting started guide.

PgBouncer is a program that sits in front of PostgreSQL to enable connection pooling, allowing the cluster to serve more clients simultaneously and offering improved performance in certain workloads. Effectively utilising PgBouncer requires an understanding of the different pool modes, the type of workload to be directed via PgBouncer, and how to connect to a PostgreSQL database via PgBouncer.

PgBouncer Pool Modes

Two connection pooling modes are available by default on the Instaclustr managed platform: These are session and transaction mode. The difference between pooling modes relates to how connections to the PostgreSQL server are assigned to clients of PgBouncer.

Session mode

In session mode, PgBouncer clients retain a connection through to the PostgreSQL server for the entire time they are connected to PgBouncer. This is necessary for programs that rely on prepared statements or other elements of session state to function correctly. The primary benefit of using PgBouncer in this scenario is reducing the overhead associated with establishing connections to the PostgreSQL server. PgBouncer will retain connections to PostgreSQL for a period of time after a client disconnects, which can then be allocated to another client. Establishing connections to PostgreSQL is quite slow and resource intensive compared to connecting to PgBouncer, and thus this can result in significant performance improvements in some workloads.

Transaction mode

In transaction mode, PgBouncer clients retain a connection through to PostgreSQL only for the time taken to execute a single transaction. Once the transaction is complete, the connection is returned to the pool and may be allocated to a different client. This breaks the use of prepared statements and will prevent programs which rely on session state from functioning correctly.

There are a number of benefits associated with the use of transaction pooling. For one, each connection to PostgreSQL has a significant memory overhead associated with it. By reducing the number of concurrent connections to PostgreSQL required to handle a certain number of client connections, the overhead associated with each client is drastically reduced. This can enable PostgreSQL to serve many more clients than would otherwise be possible.

Additionally, the use of transaction pooling can result in significantly higher throughput than that which would be achieved if each client connected directly to PostgreSQL. This can be put down to a number of factors, including both the reduced memory usage and a reduction in lock related overheads due to fewer transactions being processed simultaneously.

Further configurability

PgBouncer offers a third connection pooling option—statement mode. This mode offers a number of benefits, however it is only suitable for very specific use cases. If you believe you need statement mode enabled on your cluster, or need pool sizes or other configuration options altered, you are encouraged to raise a support ticket.

Connecting to PgBouncer

Connecting to PgBouncer is handled in exactly the same way as connecting to PostgreSQL directly. The key difference to be aware of is that PgBouncer listens on port 6432, whereas PostgreSQL listens on port 5432.

Prior to connecting to PgBouncer, you will need to ensure that the firewall rules have been configured to permit the client’s ip address access to the service.

To do so, simply navigate to the Firewall Rules page, visible in the list of pages in the cluster’s dropdown menu.

Once on this page, you will see a section for both “POSTGRESQL ALLOWED ADDRESSES” and “PGBOUNCER ALLOWED ADDRESSES”.

Simply add the relevant IPv4 CIDR address (or AWS security group, if using VPC peering) to the PgBouncer section, then click on the blue Save button. Multiple addresses should be on different lines. Once this is done, the client will be able to connect to PgBouncer.

Note: PgBouncer on the Instaclustr managed platform honours target session attributes, unlike its default configuration. When a failover occurs, connections to both nodes involved in the failover will be dropped momentarily; so as to enable clients to obtain the new session attributes and change nodes if required.

For more information on how to connect to PostgreSQL/PgBouncer, please see the examples on the Connection Info page in Console. If you have any further questions regarding the use of PgBouncer on the Instaclustr managed platform, please contact support.