The Complete Guide to Connection Pools: The Contract a Pooling Mode Makes with Your Application

• Introduction
• 1. What a Single Connection Actually Costs
• 2. The Triple Budget — Deciding Three Numbers Together
• 3. The Three Pooling Modes
• 4. What Breaks Under Transaction Pooling
• 5. The Prepared Statement Problem
• 6. The Timeout Landscape — What Belongs on Which Layer
• 7. Observability — Where Is the Line Forming
• 8. Splitting Pools by Workload
• Quiz: Test Yourself
• Closing
• References
• Continue Reading

Introduction

This blog already has a post on Why a Big Connection Pool Costs You — Deciding Where to Put the Queue. That is the sizing installment, covering why pool size should be set near the core count and where the queue belongs.

This post covers the next question. Once you have decided on a size, in what manner will you reuse that connection? Session pooling, transaction pooling, and statement pooling are each a different contract. Switching to transaction pooling improves connection efficiency dramatically, but in exchange there are features your application can no longer use. Not knowing that list before switching modes is the single most common cause of incidents in the field: SET search_path leaking into the next request, a session-level advisory lock that never gets released, a prepared statement throwing a "does not exist" error.

The reference is PostgreSQL 18 and PgBouncer, and every default value quoted here was confirmed against each project's own official documentation.

1. What a Single Connection Actually Costs

PostgreSQL spins up one operating system process for every connection. Not a thread — a process. This design is the starting point for the entire connection pool discussion.

The cost shows up at three layers.

The memory of the process itself. Every backend process carries its own cache and working area. The catalog cache and plan cache grow in proportion to the number of objects that connection has touched. When many sessions touch a table with a large number of partitions, this piece accumulates.

Working memory. work_mem defaults to 4MB, but that value applies not per connection but per individual sort or hash operation. If a single query contains three sorts and two hash joins, that one query alone uses five times that amount. Hash operations get multiplied further, up to hash_mem_multiplier (default 2.0). The common way of estimating the worst case as connection count times work_mem is, in practice, an underestimate.

The size of shared resources. The documentation states this plainly: "PostgreSQL sizes certain resources based directly on the value of max_connections; raising this value increases the allocation of that resource, including shared memory." The default for max_connections is, in the documentation's own words, "typically 100," and it can be lower depending on kernel settings.

And the biggest cost is not memory at all. It is context switching and lock contention. Attach 500 active connections to a server with 16 cores, and the CPU spends more time switching processes in and out than doing actual work. This is exactly why the earlier post said "a big pool costs you."

2. The Triple Budget — Deciding Three Numbers Together

A real-world setup has connection counts sitting in at least three places. Decide these three independently, and they are guaranteed to fall out of alignment.

[N application instances]
   connection pool size per instance = A
        ↓  up to N × A client connections
[PgBouncer]
   max_client_conn (how many clients it will accept)
   default_pool_size (server connections actually opened per DB/user pair)
        ↓  up to (number of pools × default_pool_size) server connections
[PostgreSQL]
   max_connections

Turning the relationship between the three layers into rules gives us this.

Rule 1 — PgBouncer's max_client_conn has to be greater than the maximum number of connections the application can create. Per the PgBouncer documentation, max_client_conn defaults to 100. If 20 instances each use a pool of 10, you need 200, but the default is 100, so leaving it at the default means the application gets connections refused.

Rule 2 — the total of server connections has to stay comfortably below max_connections. PgBouncer's default_pool_size defaults to 20, and that value applies per database-and-user pair. With 3 databases and 4 users, the worst case opens 12 pools × 20 = 240 server connections. It is safer to cap things per database with max_db_connections (default 0, unlimited).

Rule 3 — leave headroom in max_connections. superuser_reserved_connections defaults to 3 and reserved_connections defaults to 0. The point of this reservation is to leave a slot for an administrator to connect during an incident. Budget separately for monitoring agents, backup tools, and migration runners too.

3. The Three Pooling Modes

These are the three modes as PgBouncer's documentation defines them. Each quoted sentence is, quite literally, the contract.

• session — "the server is released back to the pool after the client disconnects. Default." In other words, one client occupies one connection outright. From the application's point of view, this behaves exactly as if PgBouncer were not there at all, so nothing breaks. In exchange, it saves almost no connections.
• transaction — "the server is released back to the pool after the transaction finishes." This is the mode used most in practice. For web applications with long idle periods, it cuts connection counts by a single-digit multiple.
• statement — "the server is released back to the pool after the query finishes. Multi-statement transactions are disallowed in this mode." Multi-statement transactions become entirely impossible, so its use is very limited.

The fact that the default is session is easy to miss. If you have put PgBouncer in front and connection counts have not moved at all, check the mode.

The criterion for choosing a mode is simple. If not a single piece of code depends on session state outside a transaction, use transaction. If even one does, you either have to fix that code or stay on session. The next section is that list.

4. What Breaks Under Transaction Pooling

PgBouncer's documentation puts it this way. In transaction pooling mode, "the client must not use any session-based features, since each transaction ends up on a different connection and thus sees different session state."

Here, concretely, is what breaks.

SET / RESET. Running SET search_path, SET timezone, or SET statement_timeout outside a transaction leaves it sitting on that server connection. The next transaction can be assigned a different connection, so the setting appears to have vanished, and conversely, it leaks into whichever other client receives that connection next. The latter is far more dangerous. A design that switches schemas via search_path in a multi-tenant application can never be combined with transaction pooling. Using SET LOCAL inside a transaction is safe, because it reverts the moment the transaction ends.

-- Safe: only valid inside the transaction boundary
BEGIN;
SET LOCAL statement_timeout = '5s';
SELECT ...;
COMMIT;

LISTEN / NOTIFY. LISTEN registers state onto the session. Once the transaction ends, the connection is returned, so there is no way left to receive the notification. If you need a notification-based architecture, that connection alone has to be split off into a separate session-mode pool.

Session-level advisory locks. This is the most dangerous item of all. Per the PostgreSQL documentation, a session-level advisory lock "is held until explicitly released or the session ends, and it survives a transaction rollback." Under transaction pooling, the connection that acquired the lock and the connection that releases it can be different ones, and when that happens the lock never gets released, ever. Always use only the transaction-level functions (pg_advisory_xact_lock, pg_try_advisory_xact_lock). These release automatically when the transaction ends.

WITH HOLD cursors. These are cursors that survive after the transaction ends, but since the connection gets returned, they become unreachable.

Temporary tables. These belong to the session, so they appear to vanish in the next transaction. Only the pattern of creating, using, and discarding within a single transaction (ON COMMIT DROP) is safe.

5. The Prepared Statement Problem

This is the item people run into most often, so it gets its own section.

Most drivers use prepared statements for parameter binding. A prepared statement is registered by name on a specific server connection, so under transaction pooling, getting assigned a different connection produces a prepared statement "S_1" does not exist error.

PgBouncer mitigates this problem with max_prepared_statements. Per the documentation, the default is 200, and it tracks prepared statements at the protocol level in transaction and statement pooling modes. The mechanism is that PgBouncer performs the necessary preparation on each server connection on the client's behalf.

Even so, here is the order for confirming a safe combination.

1. Confirm whether your PgBouncer version supports prepared statement tracking. This feature was introduced in a specific version, so check the documentation for the version you are actually running.
2. Confirm that max_prepared_statements is not 0.
3. Check the driver-side settings. Many drivers offer an option to turn off server-side preparation. Turning it off is certainly safe, but you lose the benefit of plan reuse.

There is one more performance issue connected to this. Using prepared statements lets PostgreSQL choose a generic plan, and on a column with a skewed value distribution, that can be disastrous. This behavior is controlled by plan_cache_mode, whose allowed values are auto (default), force_custom_plan, and force_generic_plan. If you decide to turn prepared statements on, you need to know this parameter exists too.

6. The Timeout Landscape — What Belongs on Which Layer

Every leg a single request passes through has its own timeout, and if these contradict each other, diagnosis becomes impossible.

The application pool layer. This is the upper bound on how long you wait to get a connection from the pool. Without this value, every application thread piles up in a waiting state the moment the database slows down.

The PgBouncer layer. query_wait_timeout defaults to 120 seconds — this is how long a client waits to be assigned a server connection from the pool. server_idle_timeout defaults to 600 seconds, and it closes server connections that have been sitting idle too long.

The PostgreSQL layer. There are three timeouts here, and all three default to 0, meaning disabled. statement_timeout aborts a statement that runs past the specified time, lock_timeout aborts a lock wait that runs past the specified time, and idle_in_transaction_session_timeout terminates a session that is sitting idle with a transaction left open. Since PostgreSQL 17 there is also transaction_timeout, which caps the total duration of a transaction, and its default is likewise 0.

The principle for ordering these is that the outer layer must be longer than the inner one. If the application timeout is shorter than statement_timeout, the application gives up while the query keeps running on the server and burning resources. Order it the other way, and the server cuts it off first, so the resources get reclaimed.

-- Give the service account and the batch account different budgets
ALTER ROLE app_web SET statement_timeout = '10s';
ALTER ROLE app_web SET idle_in_transaction_session_timeout = '30s';
ALTER ROLE app_web SET lock_timeout = '3s';

ALTER ROLE app_batch SET statement_timeout = '30min';
ALTER ROLE app_batch SET idle_in_transaction_session_timeout = '5min';

7. Observability — Where Is the Line Forming

The first question to ask when things slow down is "which layer has the queue." Each layer has different metrics to watch.

The application pool. Pool wait time and active connection count. If wait time is climbing while the database is sitting idle, the pool is too small.

PgBouncer. Connect to the admin console and check SHOW POOLS and SHOW STATS. If cl_waiting (the number of clients waiting for a server assignment) is consistently above 0, either default_pool_size is too small or the server is slow.

PostgreSQL. Check state and wait events in pg_stat_activity.

-- Connection distribution by state: a lot of idle in transaction points to an application-side problem
SELECT state, count(*), max(now() - state_change) AS longest
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY state
ORDER BY count(*) DESC;

-- What is it waiting on
SELECT wait_event_type, wait_event, count(*)
FROM pg_stat_activity
WHERE wait_event IS NOT NULL AND backend_type = 'client backend'
GROUP BY 1, 2
ORDER BY 3 DESC;

The values state can take are defined in the documentation: active, idle, idle in transaction, idle in transaction (aborted), fastpath function call, starting, and disabled. A lot of idle in transaction is not a connection pool problem — it means the application is holding a transaction open while doing something else. Enlarging the pool will not fix this; it makes things worse.

The values of wait_event_type are Lock, LWLock, BufferPin, IO, IPC, Client, Timeout, Activity, and Extension. A lot of Client means the server is waiting on the client rather than the other way around, so the database is not the bottleneck.

8. Splitting Pools by Workload

Mix every workload into one pool, and the single slowest query blocks all of it. In practice, pools get split by their character.

OLTP pool — short, frequent requests. Transaction pooling, a small default_pool_size, a short statement_timeout.

Batch/report pool — long, infrequent requests. Split this off under a separate database user, give it a long statement_timeout, and keep the pool size small. It is better to send reports to a read replica.

The pool that needs session state — the small number of connections that need LISTEN/NOTIFY or session-level advisory locks. Keep these separate in a session-mode pool.

Migration/admin pool — the path that runs DDL. Under transaction pooling, commands like CREATE INDEX CONCURRENTLY need to run outside a transaction block, so they need separate handling.

The real benefit of splitting is isolation. Even if report queries run wild, the OLTP pool's server connections stay right where they are. For the same reason, a circuit breaker should also be applied per pool.

One more thing to add. PgBouncer itself runs as a single process, so if PgBouncer maxes out one CPU core, that becomes your bottleneck. In high-throughput environments, consider running multiple PgBouncer instances or deploying one as a sidecar per application node. Whether multi-process support exists, and how to configure it, needs to be checked against the documentation for the version you are running.

Quiz: Test Yourself

BEGIN;
SELECT pg_try_advisory_xact_lock(hashtext('nightly-batch'));
-- do the work
COMMIT;  -- the lock is released automatically here

Closing

Before it is a performance tool, a connection pool is a contract. Session pooling is the contract that says "I will not change anything." Transaction pooling is the contract that says "I will save connections in exchange for giving up session state." Sign either one without reading the terms, and you will pay for it.

Here is the adoption order, laid out. First, find every place where the application depends on session state (SET, LISTEN, session advisory locks, temporary tables, prepared statements). Move those inside a transaction or split them off into a separate pool. Then switch to transaction pooling, decide the numbers for all three layers together, and arrange timeouts so they get shorter moving from the outside in. Finally, put cl_waiting and idle in transaction on your dashboard.

To experiment with connection behavior yourself, use the PostgreSQL Playground.

References

• PostgreSQL 18, Connections and Authentication: https://www.postgresql.org/docs/18/runtime-config-connection.html (retrieved 2026-08-15)
• PostgreSQL 18, Resource Consumption: https://www.postgresql.org/docs/18/runtime-config-resource.html (retrieved 2026-08-15)
• PostgreSQL 18, Client Connection Defaults: https://www.postgresql.org/docs/18/runtime-config-client.html (retrieved 2026-08-15)
• PostgreSQL 18, Explicit Locking (Advisory Locks): https://www.postgresql.org/docs/18/explicit-locking.html (retrieved 2026-08-15)
• PostgreSQL 18, Monitoring Database Activity: https://www.postgresql.org/docs/18/monitoring-stats.html (retrieved 2026-08-15)
• PostgreSQL 18, Query Planning: https://www.postgresql.org/docs/18/runtime-config-query.html (retrieved 2026-08-15)
• PgBouncer, Configuration: https://www.pgbouncer.org/config.html (retrieved 2026-08-15)

Continue Reading

• Previous: The Complete Guide to Partitioning and Sharding — the order for moving past a single node
• Next: The Complete Guide to Database Caching Strategies — invalidation is everything
• Why a Big Connection Pool Costs You — Deciding Where to Put the Queue — pool sizing
• The Complete Guide to Transaction Isolation Levels — designing around transaction boundaries
• PostgreSQL Playground — experiment with session state