Connection pooling: reuse, sizing and waiting
See how a database connection pool reuses connections. Change a twelve-query example to compare pool size, database capacity and where queries wait.
A connection pool keeps database connections open so requests can borrow and return them. Its size limits how much work the application admits at once; increasing it can help an undersized pool, but does not create database capacity.
Suppose twelve queries need a connection and the database can run four at once. A pool of two leaves some capacity unused. A pool of four admits four and makes eight wait in the application. A pool of twelve admits everyone, but eight still wait inside the database.
A request that waits for a connection and a query that waits inside the database can produce the same visible symptom: a slow response. Increasing the pool changes how many requests enter the database. To choose a useful setting, first find which wait is consuming the time.
What a pool reuses#
Without reuse, a client establishes a connection, authenticates, runs its work and disconnects. A pool instead lends an existing connection to the caller and accepts it back afterwards. Return it on both success and failure; otherwise a leaked connection stays unavailable to other callers. The pool may replace broken or expired connections, so reuse does not mean that the same connection lives forever.
Connection setup depends on the engine and transport. PostgreSQL 18 creates a backend process for an accepted connection. MySQL 8.4 documents its connection-thread handling. These mechanisms explain why maintaining connections has a cost; they do not establish a timing ranking between products. A local socket also avoids the TCP handshake of a network connection.
Change the pool size#
Start with twelve equal queries and four execution slots. With two connections, two queries finish each round: six rounds complete the batch. Four connections finish it in three rounds. Twelve connections still finish it in three rounds, but the extra queries wait inside the database. Change the inputs below, then step through the same batch.
Change the pool; see where twelve queries wait
Illustrative example: twelve queries arrive together. Each running query finishes in one round. Start with database capacity for four queries per round; change it below. Predict whether twelve connections finish the batch sooner than four.
- Running in database
- 4
- Borrowed connection, waiting in database
- 0
- Waiting for a connection in application
- 8
- Completed
- 0 of 12
Round 0: four queries run and eight wait in the application. With four connections and four execution slots, all twelve finish after three rounds. Twelve connections would move eight waits into the database without finishing sooner.
Fixed batch, equal queries, one connection per query, immediate reuse and no new arrivals. The model excludes connection setup, locks, scheduling overhead and cancellation. Rounds are invented units, not latency measurements or recommended settings. Real contention can make excess concurrency slower; this model only shows the capacity limit.
The example has an explicit capacity limit. A real database has no single fixed count of execution slots: CPU work, storage waits, locks, memory and the query mix change what concurrency is useful. Excess work may add contention and reduce completed work, or merely stop helping. It is therefore inaccurate to promise that two hundred connections are always slower than twenty.
HikariCP’s pool-sizing guide offers a hardware-based starting formula and says to test around it. That is a workload-specific starting point, not a target for every database, SSD, cloud service or application. Latency and throughput explain why a setting that completes more work can still make an individual request wait longer.
Diagnose the wait before increasing the pool#
Measure connection-acquisition wait separately from query duration. If the pool is fully borrowed while the database has room to do more work, a modest increase may help. If queries are blocked on locks or the server is already busy, admitting more work may extend the same wait. A connection held while the application performs unrelated network work also makes the pool look too small. Shorten that hold where transaction correctness allows it.
Count every pool. In an illustrative deployment, six application instances with a maximum of twenty connections each can request up to 120 connections together. They may open fewer, but the configured upper bound still matters. PostgreSQL 18’s connection settings describe the server limit, reserved access and resources affected by increasing it. Leave room for other services and operational access.
Keep deadlines distinct. An acquisition timeout limits waiting to borrow a connection. A request deadline limits the caller’s patience; it does not by itself prove the database stopped. PostgreSQL’s statement timeout bounds statement execution separately. Configure and test driver cancellation and transaction cleanup as well. A timed-out write may need the idempotency checks used for safe retries.
Application pool vs external pooler#
An application pool manages connections for its callers. An external pooler sits between clients and the database; it can give many clients access to a smaller set of backend connections. This adds another admission limit to measure.
PgBouncer distinguishes session and transaction pooling. Session mode assigns a backend for the client session; transaction mode releases it after the transaction. In transaction mode a later transaction may use a different backend, so session-dependent features require a compatibility check. A pooler is not a transparent substitute for every session contract.
IF YOU REMEMBER ONE THING
Reuse connections, bound admitted work and measure where time is spent. Raise the pool when the evidence shows unused capacity; diagnose the query, lock or connection hold when it does not.
Questions people also ask
5 QUESTIONSHow many connections should my pool have?
Test the expected workload; there is no portable number. Compare connection-acquisition wait, query duration, completed work and database pressure. Count pools across every application instance and leave capacity for other clients and operations.
Does a bigger connection pool always make queries slower?
No. A small pool can leave database capacity unused, so increasing it may help. Once the workload saturates a shared resource, extra admitted queries can add contention or simply wait elsewhere. Measure both throughput and latency rather than assuming that more connections help or hurt.
What happens when the pool is exhausted?
All available connections are borrowed. A request may wait for one, fail after an acquisition timeout, or be rejected, depending on the pool configuration. That acquisition deadline is separate from the database query deadline.
Is max_connections the same as pool size?
No. PostgreSQL max_connections is the server's concurrent-connection limit. An application pool has its own limit, and many instances may each create a pool. An external pooler can also separate client connections from database connections.
Does pooling fix a slow query?
Pooling avoids repeatedly establishing connections. It does not change the query's plan or remove a lock wait. Changing admitted concurrency can affect contention, so measure query time separately from time spent acquiring a connection.