# 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.

```plaintext
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:

```plaintext
Receive request

↓

Open database connection

↓

Run SQL query

↓

Close connection
```

If every request opens and closes a connection:

```plaintext
Request 1 → New Connection
Request 2 → New Connection
Request 3 → New Connection
...
Request 100 → New Connection
```

This creates huge overhead.

Time looks like:

```plaintext
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:

```plaintext
Pool

Connection 1
Connection 2
Connection 3
Connection 4
Connection 5
```

These connections stay open.

When a request arrives:

```plaintext
Request

↓

Borrow a connection

↓

Run query

↓

Return connection
```

The connection is **not closed**. It goes back into the pool.

* * *

## 4\. Visualisation

Without pooling

```plaintext
Request

↓

Create Connection

↓

Query

↓

Destroy Connection
```

With pooling

```plaintext
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:

```plaintext
SELECT * FROM users
```

takes only 3 ms.

Without pooling

```plaintext
Open      25 ms
Query      3 ms
Close      2 ms

Total     30 ms
```

With pooling

```plaintext
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.

```plaintext
Pool

C1
C2
C3
```

Requests arrive.

```plaintext
Request A → C1

Request B → C2

Request C → C3
```

Now another request arrives.

```plaintext
Request D
```

There are no free connections.

The application waits until one becomes available.

```plaintext
Request A finishes

↓

Returns C1

↓

Request D gets C1
```

* * *

## 7\. What if too many requests arrive?

Suppose:

Pool size = 10

But:

```plaintext
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

```plaintext
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:

```plaintext
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:

```python
from sqlalchemy.ext.asyncio import create_async_engine

engine = create_async_engine(
    DATABASE_URL,
    pool_size=10,
    max_overflow=20,
)
```

Here:

*   `pool_size=10` keeps up to 10 persistent connections.
    
*   `max_overflow=20` allows 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:

```plaintext
Request

↓

Ask pool for connection

↓

No connection available

↓

Wait in queue
```

If a connection is returned before the configured timeout:

```plaintext
Connection becomes free

↓

Request gets connection

↓

Executes query
```

If the wait exceeds the timeout:

```plaintext
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:

```plaintext
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:

```plaintext
Minimum connections = 5
Maximum connections = 30
```

At low traffic:

```plaintext
5 open connections
```

Traffic increases:

```plaintext
8

↓

12

↓

18

↓

25
```

Traffic falls:

```plaintext
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:

```plaintext
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:

```plaintext
Request arrives

↓

Free connection?

├── Yes → Use it
└── No
      ↓
Current pool < Max?

├── Yes → Create a new connection
└── No
      ↓
Wait in queue
```

When demand drops:

```plaintext
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:

1.  Measure average and peak request rates.
    
2.  Measure database query latency.
    
3.  Estimate an initial pool size using workload characteristics.
    
4.  Run load tests (e.g. with k6, JMeter, Locust).
    
5.  Monitor metrics such as:
    
    *   Pool utilisation
        
    *   Wait time for connections
        
    *   Database CPU
        
    *   Query latency
        
    *   Timeouts
        
6.  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:

```plaintext
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.

* * *