Connection Pooling

Every time an application needs to communicate with a database or another service, creating a new connection has a cost.

Connection pooling reuses existing connections instead of creating a new connection for every request.

Why Do We Need Connection Pooling?

Imagine an API receiving 1,000 requests per second.

Without pooling:

sequenceDiagram
    participant Client
    participant API
    participant DB as Database

    Client->>API: Request 1
    API->>DB: Create Connection
    DB-->>API: Connection
    API->>DB: Query
    DB-->>API: Result
    API->>DB: Close Connection

    Client->>API: Request 2
    API->>DB: Create Connection
    DB-->>API: Connection
    API->>DB: Query
    DB-->>API: Result
    API->>DB: Close Connection

    Note over API,DB: Repeated connection creation

Creating and establishing connections repeatedly adds overhead and can put unnecessary pressure on the database.

With Connection Pooling

The application maintains a pool of reusable connections.

flowchart LR
    A[API Requests] --> B[Connection Pool]

    B --> C[Connection 1]
    B --> D[Connection 2]
    B --> E[Connection 3]
    B --> F[Connection 4]

    C --> G[(Database)]
    D --> G
    E --> G
    F --> G

The application borrows a connection, executes the operation, and returns the connection to the pool.

Complete Request Example

sequenceDiagram
    participant API as ASP.NET Core API
    participant Pool as Connection Pool
    participant DB as SQL Server

    API->>Pool: Request connection
    Pool-->>API: Reusable Connection #12

    API->>DB: Execute SQL
    DB-->>API: Result

    API->>Pool: Return Connection #12
    Note over Pool: Connection remains available

    API->>Pool: Next request
    Pool-->>API: Reuse Connection #12

Connection Pool Lifecycle

flowchart TD
    A[Application Request] --> B[Request Connection]
    B --> C{Available Connection?}

    C -->|Yes| D[Borrow Connection]
    D --> E[Execute Query]
    E --> F[Return Connection]

    C -->|No| G{Pool Limit Reached?}
    G -->|No| H[Create New Connection]
    H --> E

    G -->|Yes| I[Wait for Available Connection]
    I --> D

Pool Size

A connection pool usually has limits such as:

• Minimum Pool Size — connections maintained as a baseline.
• Maximum Pool Size — maximum connections that can be created.
• Connection Timeout — how long a request waits for a connection.
• Connection Lifetime — controls how long connections are retained before replacement in systems that support this setting.

What Happens When the Pool Is Exhausted?

Suppose:

Maximum Pool Size = 100

100 connections = currently in use
101st request = waiting

flowchart TD
    A[Request 101] --> B[Connection Pool]
    B --> C[100 Connections Busy]
    C --> D[Wait]

    D --> E{Connection Returned?}
    E -->|Yes| F[Request Gets Connection]
    E -->|No + Timeout| G[Connection Timeout Error]

If requests hold connections for too long, the pool can become exhausted and API requests may start timing out.

Important Example — Bad Connection Usage

var connection = new SqlConnection(connectionString);

await connection.OpenAsync();

await DoDatabaseWork(connection);

// Connection not disposed properly

If connections are not returned to the pool correctly, they can remain occupied longer than necessary.

Better Pattern

await using var connection =
    new SqlConnection(connectionString);

await connection.OpenAsync();

await DoDatabaseWork(connection);

When the connection is disposed, the underlying pooled connection can generally be returned to the pool for reuse rather than physically closing the database connection.

EF Core and Connection Pooling

With EF Core, the database provider typically handles database connection pooling underneath.

public async Task<Product?> GetProductAsync(int id)
{
    await using var db = new AppDbContext();

    return await db.Products
        .FirstOrDefaultAsync(x => x.Id == id);
}

Creating and disposing the DbContext does not mean the physical database connection must be created from scratch every time. The provider's connection pool can reuse connections.

Connection Pooling vs DbContext Pooling

Connection Pooling in HTTP Clients

Pooling is not limited to databases. HTTP connections can also be reused.

In ASP.NET Core, IHttpClientFactory helps manage HttpClient instances and the underlying HTTP connections efficiently.

flowchart LR
    A[Order Service] --> B[IHttpClientFactory]
    B --> C[HTTP Connection Handler]
    C --> D[Payment Service]

    Note[Reuse connections instead of creating a new TCP connection for every request]

Large-Scale System Example

Suppose we have:

10 API instances
Each instance:
Maximum DB connections = 100

Potential total = 1,000 DB connections

Increasing the pool size on every application instance does not automatically improve performance.

flowchart LR
    A[API Instance 1] --> D[(SQL Server)]
    B[API Instance 2] --> D
    C[API Instance 3] --> D
    E[API Instance 10] --> D

    A1[100 Connections] --> A
    B1[100 Connections] --> B
    C1[100 Connections] --> C
    E1[100 Connections] --> E

If the database can efficiently handle only a smaller number of concurrent operations, excessive connection counts can increase contention and resource consumption.

How Do You Choose Pool Size?

There is no universal number.

Consider:

• Database CPU and memory
• Query execution time
• Number of application instances
• Expected concurrency
• Transaction duration
• Database connection limits
• Observed pool wait time

A useful system-design calculation is:

Total potential connections
=
Number of application instances
×
Maximum pool size per instance

Pool Exhaustion Troubleshooting

If production APIs report connection timeout errors, investigate:

flowchart TD
    A[Connection Pool Exhaustion] --> B[Long Running Queries]
    A --> C[Long Transactions]
    A --> D[Connection Leaks]
    A --> E[Too Many Concurrent Requests]
    A --> F[Pool Too Small]
    A --> G[Database Slow / Unavailable]

    B --> H[Measure Query Duration]
    C --> I[Shorten Transactions]
    D --> J[Dispose Correctly]
    E --> K[Control Concurrency]
    F --> L[Tune Pool Carefully]
    G --> M[Investigate Database]

Common Mistake

A common mistake is increasing the maximum pool size whenever connection timeouts occur.

For example:

100 → 200 → 500 → 1,000 connections

This can make the database situation worse instead of better.

First determine why connections are staying busy.

Interview Scenario

Interviewer: Your ASP.NET Core API suddenly receives heavy traffic and starts throwing database connection timeout errors. What would you check?

Answer:

1. Check connection pool utilization and wait time.
2. Check whether connections are being disposed correctly.
3. Identify long-running queries.
4. Check long-running transactions.
5. Check database CPU, memory, locks, and overall capacity.
6. Check the number of application instances.
7. Calculate the potential total connections across all instances.
8. Review pool configuration.
9. Reduce unnecessary database calls and improve slow queries.
10. Only then consider adjusting pool size.

Connection Pooling in a High-Scale Architecture

flowchart LR
    U[Users] --> LB[Load Balancer]

    LB --> A1[API Instance 1]
    LB --> A2[API Instance 2]
    LB --> A3[API Instance 3]

    A1 --> P1[Connection Pool]
    A2 --> P2[Connection Pool]
    A3 --> P3[Connection Pool]

    P1 --> DB[(Database)]
    P2 --> DB
    P3 --> DB

Each application instance normally has its own pool. Therefore, scaling the application horizontally also increases the potential number of database connections.

Common Interview Questions

• What is connection pooling?
• Why is connection pooling required?
• What happens when the connection pool is exhausted?
• What is maximum pool size?
• How does EF Core use connection pooling?
• Connection pooling vs DbContext pooling?
• Why can increasing pool size make performance worse?
• How would you troubleshoot connection pool exhaustion?
• How does horizontal scaling affect database connections?

Key Takeaways

• Connection pooling reuses database connections.
• It reduces connection establishment overhead.
• Connections should be returned to the pool quickly.
• Long queries and long transactions can exhaust the pool.
• Every application instance can have its own connection pool.
• Maximum connections across instances matter when designing a scalable system.
• Do not blindly increase pool size; first find the bottleneck.

Conclusion

Connection pooling is essential for high-throughput applications because creating a new database connection for every request is expensive and unnecessary.

For system design interviews, remember the key relationship:

More application instances → more potential connections → higher database connection pressure.