Why your AI-generated app needs PgBouncer, not just more database connections

Your app runs fine in testing, then you post it somewhere, a few dozen people show up at once, and everything grinds to a halt with errors about “too many connections” or “remaining connection slots reserved.” You bump the connection limit, it holds for a bit, then the whole server runs out of memory. You’re fighting the wrong problem.

Why this happens

AI coding tools almost always generate database code that opens a fresh connection for each request and, too often, forgets to close it. That’s fine when one person is clicking around. Under real load it’s a leak.

Here’s the mechanics. Every Postgres connection is a separate operating-system process that reserves memory, often 5 to 10 MB each. Postgres ships with a default max_connections of 100. So the naive fix, raising the limit, means 500 connections chewing through 2 to 5 GB of RAM before your app does any actual work. You don’t hit a connection ceiling anymore; you hit an out-of-memory crash, which is worse because it takes the whole database down.

The real issue is that connections are expensive and your app is treating them as free. A hundred simultaneous users don’t each need their own dedicated database connection. They need to take turns using a small shared pool, because any single query finishes in milliseconds. That’s exactly what PgBouncer does.

How to fix it

PgBouncer is a lightweight pooler that sits between your app and Postgres. Your app opens hundreds of cheap connections to PgBouncer; PgBouncer maintains a handful of real connections to Postgres and hands them out as queries come and go. Twenty real connections can serve hundreds of clients.

Step 1: install PgBouncer

On the database server:

sudo apt install pgbouncer

Or, if you’re on Docker, add it as a service (see our Docker Compose setup for a full example).

Step 2: configure the pool

The important file is pgbouncer.ini. This is a sane starting config for a small app:

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

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

# transaction mode: a connection is returned to the pool
# after each transaction, not held for the whole session.
# this is what lets few connections serve many clients.
pool_mode = transaction

max_client_conn = 200      # how many clients can connect to PgBouncer
default_pool_size = 20     # how many real Postgres connections to keep

The two numbers that matter: max_client_conn is how many app connections PgBouncer accepts (set it generous), and default_pool_size is how many actual Postgres connections it keeps open (keep it small, 20 is plenty for a small app). With these, 200 clients share 20 real connections.

Step 3: add the user auth

PgBouncer needs to know your database user. Put the username and a SCRAM hash in userlist.txt:

# get the hash Postgres already stores for your user
psql -U postgres -Atc "SELECT rolname, rolpassword FROM pg_authid WHERE rolname = 'myapp_user';"

Paste it into /etc/pgbouncer/userlist.txt as "myapp_user" "SCRAM-SHA-256$...", then start PgBouncer:

sudo systemctl enable --now pgbouncer

Step 4: point your app at PgBouncer

This is the one change in your app. Update DATABASE_URL to hit PgBouncer’s port instead of Postgres directly:

# before
DATABASE_URL=postgres://myapp_user:[email protected]:5432/myapp

# after
DATABASE_URL=postgres://myapp_user:[email protected]:6432/myapp

Step 5: turn off prepared statements if needed

Transaction pooling doesn’t support server-side prepared statements, which some ORMs use by default. If you see errors like prepared statement "s1" already exists, disable them. In a Postgres connection string with many drivers:

DATABASE_URL=postgres://myapp_user:[email protected]:6432/myapp?prepareThreshold=0

For Prisma, add ?pgbouncer=true to the URL instead. Check your ORM’s docs for the exact flag.

Restart your app. The “too many connections” errors should be gone, and your database memory use should drop hard, because it’s now holding 20 connections instead of 200.

Or, we do it for you

We put PgBouncer in front of every customer database as a default, tuned to their traffic, with the connection count on a dashboard so a spike shows up before it becomes an outage. If your app is throwing connection errors under load, book a free audit and we’ll find the leak.

FAQ

How do I know I have a connection problem and not something else?

Watch for errors containing “too many connections” or “remaining connection slots” under load, and a database process count that keeps climbing. Run SELECT count(*) FROM pg_stat_activity; while the app is busy. If that number tracks your user count instead of staying small, your app isn’t pooling.

Should I use transaction mode or session mode?

Transaction mode for almost everything, because it’s what lets few connections serve many clients. Use session mode only if you rely on session-level features like LISTEN/NOTIFY or advisory locks held across queries, and if you do, run a second PgBouncer in session mode just for those.

Does a managed database like Supabase already do this?

Supabase provides a pooler you can connect through (its connection-pooler port), so use that URL rather than the direct one for a serverless or high-concurrency app. The principle is identical: connect through the pooler, not straight to Postgres. If you self-host, PgBouncer is how you get the same thing.

What pool size should I actually set?

Start at default_pool_size = 20 and only raise it if you see clients waiting for a connection while Postgres has memory and CPU to spare. More pool isn’t more throughput past a point, because your queries are competing for the same CPU and disk. Measure before you tune.