Database Connection Pooling Is the Performance Win Most Teams Skip

Most teams reach for a bigger database instance long before they reach for a connection pooler. That is backwards. A surprising amount of “Postgres is slow under load” is not Postgres being slow. It is Postgres drowning in connections, each one costing 5 to 10 MB of memory and a backend process, while your application opens a fresh connection for every request and throws it away a few milliseconds later.

Connection pooling is one of the cheapest performance wins available. It is also one of the easiest to get subtly wrong, because the most common pooler, PgBouncer, has a mode that looks like a transparent proxy but quietly changes what your queries are allowed to do. This post walks through why connections are expensive, benchmarks the overhead at 500 and 1000 concurrent clients, shows the misconfiguration that half the internet copy-pastes, and then covers the part nobody warns you about: how transaction mode breaks prepared statements and advisory locks.

Why a Postgres connection is not free

A lot of engineers picture a database connection as a cheap socket. In Postgres it is not. Every connection is a full operating-system process, forked from the postmaster, with its own memory for the parser, planner, catalog cache, and work buffers. Idle or busy, it holds resources.

Rough per-connection cost on a typical box:

• 5 to 10 MB of resident memory once catalog and plan caches warm up
• A backend process the OS has to schedule
• A share of work_mem reserved per sort or hash operation, potentially several multiples per query
• Lock table and snapshot bookkeeping that grows with connection count

The math turns ugly fast. Set max_connections = 1000 and size work_mem = 64MB, and a workload where each query does two sorts can, in the worst case, try to allocate 1000 x 2 x 64 MB = 128 GB of work_mem alone. You do not have 128 GB for sort buffers. The kernel OOM killer does, however, have opinions, and it will share them with your primary at the worst possible moment.

There is also a throughput cliff that has nothing to do with memory. Postgres uses a single lightweight lock manager and a shared snapshot mechanism. Past a few hundred active backends, the contention on those shared structures means adding connections makes total throughput go down, not up. This is the counterintuitive part: the fix for “too much load” is often fewer connections, not more.

The naive setup, and exactly where it breaks

Here is the setup almost every application starts with. No pooler. The app framework opens connections directly, maybe with a small in-process pool per instance.

  50 app instances
     x 20 connections each (framework default pool)
  = 1000 connections to Postgres

This works fine in staging with three app instances. Then you scale out horizontally to handle traffic, each instance keeps its own pool, and the connection count is now the product of your instance count and your per-instance pool size. Nobody decided to open 1000 connections. It emerged from autoscaling.

What breaks, in order:

1. Memory pressure. Backends pile up. RSS climbs. Cache hit ratio drops because the OS page cache is competing with backend process memory.
2. FATAL: sorry, too many clients already. You hit max_connections. New requests cannot get a connection at all. This looks like a total outage even though the database CPU is at 30 percent.
3. The reflexive fix makes it worse. Someone raises max_connections to 2000. Now the lock-manager contention and snapshot overhead drag throughput down, and the memory problem doubles. The graph looks like the database got slower after you gave it more capacity, because it did.

The naive mental model, “more connections means more concurrency means more throughput,” is wrong past the point where active backends exceed roughly (cpu_cores x 2) + effective_spindle_count. On an 8-core box with fast NVMe, the sweet spot for active connections is often somewhere between 20 and 40. Not 1000.

Enter the pooler

A connection pooler sits between your application and Postgres. Your app opens many cheap connections to the pooler; the pooler multiplexes them onto a small, fixed set of real Postgres backends.

  App instances                PgBouncer              Postgres
  (1000 client conns) ----->  [ pool ]  ----->  (25 server conns)
   cheap, short-lived         multiplexer        expensive, reused

The win is that 1000 clients that are mostly idle between queries can share 25 real backends, because at any given instant only a handful are actually executing SQL. PgBouncer itself is a single-threaded, event-driven process. It holds tens of thousands of client connections on a few MB of memory, because a client connection to PgBouncer is just a socket and a small state struct, not a Postgres process.

PgBouncer has three pooling modes, and the choice is the whole ballgame:

Session mode is safe but barely better than not pooling, because a server connection is pinned to a client for the client’s entire lifetime. If your clients are long-lived (the usual case with app frameworks), you get almost no multiplexing. Statement mode is too aggressive for most real apps because it forbids multi-statement transactions entirely.

Transaction mode is the one everyone wants and the one that bites. It is where the real multiplexing happens, and it is where query semantics quietly change.

Benchmarking the overhead at 500 and 1000 clients

Let me put numbers on it. The scenario: an 8-core Postgres primary, 32 GB RAM, NVMe storage, a simple read-mostly OLTP workload (index lookup plus a small update), driven at 500 and then 1000 concurrent clients. Compare direct connections against PgBouncer in transaction mode with a server pool of 25.

These are representative numbers from this class of setup, not a lab certification, but the shape is what matters and it reproduces reliably.

Read the last two rows against the first two. The direct setup gets slower going from 500 to 1000 clients: throughput falls from 28k to 19k while P99 latency quadruples. That is the snapshot-and-lock contention showing up. The pooled setup barely moves between 500 and 1000 clients, because the number of real backends is fixed at 25 either way. The extra 500 clients just wait a few microseconds longer in PgBouncer’s queue.

The single most important line here is that going from 1000 direct connections to 1000 clients behind a 25-backend pool roughly doubles throughput and cuts tail latency by more than 10x, on the same hardware, with the same queries. You did not tune a single Postgres GUC. You stopped forking 1000 processes.

A tiny client that reproduces the load pattern:

import asyncio
import asyncpg
import time

# Point DSN at PgBouncer (6432), not Postgres directly (5432)
DSN = "postgresql://app@pgbouncer-host:6432/appdb"

async def worker(pool, stop_at, counter):
    while time.monotonic() < stop_at:
        async with pool.acquire() as conn:
            # one transaction: read then write, the common web shape
            async with conn.transaction():
                row = await conn.fetchrow(
                    "SELECT balance FROM accounts WHERE id = $1", 42
                )
                await conn.execute(
                    "UPDATE accounts SET seen = now() WHERE id = $1", 42
                )
        counter[0] += 1

async def main(concurrency=1000, duration=30):
    # app-side pool is small per worker; PgBouncer does the real multiplexing
    pool = await asyncpg.create_pool(DSN, min_size=10, max_size=20,
                                      statement_cache_size=0)  # see caveat below
    counter = [0]
    stop_at = time.monotonic() + duration
    await asyncio.gather(*[worker(pool, stop_at, counter)
                           for _ in range(concurrency)])
    print(f"{counter[0] / duration:.0f} txns/sec")

asyncio.run(main())

Note statement_cache_size=0. That line is not incidental. It is the first casualty of transaction mode, which brings us to the part this post is really about.

The misconfiguration everyone copies

Search for a PgBouncer config and you will find this pasted everywhere:

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

[pgbouncer]
pool_mode = transaction
max_client_conn = 10000
default_pool_size = 100

Two things here are wrong for most real deployments.

default_pool_size = 100 is often too high. People treat pool size like a “max users” dial. It is not. It is the number of real Postgres backends per user/database pair. If you run 100 here and you have several databases and users, you can silently blow past max_connections on the server. Worse, a pool size of 100 recreates the exact lock contention you deployed PgBouncer to avoid. The whole point is a small number of hot backends. Start around (cores x 2) for the actual query workload, often 20 to 40, and only raise it if PgBouncer’s cl_waiting (clients waiting for a server connection) is consistently nonzero under real traffic. A pool that is too small shows up as latency; a pool that is too large shows up as the database falling over. Bias toward too small.

max_client_conn = 10000 with no relationship to reality. This one is usually fine to keep high, since client connections are cheap. The mistake is thinking a high max_client_conn means high throughput. It does not. It means you can accept 10000 clients; how fast you serve them is bounded by default_pool_size. High client cap plus small pool is the correct shape. High client cap plus high pool is how you OOM the primary.

And critically, the config above does not mention prepared statements, which in transaction mode is a landmine.

Transaction mode changes query semantics

Here is the mental model that breaks people. In session mode, one client owns one server backend for its whole life. Anything that lives on a connection, prepared statements, session GUCs, temp tables, LISTEN/NOTIFY, advisory locks held across statements, all of it just works, because the backend is yours.

In transaction mode, a server backend is returned to the pool the instant your transaction commits or rolls back. Your next transaction may land on a completely different backend. Anything that lives on the connection rather than inside the transaction is now unsafe, because the connection you set it up on is gone.

Three concrete failure modes:

Prepared statements

Most modern drivers (the PostgreSQL wire protocol’s extended query flow, used by asyncpg, psycopg’s server-side statements, JDBC with prepareThreshold) prepare a statement once and reuse it by name. The PREPARE lives on a specific backend.

Txn 1 on backend A:  PREPARE stmt_1 AS SELECT ...   -- ok
                     ... commit, backend A returns to pool ...
Txn 2 on backend B:  EXECUTE stmt_1                  -- ERROR: prepared statement "stmt_1" does not exist

The symptom is intermittent prepared statement "..." does not exist or already exists errors that only appear under load, because they only happen when your two transactions land on different backends, which only happens when the pool is actually multiplexing. It passes every test on your laptop with one client. It fails in production.

Fixes, in order of preference:

• Disable client-side prepared-statement caching. In asyncpg set statement_cache_size=0; in psycopg use prepare_threshold=None; in JDBC set prepareThreshold=0 and preparedStatementCacheQueries=0.
• Or, on newer PgBouncer (1.21+), enable server-side prepared statement support with max_prepared_statements set above 0, which teaches PgBouncer to track and re-prepare named statements across backends. This is the modern answer and largely removes the footgun, but it must be turned on explicitly and it is not free.

Advisory locks

Session-level advisory locks (pg_advisory_lock) are held until you explicitly unlock or the session ends. In transaction mode the session does not end at commit, so the lock is not released, and it is now held on a backend that has been handed to some other unsuspecting client.

-- Client X, transaction 1:
SELECT pg_advisory_lock(12345);   -- acquired on backend A
COMMIT;                           -- backend A returns to pool, lock STILL HELD

-- Client Y later gets backend A from the pool and is silently
-- inside a session that holds lock 12345 it never asked for.

This is a genuine correctness bug, not just an error message. You can get a distributed-lock scheme that appears to work and occasionally lets two workers into the critical section, or wedges a lock forever. The rule in transaction mode: only ever use the transaction-scoped variants, pg_advisory_xact_lock, which release automatically at end of transaction. Never pg_advisory_lock behind a transaction-mode pooler.

Everything else that is session state

SET outside a transaction, SET SESSION, unqualified temp tables, LISTEN/NOTIFY, WITH HOLD cursors, and DISCARD-sensitive state all leak or disappear unpredictably. Rules of thumb behind transaction mode:

• Use SET LOCAL (transaction-scoped) instead of SET.
• Do not rely on LISTEN/NOTIFY through the pooler; give it a dedicated session-mode connection or a direct one.
• Keep everything a request needs inside a single transaction.

When pooling hurts more than it helps

Pooling is not free and it is not always right. Cases where it is a net loss:

• You genuinely need session features everywhere. Heavy LISTEN/NOTIFY, session advisory locks you cannot rewrite, session-scoped SET for row-level security context. If you cannot move to transaction-scoped equivalents, transaction mode will bite, and session mode gives you little multiplexing. Sometimes the honest answer is a direct connection with a sane app-side pool.
• Low connection counts already. If your peak is 50 connections and max_connections is 200, a pooler adds an extra network hop, an extra process to monitor, and a new failure domain for zero throughput gain. Do not deploy infrastructure to solve a problem you do not have.
• You put the pool size too high. A pooler configured with a large default_pool_size gives you all the operational complexity of PgBouncer and all the lock contention of no pooler. This is the worst of both, and it is common, because the number looks like a capacity knob.
• Latency-critical single-tenant workloads. The extra hop through PgBouncer adds real, if small, latency (typically well under a millisecond, but nonzero). For most apps this is dwarfed by the throughput and tail-latency wins. For a hot path where every microsecond counts and connection count is already controlled, direct may win.

There is also a reliability angle: PgBouncer is a single process and a single point of failure in the naive deployment. Serious setups run it redundantly (one per app node, or a small HA pair behind a virtual IP) precisely because everything now flows through it.

What to actually do

If you are running Postgres under real concurrency and you do not have a pooler, that is almost certainly the highest-leverage change on your list. Concretely:

1. Deploy PgBouncer in transaction mode. Set default_pool_size small, around twice your core count for the active workload (often 20 to 40), not 100. Set max_client_conn high; it is cheap.
2. Fix your driver before you flip the switch: disable client-side prepared-statement caching, or enable PgBouncer 1.21+ max_prepared_statements server-side tracking. Do not discover this in production.
3. Audit for session advisory locks and rewrite them to pg_advisory_xact_lock. Audit for SET and move to SET LOCAL. Audit for LISTEN/NOTIFY and give it a dedicated connection.
4. Watch PgBouncer’s SHOW POOLS: if cl_waiting is persistently above zero, your pool is too small; raise it a little. If server backends sit near the pool size and Postgres is fine, you are balanced. Tune from data, not vibes.
5. If your workload is small or genuinely session-heavy and you cannot rewrite it, it is fine to skip pooling. Just size your app-side pools so the product of instances and per-instance pool never approaches max_connections.

The honest summary: connection pooling routinely doubles throughput and slashes tail latency on unchanged hardware, which is why skipping it is the most common cheap win teams leave on the table. But transaction mode is not a transparent proxy. It is a different execution model that trades away per-connection session state for multiplexing. Get the driver and the locks right first, keep the pool small, and it is close to free money. Paste the config off a blog without reading this far, and it will pass every test and then break under exactly the load you deployed it to survive.