How to Use PgBouncer with PostgreSQL for Connection Pooling

To use PgBouncer with PostgreSQL, install PgBouncer as a lightweight connection pooler, configure it to connect to your PostgreSQL server, and update your application to connect through PgBouncer's port instead of directly to PostgreSQL. This setup reduces connection overhead and improves database performance.

Syntax

The main configuration for PgBouncer is done in the pgbouncer.ini file. Key parts include:

• [databases]: Defines database connection strings PgBouncer will use.
• [pgbouncer]: Contains PgBouncer settings like listening port and authentication.
• listen_addr: IP address PgBouncer listens on.
• listen_port: Port PgBouncer listens on (default 6432).
• auth_type: Authentication method (e.g., md5).
• auth_file: File with user credentials.

ini

[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = session
max_client_conn = 100

Example

This example shows how to configure PgBouncer to pool connections for a PostgreSQL database named mydb. It listens on localhost port 6432 and uses md5 authentication.

After configuring, start PgBouncer and connect your application to 127.0.0.1:6432 instead of PostgreSQL's port 5432.

bash

# Create userlist file with credentials

echo '"myuser" "md5passwordhash"' > /etc/pgbouncer/userlist.txt

# pgbouncer.ini content
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = session
max_client_conn = 100

# Start PgBouncer
pgbouncer /etc/pgbouncer/pgbouncer.ini

# Connect using psql through PgBouncer
psql -h 127.0.0.1 -p 6432 -U myuser mydb

Output

psql (13.4)
Type "help" for help.

mydb=>

Common Pitfalls

• Wrong authentication setup: PgBouncer requires a separate userlist file with md5 hashed passwords; forgetting this causes connection failures.
• Connecting to wrong port: Applications must connect to PgBouncer's port (default 6432), not PostgreSQL's port (5432).
• Pool mode confusion: Using transaction or statement pool modes requires understanding of session behavior; session mode is simplest for beginners.
• Not adjusting max connections: PgBouncer limits client connections; set max_client_conn appropriately.

bash

# Wrong way: connecting directly to PostgreSQL port
psql -h 127.0.0.1 -p 5432 -U myuser mydb

# Right way: connect through PgBouncer port
psql -h 127.0.0.1 -p 6432 -U myuser mydb

Quick Reference

Summary tips for using PgBouncer with PostgreSQL:

• Configure pgbouncer.ini with correct database and authentication settings.
• Use userlist.txt with md5 hashed passwords for authentication.
• Start PgBouncer and connect your app to PgBouncer's port.
• Choose pool mode based on your app's transaction needs.
• Monitor PgBouncer logs for connection issues.

Key Takeaways

Install and configure PgBouncer to act as a connection pooler between your app and PostgreSQL.

Update your application to connect to PgBouncer's listening port, not directly to PostgreSQL.

Use md5 authentication with a proper userlist file for PgBouncer access.

Choose the right pool mode (session, transaction, or statement) based on your workload.

Monitor PgBouncer to ensure it handles connections efficiently and troubleshoot issues.