PostgreSQL is memory-hungry per connection: each idle client connection can consume several megabytes of RAM, and on a small VPS with 2–4 GB of RAM, an application that opens hundreds of connections can exhaust memory before the database even does any real work. PgBouncer fixes that by sitting between your application and PostgreSQL and multiplexing many client connections onto a small pool of server connections. This guide covers installation, configuration, pool sizing, and the pitfalls to avoid.
Do you actually need a pooler?
You need PgBouncer if your application opens many short-lived connections — typical for server-side frameworks, web apps with per-request connections, or ORMs that do not reuse connections well. You may not need it if your app uses a single long-lived connection pool that is already sized to your RAM. A quick check: count connections with SELECT count(*) FROM pg_stat_activity; and compare against your VPS memory.
Installing PgBouncer
PgBouncer is in the standard repositories of every major distribution:
- Debian/Ubuntu:
apt install pgbouncer - RHEL/AlmaLinux:
dnf install pgbouncer
Configuration lives in /etc/pgbouncer/pgbouncer.ini, with authentication in /etc/pgbouncer/userlist.txt.
Configuring pgbouncer.ini
A minimal working configuration looks like this:
[databases]
mydb = host=/var/run/postgresql port=5432 dbname=mydb
[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 = 20
listen_addr = 127.0.0.1— bind to localhost so clients reach PgBouncer through the same host; never expose 6432 publiclypool_mode = transaction— the right default for web workloads; connections are recycled after each transactionmax_client_conn— the maximum number of client connections PgBouncer will acceptdefault_pool_size— server connections kept open per database, sized to your CPU and RAM
Setting up the auth file
Create userlist.txt with the database users PgBouncer may authenticate. Passwords must be MD5 hashes in the format PostgreSQL uses:
"myapp" "md5" + md5(password + username)
You can generate the hash with SELECT md5('password' || 'myapp'); in psql, or copy the value of rolpassword from pg_authid if you have access. Then restart PgBouncer: systemctl restart pgbouncer.
Pool modes: transaction vs session
The pool_mode setting changes how aggressively connections are recycled. transaction mode returns a server connection to the pool as soon as the current transaction finishes, which means 100 concurrent clients can share as few as 10–20 server connections. session mode keeps the association for the entire client session — closer to what a single long-lived connection looks like, but it needs more server connections.
Use transaction mode for web applications and APIs: it gives the biggest memory savings. Use session mode only for clients that rely on session-scoped state such as SET statements, temporary tables, or advisory locks held across transactions.
Securing the connection
PgBouncer should never be exposed to the public internet. Bind it to 127.0.0.1 and let your application connect over localhost, or put it behind a firewall if your application runs on another host. For remote clients, add TLS on top:
client_tls_sslmode = require— encrypt client-to-PgBouncer trafficserver_tls_sslmode = require— encrypt PgBouncer-to-PostgreSQL traffic- Point
client_tls_cert_fileandclient_tls_key_fileat a certificate for your hostname
Keep auth_type at md5 or scram-sha-256 rather than trust, even on localhost — a misconfigured application should not get in without credentials.
Sizing the pools for your VPS
The two numbers that matter are default_pool_size and max_client_conn. A good starting point on a 2 vCPU / 4 GB VPS:
| Setting | Value | Rationale |
|---|---|---|
| default_pool_size | 10–20 | Each server connection costs RAM; 20 connections is plenty for most web apps on 4 GB |
| max_client_conn | 500–1000 | Clients are cheap (they only queue); cap them to avoid runaway apps |
| reserve_pool_size | 5 | Kept free for admin queries so the app cannot starve you out |
| server_idle_timeout | 300 | Close idle server connections to free memory |
Monitor with SHOW POOLS; and SHOW STATS; from psql -p 6432 pgbouncer. If maxwait climbs, your pool is too small; if server connections sit idle, shrink it.
Pointing your application at PgBouncer
Change the application’s database host and port to 127.0.0.1:6432 and keep the same database name and credentials. Keep one direct connection to PostgreSQL on port 5432 for administrative work such as migrations, and be careful with features PgBouncer does not handle well:
- Prepared statements in transaction mode can break; enable
server_prepare_modeor use the session pool for those clients - Features like
LISTEN/NOTIFYand advisory locks behave differently across pooled connections - Long-running reporting queries will hold a server connection; route them through a separate pool or database entry
Monitoring and common mistakes
- Watch memory: if PostgreSQL still uses too much RAM, reduce
shared_buffersandwork_membefore growing the pool - Set
statement_timeoutin PostgreSQL so one slow query cannot hog a pooled connection - Do not expose port 6432 publicly; PgBouncer is not a security boundary
- Test failover: if PostgreSQL restarts, PgBouncer should reconnect automatically
PgBouncer removes one of the most common memory ceilings on small database servers. If you are still deciding where to host that database, see the full VPS comparison on our table to compare plans with enough RAM headroom, and browse our provider breakdowns and buying guides on the main site for PostgreSQL tuning guides that complement this setup.
Looking for a place to run these setups? InterServer VPS plans offer straightforward pricing with plenty of headroom, and Cloudways managed cloud hosting is a solid choice if you prefer a managed platform on top of fast infrastructure. (Disclosure: we may earn a commission if you sign up through these links, at no extra cost to you.)
Leave a Reply
You must be logged in to post a comment.