Connection pooling in HLD - System Design Exercise

Design: Connection Pooling System

Design focuses on the connection pooling layer between clients and a backend database or service. It excludes the design of the database or client applications themselves.

Functional Requirements

FR1: Manage a pool of reusable connections to a database or external service

FR2: Support concurrent requests efficiently without opening a new connection each time

FR3: Limit the maximum number of open connections to avoid resource exhaustion

FR4: Provide fast allocation and release of connections to clients

FR5: Handle connection failures and retries gracefully

FR6: Allow configuration of pool size, timeout, and idle connection management

Non-Functional Requirements

NFR1: Support up to 1000 concurrent client requests

NFR2: Connection acquisition latency should be under 50ms for 95% of requests

NFR3: Ensure 99.9% availability of the connection pool service

NFR4: Prevent resource leaks by timely closing idle or broken connections

Think Before You Design

Questions to Ask

❓ Question 1

❓ Question 2

❓ Question 3

❓ Question 4

❓ Question 5

❓ Question 6

Key Components

Connection pool manager

Connection factory to create new connections

Queue or data structure to hold available connections

Timeout and retry mechanism

Health check and validation module

Metrics and monitoring

Design Patterns

Object Pool pattern

Lazy initialization

Timeout and retry pattern

Circuit breaker for connection failures

Resource cleanup with idle timeout

Reference Architecture

Client Requests
    |
    v
+-------------------+
| Connection Pool   |
|  - Available Conn |
|  - In-use Conn    |
+-------------------+
    |
    v
+-------------------+
| Connection Factory |
+-------------------+
    |
    v
+-------------------+
| Backend Database   |
+-------------------+

Components

Connection Pool Manager

In-memory data structure (e.g., queue or stack)

Manages available and in-use connections, allocates and releases connections to clients

Connection Factory

Custom code or driver API

Creates new connections when pool needs to grow or replace broken connections

Timeout and Retry Module

Timer and retry logic

Handles connection acquisition timeouts and retries failed connection attempts

Health Check and Validation

Periodic checks and validation queries

Ensures connections are alive and valid before handing out

Metrics and Monitoring

Logging and monitoring tools

Tracks pool usage, connection failures, latency, and resource leaks

Request Flow

1. Client sends a request needing a connection

2. Connection Pool Manager checks for an available connection

3. If available, validates connection health and returns it to client

4. If none available and pool size < max, Connection Factory creates a new connection

5. If pool is at max size, client waits until a connection is released or timeout occurs

6. Client uses connection and returns it to the pool after use

7. Pool manager marks connection as available again

8. Idle connections are closed after configured timeout to free resources

9. Health check module periodically validates and removes broken connections

Database Schema

Not applicable as this is an in-memory connection management system without persistent storage.

Scaling Discussion

Bottlenecks

Connection pool size limits maximum concurrent connections

High contention when many clients wait for connections

Slow connection creation increases latency under load

Resource leaks if connections are not properly released

Health check overhead impacting performance

Solutions

Increase pool size carefully based on resource capacity

Use fair queueing or priority to reduce client wait times

Pre-warm connections during low load to reduce creation latency

Implement strict connection release policies and leak detection

Optimize health checks frequency and perform asynchronously

Interview Tips

Time: Spend 10 minutes understanding requirements and clarifying constraints, 20 minutes designing the architecture and data flow, 10 minutes discussing scaling and trade-offs, 5 minutes summarizing.

Explain the purpose and benefits of connection pooling

Discuss how to manage pool size and connection lifecycle

Describe handling of failures and timeouts

Highlight monitoring and resource cleanup importance

Address scaling challenges and mitigation strategies

Practice

1. What is the main purpose of connection pooling in system design?

easy

A. To reuse existing connections and reduce the overhead of creating new ones

B. To create a new connection for every request to ensure freshness

C. To store user data temporarily for faster access

D. To encrypt all data sent between client and server

Solution

1. Step 1: Understand connection creation cost

   Creating new connections for every request is slow and resource-heavy.

2. Step 2: Role of connection pooling

   Connection pooling keeps connections open and reuses them to save time and resources.

3. Final Answer:

   To reuse existing connections and reduce the overhead of creating new ones -> Option A

4. Quick Check:

   Connection pooling = reuse connections [OK]

Hint: Pooling means reuse connections to save time [OK]

Common Mistakes:

• Thinking pooling creates new connections each time
• Confusing pooling with caching user data
• Assuming pooling encrypts data

2. Which of the following is the correct way to configure a connection pool size in a system design?

easy

A. Set pool size to the maximum integer value to allow unlimited connections

B. Set pool size to zero to disable pooling

C. Set pool size to a fixed number based on expected load, e.g., 50

D. Set pool size to negative number to auto-scale connections

Solution

1. Step 1: Understand pool size meaning

   Pool size controls how many connections can be reused simultaneously.

2. Step 2: Choose appropriate pool size

   Setting a fixed number based on expected load balances resource use and performance.

3. Final Answer:

   Set pool size to a fixed number based on expected load, e.g., 50 -> Option C

4. Quick Check:

   Pool size = fixed number for load [OK]

Hint: Pool size should match expected load, not zero or negative [OK]

Common Mistakes:

• Setting pool size to zero disables pooling
• Using negative or unlimited values causes errors
• Ignoring load when setting pool size

3. Consider this pseudocode for connection pooling:

pool = createPool(size=3)
conn1 = pool.getConnection()
conn2 = pool.getConnection()
conn3 = pool.getConnection()
conn4 = pool.getConnection()

What happens when conn4 is requested?

medium

A. conn4 closes one existing connection and reuses it

B. conn4 creates a new connection ignoring the pool size

C. conn4 returns null immediately because pool is full

D. conn4 waits until one connection is released back to the pool

Solution

1. Step 1: Analyze pool size and connections taken

   The pool size is 3, and 3 connections are already taken (conn1, conn2, conn3).

2. Step 2: Behavior on extra connection request

   Requesting conn4 must wait until a connection is released because pool is full.

3. Final Answer:

   conn4 waits until one connection is released back to the pool -> Option D

4. Quick Check:

   Pool full = wait for release [OK]

Hint: Extra requests wait if pool is full [OK]

Common Mistakes:

• Assuming new connections are created beyond pool size
• Expecting null return immediately
• Thinking pool forcibly closes connections

4. A system uses connection pooling but experiences slow responses under high load. Which of the following is the most likely cause?

medium

A. Connections are not reused and closed immediately

B. Pool size is too small, causing requests to wait for connections

C. Pool size is too large, causing too many open connections

D. Timeout settings are too high, causing connections to stay open too long

Solution

1. Step 1: Identify impact of small pool size

   A small pool size means limited connections, so requests wait longer.

2. Step 2: Understand effect on response time

   Waiting for connections causes slow responses under high load.

3. Final Answer:

   Pool size is too small, causing requests to wait for connections -> Option B

4. Quick Check:

   Small pool = slow responses [OK]

Hint: Small pool size causes waiting delays [OK]

Common Mistakes:

• Blaming large pool size for slowness
• Ignoring connection reuse behavior
• Confusing timeout with pool size

5. You design a connection pool for a web service expected to handle 1000 concurrent users. Which combination of settings best balances performance and resource use?

hard

A. Pool size = 100, Timeout = 30 seconds, Max wait time = 10 seconds

B. Pool size = 1000, Timeout = 5 minutes, No max wait time

C. Pool size = 10, Timeout = 1 hour, Max wait time = 1 second

D. Pool size = 5000, Timeout = 10 seconds, No max wait time

Solution

1. Step 1: Evaluate pool size for concurrency

   Pool size of 100 allows many connections but avoids resource overload compared to 1000 or 5000.

2. Step 2: Assess timeout and wait time

   Timeout of 30 seconds frees unused connections quickly; max wait time of 10 seconds prevents long waits.

3. Step 3: Compare other options

   Too large pool sizes waste resources; too small pool or short wait times cause failures.

4. Final Answer:

   Pool size = 100, Timeout = 30 seconds, Max wait time = 10 seconds -> Option A

5. Quick Check:

   Balanced pool size and timeouts = Pool size = 100, Timeout = 30 seconds, Max wait time = 10 seconds [OK]

Hint: Balance pool size and timeouts for best performance [OK]

Common Mistakes:

• Choosing pool size equal to max users wastes resources
• Ignoring max wait time causes request failures
• Setting too long timeout holds connections unnecessarily