Connection Pooling — How Applications Handle Thousands of Requests Efficiently

Connection Pooling is a technique where applications maintain and reuse existing connections instead of creating a new connection for every request.

It is commonly used for database connections and HTTP communication between services.

1. Why Do We Need Connection Pooling?

Imagine an ASP.NET Core application receiving thousands of requests. Every request needs to access SQL Server.

Without connection pooling:

sequenceDiagram
Client->>API: Request 1
API->>Database: Create connection
Database-->>API: Connected
API->>Database: Execute query
Database-->>API: Result
API->>Database: Close connection

Client->>API: Request 2
API->>Database: Create new connection
Database-->>API: Connected
API->>Database: Execute query
Database-->>API: Result

Creating connections repeatedly introduces additional latency and consumes server resources.

2. How Connection Pooling Works

A connection pool maintains reusable connections.

flowchart LR
API["ASP.NET Core API"]
Pool["Connection Pool"]
DB["SQL Server"]

API --> Pool
Pool --> DB

When an application needs a connection, it borrows one from the pool. After completing the operation, the connection is returned to the pool for reuse.

3. Connection Pool Lifecycle

flowchart TD
Request["Incoming Request"]
Pool["Connection Pool"]
Available{"Connection Available?"}
Reuse["Reuse Connection"]
Create["Create Connection"]
Execute["Execute Query"]
Return["Return to Pool"]

Request --> Pool
Pool --> Available
Available -->|Yes| Reuse
Available -->|No, Capacity Available| Create
Reuse --> Execute
Create --> Execute
Execute --> Return
Return --> Pool

If all connections are busy and the pool has reached its maximum size, the application may wait for an available connection.

4. Database Connection Pooling

Database connection pooling is especially important for high-traffic applications.

flowchart LR
API1["Request 1"] --> Pool["SQL Connection Pool"]
API2["Request 2"] --> Pool
API3["Request 3"] --> Pool

Pool --> C1["Connection 1"]
Pool --> C2["Connection 2"]
Pool --> C3["Connection 3"]

C1 --> DB["SQL Server"]
C2 --> DB
C3 --> DB

Multiple application requests can reuse connections rather than establishing a new database connection every time.

5. Connection Pooling in ASP.NET Core

ADO.NET SQL Server providers support connection pooling by default.

Example connection string:

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=localhost;Database=AppDB;Trusted_Connection=True;TrustServerCertificate=True;Pooling=True;Min Pool Size=5;Max Pool Size=100;"
  }
}

The application can configure the minimum and maximum number of connections maintained by the pool.

6. Min Pool Size vs Max Pool Size

For example, if the maximum pool size is 100, the application can have up to 100 connections in that pool.

7. What Happens When the Pool Is Full?

Suppose all 100 connections are currently being used.

flowchart TD
Request["New Request"]
Pool["Connection Pool"]
Check{"Available Connection?"}
Execute["Execute Query"]
Wait["Wait for Connection"]
Timeout["Connection Timeout"]

Request --> Pool
Pool --> Check
Check -->|Yes| Execute
Check -->|No| Wait
Wait -->|Connection Released| Execute
Wait -->|Timeout Exceeded| Timeout

If no connection becomes available before the configured timeout, the application receives a connection timeout error.

8. Connection Pool Exhaustion

Connection pool exhaustion happens when all available connections are occupied and new requests cannot obtain a connection.

Common reasons include:

• Connections are not properly disposed.
• Long-running database queries.
• Long-running transactions.
• High concurrent request volume.
• Incorrect pool configuration.

9. How to Prevent Connection Pool Exhaustion

• Dispose connections properly.
• Optimize slow SQL queries.
• Keep transactions short.
• Use asynchronous database operations where appropriate.
• Monitor connection pool usage.
• Configure pool size based on application and database capacity.

10. Connection Pooling with ADO.NET

In C#, connections should be disposed after use so they can be returned to the pool.

await using var connection =
    new SqlConnection(connectionString);

await connection.OpenAsync();

await using var command = new SqlCommand(
    "SELECT * FROM Customers",
    connection);

await using var reader =
    await command.ExecuteReaderAsync();

while (await reader.ReadAsync())
{
    Console.WriteLine(reader["Name"]);
}

Disposing a pooled connection normally returns it to the connection pool rather than physically closing the underlying database connection.

11. Connection Pooling with Entity Framework Core

Entity Framework Core uses the underlying database provider, which can manage database connection pooling.

builder.Services.AddDbContext<AppDbContext>(
    options =>
        options.UseSqlServer(
            builder.Configuration.GetConnectionString(
                "DefaultConnection")));

EF Core DbContext pooling and database connection pooling are different concepts.

12. HTTP Connection Pooling

Connection pooling is not limited to databases.

HTTP clients can also reuse existing network connections when calling external APIs or microservices.

flowchart LR
Order["Order Service"]
Pool["HTTP Connection Pool"]
Payment["Payment Service"]

Order --> Pool
Pool --> Payment

This avoids repeatedly establishing new TCP and TLS connections for every HTTP request when an existing connection can be reused.

13. IHttpClientFactory in ASP.NET Core

ASP.NET Core provides IHttpClientFactory for configuring and creating HttpClient instances.

builder.Services.AddHttpClient(
    "PaymentClient",
    client =>
    {
        client.BaseAddress =
            new Uri("https://payment.example.com");
    });

IHttpClientFactory manages reusable HTTP message handlers, allowing underlying connections to be pooled.

14. Why Not Create HttpClient for Every Request?

Repeatedly creating and disposing HttpClient instances can prevent effective connection reuse and contribute to socket exhaustion.

A better approach is to use IHttpClientFactory or an appropriately configured long-lived HttpClient.

flowchart TD
API["ASP.NET Core API"]
Factory["IHttpClientFactory"]
Handler["Reusable HTTP Handler"]
Pool["Connection Pool"]
Service["External Service"]

API --> Factory
Factory --> Handler
Handler --> Pool
Pool --> Service

15. Connection Pooling in Microservices

Consider an application with multiple services communicating with each other and accessing databases.

flowchart LR
Client --> Gateway["API Gateway"]
Gateway --> Order["Order Service"]

Order --> HTTPPool["HTTP Connection Pool"]
HTTPPool --> Payment["Payment Service"]

Order --> DBPool["Database Connection Pool"]
DBPool --> DB["SQL Server"]

HTTP connection pooling reduces communication overhead between services, while database connection pooling reduces repeated database connection creation.

16. Connection Pooling and Horizontal Scaling

When an application is scaled across multiple instances, each instance may maintain its own database connection pool.

flowchart TD
LB["Load Balancer"]
LB --> API1["API Instance 1"]
LB --> API2["API Instance 2"]
LB --> API3["API Instance 3"]

API1 --> Pool1["Pool 1"]
API2 --> Pool2["Pool 2"]
API3 --> Pool3["Pool 3"]

Pool1 --> DB["SQL Server"]
Pool2 --> DB
Pool3 --> DB

This is an important system design consideration because increasing the number of application instances can also increase the total number of database connections.

For example:

10 Application Instances

Max Pool Size = 100 per instance

Potential Total = 1000 Connections

The database must have sufficient capacity to handle the total connection demand.

17. Important Interview Questions

• What is connection pooling?
• Why do we need connection pooling?
• How does database connection pooling work?
• What is Min Pool Size?
• What is Max Pool Size?
• What happens when the connection pool is full?
• What causes connection pool exhaustion?
• How do you prevent connection leaks?
• What is the difference between DbContext pooling and connection pooling?
• How does IHttpClientFactory manage connections?
• Why should we avoid creating HttpClient for every request?
• How does horizontal scaling affect database connection pools?

Key Takeaways

• Connection pooling reuses existing connections.
• It reduces connection creation overhead and latency.
• Database providers such as SqlClient support connection pooling.
• Connections should be properly disposed after use.
• Pool exhaustion can cause request failures and timeouts.
• IHttpClientFactory supports efficient HTTP connection reuse.
• Each application instance may maintain its own connection pool.
• Pool size must be considered together with database capacity.

Conclusion

Connection pooling improves application performance by reusing existing database and network connections instead of repeatedly creating new ones.

For ASP.NET Core and system design interviews, focus on SQL connection pooling, pool exhaustion, proper disposal, IHttpClientFactory, and connection management during horizontal scaling.