Connection Pool
A database connection pool is a cache of pre-established, ready-to-use database connections maintained by a software library or middleware.
Instead of creating and destroying a brand-new connection for every single database query, the application borrows an existing connection from the pool, executes the query, and immediately returns it. This acts like a taxi stand where cars are already idling at the curb, waiting for the next passenger, rather than calling a taxi from a faraway depot every time you want to go somewhere.
1. What is a database connection?
When your application wants to talk to a database, it first establishes a connection.
Think of it as making a phone call.
Application ─────────► Database
Connection
Creating this connection involves several expensive operations:
Opening a TCP socket
Authentication (username/password)
SSL/TLS handshake (if encrypted)
Allocating memory
Creating a backend process/thread on the database
Initializing session variables
This can take anywhere from a few milliseconds to hundreds of milliseconds depending on the environment.
2. Without connection pooling
Imagine 100 users visit your website simultaneously.
Your API does this:
Receive request
↓
Open database connection
↓
Run SQL query
↓
Close connection
If every request opens and closes a connection:
Request 1 → New Connection
Request 2 → New Connection
Request 3 → New Connection
...
Request 100 → New Connection
This creates huge overhead.
Time looks like:
Open Connection 30 ms
Execute Query 5 ms
Close Connection 5 ms
Total 40 ms
Notice that the query itself only needed 5 ms.
Most time was spent opening and closing the connection.
3. The idea of a connection pool
Instead of creating new connections every time, we create a set of reusable connections.
For example:
Pool
Connection 1
Connection 2
Connection 3
Connection 4
Connection 5
These connections stay open.
When a request arrives:
Request
↓
Borrow a connection
↓
Run query
↓
Return connection
The connection is not closed. It goes back into the pool.
4. Visualisation
Without pooling
Request
↓
Create Connection
↓
Query
↓
Destroy Connection
With pooling
Pool
Conn A
Conn B
Conn C
Conn D
Conn E
↓
Request borrows Conn C
↓
Query
↓
Return Conn C
The same connection serves thousands of requests throughout the application's lifetime.
5. Why is it much faster?
Suppose opening a connection takes 25 ms.
Query execution:
SELECT * FROM users
takes only 3 ms.
Without pooling
Open 25 ms
Query 3 ms
Close 2 ms
Total 30 ms
With pooling
Borrow 0.1 ms
Query 3 ms
Return 0.1 ms
Total 3.2 ms
The difference is significant.
6. How requests share connections
Suppose your pool size is 3.
Pool
C1
C2
C3
Requests arrive.
Request A → C1
Request B → C2
Request C → C3
Now another request arrives.
Request D
There are no free connections.
The application waits until one becomes available.
Request A finishes
↓
Returns C1
↓
Request D gets C1
7. What if too many requests arrive?
Suppose:
Pool size = 10
But:
500 concurrent requests
Only ten can use the database simultaneously.
The other 490 wait in a queue.
This prevents overwhelming the database.
8. Why not create 1000 connections?
Databases have limits.
Each connection consumes resources such as:
Memory
CPU
Backend processes or threads
Network sockets
For PostgreSQL, each client connection is served by a separate backend process, so thousands of idle connections can waste significant memory.
Connection pooling keeps the number of active connections under control.
9. Connection pool lifecycle
Application Starts
↓
Create Pool
↓
Open N connections
↓
Application Runs
↓
Borrow
↓
Query
↓
Return
↓
Borrow Again
↓
Application Stops
↓
Close all connections
Connections are created once and reused many times.
10. Mathematical model
Let:
(T_c) = connection creation time
(T_q) = query execution time
(T_r) = time to borrow/return a pooled connection
Without pooling:
$$T_{\text{request}} = T_c + T_q$$
With pooling:
$$T_{\text{request}} = T_r + T_q$$
Since:
$$T_r \ll T_c$$
the overall request latency is much lower.
11. Common pool settings
A connection pool usually has configurable parameters:
| Setting | Meaning |
|---|---|
| Min Size | Minimum number of connections kept open |
| Max Size | Maximum number of connections allowed |
| Idle Timeout | Close connections that stay unused too long |
| Connection Timeout | Maximum time to wait for a free connection |
| Max Lifetime | Recycle connections after a certain age to avoid stale sessions |
Example:
Min = 5
Max = 20
Idle Timeout = 5 min
Connection Timeout = 30 sec
12. Example in FastAPI
Most Python applications don't create connections directly. They use a pool provided by the database driver.
For example:
from sqlalchemy.ext.asyncio import create_async_engine
engine = create_async_engine(
DATABASE_URL,
pool_size=10,
max_overflow=20,
)
Here:
pool_size=10keeps up to 10 persistent connections.max_overflow=20allows temporary extra connections during traffic spikes, which are discarded when no longer needed.
Each request borrows a connection from this pool and returns it when finished.
Why connection pooling matters in enterprise systems
In high-throughput systems like API gateways, authentication services, AI inference platforms, or microservices, connection pooling:
Reduces request latency.
Limits database resource usage.
Improves throughput by reusing expensive connections.
Prevents connection storms that can overwhelm the database during traffic spikes.
Provides predictable performance under load.
For someone designing enterprise-grade systems, understanding connection pooling is essential because it directly affects scalability, reliability, and latency.
How do you find the best pool size?
A common misconception is:
"More connections = more performance."
This is often false. Beyond a certain point, adding connections increases contention, context switching, lock waits, and memory usage, reducing throughput.
A simple model
Let:
(C) = connection pool size
(T_q) = average query time (seconds)
(R) = requests per second that require the database
Using Little's Law:
$$C \approx R \times T_q$$
Example:
200 database requests/second
Average query time = 50 ms = 0.05 s
$$C = 200 \times 0.05 = 10$$
So around 10 active connections are needed on average. In practice, you add some headroom (for example, 12–15) to handle bursts.
1. What happens when no connection is available?
Suppose your pool size is 10.
All 10 connections are busy.
A new request arrives.
The typical sequence is:
Request
↓
Ask pool for connection
↓
No connection available
↓
Wait in queue
If a connection is returned before the configured timeout:
Connection becomes free
↓
Request gets connection
↓
Executes query
If the wait exceeds the timeout:
Timeout
↓
Error returned
Examples include:
"Connection pool exhausted"
"Timed out waiting for a connection"
The application can then return an error (often HTTP 503 or 500) or retry, depending on the design.
2. Why waiting is better than opening unlimited connections
Imagine your database can comfortably process 40 concurrent queries.
Now 500 requests arrive.
If every request opened a new connection:
500 active database sessions
The database would spend much of its time scheduling and managing sessions rather than executing queries.
A bounded pool intentionally limits concurrency, protecting the database.
3. What are adaptive (dynamic) connections?
Some pools can grow and shrink automatically.
For example:
Minimum connections = 5
Maximum connections = 30
At low traffic:
5 open connections
Traffic increases:
8
↓
12
↓
18
↓
25
Traffic falls:
25
↓
15
↓
8
↓
5
This reduces resource usage during quiet periods while allowing higher throughput during busy periods.
4. Should a pool grow indefinitely?
No.
A typical design is:
Min = 5
Max = 30
The pool can expand only within those limits.
Without a maximum, a traffic spike could create hundreds of connections and overwhelm the database.
5. How do adaptive pools decide to grow?
A simplified algorithm is:
Request arrives
↓
Free connection?
├── Yes → Use it
└── No
↓
Current pool < Max?
├── Yes → Create a new connection
└── No
↓
Wait in queue
When demand drops:
Idle connection
↓
Idle longer than timeout?
↓
Yes
↓
Close it
Real implementations also consider connection creation rate, failure handling, and cooldown periods to avoid repeatedly opening and closing connections.
6. How do companies choose the pool size?
They usually:
Measure average and peak request rates.
Measure database query latency.
Estimate an initial pool size using workload characteristics.
Run load tests (e.g. with k6, JMeter, Locust).
Monitor metrics such as:
Pool utilisation
Wait time for connections
Database CPU
Query latency
Timeouts
Adjust the configuration based on observed bottlenecks.
The goal is to keep the pool busy without creating long waits or overloading the database.
7. What about very large systems?
Large organisations often add another layer: a database connection pooler such as PgBouncer for PostgreSQL.
The architecture becomes:
Microservice A ─┐
Microservice B ─┼──► PgBouncer ───► PostgreSQL
Microservice C ─┘
Each service has its own application-level pool, but PgBouncer multiplexes many client connections onto a smaller number of database connections. This allows thousands of application connections while the database maintains only a few hundred actual sessions, improving scalability.