PostgreSQL spawns a separate server process for each connection. It starts with 5-10 MB of RAM before the first query even arrives. Multiply that by 100 clients and you're burning at least half a gigabyte of memory on nothing. At 200+ connections PostgreSQL starts to noticeably slow down: latency climbs, and the scheduler spends more time on context switching between processes than on actual queries. pgBouncer sits as a layer between the application and the database, collecting all incoming connections and serving them through a small pool of real connections to PostgreSQL.
How pgBouncer works
pgBouncer is a lightweight proxy server that sits between the application and PostgreSQL. The application connects to pgBouncer the same way it would to any database - over TCP on the configured port. pgBouncer maintains its own pool of connections to PostgreSQL and hands them out to clients on demand.
The key idea: the number of real connections to PostgreSQL is far smaller than the number of client connections. If 200 Django workers are connected to pgBouncer and the pool to PostgreSQL is configured for 20 connections - exactly 20 processes are open in the database. The other 180 requests wait in a queue and receive a connection as soon as one becomes available.
Unlike connection pooling inside the application itself (e.g., SQLAlchemy pool), pgBouncer operates at the network level and is independent of any language or framework. A single pgBouncer instance serves any number of applications and services.
Three pooling modes
There are three modes, and the choice between them is the key decision when configuring pgBouncer.
Session pooling - a PostgreSQL connection is assigned to the client for the entire session. While the client is connected to pgBouncer, it holds one real database connection. This is safe and supports everything PostgreSQL can do, but if the client sits idle with no active queries, the resource is wasted. The savings are modest.
Transaction pooling - the right choice for most use cases. A database connection is occupied only for the duration of a transaction. After COMMIT or ROLLBACK it goes back to the pool. 200 clients through 20 real connections - and everyone is happy, as long as transactions are short enough.
Statement pooling - the connection is returned after each individual statement. An aggressive mode with a hard limitation: BEGIN/COMMIT blocks do not work. Suitable only for analytical SELECTs without explicit transactions. Rarely seen in production.
Installing pgBouncer on Ubuntu/Debian
pgBouncer is available in the standard repositories. Installation is straightforward:
sudo apt update
sudo apt install pgbouncer -yAfter installation, check the version and service status:
pgbouncer --version
sudo systemctl status pgbouncerConfiguration files:
- /etc/pgbouncer/pgbouncer.ini - the main configuration file
- /etc/pgbouncer/userlist.txt - list of users and passwords for authentication
By default, pgBouncer listens on port 6432. PostgreSQL continues to run on the standard port 5432. Point your application at port 6432 - no other code changes are required.
Basic pgbouncer.ini configuration
Open the main config file:
sudo nano /etc/pgbouncer/pgbouncer.iniMinimal working configuration:
[databases]
myapp = host=127.0.0.1 port=5432 dbname=myapp
[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 500
default_pool_size = 20
log_file = /var/log/postgresql/pgbouncer.log
pid_file = /var/run/postgresql/pgbouncer.pidThe [databases] block maps database names. The client connects to myapp on pgBouncer, and pgBouncer proxies the request to the real myapp database at 127.0.0.1:5432.
The userlist.txt file stores credentials in the following format:
"username" "password"Passwords can be stored in plain text or as a PostgreSQL MD5 hash. To generate an MD5 hash:
echo -n "passwordusername" | md5sumThe result is inserted with the md5 prefix:
"appuser" "md5a1b2c3d4e5f6..."After changing the config, restart the service:
sudo systemctl restart pgbouncer
sudo systemctl enable pgbouncerKey performance parameters
default_pool_size - the number of real PostgreSQL connections opened per database/user pair. This is the primary parameter. For one database and one user, this number is the maximum connections PostgreSQL will receive from pgBouncer. Start with 20-30 for production.
max_client_conn - the limit on client connections to pgBouncer itself. This is not the number of connections to the database, but how many applications can connect to pgBouncer simultaneously. pgBouncer is lightweight - 1000 client connections is not a burden for it, so set this with headroom.
reserve_pool_size - a reserve of connections for peak moments. If all slots in the main pool are occupied and clients have been waiting longer than reserve_pool_timeout seconds, pgBouncer opens additional connections from this reserve. 5-10% of default_pool_size is sufficient.
server_idle_timeout - how many seconds before an idle PostgreSQL connection is closed. Default is 600 seconds. Reducing this to 300 helps free up database resources during low-traffic periods.
client_idle_timeout - the same for client connections. If a client is silent for longer than the specified time, the connection is dropped. This protects against hung connections.
server_connect_timeout - how long to wait when opening a new connection to PostgreSQL. If the database does not respond within this time, pgBouncer considers the attempt failed.
Extended config example with these parameters:
[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25
reserve_pool_size = 5
reserve_pool_timeout = 3
server_idle_timeout = 300
client_idle_timeout = 60
server_connect_timeout = 10
log_file = /var/log/postgresql/pgbouncer.log
pid_file = /var/run/postgresql/pgbouncer.pidPractical example
Consider a real-world scenario: a Django application deployed across 200 Gunicorn workers on a single VPS with 8 GB of RAM.
Before pgBouncer - direct connections to PostgreSQL:
- 200 workers x 1 connection each = 200 connections in PostgreSQL
- Each connection consumes ~8 MB
- Total: 200 x 8 MB = 1.6 GB of RAM just for connections
- Actual peak load on the database - 30-40 concurrent queries
- 170 connections are idle most of the time but still consuming resources
After pgBouncer in transaction pooling mode:
- 200 workers connect to pgBouncer - for pgBouncer these are 200 lightweight client connections
- pgBouncer maintains 20 real connections to PostgreSQL
- 20 x 8 MB = 160 MB of RAM for connections
- Savings: 1.44 GB of RAM
- Throughput under steady load is unchanged - Django transactions are short
Django configuration for use with pgBouncer:
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.postgresql',
'NAME': 'myapp',
'USER': 'appuser',
'PASSWORD': 'yourpassword',
'HOST': '127.0.0.1',
'PORT': '6432', # pgBouncer port, not PostgreSQL
'OPTIONS': {
'connect_timeout': 10,
},
'CONN_MAX_AGE': 0, # important: disable persistent connections in Django
}
}
The CONN_MAX_AGE = 0 parameter is critical. By default, Django keeps connections open between requests. With transaction pooling this breaks the pool logic - the connection is not returned to pgBouncer after the transaction. Always set CONN_MAX_AGE = 0 when using transaction mode.
Common issues and solutions
Prepared statements break in transaction mode. PostgreSQL named prepared statements are bound to a session. In transaction mode, a single PostgreSQL session can serve different clients, and the prepared statements of the previous client are not available to the next one. Solution: disable prepared statements at the ORM level. For SQLAlchemy:
create_engine(url, execution_options={"no_parameters": True})
For Django with psycopg2 - use the prepared_statements = False parameter, or switch to psycopg3, which can work with pgBouncer in transaction mode without disabling prepared statements.
SET commands and temporary tables. In transaction mode, SET commands (SET search_path, SET timezone, etc.) and temporary tables do not survive the end of a transaction - the connection moves on to another client. If your application uses SET to switch schemas, either bake those settings into ALTER ROLE, or switch to session mode.
Authentication error: password authentication failed. pgBouncer validates users against userlist.txt, not directly through PostgreSQL. If a user exists in the database but has not been added to userlist.txt, the connection will be refused. Add the user to both places. After modifying userlist.txt, a full restart is not needed - just run:
psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer -c "RELOAD;"pg_hba.conf is not allowing connections from pgBouncer. If pgBouncer runs on the same server as PostgreSQL, pg_hba.conf must have an entry for 127.0.0.1:
host all all 127.0.0.1/32 md5After modifying pg_hba.conf:
sudo systemctl reload postgresqlMonitoring current state. pgBouncer provides a built-in pgbouncer admin database for monitoring:
psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer -c "SHOW POOLS;"
psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer -c "SHOW STATS;"
psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer -c "SHOW CLIENTS;"The SHOW POOLS command shows how many clients are waiting for a connection (cl_waiting). If this number is consistently above zero, increase default_pool_size.
Frequently asked questions
What is the difference between pgBouncer and PgPool-II?
pgBouncer is a dedicated connection pooler. It does one thing and does it well: minimal latency, low resource consumption. PgPool-II is a heavier solution with additional features: replication, load balancing, query caching. If all you need is connection pooling, pgBouncer is faster and simpler to configure. PgPool-II is justified when you also need load balancing across multiple replicas.
How many connections should max_connections be set to in PostgreSQL?
A commonly used formula: (RAM in GB x 100) / work_mem in MB. With 8 GB of RAM and work_mem = 64MB, that works out to around 200. But with pgBouncer the number of real connections is far smaller, so you can lower max_connections in PostgreSQL to 100-150 and redirect the freed memory to shared_buffers and work_mem.
How do I monitor pgBouncer with Prometheus?
Use pgbouncer_exporter. It connects to the pgBouncer admin database and exports metrics in Prometheus format: client counts, pool sizes, wait times, query statistics. Installation:
docker run -d \
-e DATA_SOURCE_NAME="postgresql://pgbouncer:password@127.0.0.1:6432/pgbouncer?sslmode=disable" \
-p 9127:9127 \
prometheuscommunity/pgbouncer-exporterDoes pgBouncer support SSL?
Yes. pgBouncer supports SSL on both the client side and the PostgreSQL side. To enable SSL in the config:
[pgbouncer]
client_tls_sslmode = require
client_tls_key_file = /etc/pgbouncer/server.key
client_tls_cert_file = /etc/pgbouncer/server.crt
server_tls_sslmode = requirepgBouncer is running in a container - are any changes needed?
In a containerized environment, pgBouncer is typically deployed as a separate sidecar container or as a standalone service in Docker Compose. The configuration is identical; only the PostgreSQL address changes. In Kubernetes, pgBouncer is often run as a DaemonSet or as a separate Deployment with a Service - depending on whether you need a shared pool for the entire cluster or a local one on each node.
