Postgres + PgBouncer on a VPS for production workloads

Postgres handles a lot of load before it needs sharding, but most apps run out of connection slots long before they run out of CPU. PgBouncer is the answer. Set it up once, leave the application code alone, and your "10 to 10,000 connections" problem disappears.

Sizing

• Toy / staging: 2 vCPU, 4 GB RAM, 40 GB SSD
• Production OLTP, single-app: 4 vCPU, 16 GB RAM, 100+ GB SSD
• Multi-tenant SaaS, many small DBs: 8 vCPU, 32 GB RAM, 200+ GB SSD

Disk speed matters more than CPU for database workloads. Don't cheap out on the storage tier.

Install

This guide assumes Ubuntu 24.04, where both packages come straight from the stock repositories. On Ubuntu 22.04 or Debian 12 the archive ships an older Postgres, so configure the PGDG apt repository first (the postgresql.org download page has the exact commands for your release); everything else in this guide then applies unchanged.

sudo apt update
sudo apt install -y postgresql-16 pgbouncer

Postgres tuning

Edit /etc/postgresql/16/main/postgresql.conf. The defaults are conservative; bump for a 16 GB box:

max_connections = 100
shared_buffers = 4GB
effective_cache_size = 12GB
maintenance_work_mem = 512MB
work_mem = 16MB
wal_buffers = 16MB
checkpoint_completion_target = 0.9
random_page_cost = 1.1
effective_io_concurrency = 200
default_statistics_target = 100
huge_pages = try

max_connections = 100 looks tiny but with PgBouncer in front you don't need more. PgBouncer multiplexes thousands of client connections onto Postgres's hundred.

For 32 GB:

• shared_buffers = 8GB
• effective_cache_size = 24GB
• maintenance_work_mem = 2GB
• work_mem = 32MB

Most of these settings apply with a plain reload, but max_connections, shared_buffers, wal_buffers, and huge_pages only take effect on a full restart. A restart drops every open connection and takes the database offline for a few seconds, so if this box is already serving traffic, schedule it for a quiet window instead of running it mid-day. On a fresh install nothing is connected yet and you can restart freely:

sudo systemctl restart postgresql

Authentication

Edit /etc/postgresql/16/main/pg_hba.conf. Postgres reads this file top to bottom and the first matching line wins, so replace the distro's default access rules with the lines below rather than appending them underneath, where a stock rule higher up would match first and quietly win. PgBouncer connects to Postgres over TCP on 127.0.0.1, so it is covered by the host rules with scram-sha-256; the local line keeps sudo -u postgres psql working through peer auth:

local   all             postgres                                peer
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             ::1/128                 scram-sha-256

For remote connections (only if you must, and only over a private network):

hostssl all             all             10.0.0.0/8              scram-sha-256

Use scram-sha-256, not md5. It's the default in Postgres 16 and worth the migration if you're upgrading from an older version.

Postgres only rereads pg_hba.conf on a reload, so apply the new rules now. Unlike a restart, a reload doesn't drop any connections:

sudo systemctl reload postgresql

Create the application user

Don't put the password inline in the SQL. Anything typed on the command line lands in your shell history, and if statement logging is enabled it also lands in plaintext in the Postgres server log. Create the role first, then set the password interactively with \password, which prompts without echoing and sends only the hashed verifier to the server:

sudo -u postgres psql <<'SQL'
CREATE USER appuser;
CREATE DATABASE app_production OWNER appuser;
SQL
sudo -u postgres psql -c '\password appuser'

PgBouncer config

Edit /etc/pgbouncer/pgbouncer.ini:

[databases]
app_production = host=127.0.0.1 port=5432 dbname=app_production

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
admin_users = postgres
pool_mode = transaction
max_client_conn = 10000
default_pool_size = 25
reserve_pool_size = 5
reserve_pool_timeout = 3
server_idle_timeout = 600
server_lifetime = 3600

pool_mode = transaction is the magic. Each client claims a server connection only for the duration of a transaction, then returns it. Throughput goes up dramatically.

max_client_conn = 10000 is what backs the ten-thousand-connections claim, and every client connection costs a file descriptor. The default soft limit systemd gives the service is 1024, far below that, so raise it with a drop-in now; the restart below picks it up:

sudo mkdir -p /etc/systemd/system/pgbouncer.service.d
printf '[Service]\nLimitNOFILE=16384\n' | sudo tee /etc/systemd/system/pgbouncer.service.d/limits.conf
sudo systemctl daemon-reload

Userlist

PgBouncer checks every login against this file, including the postgres admin console logins you'll use in the Monitoring section below. On Ubuntu the postgres role has no password by default, so give it one first. Local sudo -u postgres psql access keeps using peer auth; this password only matters for TCP logins. Be aware that it unlocks superuser access from anywhere your pg_hba.conf allows password auth, including the private-network rule if you added it, so make it a strong one. Set it with the same interactive \password prompt as before, for the same reason: nothing in shell history, nothing in the server log:

sudo -u postgres psql -c '\password postgres'

Then export the SCRAM verifiers for both roles:

sudo -u postgres psql -t -c \
  "SELECT '\"' || rolname || '\" \"' || rolpassword || '\"' FROM pg_authid WHERE rolname IN ('appuser', 'postgres');" \
  | sudo tee /etc/pgbouncer/userlist.txt
sudo chown postgres:postgres /etc/pgbouncer/userlist.txt
sudo chmod 600 /etc/pgbouncer/userlist.txt

The file is owned by postgres because Ubuntu's pgbouncer package runs the service as the postgres system user; there is no dedicated pgbouncer account.

Restart PgBouncer

sudo systemctl restart pgbouncer

Connect via PgBouncer instead of Postgres directly:

psql -h 127.0.0.1 -p 6432 -U appuser app_production

Your application connects to port 6432; nothing else changes.

Transaction-mode caveats

PgBouncer in transaction mode breaks a few features:

• Prepared statements: disabled by default in some drivers, or use prepare_threshold=0.
• SET outside a transaction: doesn't persist across statements.
• LISTEN/NOTIFY: doesn't work in transaction mode (use session mode for those connections, or a separate pool).

Most modern application code is fine. Rails and Django defaults work. If you hit trouble, the PgBouncer FAQ enumerates every edge case.

Backups

Nothing so far has created a place for dumps to land, so make one first. Own it as postgres with mode 700: a dump contains every row in the database, and nobody else on the box needs to read it:

sudo mkdir -p /backups
sudo chown postgres:postgres /backups
sudo chmod 700 /backups

Run the whole pipeline as postgres, not just pg_dump, so the shell redirect can actually write into that directory:

sudo -u postgres bash -c 'pg_dump app_production | gzip > /backups/app-$(date +%F).sql.gz'

For point-in-time recovery, configure wal_level = replica, archive WAL files to off-VPS storage, and use pg_basebackup. That's a separate post.

Monitoring

-- via pgbouncer admin: psql -h 127.0.0.1 -p 6432 -U postgres pgbouncer
SHOW POOLS;
SHOW STATS;
SHOW CLIENTS;

Watch cl_waiting in SHOW POOLS. If it's frequently above zero, raise default_pool_size.

That's a Postgres install ready for real traffic. PgBouncer turns the connection-count question from "do we have enough" into "we have plenty" without you touching application code.