PgBouncer can actually increase the number of open connections your Postgres server has, but in a controlled way that prevents outright exhaustion.
Let’s see PgBouncer in action. Imagine you have a web application that needs to talk to a PostgreSQL database. Without PgBouncer, each time a web server process starts up or handles a request that requires database access, it opens a brand new connection to Postgres. If you have, say, 100 web server processes, and each of them opens a connection, that’s 100 connections to Postgres. If your application is busy and those web servers are constantly spinning up and down or needing to query the database, this can quickly add up. Postgres itself has a max_connections setting, and if you hit that limit, new connection attempts will fail with an error like FATAL: sorry, too many clients already.
# Example of what a busy application might do without pooling
# (This is conceptual, not actual code you'd run)
# Web server process 1 starts
connection_1 = connect_to_postgres(host='db.example.com', port=5432, user='app_user', dbname='app_db')
# Web server process 2 starts
connection_2 = connect_to_postgres(host='db.example.com', port=5432, user='app_user', dbname='app_db')
# ... many more processes ...
# Web server process 100 starts
connection_100 = connect_to_postgres(host='db.example.com', port=5432, user='app_user', dbname='app_db')
# If Postgres max_connections is 100, process 101 will fail to connect.
PgBouncer acts as a middleman. Instead of your application connecting directly to Postgres, it connects to PgBouncer. PgBouncer then maintains a pool of actual connections to Postgres. When your application asks for a connection, PgBouncer hands one off from its pool. When the application is done with it, it returns it to PgBouncer, rather than closing it.
Here’s a simplified view of that interaction:
# Application connects to PgBouncer (e.g., on dbpool.example.com:6432)
app_conn_1 = connect_to_pgbouncer(host='dbpool.example.com', port=6432, user='app_user', dbname='app_db')
# PgBouncer, with its own pool of connections to Postgres, hands over a connection
# from its pool (e.g., one of its 20 connections to db.example.com:5432)
# Application finishes, returns the connection to PgBouncer
app_conn_1.close() # This doesn't actually close the Postgres connection, just returns it to PgBouncer's pool.
# Another application process requests a connection
app_conn_2 = connect_to_pgbouncer(host='dbpool.example.com', port=6432, user='app_user', dbname='app_db')
# PgBouncer reuses the connection returned by app_conn_1
This way, if you have 100 web server processes, they might each connect to PgBouncer, but PgBouncer might only need 20 actual connections to Postgres to serve them all efficiently. This dramatically reduces the load on your database server.
To set this up, you’d install PgBouncer and configure its pgbouncer.ini file. The key section is [databases], where you define the connection pools.
[databases]
mydb = host=db.example.com port=5432 dbname=app_db user=pool_user password=pool_password
[pgbouncer]
listen_port = 6432
listen_addr = 0.0.0.0
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = session ; Or transaction or statement
default_pool_size = 20
max_client_conn = 1000
In this example, mydb is the database name your applications will connect to through PgBouncer. PgBouncer will manage a pool of up to 20 connections (default_pool_size) to the actual PostgreSQL server (db.example.com:5432). max_client_conn limits how many clients can connect to PgBouncer itself, preventing it from becoming a bottleneck. pool_mode is crucial:
session: A client connection is assigned a server connection for the entire duration of the client’s connection to PgBouncer. This is the most compatible mode but can lead to connection leaks if not managed carefully.transaction: A server connection is assigned to a client for the duration of a single transaction. Once the transaction commits or rolls back, the server connection is returned to the pool. This is more efficient for many workloads but can break applications that expect to hold a connection across multiple transactions (e.g., usingLISTEN/NOTIFYor holding advisory locks).statement: A server connection is assigned for the duration of a single statement. This is the most aggressive pooling and can break applications that rely on session state or temporary tables.
The userlist.txt file would contain the credentials PgBouncer uses to connect to your PostgreSQL server:
"pool_user" "md5" "hashed_password_for_pool_user"
Your application’s connection string would then point to PgBouncer, not directly to Postgres:
postgresql://app_user@dbpool.example.com:6432/mydb
The most surprising true thing about PgBouncer is that its pool_mode=session is often the default and the most compatible, but it’s also the mode that can most easily lead to a PostgreSQL connection exhaustion problem if clients don’t properly CLOSE their connections. The connections aren’t actually closed by the client; they’re returned to PgBouncer’s pool. If an application crashes or has a bug where it never returns the connection (e.g., by not calling connection.close()), that connection from PgBouncer’s pool is tied up, and PgBouncer can’t reuse it. PgBouncer itself will still have a connection to Postgres, but it’s effectively "stolen" by a defunct client. Over time, this can deplete PgBouncer’s pool and, if the pool size is large enough, still hit the PostgreSQL max_connections limit.
The next problem you’ll run into is understanding how to monitor PgBouncer’s pool usage and identify "stolen" connections.