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:
Mode | Description |
---|---|
session | One connection per client session. |
transaction | One connection per transaction. |
statement | One connection per statement. |
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.
It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.
https://www.w3resource.com/PostgreSQL/snippets/postgresql-pgbouncer.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics