Welcome dear reader, today you will learn how to keep your PostgreSQL database fast and stable under load using connection pooling with PgBouncer. Every visitor on a busy website opens database connections, and PostgreSQL handles each one as a full process — which can exhaust your server’s memory quickly. A connection pooler solves this elegantly, and setting one up takes only a few minutes.
At re:Software S.L., we create innovative solutions for businesses that aim to digitize their processes and routines, and optimizing database connections has been key for us to provide high quality database servers.
Table of contents
- What is Connection Pooling with PgBouncer?
- How to Install PgBouncer
- How to Configure PgBouncer
- Connect Your Application to the Pool
- Conclusion
What is Connection Pooling with PgBouncer?
Opening a database connection is expensive: the server must fork a process, authenticate the user, and allocate memory — for every single connection. A PHP application might open dozens of connections at once, and PostgreSQL will keep serving each of them, even when they sit idle most of the time.
Connection pooling means keeping a small set of connections open permanently and sharing them across incoming requests. Instead of 100 connections to PostgreSQL, your applications connect to the pooler, which multiplexes them over perhaps 20 real database connections.
PgBouncer is the most popular pooler for PostgreSQL — a small, single-binary tool that adds almost no overhead. If you are unsure whether PostgreSQL is the right pick in the first place, have a read about PostgreSQL vs MySQL to compare the two engines.
How to Install PgBouncer
On Ubuntu or Debian, PgBouncer is available from the package manager:
# Install PgBouncer sudo apt install pgbouncer # Enable and start the service sudo systemctl enable --now pgbouncer
How to Configure PgBouncer
The main configuration lives in /etc/pgbouncer/pgbouncer.ini. The essential settings to review are:
[databases] # Any database name can be reached through the pooler * = host=127.0.0.1 port=5432 [pgbouncer] listen_addr = 127.0.0.1 listen_port = 6432 # Share connections per transaction: highest savings pool_mode = transaction # Max client connections allowed into the pool max_client_conn = 100 default_pool_size = 20
The most important knob is pool_mode. The session mode keeps a database connection for the whole client session, while transaction mode assigns a connection only while a transaction runs — the recommended setting for most web applications.
Next, create the user list that PgBouncer authenticates against, and restart the service:
# Add your database users to the userlist echo '"webPgUser" "CrazyPassword!"' | sudo tee -a /etc/pgbouncer/userlist.txt # Restart to apply the changes sudo systemctl restart pgbouncer
As with any database credential, keep this password private and use something harder to guess than the example above.
Connect Your Application to the Pool
From your application’s point of view, PgBouncer looks like a normal PostgreSQL server — just on port 6432 instead of 5432. Update your connection string, e.g. in a Laravel .env:
DB_HOST=127.0.0.1 DB_PORT=6432 DB_DATABASE=db_mywebsoftware DB_USERNAME=webPgUser DB_PASSWORD=CrazyPassword!
No other code changes are required — your application simply connects to the pooler, and the pooler manages the real database connections behind the scenes.
Conclusion
That’s already it to get started with connection pooling in PostgreSQL. Install PgBouncer, set pool_mode = transaction, point your application at port 6432, and your database will handle far more traffic with the same hardware. Leave a comment below if you have any questions or feedback about the content of this article.
Editorial Team