Chalom Ellezam

Posted on Jun 15

Your Postgres will die at 50 concurrent users, not 50,000. Here is the connection pooling guide nobody handed you.

#postgres #webdev #devops #beginners

Disclosure: I am a senior backend tech lead in Paris and I run HostingGuru, a small European PaaS for solo founders and small teams. This article mentions HostingGuru once near the end. The fix works on any platform: Render, Fly, Railway, Supabase, your own VPS, whatever you ship on.

You launch on Product Hunt. By 11h Pacific you have 87 people poking around your dashboard. Your error rate starts climbing. Sentry begins shouting PrismaClientInitializationError: Can't reach database server. Your homepage returns blank. You open your Postgres metrics expecting to see CPU pinned at 100%. CPU is at 3%. Memory is barely touched. But pg_stat_activity shows 100 active connections, and you cannot make a new one.

Your database did not run out of compute. It ran out of seats.

This is the single most common production outage I see in indie SaaS, and it almost never gets blamed correctly. Founders blame the framework, the host, "scaling", or the AI model that wrote the code. The real cause is two paragraphs deep in the Postgres docs and nobody reads them until 11h on Product Hunt day.

Why Postgres has a seat limit at all

Every Postgres connection is a real OS process. Not a thread. A process, with its own memory footprint, typically 5 to 15 MB on a default config. The reason for this design is historical (Postgres predates threads being good at anything in 1996) and operational (process isolation makes one crashed query much less likely to take down the whole server). The cost is that connections are expensive.

So Postgres caps them with max_connections. The defaults you will see in the wild:

Supabase free tier: 60 (direct), but with their Supavisor pooler in front
Neon free tier: 100 via their built-in pooler, ~20 direct
Heroku Postgres Mini: 20
Heroku Postgres Basic: 20
RDS db.t4g.micro: 81
Self-hosted Postgres on a 2 GB VPS, default config: 100

Hit the cap and Postgres does not slow down. It refuses. FATAL: too_many_connections for role "app". Your application's retry logic kicks in. Cold-start serverless functions fan out, each opening its own connection, each getting rejected, each retrying. Your queue backs up. Within 90 seconds the system is fully wedged and the only fix is to restart enough of your app servers to drop the held connections.

I have watched this happen to three clients in the last six months. One of them was a Bolt-built B2B tool that survived its private beta and died on its public launch with 41 concurrent users.

Why this bites indie SaaS specifically

Big companies do not hit this because they paid a platform team to set up connection pooling five years ago. Indie founders hit it because three things stack:

The first is serverless and edge runtimes. Every Vercel function, every Lambda, every Netlify function that touches your database wants its own connection. A single page load on a modern Next.js app can spin up 4 to 8 functions in parallel (your loader, your action, your middleware, your API route, your auth check). Multiply by 50 concurrent users and you are at 400 simultaneous connection attempts to a database that allows 100.

The second is ORM defaults. Prisma, by default, opens a connection pool sized to your CPU count, per process. Run that on 10 Vercel functions and you have 10 separate pools, each sized to whatever Vercel reports as CPU count, often 4 or 8. Drizzle's node-postgres driver defaults to a pool of 10. SQLAlchemy defaults to pool_size=5, max_overflow=10. Stack a few worker dynos and a web server and you can blow past your max_connections limit without doing anything wrong.

The third is hot reload during development. Run npm run dev with Prisma and every save spawns a new client without closing the old one (this got better in recent Prisma versions but is still common). On a small free-tier database, three hours of local dev can hold 30 connections that the database never sees you releasing.

If any of those three apply to you (and at least one does), you are one Product Hunt feature away from the outage I just described.

The math you should actually do tonight

Before reaching for PgBouncer or Supavisor or any pooling layer, do the arithmetic. The formula is brutal but honest.

Take your real concurrency target. For most indie SaaS that is 50 simultaneously active users, not 50,000. Multiply by 2 to account for the fact that a single user action often hits 2 to 4 endpoints (page load, auth check, data fetch, mutation). Add a buffer of 20% for background workers, cron jobs, the Postgres backup process, and your own psql sessions. That is your connection demand.

Now look at your max_connections. Subtract 5 for Postgres's own internal processes (autovacuum, wal writer, etc). What is left is your real budget.

For a Heroku Postgres Basic at 20 max_connections, your real budget is 15. Divide by your per-process pool size, and you get the number of app processes you can safely run. If Prisma is opening 10 connections per process, you can run 1.5 processes. That is your headroom. One Heroku web dyno plus one worker dyno will already be over the limit, before any user shows up.

This is the math nobody runs.

When you need PgBouncer, and when you just need to fix your app

PgBouncer (or Supavisor, or PgCat, or your provider's built-in pooler) lets a small number of real Postgres connections back a much larger number of fake-looking client connections. Your 500 Next.js functions all open a connection to PgBouncer on port 6432. PgBouncer holds 15 real connections to Postgres on port 5432 and multiplexes them. As long as no single query holds a connection for too long, your fan-out problem disappears.

But pooling is not the first fix. It is the third.

Before adding a pooler, do these in order:

First, lower your ORM's per-process pool size. Most Prisma deployments do not need 10 connections per process. Set connection_limit=2 in your DATABASE_URL query string. For Drizzle/node-postgres, pass { max: 3 } to the pool constructor. For SQLAlchemy, set pool_size=2, max_overflow=0. This alone solves the issue for most indie SaaS.

Second, stop opening new clients on hot reload. The Prisma docs have a singleton pattern for Next.js dev mode that uses globalThis to reuse the client across reloads. Use it. SQLAlchemy users should make sure they are not calling create_engine() inside a request handler.

Third, find your connection leaks. Run SELECT pid, query, state, backend_start, state_change FROM pg_stat_activity WHERE state = 'idle in transaction' ORDER BY state_change ASC; against your production database. Any row older than 5 minutes is a leak. It is almost always a missing await on a transaction, or an error path that does not call client.release(). Fix those before adding any infrastructure.

If after all three you still need more headroom, then add a pooler.

The PgBouncer config that actually works

If you self-host PgBouncer, the minimum useful config is shorter than people think:

[databases]
appdb = host=10.0.0.5 port=5432 dbname=appdb

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 500
default_pool_size = 15
reserve_pool_size = 5
reserve_pool_timeout = 3
server_idle_timeout = 60

The two settings that matter are pool_mode and default_pool_size. pool_mode = transaction is what makes the multiplexing actually work: PgBouncer hands a real Postgres connection to a client only for the duration of a single transaction, then reclaims it. default_pool_size = 15 means PgBouncer will open up to 15 real connections to Postgres per (user, database) pair. That number, plus a buffer, must stay under your max_connections.

max_client_conn = 500 is the lie you tell your application. From the app's perspective there are 500 connections available. From Postgres's perspective there are 15. PgBouncer mediates.

The prepared statement footgun

Here is the part that bites everyone, including me, the first time.

In pool_mode = transaction, a single client connection can land on different real Postgres connections from one transaction to the next. Prepared statements (PREPARE name AS SELECT...) live on a specific Postgres connection. So if your ORM prepares a statement on connection A and then tries to execute it on connection B because PgBouncer rotated, you get prepared statement "p1" does not exist. Your app starts throwing 500s on perfectly correct queries.

This hits Prisma users hard because Prisma uses prepared statements by default. The fix is either:

Use Prisma's ?pgbouncer=true flag in the connection string, which disables prepared statements
Or upgrade to Prisma 5.10+ and use ?statement_cache_size=0
Or run Prisma against Postgres directly and use PgBouncer only for non-Prisma workloads
Or use Supabase's Supavisor in "session mode" instead of transaction mode (less efficient, but compatible)

Django users with psycopg2 need DISABLE_SERVER_SIDE_CURSORS = True in their database config. Rails with the pg gem is mostly fine because Rails does not use prepared statements by default in production unless you opted in via prepared_statements: true.

SQLAlchemy users need pool_pre_ping=True and to make sure they are not using server-side cursors across transactions. The default is fine.

This is the one chunk of this article I would tape to your monitor. The error message prepared statement does not exist after introducing PgBouncer is not a bug in PgBouncer. It is your ORM assuming pool_mode = session when you gave it pool_mode = transaction.

What about Supabase, Neon, and the managed providers

Most of them already do this for you, kind of.

Supabase ships a pooler called Supavisor in front of every project. Your DATABASE_URL for "direct" connections hits Postgres on port 5432. Your "transaction" pooler URL hits Supavisor on port 6543. Use the latter from any serverless context. The former is fine for migrations, long-running scripts, and Studio.

Neon does similar with a pgbouncer-derived layer. Append ?pgbouncer=true to your connection string and you are using their pooled endpoint.

Render's managed Postgres has a built-in pooler since 2024, configured via the dashboard.

Heroku Postgres has no built-in pooler. You either run PgBouncer yourself on a separate dyno, or you switch providers.

The trap: founders use the pooled URL in their .env for everything, then run Prisma migrations through it, and the migration fails because migrations need session-mode features like advisory locks and SET search_path. So you keep two DATABASE_URL values: one for the app (pooled, transaction mode), one for migrations (direct, session mode). Most ORMs let you point migrations at a different URL via env var. Do that.

What I built

I kept solving this manually for clients, then I packaged it. I run HostingGuru, a small European PaaS where the managed Postgres ships with a pooler in front by default on every plan, and the dashboard surfaces pg_stat_activity so you can see your idle-in-transaction count before it kills you. There is also AI-driven log monitoring that pattern-matches too_many_connections and prepared statement does not exist and pings you on Telegram with the likely root cause. EU and US data centers, GDPR baked in. The free Starter tier never sleeps, which matters when your Product Hunt launch happens at 02h Paris time.

That is the one mention. The actual fix in this article works the same on Render, Fly, Supabase, or your own VPS. Pick the provider that fits.

What to do tonight regardless of which platform you use

Run SELECT count(*), state FROM pg_stat_activity GROUP BY state; against your production database right now. If idle in transaction is non-zero, you have a leak.

Open your .env or your platform's env vars page. If your DATABASE_URL has no connection_limit parameter (Prisma) or no pool size in the connection options (other ORMs), add one. Set it to 2 or 3.

Check whether your managed Postgres provider offers a pooled connection URL. Switch your serverless functions to use it. Keep the direct URL for migrations only.

Add an alert (Sentry, your log aggregator, Telegram, whatever you use) for the exact strings too_many_connections and prepared statement does not exist. Both indicate you are about to have a bad day.

If you self-host, install PgBouncer with pool_mode = transaction and the config above, and set default_pool_size to no more than max_connections - 5 divided by however many user-database pairs you have.

Document somewhere (a README, a Notion page, your project's ARCHITECTURE.md) what your real max_connections is and how many connections your app is allowed to take. Future-you on Product Hunt day will thank present-you.

What I do not know

The interesting open question for me is whether the next generation of "thin client" Postgres protocols (the HTTP-based ones like Neon's Serverless Driver and Supabase's PostgREST) make all of this obsolete. They sidestep the connection-per-process model entirely by going stateless. They also break every ORM that assumes a persistent connection.

My current bet is that the connection pool will remain the right abstraction for the next 3 to 5 years, because the alternative is rewriting your data layer for a protocol that has no transactions in the traditional sense. But I would love to hear from anyone running an indie SaaS on the new thin-client protocols. What broke? What got easier? Drop a comment.

DEV Community