PgBouncer on a VPS: Step-by-Step PostgreSQL Connection Pooling for 2–4 GB Servers

PostgreSQL creates one OS process per connection, and each idle connection can consume 5–10 MB of RAM. On a small VPS with 2–4 GB of memory, an application that opens 200+ database connections will exhaust available RAM long before PostgreSQL does any real work. PgBouncer sits between your application and PostgreSQL, multiplexing hundreds of client connections through a small pool of server-side connections. This tutorial walks through installing, configuring, sizing, and securing PgBouncer on a typical Linux VPS.

Prerequisites

Before you start, you need:

• A Linux VPS (Ubuntu 22.04/24.04 or Debian 12 recommended) with PostgreSQL already installed and running
• Root or sudo access
• PostgreSQL listening on localhost (default port 5432)
• At least one database user and database to pool

If you haven’t yet chosen a VPS provider, compare VPS plans on our comparison table to find a server with enough RAM headroom for PostgreSQL plus PgBouncer.

Step 1: Install PgBouncer

PgBouncer is available in the default repositories of all major distributions. Install it with:

# Debian / Ubuntu
sudo apt update
sudo apt install pgbouncer -y

# Verify installation
pgbouncer --version
# Expected output: pgbouncer 1.xx.x

On RHEL/AlmaLinux/Rocky Linux, use dnf install pgbouncer instead. The EPEL repository may be required.

Step 2: Configure pgbouncer.ini

The main configuration file lives at /etc/pgbouncer/pgbouncer.ini. Open it and replace the contents with a minimal working setup:

[databases]
myapp = host=/var/run/postgresql 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
default_pool_size = 20
max_client_conn = 500
reserve_pool_size = 5
reserve_pool_timeout = 5
server_idle_timeout = 300

Key settings explained:

• listen_addr = 127.0.0.1 — Bind to localhost only. Never expose PgBouncer to the public internet.
• pool_mode = transaction — The best mode for web applications. Connections are returned to the pool after each transaction completes.
• default_pool_size = 20 — Only 20 server connections are kept open per database, regardless of how many clients connect.
• max_client_conn = 500 — The maximum number of client connections PgBouncer will accept. Excess connections queue.

Step 3: Set Up Authentication

Create the userlist.txt file at /etc/pgbouncer/userlist.txt with the database users PgBouncer will authenticate. The password must be stored as an MD5 hash in PostgreSQL format:

# Generate the MD5 hash in psql
psql -c "SELECT concat('md5', md5('yourpassword' || 'yourusername'));"

# Example output: md5a105d2e4e8c07c6c5e5b5b5b5b5b5b5b

# Add to /etc/pgbouncer/userlist.txt
"myappuser" "md5a105d2e4e8c07c6c5e5b5b5b5b5b5b5b"

Alternatively, copy the rolpassword value directly from PostgreSQL’s pg_authid table if you have superuser access.

Step 4: Start and Enable PgBouncer

sudo systemctl restart pgbouncer
sudo systemctl enable pgbouncer
sudo systemctl status pgbouncer

Check the logs for any errors:

sudo journalctl -u pgbouncer --no-pager -n 20

Step 5: Test the Connection

Connect to PgBouncer using psql on port 6432:

psql -h 127.0.0.1 -p 6432 -U myappuser -d myapp

If authentication works, you are connected through PgBouncer. To verify, run the PgBouncer admin commands:

psql -h 127.0.0.1 -p 6432 -U myappuser -d pgbouncer -c "SHOW POOLS;"
psql -h 127.0.0.1 -p 6432 -U myappuser -d pgbouncer -c "SHOW STATS;"

The SHOW POOLS output shows the number of active server connections, clients waiting, and the current pool size. If maxwait is consistently above 0, your pool is too small for the workload.

Step 6: Point Your Application at PgBouncer

Update your application’s database connection string to use 127.0.0.1:6432 instead of the default PostgreSQL port 5432. Keep the same database name and credentials.

Important: Keep one direct connection to PostgreSQL on port 5432 for administrative tasks like schema migrations. PgBouncer in transaction mode does not handle prepared statements or session-scoped features like LISTEN/NOTIFY well. For those use cases, either:

• Use a separate database entry in pgbouncer.ini with pool_mode=session
• Or connect directly to PostgreSQL on port 5432

Step 7: Size Your Pool Correctly

A good starting point for a 2 vCPU / 4 GB VPS:

Setting	Value	Why
default_pool_size	10–20	Each server connection uses ~5–10 MB; 20 is safe for 4 GB
max_client_conn	500–1000	Clients only queue; cap them to prevent runaway apps
reserve_pool_size	5	Reserved for admin queries so the app can’t starve you out
server_idle_timeout	300	Close idle server connections after 5 minutes to free RAM

Monitor pool usage with SHOW POOLS. If sv_active regularly equals default_pool_size and clients are queueing, increase the pool size. If server connections sit idle, reduce it.

Step 8: Secure the Setup

• Bind PgBouncer to 127.0.0.1 only — never expose port 6432 publicly
• Use auth_type = md5 or scram-sha-256, never trust
• Set statement_timeout in PostgreSQL so one slow query cannot hog a pooled connection
• For remote clients, add TLS with client_tls_sslmode = require

Troubleshooting Common Issues

• Authentication failures: Double-check the MD5 hash in userlist.txt. The format is md5 + md5(password + username).
• Prepared statement errors: Set server_prepare_mode = statement in the database section of pgbouncer.ini for clients that use prepared statements.
• High maxwait: Your default_pool_size is too small. Increase it gradually and re-check.
• PostgreSQL still uses too much RAM: Reduce shared_buffers and work_mem before growing the pool.

PgBouncer is one of the highest-value optimizations for PostgreSQL on a small VPS. It removes the most common memory ceiling — the per-connection overhead — and lets a 2–4 GB server handle workloads that would otherwise require a much larger instance. For more PostgreSQL and VPS performance guides, visit the main site.