Why PostgreSQL Connections Are Expensive (And What PgBouncer Does About It)

We launched a product with a modest database setup. During development and early load testing, everything was fine. Ten concurrent users, twenty, thirty. The connection pool in the app was set to 20 connections per instance, things looked stable.

Then we ran a realistic load test with 150 simulated concurrent users across four application instances. Each instance had a pool of 20 connections. That's 80 connections to PostgreSQL, all real, all maintaining open sessions. Query latency climbed. The database server's CPU spiked despite the queries being simple. At 300 simulated users we started seeing connection errors.

The problem wasn't the queries. It was the connections themselves.

Why connections are expensive

PostgreSQL forks a new process for every connection. Not a thread. A process, with its own memory space, its own copy of the backend state, its own stack. Each connection typically uses somewhere between 5 and 10MB of memory just for the backend process.

At 100 connections, you're looking at 500MB to 1GB of memory committed to connection overhead before your queries touch any data. At 500 connections, it's 2.5 to 5GB of process overhead, plus the OS is spending meaningful time scheduling all those processes. At some point the connections become the bottleneck even if the queries are fast.

This is different from most application servers, which use threads and share memory. PostgreSQL predates a lot of modern threading models, and the forked process architecture is intentional for isolation reasons. It is also the reason connection count matters more for PostgreSQL than for some other databases.

Why this sneaks up on teams

You deploy one application server with a connection pool of 10. Everything is fine. You scale to five application servers. Now you have 50 connections. Still fine. You add background workers, a reporting server, a staging environment pointed at the same database, a monitoring agent. Now you're at 200 connections and wondering why your queries are slower than they used to be.

Nothing changed except the number of things talking to the database. The queries are identical. The indexes are the same. But the database is now managing 200 processes where it was managing 20, and that overhead is real.

The max_connections setting in PostgreSQL defaults to 100. The moment you exceed it, new connection attempts fail. Teams often respond by raising max_connections without understanding that this consumes more memory and makes the problem worse over time.

What PgBouncer does

PgBouncer sits between your application and PostgreSQL. Your application connects to PgBouncer. PgBouncer maintains a small pool of real connections to PostgreSQL and multiplexes your application's connections across them.

In practical terms: if you have 200 application connections going to PgBouncer, PgBouncer might be maintaining 20 real connections to PostgreSQL. The 200 application connections each get a real connection when they need to execute a query, and the connection is returned to the pool when the query completes. This is how you support 200 concurrent application connections against a database that works best with 20.

PgBouncer has three pooling modes, and they are not interchangeable.

Session mode assigns a real connection to an application connection for the entire session. This gives you the least multiplexing. It's essentially connection reuse without true pooling. Useful if your application needs session-level features, but you still want connection reuse across requests.

Transaction mode assigns a real connection for the duration of a transaction and returns it immediately when the transaction ends. This gives you the most multiplexing. An application that opens a connection, starts a transaction, runs a query, commits, and then sits idle will only hold the real connection during the active transaction, not during the idle time.

Statement mode is for single-statement queries only. Each statement gets a connection; anything requiring explicit transactions breaks. This is rarely the right mode for a typical web application.

What breaks in transaction mode

Transaction mode is the most useful mode and the one most likely to surprise you. Several PostgreSQL features assume a persistent connection state that transaction mode does not provide.

Prepared statements are bound to a specific backend connection. In transaction mode, consecutive transactions may land on different backends. Any prepared statement used outside of the transaction that created it will fail.

Advisory locks (pg_advisory_lock, pg_advisory_lock_shared) are session-scoped. In transaction mode, between transactions you may lose the session. The lock disappears, and you don't know it.

SET LOCAL applies only for the duration of the current transaction and is fine. SET without LOCAL applies to the session. In transaction mode, the session you thought you were configuring may not be the session handling the next query.

LISTEN/NOTIFY is session-scoped. Transaction mode doesn't work with it.

If your application uses any of these features, you need session mode, or you need to restructure the code to avoid them.

What to check before you deploy

Before adding PgBouncer, audit your application for prepared statement usage, advisory locks, and session-level SET statements. These are the things that will fail silently or with confusing errors in transaction mode.

If you use an ORM that transparently uses prepared statements (most do), you may need to configure it to disable them when running behind PgBouncer in transaction mode.

The standard setup for most web applications: PgBouncer in transaction mode, ORM configured to not use prepared statements, application pool targeting PgBouncer rather than PostgreSQL directly. Total real connections to PostgreSQL: whatever the workload actually needs, usually 20 to 50 rather than hundreds.

Knowing which mode you're in before you go to production means you find the prepared statement failures in staging, not at 2am after a deploy. The failure mode in production is connection errors with messages that don't obviously point to the pooler, because the pooler is invisible to the application by design. By the time you're debugging it in production, it's been a long night.