---
# === IDENTITY ===
id: software/patterns/connection-pooling/2026
canonical_question: "How do I implement connection pooling correctly?"
aliases:
  - "Database connection pooling best practices"
  - "How to size a connection pool"
  - "Connection pool configuration guide"
  - "HikariCP vs PgBouncer connection pooling"
  - "Fix connection pool exhausted errors"
  - "Connection pooling in Node.js Python Java Go"
  - "How many database connections should I use"
  - "Connection leak detection and prevention"
entity_type: software_reference
domain: software > patterns > connection pooling
region: global
jurisdiction: global
temporal_scope: 2024-2026

# === VERIFICATION ===
last_verified: 2026-02-24
confidence: 0.92
version: 1.0
first_published: 2026-02-24

# === TEMPORAL VALIDITY ===
temporal_validity:
  status: stable
  last_breaking_change: null
  next_review: 2026-08-23
  change_sensitivity: low

# === CONSTRAINTS ===
constraints:
  - "Never set pool size larger than what the database server can handle; total connections across all app instances must not exceed max_connections"
  - "Always close/release connections back to the pool in a finally block or equivalent resource cleanup"
  - "Connection pool size must account for ALL application instances sharing the same database"
  - "Never use session-level SET commands in transaction pooling mode (PgBouncer); use SET LOCAL instead"

# === SKIP CONDITIONS ===
skip_this_unit_if:
  - condition: "Need to optimize individual SQL query performance"
    use_instead: "software/patterns/sql-query-optimization/2026"
  - condition: "Need HTTP connection pooling or keep-alive tuning"
    use_instead: "HTTP connection reuse / keep-alive configuration"
  - condition: "Need Redis connection management"
    use_instead: "Redis client library connection pool documentation"

# === AGENT HINTS ===
inputs_needed:
  - key: "language"
    question: "What programming language/framework are you using?"
    type: choice
    options: ["Node.js", "Python", "Java/Spring", "Go", "Other"]
  - key: "database"
    question: "Which database are you connecting to?"
    type: choice
    options: ["PostgreSQL", "MySQL", "SQL Server", "Oracle", "Other"]
  - key: "scale"
    question: "How many concurrent database-hitting requests do you expect?"
    type: choice
    options: ["<100", "100-1000", "1000-10000", ">10000"]

# === DISTRIBUTION ===
canonical_source: "https://knowledgelib.io/software/patterns/connection-pooling/2026"
suggested_citation: "Source: knowledgelib.io — AI Knowledge Library (verified 2026-02-24)"

# === RELATED UNITS ===
related_kos:
  depends_on: []
  related_to:
    - id: "software/patterns/sql-query-optimization/2026"
      label: "SQL Query Optimization"
    - id: "software/patterns/database-indexing-strategies/2026"
      label: "Database Indexing Strategies"
  solves: []
  alternative_to: []
  often_confused_with: []

# === SOURCES (7 authoritative sources) ===
sources:
  - id: src1
    title: "About Pool Sizing"
    author: HikariCP / Brett Wooldridge
    url: https://github.com/brettwooldridge/HikariCP/wiki/About-Pool-Sizing
    type: official_docs
    published: 2023-06-15
    reliability: authoritative
  - id: src2
    title: "The best way to determine the optimal connection pool size"
    author: Vlad Mihalcea
    url: https://vladmihalcea.com/optimal-connection-pool-size/
    type: technical_blog
    published: 2024-03-10
    reliability: high
  - id: src3
    title: "PgBouncer Configuration"
    author: PgBouncer
    url: https://www.pgbouncer.org/config.html
    type: official_docs
    published: 2024-01-01
    reliability: authoritative
  - id: src4
    title: "Connection Pooling — SQLAlchemy 2.0 Documentation"
    author: SQLAlchemy
    url: https://docs.sqlalchemy.org/en/20/core/pooling.html
    type: official_docs
    published: 2024-06-01
    reliability: authoritative
  - id: src5
    title: "Pooling — node-postgres"
    author: node-postgres
    url: https://node-postgres.com/features/pooling
    type: official_docs
    published: 2024-01-01
    reliability: authoritative
  - id: src6
    title: "Common Pitfalls in Database Connection Pool Configuration"
    author: Leapcell
    url: https://leapcell.io/blog/common-pitfalls-in-database-connection-pool-configuration
    type: technical_blog
    published: 2025-05-15
    reliability: moderate_high
  - id: src7
    title: "PgBouncer Best Practices in Azure Database for PostgreSQL"
    author: Microsoft
    url: https://techcommunity.microsoft.com/blog/adforpostgresql/pgbouncer-best-practices-in-azure-database-for-postgresql-%E2%80%93-part-1/4453323
    type: technical_blog
    published: 2025-01-20
    reliability: high
---

# Connection Pooling: Implementation & Sizing Guide

## TL;DR

- **Bottom line**: Reuse database connections through a pool to amortize TCP/TLS handshake and authentication cost; a properly sized pool dramatically improves throughput while reducing database load.
- **Key tool/command**: `pool_size = (core_count * 2) + effective_spindle_count` (HikariCP formula; for SSDs, spindle count is typically 1)
- **Watch out for**: Connection leaks from unclosed connections in error paths -- the #1 cause of pool exhaustion in production.
- **Works with**: Any relational database (PostgreSQL, MySQL, SQL Server, Oracle) and most languages (Java, Python, Node.js, Go, .NET).

## Constraints
<!-- Agents: read this section before recommending any action from this unit.
     These are hard constraints that must not be violated. -->

- Never set pool size larger than what the database can handle; total connections across ALL application instances must not exceed `max_connections` (PostgreSQL default: 100)
- Always release connections in a `finally` block, `defer` statement, context manager, or try-with-resources -- never rely on garbage collection
- Connection pool size must account for all app instances: `per_instance_pool = max_connections / instance_count`
- In PgBouncer transaction pooling mode, never use session-level `SET`, `PREPARE`, `LISTEN/NOTIFY`, or temporary tables outside a single transaction
- Never disable connection validation/health checks in production -- stale connections cause intermittent failures

## Quick Reference

| Pool Type | Language/Platform | Library | Default Pool Size | Key Config Parameters |
|---|---|---|---|---|
| HikariCP | Java | `com.zaxxer.hikari` | 10 | `maximumPoolSize`, `minimumIdle`, `connectionTimeout`, `idleTimeout`, `maxLifetime` |
| PgBouncer | PostgreSQL (proxy) | pgbouncer | 20 per db | `pool_mode`, `default_pool_size`, `max_client_conn`, `server_idle_timeout` |
| node-postgres | Node.js | `pg.Pool` | 10 | `max`, `idleTimeoutMillis`, `connectionTimeoutMillis`, `allowExitOnIdle` |
| SQLAlchemy | Python | `create_engine()` | 5 | `pool_size`, `max_overflow`, `pool_recycle`, `pool_timeout`, `pool_pre_ping` |
| database/sql | Go | stdlib | unlimited (2 idle) | `SetMaxOpenConns`, `SetMaxIdleConns`, `SetConnMaxLifetime`, `SetConnMaxIdleTime` |
| pgxpool | Go | `github.com/jackc/pgx/v5` | 4 (min) | `MaxConns`, `MinConns`, `MaxConnLifetime`, `MaxConnIdleTime`, `HealthCheckPeriod` |
| c3p0 | Java | `com.mchange.v2.c3p0` | 3 | `maxPoolSize`, `minPoolSize`, `acquireIncrement`, `maxIdleTime` |
| Drizzle/Neon | Node.js (serverless) | `@neondatabase/serverless` | 1 | Serverless per-request connections; use external pooler |
| DBCP2 | Java | `commons-dbcp2` | 8 | `maxTotal`, `maxIdle`, `minIdle`, `maxWaitMillis` |

### Sizing Formula (HikariCP)

```
pool_size = (core_count * 2) + effective_spindle_count
```

- `core_count`: Physical cores (not HT threads) on the **database server**
- `effective_spindle_count`: 0 if dataset fits in RAM; 1 for SSD; actual spindle count for HDD arrays
- Example: 4-core DB server with SSD = `(4 * 2) + 1 = 9` connections (round to 10) [src1]

## Decision Tree

```
START: How many app instances hit this database?
├── Single instance?
│   ├── YES → pool_size = (db_cores * 2) + spindle_count
│   └── NO ↓
├── Multiple instances (N)?
│   ├── YES → per_instance_pool = (db_cores * 2 + spindle_count) / N
│   └── Consider external pooler (PgBouncer) ↓
├── Using serverless / many short-lived processes?
│   ├── YES → Use PgBouncer in transaction mode between app and DB
│   └── NO ↓
├── Need prepared statements or LISTEN/NOTIFY?
│   ├── YES → PgBouncer session mode (or direct connection for those features)
│   └── NO → PgBouncer transaction mode (best efficiency)
└── DEFAULT → Start with pool_size=10, monitor pg_stat_activity, adjust
```

## Step-by-Step Guide

### 1. Calculate your target pool size

Determine the database server's core count and storage type. Apply the HikariCP formula, then divide by the number of application instances. [src1]

```bash
# PostgreSQL: check current connection limits and usage
SELECT setting FROM pg_settings WHERE name = 'max_connections';
SELECT count(*) FROM pg_stat_activity;
```

**Verify**: `SELECT count(*) FROM pg_stat_activity;` should show current connections well below `max_connections`.

### 2. Configure pool parameters

Set the pool size, timeouts, and health checks in your application. Every pool library needs at minimum: max size, idle timeout, connection timeout, and max lifetime. [src2]

```
# Universal pool config checklist:
max_pool_size:        10          # From sizing formula
connection_timeout:   30s         # How long to wait for a connection from the pool
idle_timeout:         10min       # Remove idle connections after this duration
max_lifetime:         30min       # Recycle connections to avoid stale server-side state
validation_query:     SELECT 1    # Health check (or pool_pre_ping equivalent)
leak_detection:       60s         # Alert if connection held longer than this
```

**Verify**: Application logs should show pool initialized with expected size, no timeout warnings.

### 3. Implement proper connection lifecycle

Wrap every database operation in try/finally (or language equivalent) to guarantee connection release. [src4]

```python
# Python example pattern
from contextlib import contextmanager

@contextmanager
def get_connection(pool):
    conn = pool.connect()
    try:
        yield conn
    finally:
        conn.close()  # Returns to pool, does not destroy
```

**Verify**: Under load, `SELECT count(*) FROM pg_stat_activity WHERE state = 'idle';` should remain near your pool size, not grow unbounded.

### 4. Add monitoring and alerting

Track pool metrics: active connections, idle connections, wait time, and timeouts. Set alerts for pool exhaustion. [src6]

```sql
-- PostgreSQL: connection breakdown by state
SELECT state, count(*) FROM pg_stat_activity
WHERE datname = 'your_db'
GROUP BY state ORDER BY count DESC;
```

**Verify**: Active connections stay within pool_size bounds; wait_count metrics stay near zero.

## Code Examples

### Node.js (pg): PostgreSQL connection pool

> Full script: [node-js-pg-postgresql-connection-pool.js](scripts/node-js-pg-postgresql-connection-pool.js) (25 lines)

```javascript
// Input:  PostgreSQL connection string
// Output: Pooled query results with automatic connection management
const { Pool } = require('pg');  // pg@8.x
const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
# ... (see full script)
```

### Python (SQLAlchemy): Connection pool with health checks

```python
# Input:  PostgreSQL connection URL
# Output: Engine with QueuePool, pre-ping validation, overflow support

from sqlalchemy import create_engine, text

engine = create_engine(
    "postgresql+psycopg2://user:pass@host:5432/dbname",
    pool_size=10,          # Permanent connections
    max_overflow=5,        # Temporary connections beyond pool_size
    pool_timeout=30,       # Seconds to wait for available connection
    pool_recycle=1800,     # Recycle connections every 30 minutes
    pool_pre_ping=True,    # Validate connection before use (SELECT 1)
)

# Context manager guarantees connection release
with engine.connect() as conn:
    result = conn.execute(text("SELECT * FROM users WHERE id = :id"), {"id": 1})
    rows = result.fetchall()

# Transaction block
with engine.begin() as conn:  # auto-commits on success, rolls back on exception
    conn.execute(text("INSERT INTO logs (msg) VALUES (:msg)"), {"msg": "hello"})
```

### Java (HikariCP): Spring Boot connection pool

```java
// Input:  JDBC connection properties
// Output: High-performance connection pool (Spring Boot default)

// application.yml
// spring.datasource.hikari:
//   maximum-pool-size: 10
//   minimum-idle: 5
//   connection-timeout: 30000    # 30s
//   idle-timeout: 600000         # 10min
//   max-lifetime: 1800000        # 30min
//   leak-detection-threshold: 60000  # Warn if held > 60s

// Usage with try-with-resources (auto-closes connection)
try (Connection conn = dataSource.getConnection();
     PreparedStatement ps = conn.prepareStatement("SELECT * FROM users WHERE id = ?")) {
    ps.setInt(1, userId);
    try (ResultSet rs = ps.executeQuery()) {
        while (rs.next()) {
            // process row
        }
    }
}  // Connection automatically returned to HikariCP pool
```

### Go (database/sql): Standard library pool

> Full script: [go-database-sql-standard-library-pool.go](scripts/go-database-sql-standard-library-pool.go) (27 lines)

```go
// Input:  PostgreSQL DSN
// Output: Managed connection pool with bounded connections
import (
    "database/sql"
    "time"
# ... (see full script)
```

## Anti-Patterns

### Wrong: Opening a new connection per request

```javascript
// BAD -- creates a new TCP connection + TLS handshake + auth for EVERY query
async function getUser(id) {
  const client = new Client({ connectionString: process.env.DATABASE_URL });
  await client.connect();
  const result = await client.query('SELECT * FROM users WHERE id = $1', [id]);
  await client.end();
  return result.rows[0];
}
```

### Correct: Reuse a shared pool

```javascript
// GOOD -- pool created once, connections reused across requests
const pool = new Pool({ connectionString: process.env.DATABASE_URL, max: 10 });

async function getUser(id) {
  const result = await pool.query('SELECT * FROM users WHERE id = $1', [id]);
  return result.rows[0];
}
```

### Wrong: Not releasing connections on error

```python
# BAD -- connection leaked if execute() throws an exception
conn = engine.raw_connection()
cursor = conn.cursor()
cursor.execute("INSERT INTO logs (msg) VALUES (%s)", ("test",))
conn.commit()
conn.close()
```

### Correct: Use context manager or try/finally

```python
# GOOD -- connection always returned to pool, even on exception
with engine.connect() as conn:
    conn.execute(text("INSERT INTO logs (msg) VALUES (:msg)"), {"msg": "test"})
    conn.commit()
```

### Wrong: Setting pool size too large

```yaml
# BAD -- 200 connections per instance x 10 instances = 2000 connections
# PostgreSQL default max_connections is 100; this will cause connection refused errors
spring.datasource.hikari.maximum-pool-size: 200
```

### Correct: Size pool based on database capacity

```yaml
# GOOD -- 10 connections x 10 instances = 100 total (within max_connections)
spring.datasource.hikari.maximum-pool-size: 10
```

### Wrong: No connection validation

```python
# BAD -- stale connections cause intermittent "server closed the connection" errors
engine = create_engine("postgresql://...", pool_size=10)
# No pool_pre_ping, no pool_recycle
```

### Correct: Enable health checks and recycling

```python
# GOOD -- validates connections before use, recycles every 30 minutes
engine = create_engine(
    "postgresql://...",
    pool_size=10,
    pool_pre_ping=True,
    pool_recycle=1800,
)
```

## Common Pitfalls

- **Connection leaks under error conditions**: Connections acquired but not released when exceptions occur in business logic. Fix: wrap all DB operations in `try/finally`, context managers, `defer`, or try-with-resources. [src6]
- **Pool sized for single instance but deployed on multiple**: 10 instances with pool_size=20 each = 200 connections, exceeding PostgreSQL's default `max_connections=100`. Fix: `per_instance = max_connections / instance_count` with headroom for maintenance connections. [src1]
- **Idle connections killed by firewall/load balancer**: Cloud load balancers and firewalls silently drop idle TCP connections after 5-15 minutes, leaving stale connections in the pool. Fix: set `maxLifetime` (HikariCP) or `pool_recycle` (SQLAlchemy) shorter than the network timeout. [src4]
- **Missing connection timeout**: Without a connection timeout, requests block indefinitely waiting for a pool connection during traffic spikes. Fix: always set `connectionTimeout` to 5-30 seconds. [src2]
- **Go database/sql unbounded defaults**: Go's `database/sql` has no default limit on open connections, which can overwhelm the database under load. Fix: always call `SetMaxOpenConns()` and `SetMaxIdleConns()`. [src5]
- **Using SET in PgBouncer transaction mode**: Session-level `SET` commands leak to other clients sharing the same server connection. Fix: use `SET LOCAL` (transaction-scoped) or switch to session pooling mode. [src3]
- **Pool too small causes queuing, not errors**: An undersized pool doesn't fail immediately -- requests silently queue, adding latency. Monitor `wait_count` and p99 connection acquisition time. Fix: increase pool_size or optimize query duration. [src6]

## Diagnostic Commands

```bash
# PostgreSQL: Check total connections and per-state breakdown
psql -c "SELECT state, count(*) FROM pg_stat_activity GROUP BY state;"

# PostgreSQL: Find long-running queries holding connections
psql -c "SELECT pid, now() - pg_stat_activity.query_start AS duration, query, state
FROM pg_stat_activity WHERE state != 'idle' ORDER BY duration DESC LIMIT 10;"

# PostgreSQL: Check max_connections setting
psql -c "SHOW max_connections;"

# PgBouncer: Check pool status
psql -p 6432 pgbouncer -c "SHOW POOLS;"

# PgBouncer: Check connection stats
psql -p 6432 pgbouncer -c "SHOW STATS;"

# PgBouncer: Check active clients
psql -p 6432 pgbouncer -c "SHOW CLIENTS;"

# HikariCP: Check pool metrics (JMX or Spring Actuator)
curl http://localhost:8080/actuator/metrics/hikaricp.connections.active
curl http://localhost:8080/actuator/metrics/hikaricp.connections.idle
curl http://localhost:8080/actuator/metrics/hikaricp.connections.pending

# Node.js pg: Log pool metrics
# pool.totalCount, pool.idleCount, pool.waitingCount
```

## When to Use / When Not to Use

| Use When | Don't Use When | Use Instead |
|---|---|---|
| Multi-request application server (web app, API) | Single-shot CLI script that runs once and exits | Direct connection (no pool overhead) |
| Multiple concurrent database queries | Serverless function with 1 query per invocation | External pooler (PgBouncer) or serverless-native driver |
| Long-running application process | Database already has a built-in proxy/pooler (e.g., Aurora Proxy, PlanetScale) | Use the managed pooler |
| Need to limit total connections to database | Embedded/mobile app with SQLite | SQLite uses file locking, not connection pooling |
| Multiple application instances sharing one DB | Redis or other non-relational store | Use the client library's built-in pool (e.g., ioredis) |

## Important Caveats

- The HikariCP sizing formula `(cores * 2) + spindle_count` is a starting point for OLTP workloads, not a universal law. OLAP, batch, or mixed workloads may need different sizing based on query duration and I/O patterns. Always benchmark.
- PgBouncer transaction pooling mode breaks PostgreSQL features that depend on session state: `PREPARE`/`EXECUTE`, `LISTEN`/`NOTIFY`, `SET` (non-LOCAL), temporary tables, advisory locks, and `DECLARE CURSOR` outside transactions.
- In containerized deployments (Kubernetes), connection pools do not survive pod restarts. Set `minimumIdle` thoughtfully -- too high causes a connection storm on pod startup across a fleet.
- Cloud database services (AWS RDS, GCP Cloud SQL, Azure) often have their own proxy/pooler options. Using both an external pooler AND an application-level pool can cause double-pooling issues; understand your topology.
- Connection pool metrics (active, idle, wait time) are the most important signals for right-sizing. Never guess -- instrument and observe under realistic load.

## Related Units
<!-- Generated from related_kos frontmatter -->

- [SQL Query Optimization](/software/patterns/sql-query-optimization/2026)
- [Database Indexing Strategies](/software/patterns/database-indexing-strategies/2026)