When Does PostgreSQL Need Connection Pooling?

The first time I saw FATAL: sorry, too many clients already in production, the database was barely working. CPU was at 15 percent. Queries that did get through came back in a couple of milliseconds. The app was throwing 500s anyway, because every new request that needed a connection was being turned away at the door.

It happened during a deploy. We ran four app containers, each with a connection pool of 20. That’s 80 connections against a max_connections of 100, which looked comfortable. But our rolling deploy started the new containers before stopping the old ones, so for about 90 seconds we had eight containers, each trying to hold 20 connections. Add a background worker and a cron job that opened its own connections, and we were over the limit.

The fix that day was a smaller pool per container. The fix a month later was PgBouncer. The gap between those two fixes is what this article is about, because “does Postgres need connection pooling” has two different answers depending on which pooling you mean.

Two kinds of pooling

People say “connection pooling” and mean one of two things.

An application-side pool lives inside your app process. node-postgres has Pool, SQLAlchemy has its engine pool, Java apps use HikariCP, Rails has ActiveRecord’s pool. The app opens some connections at startup and reuses them across requests. You almost certainly already have one of these, often with defaults you’ve never looked at.

A server-side pooler is a separate process that sits between your apps and Postgres. PgBouncer is the classic one; managed platforms ship their own (Supavisor, RDS Proxy, and others). Your apps connect to the pooler, possibly with hundreds of client connections, and the pooler funnels them onto a much smaller number of real Postgres connections.

Almost every app needs the first kind. The second kind is the one people ask about, and the answer is: only when the number of processes that want connections gets ahead of what Postgres can hold.

Why Postgres cares about connection count at all

Postgres uses a process per connection. Each client connection gets its own backend process on the server, with its own memory. An idle connection is relatively cheap, a few megabytes, but it’s not free, and a connection running a query with sorts or hashes can use a lot more depending on work_mem.

More importantly, active connections compete for the same CPU cores and the same locks. Throwing 400 concurrent queries at an 8-core machine doesn’t make it do 400 things at once. It makes it context-switch between 400 things, and throughput often goes down past a certain point. The common rule of thumb for how many connections should actually be running queries at the same time is on the order of a couple per CPU core. The exact number depends on your workload and storage, but it’s much closer to 20 than to 500.

Opening a connection also has a cost: a process fork, authentication, and some setup. It’s a few milliseconds. That’s irrelevant if you open a connection once and keep it, and very relevant if you open one per HTTP request.

When an application pool is enough

If your setup looks like this, you probably don’t need PgBouncer:

• A handful of long-running app processes. One to maybe a dozen.
• Each with a sensibly sized pool.
• A total that stays well under max_connections, including during deploys.

The work here is arithmetic, not infrastructure. Write down every process that connects to the database:

• Web app containers × pool size per container
• Background workers × their pool size
• Cron jobs, migrations, admin scripts
• Your own psql session, a BI tool, a monitoring agent
• The extra copies that exist during a rolling deploy

Then compare it to max_connections minus the few reserved for superusers. When I did this after that outage, the number during a deploy was 187. Our limit was 100. We’d been lucky for months, because deploys usually happened when traffic was low and the pools hadn’t filled.

The first fix was to shrink the pools. Each web container went from 20 connections to 8. Response times didn’t change at all, because the database was never the bottleneck at 8 per container; requests spent most of their time in application code and waiting on other APIs. That’s the most common finding when people actually measure: pool sizes are set high “to be safe,” and they aren’t doing anything except making the peak count dangerous.

A few other checks that belong here before reaching for a new component:

• Look for idle-in-transaction connections. SELECT state, count(*) FROM pg_stat_activity GROUP BY state; If you see a lot of idle in transaction, some code is opening a transaction and then waiting on something slow (an HTTP call, a file upload) before committing. Those connections are held but doing nothing. Fix the code, and set idle_in_transaction_session_timeout as a backstop.
• Check that connections are actually returned. A leak in the app pool (a code path that checks out a connection and never releases it on error) looks exactly like “we need more connections.”
• Set a pool checkout timeout. When the pool is exhausted, a request should fail quickly with a clear error, not hang for 30 seconds.

When you actually need a pooler like PgBouncer

The app pool stops being enough when the number of processes grows, not when traffic grows. Traffic mostly makes each connection busier. Process count multiplies connections directly.

You scale horizontally

Four containers with 10 connections each is 40. Autoscale to 30 containers on a busy day and it’s 300. Every container needs at least a small pool, and the total quickly exceeds what Postgres should be running. A pooler lets each container keep its own pool while the database only sees a fixed number of real connections.

You run serverless functions or per-request processes

This is the classic case. Each function instance is its own process with its own connection, and a traffic spike means hundreds of instances at once, each opening a fresh connection with nothing to reuse. The same applies to older PHP setups that connect per request. Without a pooler in front, you hit the limit and pay the connection setup cost on every invocation. Most managed Postgres providers that advertise serverless support give you a pooled connection string for exactly this reason.

You have many services sharing one database

Five small services, each with a few replicas and a pool, plus workers, plus scheduled jobs. No single service is unreasonable. Together they are. A pooler gives you one place to cap total connections instead of coordinating pool sizes across teams or repos.

Deploys keep pushing you over

This was our case in the end. Even with smaller pools, blue-green deploys briefly doubled the fleet. PgBouncer absorbed the spike: 16 containers could connect to it, and it still used at most 25 connections to Postgres.

The catch: transaction pooling changes what a connection means

PgBouncer has three modes, and the one that gives you most of the benefit comes with rules.

• Session mode assigns a real Postgres connection to a client for as long as the client stays connected. It’s safe and compatible with everything, but it doesn’t help much if clients hold connections for a long time, which app pools do.
• Transaction mode assigns a real connection only for the duration of a transaction, then returns it. This is where the big multiplier comes from: hundreds of clients can share a few dozen server connections because most of them are idle most of the time.
• Statement mode is stricter still and rarely what you want for an application.

In transaction mode, your next transaction might run on a different server connection than your last one. Anything that relies on session state breaks or behaves strangely:

• SET commands outside a transaction (like SET search_path or SET timezone) may apply to a connection someone else gets next.
• Session-level advisory locks can be held on a connection you no longer own.
• LISTEN/NOTIFY doesn’t work reliably.
• Temporary tables that span transactions disappear or show up in the wrong place.
• Prepared statements used to be a well-known problem. Recent PgBouncer versions can track protocol-level prepared statements in transaction mode if you enable it, but check your version and your driver’s behavior rather than assuming.

We found two of these when we switched. A migration tool used a session advisory lock to prevent concurrent migrations, and a reporting query set statement_timeout at the session level. The fix was to run migrations with a direct connection that bypasses PgBouncer, and to use SET LOCAL inside a transaction for the timeout. Neither was hard, but neither was obvious until something failed.

The practical setup most teams end up with: application traffic goes through the pooler in transaction mode, and migrations, admin work, and anything that needs LISTEN use a direct connection.

What about just raising max_connections?

You can, and for a modest bump it’s fine. Going from 100 to 200 on a server with plenty of RAM won’t hurt most workloads. Newer Postgres versions also handle large numbers of mostly idle connections better than older ones did.

But it treats the symptom. More allowed connections means more possible concurrent queries, and the problem during a spike is rarely that you needed 400 queries running at once. It’s that 400 processes each wanted a connection “just in case.” Raising the ceiling lets them all in, and now they compete for CPU and locks on the database instead of waiting politely in a pool. A pooler keeps the actual concurrency near what the hardware can do and queues the rest.

A decision path

1. Count every process that connects, including deploy overlap. If the peak total is comfortably under max_connections, stop here.
2. If you’re over, shrink per-process pools first. Measure response time before and after. It often doesn’t change.
3. Fix leaks and idle-in-transaction connections. They inflate the count without doing work.
4. If you’re still over, or your process count is going to grow (autoscaling, serverless, more services), add a pooler. Use transaction mode for app traffic, keep a direct connection for migrations and session-dependent work, and test for the session-state issues above before you flip production over.

Our database now sits behind PgBouncer with 25 server connections. On a normal day, fewer than 10 are active. The deploy spike that took us down now shows up as a brief bump in PgBouncer’s client count and nothing at all on the Postgres side. The shrunken app pools did most of the work, though. PgBouncer handles the peaks that sizing couldn’t.