Understanding Connection Pooling: Why Every Connection Counts

1. Why every database connection costs resources

A database connection is not a lightweight abstraction, it is a process or thread with its own memory area on the database server. PostgreSQL starts a dedicated operating system process for every connection, which already occupies several megabytes of memory while idle. MySQL uses threads, which are lighter but still create significant memory and scheduling overhead when many connections exist at once. Connection pooling addresses exactly this: instead of opening and closing a new connection for every request, a limited set of connections gets reused.

Setting up a new connection is expensive by itself, regardless of the underlying database system. TCP handshake, TLS negotiation, authentication, and initializing session variables add up to several milliseconds per connection. In an application that opens a new connection per request, this overhead accumulates on every single request, pushing the actual payload of the request into the background under load.

Without connection pooling, applications grow straight into the database's connection limits as user count rises. PostgreSQL limits the number of simultaneous connections through the max_connections parameter, typically to a few hundred. A horizontally scaled application with many application servers, each opening its own connections without pooling, often crosses this limit faster than expected, and the database starts rejecting new connection attempts.

2. How a connection pool actually works

A connection pool keeps a fixed number of already open connections to the database ready. An application requests a connection from the pool when needed, instead of opening a new one. Once the operation completes, the connection is not closed but returned to the pool, where it becomes available for the next request. If the pool is exhausted because all connections are currently in use, the requesting component either waits in a queue or receives an error after a timeout.

Two basic architectures exist: application side pooling, where the pool runs inside the application process itself, for example as a library in the application server, and external pooling, where a standalone process such as PgBouncer sits between the application and the database and bundles connections regardless of how many application instances run behind it. External pooling is especially valuable with many small application instances, for example in Kubernetes environments with many pods that would otherwise each hold a small pool and, combined, overwhelm the database.

The central benefit of connection pooling is that the number of actual database connections becomes decoupled from the number of application threads or requests. An application can serve thousands of concurrent user requests while the pool holds open only a few dozen real database connections, because most requests occupy a connection for only a few milliseconds.

-- PostgreSQL: check current connections and their state
SELECT
    datname,
    state,
    count(*) AS connections
FROM pg_stat_activity
GROUP BY datname, state
ORDER BY connections DESC;

-- Show the current value of max_connections
SHOW max_connections;

-- MySQL: check active connections and the limit
SHOW STATUS LIKE 'Threads_connected';
SHOW VARIABLES LIKE 'max_connections';

3. Pooling modes: session, transaction, statement

Connection poolers such as PgBouncer distinguish several modes that determine when a connection gets returned to the pool. In session mode, a client connection keeps its assigned database connection for the entire duration of the session. This behaves almost like a direct connection without pooling and brings little savings, but it is necessary when session variables, temporary tables, or advisory locks need to persist across multiple requests.

In transaction mode, the database connection is returned to the pool after each completed transaction, not only at the end of the client session. This is by far the most efficient mode and lets a small number of real connections serve thousands of client sessions. The downside: session state such as SET search_path or prepared statements can be lost between transactions, because the next transaction may run on a different physical connection.

Statement mode, the most granular one, returns the connection right after every single statement. It is rarely used because it makes explicit transactions spanning multiple statements impossible, but it shows up in very specific scenarios with pure single queries without a transaction wrapper. For most web applications, transaction mode is the right choice, with an awareness of its limitations regarding session state.

-- pgbouncer.ini: configure the pooling mode
[databases]
shop = host=db-primary.internal port=5432 dbname=shop

[pgbouncer]
pool_mode = transaction
max_client_conn = 2000
default_pool_size = 25
reserve_pool_size = 5
reserve_pool_timeout = 3
server_idle_timeout = 600

4. Sizing a pool correctly

The intuitive assumption that a bigger pool is always better is wrong. Beyond a certain size, database throughput actually drops, because the database spends more time context switching between competing connections than doing actual work. A well known rule of thumb from the PostgreSQL community says: optimal pool size roughly equals the number of CPU cores multiplied by two, plus the number of effective disk spindles. On modern SSD systems, most teams orient around core count with a moderate factor.

More important than a rigid formula is empirical measurement: increase pool size step by step while watching throughput and latency. Once additional connections no longer produce measurable throughput gains but latency of individual requests increases, you have reached the sensible upper limit. This limit varies significantly by workload: many short, simple queries tolerate larger pools than a few long running, complex analytics queries that already load the database heavily on their own.

A common mistake is configuring a generously sized pool per application instance without considering the sum across all instances. Ten application instances with twenty connections each add up to two hundred simultaneous database connections, regardless of whether the database can handle that. A central external pooler such as PgBouncer solves this problem by limiting the total number of backend connections independently of the number of application instances.

5. Configuring PgBouncer in practice

PgBouncer is the de facto standard for external connection pooling in the PostgreSQL ecosystem. Configuration happens through an INI file with connection definitions per logical database and global pooling parameters. The default_pool_size parameter defines how many backend connections are opened at most per database user combination, while max_client_conn limits the total number of incoming client connections PgBouncer itself accepts, independent of the backend pool size.

The reserve_pool_size parameter reserves additional connections for load spikes, activated after reserve_pool_timeout seconds of waiting. This prevents short lived load spikes from immediately causing connection errors, without permanently tying up more resources than needed. In production, PgBouncer is often run on a small dedicated instance close to the database to minimize additional network latency.

An often underestimated aspect is the behavior on a PgBouncer restart: existing client connections get dropped, which requires corresponding retry logic in the application. Introducing PgBouncer without this precaution replaces one scaling problem with a new availability risk that needs to be accounted for during planning.

6. Common pitfalls: prepared statements and session state

Prepared statements are one of the most common causes of problems with connection pooling in transaction mode. A prepared statement is bound to a specific database connection. If the next transaction switches to a different physical connection in the pool, the prepared statement is unknown there, causing errors like "prepared statement does not exist". Many ORMs and drivers cache prepared statements by default, which combined with transaction pooling leads to hard to reproduce, intermittent errors.

Session specific settings such as SET timezone, temporary tables, or advisory locks suffer from the same fundamental problem: they are bound to a physical connection, not to a logical client session. Anyone who needs such features has to either fall back to session pooling, which reduces efficiency, or design the application so that session dependent state is set and released within a single transaction.

A third pitfall involves LISTEN/NOTIFY in PostgreSQL, a database notification mechanism that requires a permanently open connection. Such use cases fundamentally do not work with transaction pooling and should run over a dedicated, direct connection outside the pool, while the rest of the application continues to benefit from pooled access.

7. Application level pooling vs. an external pooler

Many programming languages and frameworks come with built in connection pooling, such as HikariCP in the Java ecosystem or the pools integrated into many PHP and Node.js database drivers. These application side pools work well within a single application process but do not scale across multiple application instances, because each instance keeps its own independent pool.

An external pooler such as PgBouncer complements or replaces application side pooling by serving as a shared resource for all application instances. In practice, teams often combine both layers: a small application side pool per instance reduces the number of simultaneous connection requests hitting PgBouncer, while PgBouncer itself limits the total number of actual database connections across all instances.

In serverless architectures, where short lived functions frequently spin up new processes, application side pooling is practically useless because every function invocation can mean a new process without an existing pool. Here an external pooler is not optional but the only practical solution to protect the database from a flood of short lived connections.

8. Monitoring: pool utilization and wait times

An unmonitored connection pool is a blind spot that usually only becomes visible through timeout errors in production. Important metrics include current pool utilization, meaning the ratio of occupied to available connections, the average wait time for a free connection, and the number of rejected or timed out requests. PgBouncer exposes these numbers through its administrative console, reachable via SHOW POOLS and SHOW STATS.

A pool that is consistently near full capacity is an early warning sign, not yet an acute incident. If the average wait time for a connection measurably increases, it points either to a pool size that is too small or to slow, long running queries that occupy connections disproportionately long. Both causes require different remedies, which is why the distinction matters in monitoring before reflexively increasing pool size.

Alerts should react to rejected connection requests, not only to complete pool exhaustion. A pool that regularly hits its limit briefly without requests visibly failing can be perfectly acceptable. A pool that repeatedly rejects requests, on the other hand, indicates a capacity problem with direct user impact and should be addressed promptly.

9. Connection pooling compared: tools and approaches

Choosing the right pooling approach depends heavily on the database system in use, the application architecture, and the operating model. The table below compares the most common options along practically relevant criteria.

In practice, combining a moderate application side pool with a central PgBouncer in transaction mode is the most common working solution for classic web applications. For workloads with heavy dependence on session state, switching to session pooling is worth the reduced efficiency, because it eliminates error sources that would otherwise be hard to diagnose.

10. Summary

Understanding connection pooling mainly means grasping why every database connection is a scarce resource, not an operation you can repeat freely. Setting up a connection costs time, open connections cost memory on the database server, and without limits applications inevitably grow into the database's connection limits. A well sized pool decouples the number of application requests from the number of actual database connections.

The choice between session, transaction, and statement mode determines how efficiently the pool operates and whether features such as prepared statements or session variables continue to work reliably. External poolers such as PgBouncer are indispensable especially with many application instances, while monitoring pool utilization and wait times shows early on when an adjustment is needed, instead of reacting only after the first production outage.

11. FAQ: Understanding Connection Pooling