Q16 — What is connection pooling and why does it exist?

What does establishing a database connection actually cost?

When your application opens a connection to a database, it's not just a lightweight function call. Several things happen:

A TCP connection is established — a 3-way handshake with the database server. Then authentication: the client sends credentials, the database verifies them against its user table (which may involve hashing), and if valid, grants access. Then session setup: the database allocates memory for the connection's state — a backend process in PostgreSQL (a full OS process, not a thread), or a thread in MySQL — with its own stack, buffer space, and session-level configuration.

In PostgreSQL, each connection spawns a new backend process. Process creation on a Unix system involves forking, copying memory mappings, and initializing the new process's address space. This takes roughly 1–5 milliseconds per connection.

That sounds small. But now consider what a web application does: it handles thousands of HTTP requests per second, and in a naive implementation, each request opens a new database connection, uses it, and closes it. That's thousands of connection establishments per second — each paying the TCP handshake, authentication, and process fork cost. At 3ms per connection, a server handling 500 requests per second is spending 1.5 seconds per second just on connection overhead — an impossible overhead budget.

Why does a database have a maximum connection limit?

Even if connection establishment were free, databases can't handle unlimited concurrent connections.

In PostgreSQL, each connection is a separate OS process. Each process uses memory — shared memory for the buffer pool plus per-process working memory. On a typical PostgreSQL instance, each connection uses roughly 5–10MB of RAM. 1000 connections = 5–10GB of RAM used just for connection overhead, before a single query is processed.

More critically: each active connection is a potential lock holder, a potential blocking query, a potential long-running transaction. The more concurrent connections, the more complex the contention landscape, and the more overhead the database spends managing lock graphs and scheduling.

PostgreSQL's default maximum connection limit is 100. MySQL's is 151. In practice, most production instances are tuned to a few hundred. These limits are not bugs — they reflect the real resource cost of each connection.

A web application with 10 servers, each handling 100 concurrent requests with a naive per-request connection model, would try to establish 1000 simultaneous connections — immediately hitting and exceeding the database's connection limit. Requests would fail with "too many connections."

What is connection pooling?

Connection pooling keeps a set of pre-established database connections alive and reuses them across many application requests.

At startup, the pool establishes N connections to the database (N is the pool size, configured based on the database's capacity). When a request needs the database, it borrows a connection from the pool, uses it, and returns it when done. The connection is not closed — it goes back to the pool for the next request to use.

This way:

Connection establishment cost is paid once at startup, not per request
The database sees a small, stable number of connections (the pool size), never exceeding its limit
Connections are reused thousands of times, amortizing the setup cost to near zero

Where does the pool live?

Application-side pooling (embedded pool): The pool lives inside the application process. Libraries like HikariCP (Java), SQLAlchemy's pool (Python), and pg (Node.js) manage pools inside the application. This is simple and low-latency — getting a connection from the pool is a memory operation within the same process. The downside: each application server has its own pool. 10 app servers × 10 connections per pool = 100 connections total to the database. Manageable, but the pool size must be tuned per server.

External pooler (proxy-side pooling): A separate process sits between the application and the database, managing a shared pool. PgBouncer (PostgreSQL), ProxySQL (MySQL), and Pgpool-II are common examples. All application servers connect to the pooler, and the pooler maintains a smaller pool to the actual database. 10 app servers can each open 100 connections to PgBouncer, and PgBouncer maintains only 20 connections to PostgreSQL. This lets you scale application servers without proportionally scaling database connections.

What are the pooling modes?

Not all pools work the same way:

Session pooling: A client gets a connection for the entire duration of its session. The connection is returned only when the client disconnects. This is effectively no pooling for connection reuse within a session, but it still caps total connections.

Transaction pooling: A client borrows a connection for the duration of one transaction, then returns it. The next request from the same client might get a different physical connection. This allows very high multiplexing — the pool can serve far more clients than it has connections, as long as transactions are short. PgBouncer defaults to this mode.

Statement pooling: A client borrows a connection for one SQL statement, then returns it. Maximum reuse, but breaks any feature that requires state to persist between statements (prepared statements, session variables, advisory locks, temp tables). Rarely used in practice.

Transaction pooling is the sweet spot for most web applications: connections are reused aggressively while still supporting multi-statement transactions.

What happens when all connections in the pool are busy?

A request that needs a database connection must wait in a queue until one becomes available. This is pool exhaustion — a common bottleneck in high-traffic systems.

Pool exhaustion happens when:

The pool is sized too small for the traffic load
Queries are running slow (holding connections longer than expected)
A downstream issue (slow disk, lock contention) causes queries to pile up

The symptoms: requests queue up behind the pool, response latency climbs, timeouts start occurring, the system degrades under exactly the load conditions you'd most want it to handle.

This is why pool sizing matters. Too small: exhaustion under load. Too large: the database is overwhelmed with concurrent connections. The right pool size is roughly: pool size = (database max connections) / (number of app servers), tuned based on observed connection hold times and concurrency.

A common mistake is setting pool size = max connections per app server without accounting for how many app servers are running. Five app servers each with a pool of 100 connections = 500 connections, well above PostgreSQL's default 100 limit.

The mental model

code

Without pooling:
  Each request → open TCP connection → authenticate → allocate DB process
  Cost: 1–5ms per connection, pays every request
  At scale: hundreds of connections/second → DB limit exceeded, connections fail

With pooling:
  Startup → establish N connections once
  Each request → borrow connection (memory op, ~microseconds)
  Request done → return connection to pool
  Cost: paid once, amortized over thousands of requests

Pool types:
  Embedded (HikariCP, pg) → lives in app process, simple, per-server pool
  External (PgBouncer) → shared proxy, apps connect to pooler, pooler talks to DB

Modes:
  Session pooling → one connection per client session
  Transaction pooling → one connection per transaction (best for web apps)
  Statement pooling → one connection per statement (breaks session state)

Pool exhaustion:
  All connections busy → new requests queue → latency rises → timeouts
  Fix: tune pool size, speed up queries, or add DB replicas for read traffic

Sizing rule of thumb:
  pool_size per app server = total_db_connections / number_of_app_servers

Connection pooling exists because database connections are expensive to create and limited in total count, and web applications create far more request-level parallelism than any database can support as individual connections. The pool bridges the gap — hiding connection cost from the request path and presenting the database with a manageable, stable number of clients.