Introduction

An application needs a database connection before it can execute SQL. Creating a new connection can involve network setup, transport security, authentication, database-session creation, and connection-level initialization.

Opening and destroying a physical connection for every application request repeats this work and can place unnecessary pressure on the application and database.

A connection pool maintains a controlled collection of reusable database connections. Application code borrows a connection, performs a short unit of database work, and returns the connection so another operation can reuse it.

In your System Design curriculum, Connection Pools is Topic 5.9 and completes the Relational Data Modeling & SQL module. It follows query plans and is followed by the Storage, Files, Objects & Search Basics module.

Prerequisites

What Is a Database Connection?

A database connection is a communication session between an application process and a database server.

Establishing a connection can involve:

• Resolving the database endpoint
• Opening a network socket
• Negotiating transport security
• Authenticating the application identity
• Selecting a database
• Creating server-side session state
• Applying connection-level settings
• Preparing driver-specific state

Application
    |
    v
Resolve database endpoint
    |
    v
Open network connection
    |
    v
Negotiate secure transport
    |
    v
Authenticate
    |
    v
Initialize database session
    |
    v
Connection ready

The exact process depends on the database, driver, authentication method, security configuration, and network architecture.

What Is a Connection Pool?

A connection pool manages a collection of reusable database connections.

Application requests
        |
        v
Connection pool
        |
        +-- Idle connection 1
        +-- Idle connection 2
        +-- Active connection 3
        +-- Active connection 4
        |
        v
Database server

Application code requests a logical connection from the pool instead of creating a new physical database connection for every operation.

Connection Reuse

When application code closes a pooled connection, the underlying physical connection can remain open. The logical close operation returns it to the pool.

Without pooling:

Request 1:
Open -> Query -> Close physical connection

Request 2:
Open -> Query -> Close physical connection

Request 3:
Open -> Query -> Close physical connection


With pooling:

Create physical connection

Request 1:
Borrow -> Query -> Return

Request 2:
Borrow -> Query -> Return

Request 3:
Borrow -> Query -> Return

Reuse avoids repeating connection establishment for every database operation.

Logical vs Physical Connections

Closing the logical handle should return the connection to the pool. Physically closing the underlying session normally occurs only when the pool evicts, expires, invalidates, or shuts down the connection.

Pool Lifecycle

Benefits of Connection Pooling

A correctly configured pool can:

• Reuse established database sessions
• Reduce repeated connection setup
• Bound per-instance database concurrency
• Reduce connection storms
• Improve request latency for short operations
• Provide connection-acquisition timeouts
• Centralize connection validation and retirement
• Provide pool-level observability

Important Pool Settings

Exact setting names and behaviour depend on the pool implementation and database driver.

Maximum Pool Size

Maximum pool size defines the highest number of database connections one pool can maintain.

More database connections
always mean more throughput.

Increase concurrency only while:

- Database throughput improves
- Latency remains acceptable
- CPU and memory remain healthy
- Lock contention remains controlled
- Query queues remain bounded

An oversized pool can move request queuing into the database, increase concurrency contention, and consume database resources without improving useful throughput.

Total Connection Budget

Per-instance pool size must be evaluated across every application instance that connects to the database.

A simplified maximum connection estimate is:

\[ TotalPotentialConnections = ApplicationInstances \times MaximumPoolSizePerInstance \]

Example

Application instances:

8


Maximum pool size per instance:

20


Potential application connections:

8 x 20 = 160

The complete capacity plan should also include administrative access, migrations, background jobs, monitoring, reporting, failover capacity, and other applications using the same database.

Concurrency Is Not Request Volume

A service can process many requests over time without requiring one database connection for every request simultaneously.

A simplified concurrency approximation is:

\[ ConcurrentDatabaseWork \approx RequestRate \times AverageDatabaseHoldTime \]

For example, lowering connection hold time allows the same pool to serve more operations over time.

Connection-acquisition Timeout

When every connection is active, another operation can wait for a connection to be returned.

Request asks for connection
        |
        +--> Idle connection available:
        |       borrow immediately
        |
        +--> Pool below maximum:
        |       create connection
        |
        +--> Pool at maximum:
                wait in bounded queue
                |
                +--> Connection returned:
                |       borrow connection
                |
                +--> Timeout:
                        fail request safely

An acquisition timeout prevents requests from waiting indefinitely. It should be shorter than the complete request deadline and should produce a controlled failure rather than an unbounded queue.

Backpressure

When the pool is full, waiting requests experience backpressure instead of creating unlimited database sessions.

Incoming traffic
      |
      v
Application workers
      |
      v
Bounded connection pool
      |
      v
Database capacity

A bounded pool protects the database, but the application also needs bounded request queues, timeouts, overload handling, and retry controls.

Pool Exhaustion

Pool exhaustion occurs when all available connections are borrowed and new operations cannot acquire one within the configured timeout.

Common causes include:

• Connections not returned
• Long-running queries
• Long-running transactions
• Lock waits and deadlocks
• Remote calls while holding connections
• Unbounded request concurrency
• Pool size smaller than validated workload needs
• Database slowdown
• Connection validation delays
• Application thread starvation

Database becomes slow
      |
      v
Connections remain active longer
      |
      v
Fewer connections return to pool
      |
      v
Waiting requests increase
      |
      v
Acquisition timeouts increase
      |
      v
Retries can add more traffic

Connection Leaks

A connection leak occurs when application code borrows a connection and fails to return it.

Borrow connection
      |
      v
Execute database work
      |
      v
Exception occurs
      |
      v
Connection is not closed
      |
      v
Connection remains unavailable
      |
      v
Pool eventually becomes exhausted

Unsafe PHP Pattern

<?php

declare(strict_types=1);

$connection =
    $pool->borrow();

$result =
    executeDatabaseWork(
        $connection
    );

// An exception before this line
// can prevent the return.

$pool->release(
    $connection
);

Safe Cleanup Pattern

<?php

declare(strict_types=1);

$connection =
    $pool->borrow();

try {
    $result =
        executeDatabaseWork(
            $connection
        );
} finally {
    $pool->release(
        $connection
    );
}

Framework-provided connection handles can use different release semantics. Follow the library's documented lifecycle rather than implementing pooling manually.

Transaction Cleanup before Return

A borrowed connection must not return to the pool with an unintended open transaction.

Borrower A:

Begins transaction.
Updates row.
Returns connection without commit or rollback.


Borrower B:

Receives same physical connection.


Risk:

Borrower B inherits transaction state
or unintentionally commits or rolls back
Borrower A's work.

Safe PHP Transaction Handling

<?php

declare(strict_types=1);

function createEnrollment(
    PDO $connection,
    array $data
): void {
    $connection->beginTransaction();

    try {
        insertEnrollment(
            $connection,
            $data
        );

        updateCourseCapacity(
            $connection,
            $data['courseId']
        );

        $connection->commit();
    } catch (Throwable $exception) {
        if ($connection->inTransaction()) {
            $connection->rollBack();
        }

        throw $exception;
    }
}

Session-state Leakage

Physical database sessions can retain settings or objects between logical borrowers.

Potential session state includes:

• Open transactions
• Isolation level
• Selected schema or database
• Time zone
• Language and date settings
• Temporary tables
• Session variables
• Prepared statements
• Advisory locks
• Role or impersonation state

Borrower A:

Changes session time zone.
Creates temporary table.
Sets elevated role.
Returns connection.


Borrower B:

Receives same physical session.


Risk:

Borrower B inherits unexpected state.

The pool, driver, framework, and application must define how reusable connections are returned to a known state.

Security-context Reset

A pooled connection must not preserve tenant-specific or elevated security context for the next borrower.

Before return:

- End transaction
- Release application locks
- Reset role or impersonation
- Clear tenant-specific session values
- Remove temporary state
- Restore expected isolation and session settings

Tenant authorization should remain enforced through trusted application and database controls rather than depending only on mutable session state.

Connection Validation

An idle connection can become unusable because of network interruption, database restart, credential changes, failover, or infrastructure timeouts.

A pool can validate connections:

• When a connection is borrowed
• When a connection is returned
• Periodically while idle
• After an operation reports a connection-level failure

SELECT 1;

A validation query is one possible mechanism. Drivers can also provide protocol-level connection checks. Validate according to the selected database and pool documentation.

Idle Timeout

Idle timeout determines how long an unnecessary idle connection remains in the pool before it becomes eligible for closure.

Connection becomes idle
        |
        v
Idle timer starts
        |
        +--> Borrowed again:
        |       timer resets
        |
        +--> Idle timeout reached:
                close according to pool policy

A short idle timeout can cause frequent connection recreation. A very long timeout can retain unnecessary database sessions during low traffic.

Maximum Connection Lifetime

Maximum lifetime retires a physical connection after it has existed for a configured period.

This can help the pool:

• Replace long-lived sessions
• Adapt to infrastructure rotation
• Retire connections before an external forced timeout
• Distribute connection renewal over time

Connection retirement should be staggered where possible so many application instances do not reconnect simultaneously.

Connection Storms

A connection storm occurs when many application instances attempt to create database connections simultaneously.

Database restarts
      |
      v
All existing connections fail
      |
      v
Every application instance reconnects
      |
      v
Many pools create connections simultaneously
      |
      v
Database recovery load increases

Controls can include:

• Bounded connection-creation concurrency
• Exponential backoff
• Randomized jitter
• Application readiness checks
• Gradual instance startup
• Pool prewarming only when justified
• Database-side admission controls

Application-level Pools

Application Instance A
      |
      +-- Pool A
             |
             v
          Database


Application Instance B
      |
      +-- Pool B
             |
             v
          Database

Each application process or instance can maintain an independent pool. Therefore, total database connections increase as application instances are added.

External Poolers and Proxies

Some architectures place an external connection pooler or database proxy between applications and the database.

Application instances
        |
        v
External connection pooler
or database proxy
        |
        v
Database server

An external layer can consolidate or multiplex client connections according to its supported mode. Transaction, session, prepared-statement, temporary object, and session-state behaviour must be verified for the selected technology.

Pooling Modes

Actual capabilities depend on the selected pooler and database protocol.

Prepared Statements and Pooling

Prepared statements can belong to a physical connection or database session. An application borrowing a different physical connection later might not find the same prepared statement.

Borrower prepares statement
on Connection A
        |
        v
Connection A returns to pool
        |
        v
Next operation receives Connection B
        |
        v
Prepared statement from Connection A
is not necessarily available

Driver-level statement caching, server-side preparation, and external proxy compatibility must be verified for the chosen stack.

Transactions Require Connection Affinity

Every statement in one local transaction must execute through the same database connection.

Connection A:

BEGIN
Insert order


Connection B:

Insert order lines


Connection A:

ROLLBACK


Problem:

Work on Connection B is not automatically
part of Connection A's transaction.

Borrow Connection A

BEGIN
Insert order
Insert order lines
Reserve inventory
Insert outbox event
COMMIT

Return Connection A

PHP Connection Scope

<?php

declare(strict_types=1);

function createOrder(
    PDO $connection,
    array $order
): void {
    $connection->beginTransaction();

    try {
        insertOrder(
            $connection,
            $order
        );

        insertOrderLines(
            $connection,
            $order['lines']
        );

        reserveInventory(
            $connection,
            $order['lines']
        );

        insertOutboxEvent(
            $connection,
            $order
        );

        $connection->commit();
    } catch (Throwable $exception) {
        if ($connection->inTransaction()) {
            $connection->rollBack();
        }

        throw $exception;
    }
}

Every repository method receives the same connection, ensuring that all SQL statements participate in the same local transaction.

PHP Persistent Connections

Some PHP drivers provide persistent-connection settings that can reuse database connections across request processing within the supported runtime model.

<?php

declare(strict_types=1);

$pdo =
    new PDO(
        $dsn,
        $username,
        $password,
        [
            PDO::ATTR_ERRMODE =>
                PDO::ERRMODE_EXCEPTION,

            PDO::ATTR_DEFAULT_FETCH_MODE =>
                PDO::FETCH_ASSOC,

            PDO::ATTR_PERSISTENT =>
                true
        ]
    );

Persistent connections are not automatically equivalent to a fully managed, configurable application connection pool. Behaviour depends on the PHP runtime, worker model, driver, database, and deployment architecture.

Illustrative Pool Configuration

The following configuration is conceptual. Property names differ between frameworks and pool implementations.

database:
  pool:
    minimum-size: 2
    maximum-size: 20
    acquisition-timeout: 3s
    validation-timeout: 1s
    idle-timeout: 5m
    maximum-lifetime: 30m
    leak-detection-threshold: 10s

Do not copy example values into production without measuring the complete application and database capacity.

Pool-sizing Inputs

Pool sizing should consider:

• Database connection capacity
• Number of application instances
• Number of other database clients
• Request concurrency
• Database time per request
• Transaction duration
• Query latency distribution
• Lock and contention behaviour
• Background-job concurrency
• Failover and scaling scenarios
• Required administrative reserve

Connection-budget Template

Little's Law Intuition

Connection demand can be reasoned about using the relationship between throughput, database hold time, and concurrent work:

\[ Concurrency = Throughput \times AverageDatabaseTime \]

This is a starting point for reasoning, not a complete pool-sizing formula. Real systems also need headroom for variation, high-percentile latency, transactions, background work, and failover conditions.

Why Query Performance Affects the Pool

Query becomes slower
      |
      v
Connection held longer
      |
      v
Active connection count increases
      |
      v
Idle connections decrease
      |
      v
Waiting requests increase
      |
      v
Acquisition timeout risk increases

Increasing the pool size can temporarily hide slow queries while increasing database concurrency and contention. Inspect query plans and lock waits before treating pool growth as the solution.

Long Transactions and Pools

A transaction keeps its connection borrowed until commit or rollback.

Borrow connection
Begin transaction
Execute query
Call remote API
Wait for response
Generate document
Commit
Return connection

Perform safe external preparation

Borrow connection
Begin transaction
Reread current state
Apply database changes
Commit
Return connection

Process follow-up work separately

Retries and Pool Exhaustion

Immediate retries can worsen pool exhaustion.

Pool acquisition fails
      |
      v
Client retries immediately
      |
      v
Waiting demand increases
      |
      v
Database remains slow
      |
      v
More requests time out

Retry policies should distinguish:

• Connection-acquisition timeout
• Connection-establishment failure
• Transient network interruption
• Query timeout
• Deadlock or serialization conflict
• Permanent authentication or configuration failure

Retry only documented transient failures using bounded attempts, backoff, jitter, deadlines, and idempotency where required.

Readiness and Health Checks

A service can distinguish between process health and readiness to serve database-dependent requests.

Liveness:

Is the application process functioning?


Readiness:

Can the application currently serve
its required workload?


Database dependency check:

Can a connection be acquired and
a bounded validation operation complete?

Health checks should be lightweight and bounded. Aggressive checks from many instances can add load during an outage.

Graceful Shutdown

Application shutdown begins
        |
        v
Stop accepting new work
        |
        v
Allow bounded completion of active requests
        |
        v
Complete or roll back transactions
        |
        v
Return borrowed connections
        |
        v
Close the pool
        |
        v
Close physical database sessions

Abrupt termination can leave transactions to be recovered by the database and can cause all replacement instances to reconnect simultaneously.

Credentials and Pooling

Physical pooled connections are authenticated using database credentials or workload identity.

Review:

• Least-privilege database permissions
• Secure credential storage
• Credential rotation
• Connection lifetime during rotation
• Encrypted transport
• Failure behaviour after expiration
• Separate identities for different application responsibilities

Do not place credentials in source code, logs, pool metrics, connection errors, or diagnostic screenshots.

Connection-pool Metrics

Important metrics include:

• Total physical connections
• Active or borrowed connections
• Idle connections
• Waiting request count
• Connection-acquisition duration
• Acquisition-timeout count
• Connection-creation count
• Connection-creation failures
• Connection usage duration
• Maximum observed pool utilization
• Connection validation failures
• Connection eviction count
• Potential leak detections

Interpreting Pool Metrics

These observations are diagnostic starting points. Correlate them with query latency, transaction duration, lock waits, database utilization, deployment events, and application traffic.

Troubleshooting Pool Exhaustion

1. Confirm which pool and database endpoint are affected.
2. Record active, idle, total, and waiting connection counts.
3. Record connection-acquisition duration and timeout rate.
4. Inspect connection usage duration.
5. Identify long-running queries and transactions.
6. Inspect database lock waits and blocking.
7. Check for connection leaks.
8. Check application-instance count and total connection multiplier.
9. Check recent deployments or pool-configuration changes.
10. Check database CPU, memory, storage, and connection limits.
11. Inspect retries that can amplify load.
12. Change pool size only after identifying the capacity bottleneck.

Load-testing a Connection Pool

Test the pool with representative SQL, transaction duration, concurrency, and database capacity.

Measure:

• Application throughput
• Request latency
• Connection-acquisition latency
• Active and waiting connections
• Query duration
• Transaction duration
• Database CPU and memory
• Lock waits
• Timeouts and errors
• Behaviour during instance scaling
• Behaviour during database restart or failover

Increase pool size gradually during an approved test and stop when useful throughput stops improving or database contention becomes unacceptable.

Common Connection-pool Mistakes

Recommended Test Cases

Connection-pool Best Practices

Practice Exercise

Design and validate database connection management for the online learning platform developed in the previous lessons.

Requirements

1. Identify the web, background-worker, reporting, and administrative database consumers.
2. Record the maximum number of application instances for each consumer.
3. Define a maximum pool size per instance.
4. Calculate the total potential database connection count.
5. Reserve database capacity for administration and recovery.
6. Define acquisition, idle, validation, and lifetime timeouts.
7. Guarantee connection release after successful and failed operations.
8. Guarantee rollback after transaction failure.
9. Prevent tenant and session state from leaking between borrowers.
10. Test one slow quiz-results query.
11. Test one long-running enrollment transaction.
12. Simulate pool exhaustion.
13. Simulate a broken idle connection.
14. Simulate horizontal application scaling.
15. Collect active, idle, waiting, acquisition-time, and usage-time metrics.

Pool-design Template

Frequently Asked Questions