Why Your Database Needs Connection Pooling

The short answer

Quick answer: Opening a database connection is slow and each open connection costs the database memory. A connection pool keeps a small set of connections open and lends them out: your code borrows one, runs its queries, and returns it for the next request to reuse. This removes the set-up cost from every request, caps the number of connections so the database is not overwhelmed, and lets hundreds of application threads share a few dozen connections. Without pooling, busy applications are slower and hit "too many connections" errors.

Why opening a connection is expensive

Creating a connection involves several steps before any query can run:

1. A TCP handshake between application and database.
2. A TLS handshake if the connection is encrypted.
3. Authentication, often with a deliberately slow password hash.
4. Server-side set-up. PostgreSQL starts a whole new operating system process for each connection. MySQL starts a thread.
5. Session initialisation: settings, caches, and so on.

Together these take from a few milliseconds to tens of milliseconds, often longer than the query itself. Doing that on every web request wastes most of the request time.

Connections are also costly to keep. In PostgreSQL each one is a process using several megabytes of memory, and thousands of them cause heavy context switching. That is why databases set a max_connections limit, and why exceeding it gives the familiar "sorry, too many clients already" error.

How a pool works

1. At start-up, the pool opens a few connections.
2. When code needs the database, it borrows a connection.
3. It runs its queries.
4. It returns the connection to the pool (in most libraries, by "closing" it, which really just releases it).
5. If all connections are busy, the next borrower waits in a queue, up to a timeout.

Typical settings:

Sizing the pool: smaller than you think

The instinct is to make the pool large. That usually makes things worse.

A database server has a fixed number of CPU cores and a limited rate of disk I/O. If 200 queries run at once on 8 cores, they do not run faster. They compete, switching constantly and fighting over locks and caches. Total throughput goes down.

The HikariCP project's page About Pool Sizing makes this argument with benchmarks and suggests a starting point of roughly:

connections = (CPU cores × 2) + effective number of disks

For many systems that means a pool of perhaps 10 to 30 connections serving thousands of users. Requests queue briefly in the application, which is cheap, instead of piling into the database, which is not.

One more calculation matters: total connections across all application instances. A pool of 20 on each of 50 app servers is 1,000 connections. That total must stay under the database's limit, with room for admin and maintenance connections.

Where the pool lives

In the application

Most database libraries include a pool: HikariCP for Java, SQLAlchemy's pool for Python, database/sql in Go, pg.Pool for Node.js, and the pools built into Rails and Django. This is the simplest option and works well with a modest number of long-lived application processes.

An external pooler

When there are many application processes, or they come and go, a separate pooling proxy sits between applications and the database. Clients open cheap connections to the proxy, and the proxy multiplexes them over a small number of real database connections. PgBouncer is the classic example for PostgreSQL; ProxySQL does the same for MySQL, and cloud providers offer managed equivalents.

PgBouncer has three pooling modes, described on its features page:

Transaction mode is the most popular, because a connection is only tied up while a transaction is actually running. The catch is that features relying on session state, such as session variables, advisory locks and some uses of prepared statements, need care.

Serverless and autoscaling make it worse

Serverless functions and aggressively autoscaled containers can create hundreds of instances in seconds, each opening its own connections. A traffic spike becomes a connection storm that knocks the database over.

The fixes:

• Put an external pooler or a provider's database proxy in front of the database.
• Keep the per-instance pool very small, often 1.
• Use databases or drivers that offer an HTTP-based data API for short-lived functions.

More on that environment in what serverless really means.

Common pitfalls

• Connection leaks. Code borrows a connection and never returns it, usually because an error path skips the release. The pool slowly empties and requests hang. Always release in a finally block, or use a with / try-with-resources construct.
• Holding a connection while waiting on something slow. Calling an external API in the middle of a transaction ties up a connection for the whole wait. Do the slow work first, then open the transaction.
• Pool exhaustion from slow queries. One slow query pattern can occupy every connection. Fix the query; do not just enlarge the pool. See how query planners work and the N+1 query problem.
• Stale connections. Firewalls and load balancers silently drop idle connections. Set a maximum lifetime shorter than the network's idle timeout, and enable validation.
• Leftover session state. A returned connection may still have a changed setting or an open transaction. Good pools reset connections before reuse.
• No timeout. Without an acquisition timeout, a saturated pool makes requests hang forever instead of failing fast.

What to monitor

• Active versus idle connections in the pool.
• How many callers are waiting, and for how long.
• Acquisition timeouts.
• The database's own connection count against its limit.

A pool where callers regularly wait means the database is saturated or queries are too slow. It is a signal to investigate, not necessarily to raise the limit.

Frequently asked questions

What is a connection pool?

A cache of open database connections that application code borrows and returns, so connections are reused instead of being created for every request.

How big should my connection pool be?

Start small, around twice the database's CPU core count, and measure. Bigger pools often reduce throughput.

What causes "too many connections"?

The total connections opened by all clients exceeded the database's limit, commonly from many app instances each with a large pool, leaked connections, or a serverless traffic spike.

Do I need PgBouncer if my framework already has a pool?

Not for a few long-lived app servers. It becomes valuable with many processes, serverless functions, or when total connections approach the database limit.

Conclusion

Connection pooling is a small piece of infrastructure with a large effect: it removes connection set-up from every request and protects the database from being flooded. Keep pools small, return connections promptly, add an external pooler when instances multiply, and treat a waiting queue as a clue about slow queries.

Related articles

• How Query Planners Decide the Fastest Way to Run Your SQL
• What Is the N+1 Query Problem and How to Fix It
• TCP vs UDP: Why the Internet Needs Both
• What "Serverless" Really Means (Spoiler: There Are Servers)

Sources and further reading

• PgBouncer features
• About Pool Sizing (HikariCP wiki)