PostgreSQL Connection Pooling: PgBouncer, max_connections and Sizing

What is PostgreSQL connection pooling?

PostgreSQL connection pooling is the practice of keeping a limited set of database connections open and reusing them across many client requests, instead of opening and closing a connection for each one. It happens at two levels. Application drivers and frameworks keep an in-process pool per application instance. An external pooler such as PgBouncer runs as a separate lightweight process between all clients and the database server.

PgBouncer is an open-source, single-purpose connection pooler for PostgreSQL. It speaks the PostgreSQL wire protocol, so applications connect to it exactly as they would connect to PostgreSQL, usually by changing only the host and port in the connection string. It holds a small number of real server connections and assigns them to clients according to a configurable pool mode. This article covers why PostgreSQL needs pooling, how PgBouncer's pool modes behave, how max_connections relates to pool size, and how to size and operate the pool.

Why PostgreSQL connections are expensive

Connection pooling keeps a small set of established database sessions open and lends them to application requests, so the database never sees the full number of clients at once. PostgreSQL needs this more than most databases because of its process-per-connection model. Each client connection is served by a dedicated backend process, forked by the postmaster.

That process has real costs:

• Startup: a new connection pays for the fork, authentication (password hashing, TLS handshake) and loading catalog caches before it runs a single query.
• Memory: each backend keeps private memory such as caches and per-operation work_mem allocations, which can multiply when a query sorts or hashes.
• Idle overhead: idle backends still cost something. Every transaction snapshot has to account for all open sessions, and lock and shared-memory structures are sized to the connection count, so a large number of idle sessions adds contention for the active ones.

Raising max_connections is not a free fix. It requires a restart, enlarges shared memory, and lets more backends compete for the same CPU cores and I/O. The usable count for applications is also smaller than the setting suggests. superuser_reserved_connections holds slots back for administrators, and autovacuum workers, background workers and replication senders consume process slots of their own.

To see where you stand, query pg_stat_activity, filter on backend_type = 'client backend', and group by state. A high idle count suggests oversized application pools. Any meaningful idle in transaction count points to sessions holding locks and snapshots while doing nothing.

For the exact parameters, see the Server Configuration and Client Authentication chapters of the official PostgreSQL documentation.

Copy-- 1. Client sessions grouped by state (PostgreSQL 10+ exposes backend_type).
--    A NULL state can appear if track_activities is off.
SELECT state, count(*) AS sessions
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY state
ORDER BY sessions DESC;

-- 2. Every process the server is running, not only clients.
--    Autovacuum, WAL senders and other background workers show up here.
SELECT backend_type, count(*) AS processes
FROM pg_stat_activity
GROUP BY backend_type
ORDER BY processes DESC;

-- 3. Headroom for ordinary (non-superuser) application roles.
--    On PostgreSQL 16+, also subtract reserved_connections if you set it.
SELECT current_setting('max_connections')::int
         - current_setting('superuser_reserved_connections')::int AS usable_app_slots,
       (SELECT count(*) FROM pg_stat_activity
         WHERE backend_type = 'client backend') AS client_sessions_now;

-- 4. Sessions idle inside an open transaction, oldest first.
--    These hold locks and snapshots while doing no work.
SELECT pid, usename, application_name,
       now() - state_change AS idle_for,
       left(query, 60) AS last_query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY state_change
LIMIT 20;

Queries against pg_stat_activity that break client sessions down by state, list every server process by type, show how many slots remain for non-superuser roles, and surface long-running idle-in-transaction sessions.

How does connection pooling work?

A pooler separates two kinds of connection: client connections, which applications open to the pooler, and server connections, which the pooler opens to PostgreSQL. Client connections are cheap for PgBouncer. It is a single event-driven process, so each one costs a small amount of memory and a socket, not a forked backend.

A request through PgBouncer follows these steps:

1. The application connects to PgBouncer's listen port and authenticates. PgBouncer checks the credentials against its auth_file or an auth_query.
2. The database name the client asks for is looked up in the [databases] section, which maps it to a real host, port and database.
3. When the client needs to run work, PgBouncer assigns it an idle server connection from the pool for that database and user pair. If none is idle and the pool is below its size limit, PgBouncer opens a new server connection. If the pool is full, the client waits in a queue.
4. When the pool mode's release point is reached (client disconnect, end of transaction, or end of statement), the server connection returns to the pool and can be given to the next waiting client. In session mode, PgBouncer runs a reset query, by default DISCARD ALL, before reuse.

The result is that PostgreSQL sees a stable, bounded number of backends no matter how many clients connect to the pooler. The sections below cover each layer and the trade-offs of each pool mode in more detail.

How the two layers of pooling work

The application pool

Most drivers and frameworks keep an in-process pool: a fixed set of open connections that request handlers borrow and return. This removes the per-request cost of connecting, but each pool belongs to a single process and has no visibility into the others. The database sees the sum of every pool's open connections, whether or not they are doing work.

Why horizontal scaling breaks it

Total server connections equal instances multiplied by pool size. As an illustrative estimate, assume 20 pods with a pool of 10 each, which is 200 connections. Autoscale to 40 pods during a traffic spike and you reach 400, with no change to the configuration. Serverless functions make this worse, because each concurrent invocation can open its own connection and none of them coordinate. Background workers, cron jobs and migration runners add to the same total. Once you reach max_connections, new connections are refused, usually during the peak traffic you scaled out to handle.

The external pooler

An external pooler such as PgBouncer sits between clients and PostgreSQL. It accepts many lightweight client connections and multiplexes them onto a small, fixed set of server connections. App instances can then scale independently, because the database only sees the pooler's server-side pool.

You need one if you run many instances, have bursty or serverless workloads, or have connection counts that regularly approach max_connections. A single long-lived service with a modest pool usually does not.

Copy WITHOUT AN EXTERNAL POOLER: every app pool connects straight to Postgres

 +-----------+  +-----------+       +-----------+  +--------------------+
 | pod 1     |  | pod 2     |  ...  | pod 40    |  | workers, cron,     |
 | pool = 10 |  | pool = 10 |       | pool = 10 |  | serverless fns     |
 +-----+-----+  +-----+-----+       +-----+-----+  +---------+----------+
       |10            |10                 |10                |  +N
       v              v                   v                  v
 +-----------------------------------------------------------------------+
 | PostgreSQL   server connections = 40 x 10 + N  -> hits max_connections|
 +-----------------------------------------------------------------------+

 WITH PGBOUNCER: many client connections, few server connections

 +-----------+  +-----------+       +-----------+  +--------------------+
 | pod 1     |  | pod 2     |  ...  | pod 40    |  | workers, cron,     |
 | pool = 10 |  | pool = 10 |       | pool = 10 |  | serverless fns     |
 +-----+-----+  +-----+-----+       +-----+-----+  +---------+----------+
       |              |                   |                  |
       v              v                   v                  v
 +-----------------------------------------------------------------------+
 | PgBouncer    accepts 400 + N lightweight client connections           |
 +-----------------------------------+-----------------------------------+
                                     |  small fixed server pool, e.g. 20
                                     v
 +-----------------------------------------------------------------------+
 | PostgreSQL   sees only PgBouncer's server connections, e.g. 20        |
 +-----------------------------------------------------------------------+

Illustrative estimate: without a pooler, 40 pods with 10-connection pools plus workers and serverless functions open 400 + N server connections, while PgBouncer multiplexes the same clients onto a small fixed server pool.

Who should use an external pooler?

An external pooler like PgBouncer is worth running if one or more of these describe your system:

• Horizontally scaled services. Many application instances, especially under autoscaling, where total connections grow with instance count.
• Serverless or short-lived compute. Functions or jobs that connect on each invocation and cannot share an in-process pool.
• Connection counts near the limit. pg_stat_activity regularly shows client sessions approaching usable max_connections, or you see connection-refused errors under load.
• Many idle sessions. Large numbers of idle client backends, which indicate application pools sized well beyond the concurrent work they do.
• Short OLTP transactions. Workloads made of many brief transactions benefit most from transaction-mode multiplexing.

It is usually unnecessary for a small number of long-lived services whose combined application pools stay comfortably below max_connections. Workloads that depend heavily on session state (session-level advisory locks, LISTEN, session SETs) need either session mode or direct connections, as covered below. Platform and database teams who own the shared database are typically the ones to deploy and operate the pooler, while application teams need to audit their code for the session-state assumptions described in the next section.

PgBouncer pool modes and what each one breaks

PgBouncer decides when a server connection goes back to its pool. The earlier it releases the connection, the more clients can share each backend, and the more session state your application loses.

In transaction mode, any state attached to a backend instead of a transaction can surface in another client's session or disappear between your queries. A SET search_path can leak to an unrelated request. An advisory lock can be released by a client that never acquired it. A LISTEN stops receiving notifications once its backend is reassigned. Prepared statement support differs across PgBouncer releases and settings, so check the documentation for the version you run.

The risk is that these bugs stay hidden in development. Locally your app holds a dedicated connection and all of this works. Behind a transaction-mode pooler in production, it fails only intermittently and only under concurrency.

Before switching modes, audit the stack:

• Find every SET and advisory lock call in your code.
• Check whether your ORM or driver prepares statements automatically, and whether you can turn that off.
• Review the session parameters the driver sends at connect time.
• Run your integration tests through PgBouncer in the same mode you use in production.

Sizing the pool from first principles

A connection count is not a throughput setting. Each active connection is served by its own backend process, and only as many of those can make progress at once as there are CPU cores and outstanding I/O operations the storage can service. Once active backends outnumber that capacity, extra connections only wait. The OS spends more time context switching between them, and they contend for the same locks and shared buffers. Throughput flattens and tail latency climbs.

The method is empirical. Start the server-side pool small relative to the database's core count. Run a load test with a realistic query mix and increase the pool in steps. Stop when throughput stops improving or p95/p99 latency starts to degrade. That point is your ceiling.

Illustrative estimate (assumed inputs, not a measurement): 20 application instances, each with a client-side pool of 10, can open 200 client connections. PgBouncer accepts all 200 and multiplexes them onto a server pool of about 40, which we assume is the level this database serves comfortably. Short transactions queue briefly at the pooler instead of piling up as active backends on the server.

The two failure modes pull in opposite directions. An undersized pool makes clients queue at the pooler, and under load those waits turn into timeouts. An oversized pool moves the queue into the database, where it competes for CPU and locks and can saturate the server for every client. No formula based on core count replaces a load test on your own hardware with your own queries.

Copy; pgbouncer.ini: illustrative values matching the worked example above.
; 20 app instances x client pool of 10 = 200 client connections,
; multiplexed onto roughly 40 server connections.

[databases]
; Clients connect to "appdb" on PgBouncer, which forwards to the real server.
appdb = host=db.internal.example 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

; A server connection returns to the pool when each transaction ends,
; so 200 clients with short transactions can share 40 backends.
pool_mode = transaction

; Client side: accept every connection the app tier can open, with headroom.
max_client_conn = 250

; Server side: the number you tune by load testing, raising it in steps
; until throughput plateaus or p95/p99 latency degrades.
default_pool_size = 40

; Hard cap per database across all users, so adding a role cannot
; push more active backends onto the server.
max_db_connections = 40

; Undersized-pool symptom: clients waiting longer than this for a server
; connection are disconnected with an error instead of hanging indefinitely.
query_wait_timeout = 30

; On the PostgreSQL side, max_connections must exceed this pool
; plus admin, replication and monitoring connections.

A PgBouncer config in transaction pooling mode that accepts up to 250 client connections and caps server connections at 40, where 40 is the assumed pool size to validate by load testing.

Implementing it: configuration and timeouts that keep pools healthy

A minimal transaction-mode setup needs only a few PgBouncer settings: pool_mode = transaction, a default_pool_size taken from your load test, and a max_client_conn large enough to absorb every application instance's local pool. Add query_wait_timeout so clients queued inside PgBouncer get an error instead of waiting indefinitely. Set server_lifetime and server_idle_timeout so server connections are recycled periodically, which limits per-backend memory growth and stale state.

Stop transactions from pinning connections

In transaction mode, a server connection stays assigned to one client until that client commits or rolls back. A request handler that opens a transaction and then waits on an HTTP call holds a pooled connection the entire time. On the PostgreSQL side, idle_in_transaction_session_timeout terminates sessions that sit idle inside an open transaction. statement_timeout cancels runaway queries. Set both per role with ALTER ROLE ... SET. Avoid session-level SET statements, because in transaction mode they persist on the server connection and apply to whichever client gets it next. For a per-transaction override, use SET LOCAL.

Fail fast on the client

Configure your driver's pool with an acquisition timeout shorter than your request deadline, and a maximum connection lifetime. A request then fails quickly with a clear pool-exhaustion error instead of hanging until an upstream load balancer gives up.

Auth and identity

Credentials are now checked in two places. PgBouncer authenticates clients through auth_file or auth_query, and then authenticates to PostgreSQL itself. Password rotation and auth method changes (for example, moving to SCRAM) must be applied to both layers. In pg_stat_activity and the server logs, every session appears to come from PgBouncer's address. Set application_name_add_host = 1 so the original client host and port are appended to application_name, which lets you trace a session back to the instance that opened it.

Copy; pgbouncer.ini: minimal transaction-mode setup (placeholder values)
[databases]
; Clients connect to "appdb" on PgBouncer, which forwards to this server.
appdb = host=10.0.0.5 port=5432 dbname=appdb

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432

; Auth layer 1: PgBouncer checks clients itself. Rotating passwords or
; changing the method (e.g. to SCRAM) must also be done on PostgreSQL.
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

; A server connection is held only for the duration of one transaction.
pool_mode = transaction

; Server connections per (database, user) pair. Take this from your load test.
default_pool_size = 20

; Must cover the sum of every app instance's local pool max.
max_client_conn = 2000

; Seconds a client may wait in PgBouncer's queue before getting an error,
; so it fails instead of hanging. Keep below your request deadline.
query_wait_timeout = 10

; Recycle server connections to bound per-backend memory growth and stale state.
server_lifetime = 3600
server_idle_timeout = 600

; Append the client's address to application_name so pg_stat_activity shows
; more than PgBouncer's own IP as the origin.
application_name_add_host = 1

; Run on PostgreSQL, not here, to stop open transactions pinning connections:
;   ALTER ROLE app_user SET idle_in_transaction_session_timeout = '15s';
;   ALTER ROLE app_user SET statement_timeout = '5s';

A minimal PgBouncer transaction-mode configuration that uses a queue timeout and connection recycling so failures surface quickly, with the per-role PostgreSQL timeouts that stop idle transactions from holding pooled connections.

Where to run the pooler and how to observe it

Placement trade-offs

A sidecar or per-host pooler sits on the same machine as the application, so the hop to it is local and a pooler crash affects only that host. The cost is that every host keeps its own server pool, and total backend connections become hosts × default_pool_size. That total grows each time you scale out. A central pooler tier sees all clients, so it can enforce one global connection budget. It adds a network hop, and it needs its own redundancy, because an outage there takes down every client.

A single PgBouncer process is single-threaded. At high throughput it saturates one CPU core before PostgreSQL is busy. To scale it, run several processes, either behind a load balancer or sharing one port with so_reuseport. Count each process's pool against max_connections.

Observing the pooler

Connect to the virtual pgbouncer database with a user listed in admin_users or stats_users. In SHOW POOLS, watch cl_waiting, which counts clients queued for a server connection, and maxwait, which shows how long the oldest of them has waited. In SHOW STATS, avg_wait_time shows the trend over time.

Occasional brief waits are normal. A queue that persists means server connections are held too long or the database is at capacity. Enlarging the server pool in that state pushes more concurrent work onto a saturated server and makes latency worse. Fix the slow or idle-in-transaction sessions first, then add database capacity if the queue remains.

When to avoid transaction pooling, alternatives, and impact on cost and engineering time

Some workloads should bypass a transaction-mode pool entirely:

• Logical replication and other replication-protocol clients need a dedicated, long-lived session. Connect them directly to the server.
• LISTEN/NOTIFY consumers register on one specific backend. If the pooler hands that backend to another client after commit, the listener silently stops receiving notifications.
• Some migration tools take session-level advisory locks or rely on SET. That state can be lost or leak between statements.
• Long-running analytics and batch jobs work correctly, but they hold server connections for minutes. When they share a pool with OLTP traffic, short requests queue behind them.

The pooler has real costs. It is another component that you must deploy, monitor and make highly available. It adds a network hop to every query. Teams also have to audit their code for session-state assumptions before switching to transaction mode.

Against that, a pooler prevents a specific class of incident: connection exhaustion during a traffic spike. Without one, these spikes typically lead to emergency max_connections restarts and database-wide slowdowns that can take engineers hours to diagnose. For most fleets running more than a handful of app instances, avoiding those incidents saves more engineering time than operating the pooler costs.

How OneEnv can help

Disclosure: OneEnv is the product behind this blog.

OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.

Applied to postgresql connection pooling, this is where it helps:

Saves time

• Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
• One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
• Changing a shared variable is one edit and one review instead of editing every service's .env by hand.

Saves cost

• Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
• Less duplicated configuration to maintain: one shared definition inherited by many services.
• A wrong or stale value is caught at review time by the services it affects, not by a failed deploy.
• OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.

These are qualitative benefits, not measured results — how much you save depends on how many services share the value.

Want to try it? Start at oneenv.io.