PostgreSQL Connection Pooling with PgBouncer

PostgreSQL Connection Pooling with PgBouncer

PgBouncer is a lightweight PostgreSQL connection pooler that improves database performance and scalability by managing client connections efficiently. It helps reduce the overhead of frequently opening and closing connections, making it ideal for high-traffic applications.

1. Why use PgBouncer?

• Reduces connection overhead.
• Handles thousands of client connections efficiently.
• Lightweight with minimal resource usage.
• Supports session pooling, transaction pooling, and statement pooling.

2. Installation

On Debian/Ubuntu:

sudo apt update  
sudo apt install pgbouncer  

On CentOS/RHEL:

sudo yum install pgbouncer    

3. Configuration

Key Configuration Files:

• pgbouncer.ini: Primary configuration file.
• userlist.txt: Stores credentials for authentication.

Sample pgbouncer.ini:

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

[pgbouncer]  
listen_addr = 0.0.0.0  
listen_port = 6432  
auth_type = md5  
auth_file = /etc/pgbouncer/userlist.txt  
pool_mode = session  
max_client_conn = 100  
default_pool_size = 20  
logfile = /var/log/pgbouncer/pgbouncer.log  
pidfile = /var/run/pgbouncer/pgbouncer.pid   

Add Users to userlist.txt:

 "postgres" "password"  
"myuser" "mysecurepassword"  

4. Starting PgBouncer

To start PgBouncer:

pgbouncer /etc/pgbouncer/pgbouncer.ini   

To restart or stop PgBouncer:

sudo systemctl restart pgbouncer  
sudo systemctl stop pgbouncer    

5. Pooling Modes

PgBouncer offers three pooling modes:

Example (in pgbouncer.ini):

pool_mode = transaction  

6. Performance Tuning

• Increase max_client_conn to handle more connections.
• Adjust default_pool_size based on available resources.
• Enable connection timeouts:

server_idle_timeout = 60  

7. Integration with PostgreSQL

Change your application’s connection settings to point to PgBouncer:

Database Host: 127.0.0.1  
Database Port: 6432  
Database User: <pgbouncer_user>  
Database Password: <pgbouncer_password>  

Example use Case

Scaling High-Traffic Applications

For applications handling 10,000+ concurrent users, PgBouncer reduces overhead and ensures optimal performance without increasing PostgreSQL's native connection limits.

All PostgreSQL Questions, Answers, and Code Snippets Collection.