System Design (LLD)Connection Pooling

Connection Pooling

LevelIntermediate

Duration60 mins

TopicConnection Pooling

3 / 4

Pool Size Management

The Goldilocks Problem

Pool sizing is one of the most frequently asked—and most frequently misunderstood—questions in connection management. How many connections should my pool have? The answer is deceptively complex. Too few connections and your application queues requests, increasing latency and reducing throughput. Too many connections and you overwhelm the database, consume excessive memory, increase context switching overhead, and paradoxically decrease performance. The sweet spot depends on dozens of factors: workload characteristics, database capacity, network latency, query patterns, and hardware specifications. This page provides the frameworks and formulas to find your optimal pool size.

What You Will Learn

By the end of this page, you will understand the mathematics behind pool sizing, Little's Law and its application to connection pools, the database-side perspective on connection limits, static vs dynamic sizing strategies, and practical capacity planning approaches for production systems.

The Mathematics of Pool Sizing

Pool sizing isn't guesswork—it's applied queueing theory. The foundational principle is Little's Law, which relates the average number of items in a system to the arrival rate and average time in system: L = λ \times W Where: L = Average number of items in the system (connections in use) λ = Arrival rate (requests per second requiring connections) W = Average time in system (connection hold time per request) This simple formula has profound implications for pool sizing.

Applying Little's Law to Connection Pools
Request Rate	Avg Connection Hold Time	Required Connections	Practical Recommendation
100 req/s	10ms (0.01s)	100 × 0.01 = 1	5-10 (headroom for variance)
100 req/s	100ms (0.1s)	100 × 0.1 = 10	15-20 (headroom for spikes)
500 req/s	50ms (0.05s)	500 × 0.05 = 25	35-50 (buffer for bursts)
1000 req/s	100ms (0.1s)	1000 × 0.1 = 100	120-150 (significant scale)
100 req/s	500ms (0.5s)	100 × 0.5 = 50	60-80 (long queries need buffer)

Little's Law Provides Minimum, Not Optimum

Little's Law tells you the minimum connections needed for steady-state. Real systems experience variance—bursts of traffic, occasional slow queries, background jobs. Add 30-50% headroom to the theoretical minimum. But don't add too much—excess connections have real costs.

Beyond Averages: The Variance Problem Little's Law uses averages, but real systems don't behave in averages. Consider: Traffic bursts : A cache invalidation causes 10x normal traffic for 30 seconds Slow query outliers : 99% of queries take 10ms, but 1% take 500ms Batch job overlap : Nightly batch job doubles database load Cascading delays : One slow service causes connections to be held longer To handle variance, we model connections needed at different percentiles: P99 Connections = λ_peak \times W_p99 Where: λ_peak = Peak request rate (e.g., 2x average) W_p99 = 99th percentile connection hold time For a system with 100 req/s average, 200 req/s peak, 100ms average hold, and 300ms P99 hold: Average case: 100 \times 0.1 = 10 connections P99 case: 200 \times 0.3 = 60 connections Sizing for average (10) would cause severe queuing during peaks. Sizing for P99 (60) provides resilience at higher cost.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
/**
 * Pool Size Calculator
 * 
 * Uses queueing theory to calculate recommended pool sizes
 * for different scenarios and confidence levels.
 */
 
interface WorkloadCharacteristics {
    // Request rates
    averageRequestsPerSecond: number;
    peakRequestsPerSecond: number;     // e.g., 2x average
    
    // Connection hold times (in milliseconds)
    averageHoldTimeMs: number;
    p99HoldTimeMs: number;
    maxHoldTimeMs: number;
    
    // Concurrency
    applicationInstances: number;
}
 
interface DatabaseConstraints {
    maxConnections: number;              // Database max_connections setting
    connectionMemoryMb: number;          // Memory per connection
    availableMemoryMb: number;           // Total database memory
    reservedForAdminConnections: number; // Keep some connections for admin
}
 
interface PoolSizeRecommendation {
    minConnections: number;
    maxConnections: number;
    rationale: string[];
}
 
function calculatePoolSize(
    workload: WorkloadCharacteristics,
    constraints: DatabaseConstraints
): PoolSizeRecommendation {
    const rationale: string[] = [];
    
    // 1. Little's Law baseline (per instance)
    const perInstanceRps = workload.averageRequestsPerSecond / workload.applicationInstances;
    const avgHoldSec = workload.averageHoldTimeMs / 1000;
    const littlesLawMin = Math.ceil(perInstanceRps * avgHoldSec);
    rationale.push(
        `Little's Law baseline: ${perInstanceRps} req/s × ${avgHoldSec}s = ${littlesLawMin} connections`
    );
    
    // 2. P99 scenario (handles 99% of situations)
    const peakPerInstance = workload.peakRequestsPerSecond / workload.applicationInstances;
    const p99HoldSec = workload.p99HoldTimeMs / 1000;
    const p99Connections = Math.ceil(peakPerInstance * p99HoldSec);
    rationale.push(
        `P99 scenario: ${peakPerInstance} peak req/s × ${p99HoldSec}s P99 = ${p99Connections} connections`
    );
    
    // 3. Database constraint (connections available per instance)
    const availableForApp = constraints.maxConnections - constraints.reservedForAdminConnections;
    const maxPerInstance = Math.floor(availableForApp / workload.applicationInstances);
    rationale.push(
        `Database limit: (${constraints.maxConnections} - ${constraints.reservedForAdminConnections}) / ${workload.applicationInstances} = ${maxPerInstance} max`
    );
    
    // 4. Memory constraint
    const memoryLimitedConnections = Math.floor(
        constraints.availableMemoryMb / constraints.connectionMemoryMb
    );
    const memoryMaxPerInstance = Math.floor(memoryLimitedConnections / workload.applicationInstances);
    rationale.push(
        `Memory limit: ${constraints.availableMemoryMb}MB / ${constraints.connectionMemoryMb}MB per conn = ${memoryLimitedConnections} total`
    );
    
    // 5. Calculate recommendation
    // Min: Baseline + 50% buffer, but at least 5
    const calculatedMin = Math.max(5, Math.ceil(littlesLawMin * 1.5));
    
    // Max: P99 + 20% buffer, but constrained by database/memory limits
    const calculatedMax = Math.ceil(p99Connections * 1.2);
    const constrainedMax = Math.min(calculatedMax, maxPerInstance, memoryMaxPerInstance);
    
    // Ensure max >= min
    const finalMax = Math.max(constrainedMax, calculatedMin);
    
    rationale.push(
        `Recommendation: min=${calculatedMin} (baseline+50%), max=${finalMax} (P99+20%, constrained)`
    );
    
    return {
        minConnections: calculatedMin,
        maxConnections: finalMax,
        rationale
    };
}
 
// Example calculation
const workload: WorkloadCharacteristics = {
    averageRequestsPerSecond: 500,
    peakRequestsPerSecond: 1000,
    averageHoldTimeMs: 50,
    p99HoldTimeMs: 200,
    maxHoldTimeMs: 1000,
    applicationInstances: 10
};
 
const constraints: DatabaseConstraints = {
    maxConnections: 500,
    connectionMemoryMb: 10,  // PostgreSQL default
    availableMemoryMb: 4096,
    reservedForAdminConnections: 20
};
 
const recommendation = calculatePoolSize(workload, constraints);
// Result:
// {
//   minConnections: 8,   // ceil(50 req/s × 0.05s × 1.5)
//   maxConnections: 24,  // min(24 calculated, 48 db limit, 40 memory limit)
//   rationale: [...]
// }

Database-Side Connection Limits

Application pools don't exist in isolation—they connect to databases that have their own connection limits and overhead. Understanding the database perspective is essential for holistic pool sizing. Why Databases Limit Connections Each database connection consumes resources: Memory : Session state, prepared statements, query buffers, sort areas CPU : Context switching overhead, connection maintenance File Descriptors : Operating system limits on open files Locks/Latches : Internal synchronization structures At high connection counts, these overheads compound, actually reducing throughput.

Memory Per Connection by Database
Database	Memory Per Connection	Default max_connections	Notes
PostgreSQL	~10MB default (work_mem, shared_buffers)	100	Can increase with complex queries
MySQL/InnoDB	~256KB-1MB base	151	Grows with buffer pool settings
Oracle	~1-5MB (PGA per session)	Varies by edition	Shared server mode reduces memory
SQL Server	~1-2MB minimum	32,767 (theoretical)	Memory pressure affects performance before limit
MongoDB	~1MB per connection	Unlimited (practical ~65,000)	Connection storms cause issues well before limit

The Connection Paradox

More connections doesn't mean more throughput. PostgreSQL benchmarks show that throughput often peaks at 200-300 connections for a typical server, then decreases as connections increase. Context switching, lock contention, and memory pressure counteract the parallelism benefits. Size pools for optimal throughput, not maximum connections.

The Connection Budget Consider your entire stack when sizing pools: Total Application Connections = Pool Max \times Application Instances Connection Budget: Database max_connections: 500 - Reserved for admin/monitoring: 20 - Reserved for replication: 10 - Reserved for PgBouncer/proxy: 10 = Available for applications: 460 10 application instances = 46 max connections per instance Now work backwards from this constraint. If your P99 calculation suggests needing 60 connections per instance but you only have budget for 46, you must either: Increase database max_connections (if resources allow) Reduce application instances (scale differently) Optimize query hold times (reduce W in Little's Law) Add a connection proxy (PgBouncer, ProxySQL) for multiplexing

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
/**
 * Connection Budget Calculator
 * 
 * Plans pool sizes considering all consumers of database connections.
 */
 
interface ConnectionConsumer {
    name: string;
    instances: number;
    maxConnectionsPerInstance: number;
    priority: 'critical' | 'high' | 'normal' | 'low';
}
 
interface ConnectionBudget {
    databaseMaxConnections: number;
    reservedForAdmin: number;
    reservedForReplication: number;
    reservedForProxies: number;
    consumers: ConnectionConsumer[];
}
 
interface BudgetAllocation {
    consumer: string;
    requestedTotal: number;
    allocated: number;
    perInstance: number;
    satisfied: boolean;
}
 
function allocateConnectionBudget(budget: ConnectionBudget): BudgetAllocation[] {
    // Calculate available connections
    const available = budget.databaseMaxConnections
        - budget.reservedForAdmin
        - budget.reservedForReplication
        - budget.reservedForProxies;
    
    console.log(`Total available for applications: ${available}`);
    
    // Calculate total requested
    const totalRequested = budget.consumers.reduce(
        (sum, c) => sum + (c.instances * c.maxConnectionsPerInstance), 0
    );
    
    if (totalRequested <= available) {
        // Everyone gets what they want
        return budget.consumers.map(c => ({
            consumer: c.name,
            requestedTotal: c.instances * c.maxConnectionsPerInstance,
            allocated: c.instances * c.maxConnectionsPerInstance,
            perInstance: c.maxConnectionsPerInstance,
            satisfied: true
        }));
    }
    
    // Need to allocate proportionally with priority weighting
    const priorityWeights = {
        critical: 4,
        high: 2,
        normal: 1,
        low: 0.5
    };
    
    // Calculate weighted shares
    const weightedRequests = budget.consumers.map(c => ({
        ...c,
        requested: c.instances * c.maxConnectionsPerInstance,
        weight: priorityWeights[c.priority]
    }));
    
    const totalWeight = weightedRequests.reduce(
        (sum, r) => sum + (r.requested * r.weight), 0
    );
    
    // Allocate based on weighted proportion
    return weightedRequests.map(c => {
        const weightedShare = (c.requested * c.weight) / totalWeight;
        const allocated = Math.floor(available * weightedShare);
        const perInstance = Math.floor(allocated / c.instances);
        
        return {
            consumer: c.name,
            requestedTotal: c.requested,
            allocated: perInstance * c.instances,  // Round to instance boundaries
            perInstance,
            satisfied: perInstance >= c.maxConnectionsPerInstance
        };
    });
}
 
// Example: Multi-service architecture
const budget: ConnectionBudget = {
    databaseMaxConnections: 500,
    reservedForAdmin: 20,
    reservedForReplication: 10,
    reservedForProxies: 20,
    consumers: [
        { name: 'api-gateway', instances: 20, maxConnectionsPerInstance: 30, priority: 'critical' },
        { name: 'order-service', instances: 10, maxConnectionsPerInstance: 20, priority: 'high' },
        { name: 'analytics', instances: 5, maxConnectionsPerInstance: 15, priority: 'normal' },
        { name: 'batch-jobs', instances: 3, maxConnectionsPerInstance: 10, priority: 'low' }
    ]
};
 
const allocations = allocateConnectionBudget(budget);
// Shows how to distribute limited connection capacity across services

Static vs Dynamic Sizing Strategies

Pools can maintain fixed sizes or adjust dynamically. Each approach has merits, and many production systems use hybrid strategies.

Static Sizing

•Fixed min = max: All connections created at startup
•Predictable: Resource usage doesn't vary
•Simple: No sizing logic, no elasticity bugs
•Immediate capacity: No cold start delays
•Wasteful: Maintains connections during low load
•Capacity bound: Cannot handle unexpected peaks beyond max

Dynamic Sizing

•min < max: Pool grows and shrinks with demand
•Efficient: Only maintains needed connections
•Adaptive: Handles varying workloads gracefully
•Cold start risk: May need to create connections on demand
•Complexity: Sizing algorithms can have bugs
•Churn: Frequent create/destroy cycles add overhead

The HikariCP Philosophy

HikariCP, one of the fastest Java connection pools, recommends: 'Don't configure a minimumIdle value different from maximumPoolSize. The pool performs best when operating as a fixed-size pool.' Their reasoning: The overhead of dynamically sizing rarely justifies the complexity. When in doubt, use static sizing at the P99 level.

When Dynamic Sizing Makes Sense Despite HikariCP's advice, dynamic sizing is valuable in specific scenarios: Multi-tenant with varying load — Different tenants have different peak times Serverless-adjacent architectures — Instances scale frequently, connection needs vary Shared database resources — Reducing connections during quiet periods frees resources for other apps Cost-sensitive environments — Database charges per-connection (some managed services) Large variance workloads — Batch processing peaks differ dramatically from normal operation

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
/**
 * Dynamic Pool Sizing Algorithm
 * 
 * Adjusts pool size based on observed utilization patterns.
 * Uses moving averages to smooth out temporary spikes.
 */
 
interface DynamicSizingConfig {
    minimumPoolSize: number;        // Never go below this
    maximumPoolSize: number;        // Never exceed this
    targetUtilization: number;      // Aim for this % of pool in use (0.6-0.8)
    scaleUpThreshold: number;       // Scale up when utilization exceeds (0.8-0.9)
    scaleDownThreshold: number;     // Scale down when utilization below (0.3-0.5)
    evaluationWindowMs: number;     // How long to observe before deciding
    scaleUpIncrement: number;       // Connections to add when scaling up
    scaleDownIncrement: number;     // Connections to remove when scaling down
    cooldownPeriodMs: number;       // Minimum time between resize operations
}
 
class DynamicallySizedPool {
    private config: DynamicSizingConfig;
    private currentMaxSize: number;
    private utilizationHistory: number[] = [];
    private lastResizeTime: number = 0;
    
    constructor(config: DynamicSizingConfig) {
        this.config = config;
        this.currentMaxSize = config.minimumPoolSize;
    }
    
    /**
     * Record current utilization
     * Called periodically (e.g., every second)
     */
    recordUtilization(): void {
        const utilization = this.inUse.size / this.currentMaxSize;
        
        this.utilizationHistory.push(utilization);
        
        // Keep only recent history
        const maxSamples = this.config.evaluationWindowMs / 1000;
        if (this.utilizationHistory.length > maxSamples) {
            this.utilizationHistory.shift();
        }
    }
    
    /**
     * Evaluate and potentially resize
     * Called periodically after utilization recording
     */
    evaluateResize(): void {
        // Respect cooldown period
        if (Date.now() - this.lastResizeTime < this.config.cooldownPeriodMs) {
            return;
        }
        
        // Need enough history for decision
        const minSamples = this.config.evaluationWindowMs / 2000;
        if (this.utilizationHistory.length < minSamples) {
            return;
        }
        
        // Calculate average utilization
        const avgUtilization = this.utilizationHistory.reduce((a, b) => a + b, 0) 
            / this.utilizationHistory.length;
        
        // Calculate P95 utilization (for spike detection)
        const sorted = [...this.utilizationHistory].sort((a, b) => a - b);
        const p95Index = Math.floor(sorted.length * 0.95);
        const p95Utilization = sorted[p95Index];
        
        // Decision logic
        if (p95Utilization > this.config.scaleUpThreshold) {
            // High utilization sustained - scale up
            this.scaleUp();
        } else if (avgUtilization < this.config.scaleDownThreshold) {
            // Low utilization sustained - scale down
            this.scaleDown();
        }
    }
    
    private scaleUp(): void {
        const newMax = Math.min(
            this.currentMaxSize + this.config.scaleUpIncrement,
            this.config.maximumPoolSize
        );
        
        if (newMax > this.currentMaxSize) {
            console.log(
                `Scaling pool up: ${this.currentMaxSize} → ${newMax} ` +
                `(utilization: ${this.getAvgUtilization().toFixed(2)})`
            );
            
            this.currentMaxSize = newMax;
            this.lastResizeTime = Date.now();
            this.utilizationHistory = [];  // Reset history after resize
        }
    }
    
    private scaleDown(): void {
        // Don't scale below current usage
        const currentlyInUse = this.inUse.size;
        const safeMin = Math.max(
            this.config.minimumPoolSize,
            currentlyInUse + 2  // Buffer above current use
        );
        
        const newMax = Math.max(
            this.currentMaxSize - this.config.scaleDownIncrement,
            safeMin
        );
        
        if (newMax < this.currentMaxSize) {
            console.log(
                `Scaling pool down: ${this.currentMaxSize} → ${newMax} ` +
                `(utilization: ${this.getAvgUtilization().toFixed(2)})`
            );
            
            // Close excess idle connections
            this.trimIdleConnections(newMax);
            
            this.currentMaxSize = newMax;
            this.lastResizeTime = Date.now();
            this.utilizationHistory = [];
        }
    }
    
    private trimIdleConnections(targetSize: number): void {
        while (this.available.length + this.inUse.size > targetSize 
               && this.available.length > 0) {
            const conn = this.available.pop();
            if (conn) {
                conn.close().catch(console.error);
            }
        }
    }
}
 
// Recommended configuration
const dynamicConfig: DynamicSizingConfig = {
    minimumPoolSize: 10,
    maximumPoolSize: 50,
    targetUtilization: 0.7,         // Aim for 70% usage
    scaleUpThreshold: 0.85,         // Scale up if consistently >85%
    scaleDownThreshold: 0.4,        // Scale down if consistently <40%
    evaluationWindowMs: 60000,      // Evaluate over 1 minute
    scaleUpIncrement: 5,            // Add 5 connections at a time
    scaleDownIncrement: 2,          // Remove 2 at a time (more conservative)
    cooldownPeriodMs: 120000        // 2 minutes between resize operations
};

The Small Pool Counter-Intuition

One of the most surprising insights in connection pooling is that larger pools often perform worse . This counter-intuitive result stems from fundamental computer science principles that apply across databases, operating systems, and distributed systems. The Sources of Overhead

Why Large Pools Hurt Performance

•Context Switching — Each active connection requires CPU time. More connections = more context switches. At 500 connections, the database may spend more time switching than working.
•Lock Contention — Internal database locks (buffer pool, log buffer, shared memory) become bottlenecks as concurrent connections increase.
•Memory Pressure — Each connection consumes memory for buffers and state. Excessive connections can push the database to swap or reduce cache efficiency.
•I/O Saturation — Disk I/O is finite. More connections doesn't create more disk bandwidth—it just creates more contention.
•Connection Overhead — Even idle connections have keepalive traffic, periodic health checks, and memory footprint.

The Formula for Maximum Effective Connections A well-known formula provides a starting point for database pool sizing: connections = ((core_count \times 2) + effective_spindle_count) Where: core_count = Number of CPU cores on database server effective_spindle_count = Number of disks in RAID array (for SSD, use 1 per NVME device) For a database server with 8 cores and 2 NVMe drives: connections = (8 \times 2) + 2 = 18 connections This seems shockingly small! But consider: Each connection can execute work while others wait for I/O With 18 connections, the database is always busy—never idle The \times 2 factor accounts for I/O wait during query execution Adding more connections only adds overhead, not parallelism

PostgreSQL Benchmark Evidence

PostgreSQL benchmarks consistently show throughput peaking at 50-200 connections, then declining . A 16-core server often performs best at 100-150 connections total across all applications. Setting max_connections to 500 'just in case' actively hurts performance by encouraging oversized pools.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
/**
 * Optimal Pool Size Estimator
 * 
 * Based on database server hardware characteristics,
 * estimates the maximum effective connections.
 */
 
interface DatabaseServerSpec {
    cpuCores: number;
    storageType: 'hdd' | 'ssd' | 'nvme';
    storageDevices: number;  // Number of drives/devices
    availableMemoryGb: number;
    memoryPerConnectionMb: number;
}
 
interface ClusterSpec {
    databaseServers: DatabaseServerSpec[];
    applicationInstances: number;
    includesProxy: boolean;  // PgBouncer, ProxySQL, etc.
    proxyMaxConnections?: number;
}
 
function estimateOptimalConnections(server: DatabaseServerSpec): {
    formulaBased: number;
    memoryConstrained: number;
    recommended: number;
} {
    // Formula-based estimate
    let effectiveSpindles: number;
    switch (server.storageType) {
        case 'hdd':
            effectiveSpindles = server.storageDevices;
            break;
        case 'ssd':
            effectiveSpindles = server.storageDevices * 4;  // SSDs support more parallel ops
            break;
        case 'nvme':
            effectiveSpindles = server.storageDevices * 8;  // NVMe even more parallel
            break;
    }
    
    const formulaBased = (server.cpuCores * 2) + effectiveSpindles;
    
    // Memory-constrained estimate
    const totalMemoryMb = server.availableMemoryGb * 1024;
    const memoryForConnections = totalMemoryMb * 0.25;  // Reserve 25% for connections
    const memoryConstrained = Math.floor(memoryForConnections / server.memoryPerConnectionMb);
    
    // Recommended is the lower of formula and memory limits
    const recommended = Math.min(formulaBased, memoryConstrained);
    
    return { formulaBased, memoryConstrained, recommended };
}
 
function calculateClusterPoolSizes(cluster: ClusterSpec): {
    totalDatabaseConnections: number;
    perInstancePoolSize: number;
    reasoning: string[];
} {
    const reasoning: string[] = [];
    
    // Calculate optimal connections per database server
    let totalOptimal = 0;
    for (const server of cluster.databaseServers) {
        const estimate = estimateOptimalConnections(server);
        totalOptimal += estimate.recommended;
        reasoning.push(
            `Server (${server.cpuCores} cores, ${server.storageType}): ` +
            `${estimate.recommended} connections optimal`
        );
    }
    
    reasoning.push(`Total cluster optimal connections: ${totalOptimal}`);
    
    // Reserve for admin/monitoring
    const reservedConnections = Math.ceil(totalOptimal * 0.1);
    const availableForApps = totalOptimal - reservedConnections;
    reasoning.push(`After reserving 10% for admin: ${availableForApps} available`);
    
    // Divide among application instances
    let perInstance = Math.floor(availableForApps / cluster.applicationInstances);
    
    // If using proxy, different calculation
    if (cluster.includesProxy && cluster.proxyMaxConnections) {
        // Proxy multiplexes - apps can have more connections than database
        perInstance = Math.floor(cluster.proxyMaxConnections / cluster.applicationInstances);
        reasoning.push(
            `With proxy multiplexing: ${perInstance} connections per application instance`
        );
    } else {
        reasoning.push(
            `Per application instance: ${perInstance} connections`
        );
    }
    
    return {
        totalDatabaseConnections: totalOptimal,
        perInstancePoolSize: perInstance,
        reasoning
    };
}
 
// Example: Typical production cluster
const cluster: ClusterSpec = {
    databaseServers: [
        { cpuCores: 16, storageType: 'nvme', storageDevices: 2, 
          availableMemoryGb: 64, memoryPerConnectionMb: 10 },
    ],
    applicationInstances: 10,
    includesProxy: false
};
 
const sizing = calculateClusterPoolSizes(cluster);
// Result might suggest: 48 connections per instance (480 total)
// Much less than naive "set to 200" approach!

Connection Proxies and Multiplexing

When application connection needs exceed database optimal capacity, connection proxies provide multiplexing—allowing many application connections to share fewer database connections. Popular Connection Proxies

Connection Proxy Options
Proxy	Database Support	Key Features	Multiplexing Mode
PgBouncer	PostgreSQL only	Lightweight, battle-tested, simple configuration	Transaction, Session, Statement pooling
ProxySQL	MySQL, MariaDB	Query routing, caching, connection multiplexing	Connection pooling with query routing
Amazon RDS Proxy	PostgreSQL, MySQL	Managed service, IAM integration, auto-failover	Transparent connection pooling
PgCat	PostgreSQL	Modern, written in Rust, sharding support	Transaction pooling, load balancing
Odyssey	PostgreSQL	Multi-threaded, connection management	Transaction pooling

How Multiplexing Works Consider a scenario: 10 application instances, each with 50-connection pool (500 total application connections) Database can optimally handle 100 connections PgBouncer sits between apps and database ┌─────────────────────────────────────────────────────────────────┐ │ CONNECTION MULTIPLEXING │ ├─────────────────────────────────────────────────────────────────┤ │ │ │ Application Layer Proxy Layer Database │ │ │ │ App 1 [Pool: 50] ─┐ ┌──────┐ │ │ App 2 [Pool: 50] ─┤ │ │ │ │ App 3 [Pool: 50] ─┤ ┌───────────────┐ │ │ │ │ App 4 [Pool: 50] ─┼────▶│ PgBouncer │──────▶│ DB │ │ │ App 5 [Pool: 50] ─┤ │ [100 conns] │ │ │ │ │ ... │ └───────────────┘ │ │ │ │ App 10 [Pool: 50]─┘ │ │ │ │ └──────┘ │ │ Total: 500 app Multiplexed: Sees: 100 │ │ connections transaction-level connections │ │ │ └─────────────────────────────────────────────────────────────────┘ The proxy maintains 100 persistent connections to the database. When an application transaction begins, the proxy assigns one of its database connections to that transaction. When the transaction completes, the connection is returned to the proxy's pool for reuse by other applications.

# PgBouncer Configuration (pgbouncer.ini)
 
[databases]
# Define backend database connections
mydb = host=db-primary.internal port=5432 dbname=production
 
[pgbouncer]
# Listen for incoming connections
listen_addr = 0.0.0.0
listen_port = 6432
 
# Authentication
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
 
# Pool mode determines when connections are returned to pool:
# - session: Connection is held for entire session (like no pooling)
# - transaction: Connection returned after each transaction (most common)
# - statement: Connection returned after each statement (most aggressive)
pool_mode = transaction
 
# Pool size limits
default_pool_size = 100          # Connections per user/database pair
max_client_conn = 1000           # Total client connections allowed
min_pool_size = 10               # Minimum connections to maintain
 
# Connection lifetime
server_lifetime = 3600           # Close connections older than 1 hour
server_idle_timeout = 600        # Close idle connections after 10 min
 
# Timeouts
server_connect_timeout = 15      # Timeout connecting to PostgreSQL
server_login_retry = 3           # Retries on connect failure
 
# Query handling in transaction mode
# IMPORTANT: Set these for transaction pooling
server_reset_query = DISCARD ALL # Reset connection state between uses
 
# Logging
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1
 
# Statistics
stats_period = 60                # Log stats every minute

Transaction Pooling Constraints

Transaction-mode pooling has limitations: features requiring session state (prepared statements, SET commands, LISTEN/NOTIFY, session variables) may not work correctly since the underlying connection changes between transactions. Design applications accordingly or use session-mode pooling for these features.

Monitoring Pool Health

Pool sizing isn't a one-time decision—it requires ongoing monitoring and adjustment. The right metrics tell you whether your pool is sized correctly and when adjustments are needed.

Essential Pool Metrics
Metric	What It Measures	Healthy Range	Action If Unhealthy
Pool Utilization %	Active / Max connections	40-80%	< 40%: Reduce max; > 80%: Increase max or optimize queries
Wait Time	Time waiting for connection	< 1ms average	10ms: Pool too small or queries too slow
Wait Queue Depth	Requests waiting for connections	Usually 0	Persistent queue: Pool too small for load
Connection Age	Age of oldest connection	< maxLifetime	Old connections: Check lifecycle management
Creation Rate	New connections per minute	Low, stable	High rate: Connections dying prematurely
Timeout Rate	Acquire timeouts per minute	0	Any timeouts: Pool exhausted or database down

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
/**
 * Pool Metrics Collection
 * 
 * Comprehensive metrics for pool health monitoring.
 * Compatible with Prometheus, StatsD, or any metrics system.
 */
 
interface PoolMetrics {
    // Current state
    activeConnections: number;
    idleConnections: number;
    maxConnections: number;
    waitingRequests: number;
    
    // Rates (per minute)
    acquisitionsPerMinute: number;
    releasesPerMinute: number;
    creationsPerMinute: number;
    destructionsPerMinute: number;
    timeoutsPerMinute: number;
    
    // Latencies (milliseconds)
    avgWaitTimeMs: number;
    p99WaitTimeMs: number;
    maxWaitTimeMs: number;
    
    // Health indicators
    utilizationPercent: number;
    connectionAgeMaxSeconds: number;
}
 
class InstrumentedPool {
    private metrics: PoolMetrics;
    private waitTimeHistogram: number[] = [];
    private acquisitionCounter = 0;
    private creationCounter = 0;
    private lastMetricsClear = Date.now();
    
    /**
     * Record wait time when connection is acquired
     */
    async acquire(): Promise<Connection> {
        const waitStart = Date.now();
        this.metrics.waitingRequests++;
        
        try {
            const connection = await this.doAcquire();
            
            const waitTime = Date.now() - waitStart;
            this.recordWaitTime(waitTime);
            
            this.acquisitionCounter++;
            
            return connection;
        } finally {
            this.metrics.waitingRequests--;
        }
    }
    
    /**
     * Get current metrics snapshot
     */
    getMetrics(): PoolMetrics {
        const elapsed = (Date.now() - this.lastMetricsClear) / 60000; // Minutes
        
        return {
            activeConnections: this.inUse.size,
            idleConnections: this.available.length,
            maxConnections: this.config.maxConnections,
            waitingRequests: this.metrics.waitingRequests,
            
            acquisitionsPerMinute: this.acquisitionCounter / elapsed,
            releasesPerMinute: this.releaseCounter / elapsed,
            creationsPerMinute: this.creationCounter / elapsed,
            destructionsPerMinute: this.destructionCounter / elapsed,
            timeoutsPerMinute: this.timeoutCounter / elapsed,
            
            avgWaitTimeMs: this.calculateAverage(this.waitTimeHistogram),
            p99WaitTimeMs: this.calculatePercentile(this.waitTimeHistogram, 99),
            maxWaitTimeMs: Math.max(...this.waitTimeHistogram),
            
            utilizationPercent: (this.inUse.size / this.config.maxConnections) * 100,
            connectionAgeMaxSeconds: this.getOldestConnectionAge(),
        };
    }
    
    /**
     * Export metrics in Prometheus format
     */
    getPrometheusMetrics(): string {
        const m = this.getMetrics();
        const prefix = 'db_pool';
        
        return `
# HELP ${prefix}_connections_active Currently active connections
# TYPE ${prefix}_connections_active gauge
${prefix}_connections_active ${m.activeConnections}
 
# HELP ${prefix}_connections_idle Currently idle connections
# TYPE ${prefix}_connections_idle gauge
${prefix}_connections_idle ${m.idleConnections}
 
# HELP ${prefix}_connections_max Maximum pool size
# TYPE ${prefix}_connections_max gauge
${prefix}_connections_max ${m.maxConnections}
 
# HELP ${prefix}_waiting_requests Requests waiting for connection
# TYPE ${prefix}_waiting_requests gauge
${prefix}_waiting_requests ${m.waitingRequests}
 
# HELP ${prefix}_utilization_percent Pool utilization percentage
# TYPE ${prefix}_utilization_percent gauge
${prefix}_utilization_percent ${m.utilizationPercent}
 
# HELP ${prefix}_wait_time_milliseconds Connection wait time distribution
# TYPE ${prefix}_wait_time_milliseconds histogram
${prefix}_wait_time_milliseconds{quantile="0.5"} ${this.calculatePercentile(this.waitTimeHistogram, 50)}
${prefix}_wait_time_milliseconds{quantile="0.99"} ${m.p99WaitTimeMs}
 
# HELP ${prefix}_acquisitions_total Total connection acquisitions
# TYPE ${prefix}_acquisitions_total counter
${prefix}_acquisitions_total ${this.acquisitionCounter}
 
# HELP ${prefix}_timeouts_total Total acquisition timeouts
# TYPE ${prefix}_timeouts_total counter
${prefix}_timeouts_total ${this.timeoutCounter}
        `.trim();
    }
}
 
// Alert thresholds
const alertThresholds = {
    // Utilization > 90% for 5 minutes
    highUtilization: {
        condition: (m: PoolMetrics) => m.utilizationPercent > 90,
        message: 'Pool utilization critically high',
        severity: 'warning'
    },
    
    // Any timeouts
    connectionTimeouts: {
        condition: (m: PoolMetrics) => m.timeoutsPerMinute > 0,
        message: 'Connection timeouts occurring',
        severity: 'critical'
    },
    
    // Wait time > 100ms P99
    highWaitTime: {
        condition: (m: PoolMetrics) => m.p99WaitTimeMs > 100,
        message: 'High connection wait times',
        severity: 'warning'
    },
    
    // High creation rate (indicates connection churn)
    highChurn: {
        condition: (m: PoolMetrics) => m.creationsPerMinute > 10,
        message: 'High connection creation rate - possible instability',
        severity: 'warning'
    }
};

Summary: Pool Size Management

We've explored the science and art of pool sizing. Let's consolidate the key insights:

Key Takeaways

•Use Little's Law as baseline — Connections = request_rate × hold_time. Add 30-50% headroom for variance, but don't oversize.
•Respect database limits — Calculate your connection budget considering max_connections, memory constraints, and other consumers.
•Smaller pools often perform better — The formula (cores × 2) + spindles provides surprisingly small but optimal connection counts.
•Static sizing is often best — Unless workload varies dramatically, fixed pools at P99 size are simpler and equally effective.
•Connection proxies enable scale — When applications need more connections than databases handle efficiently, use PgBouncer or similar.
•Monitor continuously — Pool metrics (utilization, wait time, timeouts) tell you if sizing is correct and when adjustments are needed.

What's next: With pool sizing understood, we'll complete our connection pooling journey by exploring how to return connections to the pool safely. The next page covers connection release patterns, cleanup procedures, and ensuring connections are properly reset between uses.

Page Complete

You now understand pool size management: the mathematics of sizing, database constraints, static vs dynamic strategies, the counter-intuitive value of small pools, connection proxies for scale, and metrics for ongoing optimization. Next, we'll tackle the final piece: safely returning connections to the pool.

3 / 4

L = λ × W Where: L = Average number of items in the system (connections in use) λ = Arrival rate (requests per second requiring connections) W = Average time in system (connection hold time per request)

Applying Little's Law to Connection Pools

Request Rate

Avg Connection Hold Time

Required Connections

Practical Recommendation

100 req/s

10ms (0.01s)

100 × 0.01 = 1

5-10 (headroom for variance)

100 req/s

100ms (0.1s)

100 × 0.1 = 10

15-20 (headroom for spikes)

500 req/s

50ms (0.05s)

500 × 0.05 = 25

35-50 (buffer for bursts)

1000 req/s

100ms (0.1s)

1000 × 0.1 = 100

120-150 (significant scale)

100 req/s

500ms (0.5s)

100 × 0.5 = 50

60-80 (long queries need buffer)

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

/** * Pool Size Calculator * * Uses queueing theory to calculate recommended pool sizes * for different scenarios and confidence levels. */ interface WorkloadCharacteristics { // Request rates averageRequestsPerSecond: number; peakRequestsPerSecond: number; // e.g., 2x average // Connection hold times (in milliseconds) averageHoldTimeMs: number; p99HoldTimeMs: number; maxHoldTimeMs: number; // Concurrency applicationInstances: number; } interface DatabaseConstraints { maxConnections: number; // Database max_connections setting connectionMemoryMb: number; // Memory per connection availableMemoryMb: number; // Total database memory reservedForAdminConnections: number; // Keep some connections for admin } interface PoolSizeRecommendation { minConnections: number; maxConnections: number; rationale: string[]; } function calculatePoolSize( workload: WorkloadCharacteristics, constraints: DatabaseConstraints ): PoolSizeRecommendation { const rationale: string[] = []; // 1. Little's Law baseline (per instance) const perInstanceRps = workload.averageRequestsPerSecond / workload.applicationInstances; const avgHoldSec = workload.averageHoldTimeMs / 1000; const littlesLawMin = Math.ceil(perInstanceRps * avgHoldSec); rationale.push( `Little's Law baseline: ${perInstanceRps} req/s × ${avgHoldSec}s = ${littlesLawMin} connections` ); // 2. P99 scenario (handles 99% of situations) const peakPerInstance = workload.peakRequestsPerSecond / workload.applicationInstances; const p99HoldSec = workload.p99HoldTimeMs / 1000; const p99Connections = Math.ceil(peakPerInstance * p99HoldSec); rationale.push( `P99 scenario: ${peakPerInstance} peak req/s × ${p99HoldSec}s P99 = ${p99Connections} connections` ); // 3. Database constraint (connections available per instance) const availableForApp = constraints.maxConnections - constraints.reservedForAdminConnections; const maxPerInstance = Math.floor(availableForApp / workload.applicationInstances); rationale.push( `Database limit: (${constraints.maxConnections} - ${constraints.reservedForAdminConnections}) / ${workload.applicationInstances} = ${maxPerInstance} max` ); // 4. Memory constraint const memoryLimitedConnections = Math.floor( constraints.availableMemoryMb / constraints.connectionMemoryMb ); const memoryMaxPerInstance = Math.floor(memoryLimitedConnections / workload.applicationInstances); rationale.push( `Memory limit: ${constraints.availableMemoryMb}MB / ${constraints.connectionMemoryMb}MB per conn = ${memoryLimitedConnections} total` ); // 5. Calculate recommendation // Min: Baseline + 50% buffer, but at least 5 const calculatedMin = Math.max(5, Math.ceil(littlesLawMin * 1.5)); // Max: P99 + 20% buffer, but constrained by database/memory limits const calculatedMax = Math.ceil(p99Connections * 1.2); const constrainedMax = Math.min(calculatedMax, maxPerInstance, memoryMaxPerInstance); // Ensure max >= min const finalMax = Math.max(constrainedMax, calculatedMin); rationale.push( `Recommendation: min=${calculatedMin} (baseline+50%), max=${finalMax} (P99+20%, constrained)` ); return { minConnections: calculatedMin, maxConnections: finalMax, rationale }; } // Example calculation const workload: WorkloadCharacteristics = { averageRequestsPerSecond: 500, peakRequestsPerSecond: 1000, averageHoldTimeMs: 50, p99HoldTimeMs: 200, maxHoldTimeMs: 1000, applicationInstances: 10 }; const constraints: DatabaseConstraints = { maxConnections: 500, connectionMemoryMb: 10, // PostgreSQL default availableMemoryMb: 4096, reservedForAdminConnections: 20 }; const recommendation = calculatePoolSize(workload, constraints); // Result: // { // minConnections: 8, // ceil(50 req/s × 0.05s × 1.5) // maxConnections: 24, // min(24 calculated, 48 db limit, 40 memory limit) // rationale: [...] // }

Memory Per Connection by Database

Database

Memory Per Connection

Default max_connections

Notes

PostgreSQL

~10MB default (work_mem, shared_buffers)

100

Can increase with complex queries

MySQL/InnoDB

~256KB-1MB base

151

Grows with buffer pool settings

Oracle

~1-5MB (PGA per session)

Varies by edition

Shared server mode reduces memory

SQL Server

~1-2MB minimum

32,767 (theoretical)

Memory pressure affects performance before limit

MongoDB

~1MB per connection

Unlimited (practical ~65,000)

Connection storms cause issues well before limit

Total Application Connections = Pool Max × Application Instances Connection Budget: Database max_connections: 500 - Reserved for admin/monitoring: 20 - Reserved for replication: 10 - Reserved for PgBouncer/proxy: 10 = Available for applications: 460 10 application instances = 46 max connections per instance

100

101

102

103

104

105

/** * Connection Budget Calculator * * Plans pool sizes considering all consumers of database connections. */ interface ConnectionConsumer { name: string; instances: number; maxConnectionsPerInstance: number; priority: 'critical' | 'high' | 'normal' | 'low'; } interface ConnectionBudget { databaseMaxConnections: number; reservedForAdmin: number; reservedForReplication: number; reservedForProxies: number; consumers: ConnectionConsumer[]; } interface BudgetAllocation { consumer: string; requestedTotal: number; allocated: number; perInstance: number; satisfied: boolean; } function allocateConnectionBudget(budget: ConnectionBudget): BudgetAllocation[] { // Calculate available connections const available = budget.databaseMaxConnections - budget.reservedForAdmin - budget.reservedForReplication - budget.reservedForProxies; console.log(`Total available for applications: ${available}`); // Calculate total requested const totalRequested = budget.consumers.reduce( (sum, c) => sum + (c.instances * c.maxConnectionsPerInstance), 0 ); if (totalRequested <= available) { // Everyone gets what they want return budget.consumers.map(c => ({ consumer: c.name, requestedTotal: c.instances * c.maxConnectionsPerInstance, allocated: c.instances * c.maxConnectionsPerInstance, perInstance: c.maxConnectionsPerInstance, satisfied: true })); } // Need to allocate proportionally with priority weighting const priorityWeights = { critical: 4, high: 2, normal: 1, low: 0.5 }; // Calculate weighted shares const weightedRequests = budget.consumers.map(c => ({ ...c, requested: c.instances * c.maxConnectionsPerInstance, weight: priorityWeights[c.priority] })); const totalWeight = weightedRequests.reduce( (sum, r) => sum + (r.requested * r.weight), 0 ); // Allocate based on weighted proportion return weightedRequests.map(c => { const weightedShare = (c.requested * c.weight) / totalWeight; const allocated = Math.floor(available * weightedShare); const perInstance = Math.floor(allocated / c.instances); return { consumer: c.name, requestedTotal: c.requested, allocated: perInstance * c.instances, // Round to instance boundaries perInstance, satisfied: perInstance >= c.maxConnectionsPerInstance }; }); } // Example: Multi-service architecture const budget: ConnectionBudget = { databaseMaxConnections: 500, reservedForAdmin: 20, reservedForReplication: 10, reservedForProxies: 20, consumers: [ { name: 'api-gateway', instances: 20, maxConnectionsPerInstance: 30, priority: 'critical' }, { name: 'order-service', instances: 10, maxConnectionsPerInstance: 20, priority: 'high' }, { name: 'analytics', instances: 5, maxConnectionsPerInstance: 15, priority: 'normal' }, { name: 'batch-jobs', instances: 3, maxConnectionsPerInstance: 10, priority: 'low' } ] }; const allocations = allocateConnectionBudget(budget); // Shows how to distribute limited connection capacity across services

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

/** * Dynamic Pool Sizing Algorithm * * Adjusts pool size based on observed utilization patterns. * Uses moving averages to smooth out temporary spikes. */ interface DynamicSizingConfig { minimumPoolSize: number; // Never go below this maximumPoolSize: number; // Never exceed this targetUtilization: number; // Aim for this % of pool in use (0.6-0.8) scaleUpThreshold: number; // Scale up when utilization exceeds (0.8-0.9) scaleDownThreshold: number; // Scale down when utilization below (0.3-0.5) evaluationWindowMs: number; // How long to observe before deciding scaleUpIncrement: number; // Connections to add when scaling up scaleDownIncrement: number; // Connections to remove when scaling down cooldownPeriodMs: number; // Minimum time between resize operations } class DynamicallySizedPool { private config: DynamicSizingConfig; private currentMaxSize: number; private utilizationHistory: number[] = []; private lastResizeTime: number = 0; constructor(config: DynamicSizingConfig) { this.config = config; this.currentMaxSize = config.minimumPoolSize; } /** * Record current utilization * Called periodically (e.g., every second) */ recordUtilization(): void { const utilization = this.inUse.size / this.currentMaxSize; this.utilizationHistory.push(utilization); // Keep only recent history const maxSamples = this.config.evaluationWindowMs / 1000; if (this.utilizationHistory.length > maxSamples) { this.utilizationHistory.shift(); } } /** * Evaluate and potentially resize * Called periodically after utilization recording */ evaluateResize(): void { // Respect cooldown period if (Date.now() - this.lastResizeTime < this.config.cooldownPeriodMs) { return; } // Need enough history for decision const minSamples = this.config.evaluationWindowMs / 2000; if (this.utilizationHistory.length < minSamples) { return; } // Calculate average utilization const avgUtilization = this.utilizationHistory.reduce((a, b) => a + b, 0) / this.utilizationHistory.length; // Calculate P95 utilization (for spike detection) const sorted = [...this.utilizationHistory].sort((a, b) => a - b); const p95Index = Math.floor(sorted.length * 0.95); const p95Utilization = sorted[p95Index]; // Decision logic if (p95Utilization > this.config.scaleUpThreshold) { // High utilization sustained - scale up this.scaleUp(); } else if (avgUtilization < this.config.scaleDownThreshold) { // Low utilization sustained - scale down this.scaleDown(); } } private scaleUp(): void { const newMax = Math.min( this.currentMaxSize + this.config.scaleUpIncrement, this.config.maximumPoolSize ); if (newMax > this.currentMaxSize) { console.log( `Scaling pool up: ${this.currentMaxSize} → ${newMax} ` + `(utilization: ${this.getAvgUtilization().toFixed(2)})` ); this.currentMaxSize = newMax; this.lastResizeTime = Date.now(); this.utilizationHistory = []; // Reset history after resize } } private scaleDown(): void { // Don't scale below current usage const currentlyInUse = this.inUse.size; const safeMin = Math.max( this.config.minimumPoolSize, currentlyInUse + 2 // Buffer above current use ); const newMax = Math.max( this.currentMaxSize - this.config.scaleDownIncrement, safeMin ); if (newMax < this.currentMaxSize) { console.log( `Scaling pool down: ${this.currentMaxSize} → ${newMax} ` + `(utilization: ${this.getAvgUtilization().toFixed(2)})` ); // Close excess idle connections this.trimIdleConnections(newMax); this.currentMaxSize = newMax; this.lastResizeTime = Date.now(); this.utilizationHistory = []; } } private trimIdleConnections(targetSize: number): void { while (this.available.length + this.inUse.size > targetSize && this.available.length > 0) { const conn = this.available.pop(); if (conn) { conn.close().catch(console.error); } } } } // Recommended configuration const dynamicConfig: DynamicSizingConfig = { minimumPoolSize: 10, maximumPoolSize: 50, targetUtilization: 0.7, // Aim for 70% usage scaleUpThreshold: 0.85, // Scale up if consistently >85% scaleDownThreshold: 0.4, // Scale down if consistently <40% evaluationWindowMs: 60000, // Evaluate over 1 minute scaleUpIncrement: 5, // Add 5 connections at a time scaleDownIncrement: 2, // Remove 2 at a time (more conservative) cooldownPeriodMs: 120000 // 2 minutes between resize operations };

connections = ((core_count × 2) + effective_spindle_count) Where: core_count = Number of CPU cores on database server effective_spindle_count = Number of disks in RAID array (for SSD, use 1 per NVME device)

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

/** * Optimal Pool Size Estimator * * Based on database server hardware characteristics, * estimates the maximum effective connections. */ interface DatabaseServerSpec { cpuCores: number; storageType: 'hdd' | 'ssd' | 'nvme'; storageDevices: number; // Number of drives/devices availableMemoryGb: number; memoryPerConnectionMb: number; } interface ClusterSpec { databaseServers: DatabaseServerSpec[]; applicationInstances: number; includesProxy: boolean; // PgBouncer, ProxySQL, etc. proxyMaxConnections?: number; } function estimateOptimalConnections(server: DatabaseServerSpec): { formulaBased: number; memoryConstrained: number; recommended: number; } { // Formula-based estimate let effectiveSpindles: number; switch (server.storageType) { case 'hdd': effectiveSpindles = server.storageDevices; break; case 'ssd': effectiveSpindles = server.storageDevices * 4; // SSDs support more parallel ops break; case 'nvme': effectiveSpindles = server.storageDevices * 8; // NVMe even more parallel break; } const formulaBased = (server.cpuCores * 2) + effectiveSpindles; // Memory-constrained estimate const totalMemoryMb = server.availableMemoryGb * 1024; const memoryForConnections = totalMemoryMb * 0.25; // Reserve 25% for connections const memoryConstrained = Math.floor(memoryForConnections / server.memoryPerConnectionMb); // Recommended is the lower of formula and memory limits const recommended = Math.min(formulaBased, memoryConstrained); return { formulaBased, memoryConstrained, recommended }; } function calculateClusterPoolSizes(cluster: ClusterSpec): { totalDatabaseConnections: number; perInstancePoolSize: number; reasoning: string[]; } { const reasoning: string[] = []; // Calculate optimal connections per database server let totalOptimal = 0; for (const server of cluster.databaseServers) { const estimate = estimateOptimalConnections(server); totalOptimal += estimate.recommended; reasoning.push( `Server (${server.cpuCores} cores, ${server.storageType}): ` + `${estimate.recommended} connections optimal` ); } reasoning.push(`Total cluster optimal connections: ${totalOptimal}`); // Reserve for admin/monitoring const reservedConnections = Math.ceil(totalOptimal * 0.1); const availableForApps = totalOptimal - reservedConnections; reasoning.push(`After reserving 10% for admin: ${availableForApps} available`); // Divide among application instances let perInstance = Math.floor(availableForApps / cluster.applicationInstances); // If using proxy, different calculation if (cluster.includesProxy && cluster.proxyMaxConnections) { // Proxy multiplexes - apps can have more connections than database perInstance = Math.floor(cluster.proxyMaxConnections / cluster.applicationInstances); reasoning.push( `With proxy multiplexing: ${perInstance} connections per application instance` ); } else { reasoning.push( `Per application instance: ${perInstance} connections` ); } return { totalDatabaseConnections: totalOptimal, perInstancePoolSize: perInstance, reasoning }; } // Example: Typical production cluster const cluster: ClusterSpec = { databaseServers: [ { cpuCores: 16, storageType: 'nvme', storageDevices: 2, availableMemoryGb: 64, memoryPerConnectionMb: 10 }, ], applicationInstances: 10, includesProxy: false }; const sizing = calculateClusterPoolSizes(cluster); // Result might suggest: 48 connections per instance (480 total) // Much less than naive "set to 200" approach!

Connection Proxy Options

Proxy

Database Support

Key Features

Multiplexing Mode

PgBouncer

PostgreSQL only

Lightweight, battle-tested, simple configuration

Transaction, Session, Statement pooling

ProxySQL

MySQL, MariaDB

Query routing, caching, connection multiplexing

Connection pooling with query routing

Amazon RDS Proxy

PostgreSQL, MySQL

Managed service, IAM integration, auto-failover

Transparent connection pooling

PgCat

PostgreSQL

Modern, written in Rust, sharding support

Transaction pooling, load balancing

Odyssey

PostgreSQL

Multi-threaded, connection management

Transaction pooling

┌─────────────────────────────────────────────────────────────────┐ │ CONNECTION MULTIPLEXING │ ├─────────────────────────────────────────────────────────────────┤ │ │ │ Application Layer Proxy Layer Database │ │ │ │ App 1 [Pool: 50] ─┐ ┌──────┐ │ │ App 2 [Pool: 50] ─┤ │ │ │ │ App 3 [Pool: 50] ─┤ ┌───────────────┐ │ │ │ │ App 4 [Pool: 50] ─┼────▶│ PgBouncer │──────▶│ DB │ │ │ App 5 [Pool: 50] ─┤ │ [100 conns] │ │ │ │ │ ... │ └───────────────┘ │ │ │ │ App 10 [Pool: 50]─┘ │ │ │ │ └──────┘ │ │ Total: 500 app Multiplexed: Sees: 100 │ │ connections transaction-level connections │ │ │ └─────────────────────────────────────────────────────────────────┘

# PgBouncer Configuration (pgbouncer.ini) [databases] # Define backend database connections mydb = host=db-primary.internal port=5432 dbname=production [pgbouncer] # Listen for incoming connections listen_addr = 0.0.0.0 listen_port = 6432 # Authentication auth_type = md5 auth_file = /etc/pgbouncer/userlist.txt # Pool mode determines when connections are returned to pool: # - session: Connection is held for entire session (like no pooling) # - transaction: Connection returned after each transaction (most common) # - statement: Connection returned after each statement (most aggressive) pool_mode = transaction # Pool size limits default_pool_size = 100 # Connections per user/database pair max_client_conn = 1000 # Total client connections allowed min_pool_size = 10 # Minimum connections to maintain # Connection lifetime server_lifetime = 3600 # Close connections older than 1 hour server_idle_timeout = 600 # Close idle connections after 10 min # Timeouts server_connect_timeout = 15 # Timeout connecting to PostgreSQL server_login_retry = 3 # Retries on connect failure # Query handling in transaction mode # IMPORTANT: Set these for transaction pooling server_reset_query = DISCARD ALL # Reset connection state between uses # Logging log_connections = 1 log_disconnections = 1 log_pooler_errors = 1 # Statistics stats_period = 60 # Log stats every minute

Essential Pool Metrics

Metric

What It Measures

Healthy Range

Action If Unhealthy

Pool Utilization %

Active / Max connections

40-80%

< 40%: Reduce max; > 80%: Increase max or optimize queries

Wait Time

Time waiting for connection

< 1ms average

10ms: Pool too small or queries too slow

Wait Queue Depth

Requests waiting for connections

Usually 0

Persistent queue: Pool too small for load

Connection Age

Age of oldest connection

< maxLifetime

Old connections: Check lifecycle management

Creation Rate

New connections per minute

Low, stable

High rate: Connections dying prematurely

Timeout Rate

Acquire timeouts per minute

Any timeouts: Pool exhausted or database down

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

/** * Pool Metrics Collection * * Comprehensive metrics for pool health monitoring. * Compatible with Prometheus, StatsD, or any metrics system. */ interface PoolMetrics { // Current state activeConnections: number; idleConnections: number; maxConnections: number; waitingRequests: number; // Rates (per minute) acquisitionsPerMinute: number; releasesPerMinute: number; creationsPerMinute: number; destructionsPerMinute: number; timeoutsPerMinute: number; // Latencies (milliseconds) avgWaitTimeMs: number; p99WaitTimeMs: number; maxWaitTimeMs: number; // Health indicators utilizationPercent: number; connectionAgeMaxSeconds: number; } class InstrumentedPool { private metrics: PoolMetrics; private waitTimeHistogram: number[] = []; private acquisitionCounter = 0; private creationCounter = 0; private lastMetricsClear = Date.now(); /** * Record wait time when connection is acquired */ async acquire(): Promise<Connection> { const waitStart = Date.now(); this.metrics.waitingRequests++; try { const connection = await this.doAcquire(); const waitTime = Date.now() - waitStart; this.recordWaitTime(waitTime); this.acquisitionCounter++; return connection; } finally { this.metrics.waitingRequests--; } } /** * Get current metrics snapshot */ getMetrics(): PoolMetrics { const elapsed = (Date.now() - this.lastMetricsClear) / 60000; // Minutes return { activeConnections: this.inUse.size, idleConnections: this.available.length, maxConnections: this.config.maxConnections, waitingRequests: this.metrics.waitingRequests, acquisitionsPerMinute: this.acquisitionCounter / elapsed, releasesPerMinute: this.releaseCounter / elapsed, creationsPerMinute: this.creationCounter / elapsed, destructionsPerMinute: this.destructionCounter / elapsed, timeoutsPerMinute: this.timeoutCounter / elapsed, avgWaitTimeMs: this.calculateAverage(this.waitTimeHistogram), p99WaitTimeMs: this.calculatePercentile(this.waitTimeHistogram, 99), maxWaitTimeMs: Math.max(...this.waitTimeHistogram), utilizationPercent: (this.inUse.size / this.config.maxConnections) * 100, connectionAgeMaxSeconds: this.getOldestConnectionAge(), }; } /** * Export metrics in Prometheus format */ getPrometheusMetrics(): string { const m = this.getMetrics(); const prefix = 'db_pool'; return ` # HELP ${prefix}_connections_active Currently active connections # TYPE ${prefix}_connections_active gauge ${prefix}_connections_active ${m.activeConnections} # HELP ${prefix}_connections_idle Currently idle connections # TYPE ${prefix}_connections_idle gauge ${prefix}_connections_idle ${m.idleConnections} # HELP ${prefix}_connections_max Maximum pool size # TYPE ${prefix}_connections_max gauge ${prefix}_connections_max ${m.maxConnections} # HELP ${prefix}_waiting_requests Requests waiting for connection # TYPE ${prefix}_waiting_requests gauge ${prefix}_waiting_requests ${m.waitingRequests} # HELP ${prefix}_utilization_percent Pool utilization percentage # TYPE ${prefix}_utilization_percent gauge ${prefix}_utilization_percent ${m.utilizationPercent} # HELP ${prefix}_wait_time_milliseconds Connection wait time distribution # TYPE ${prefix}_wait_time_milliseconds histogram ${prefix}_wait_time_milliseconds{quantile="0.5"} ${this.calculatePercentile(this.waitTimeHistogram, 50)} ${prefix}_wait_time_milliseconds{quantile="0.99"} ${m.p99WaitTimeMs} # HELP ${prefix}_acquisitions_total Total connection acquisitions # TYPE ${prefix}_acquisitions_total counter ${prefix}_acquisitions_total ${this.acquisitionCounter} # HELP ${prefix}_timeouts_total Total acquisition timeouts # TYPE ${prefix}_timeouts_total counter ${prefix}_timeouts_total ${this.timeoutCounter} `.trim(); } } // Alert thresholds const alertThresholds = { // Utilization > 90% for 5 minutes highUtilization: { condition: (m: PoolMetrics) => m.utilizationPercent > 90, message: 'Pool utilization critically high', severity: 'warning' }, // Any timeouts connectionTimeouts: { condition: (m: PoolMetrics) => m.timeoutsPerMinute > 0, message: 'Connection timeouts occurring', severity: 'critical' }, // Wait time > 100ms P99 highWaitTime: { condition: (m: PoolMetrics) => m.p99WaitTimeMs > 100, message: 'High connection wait times', severity: 'warning' }, // High creation rate (indicates connection churn) highChurn: { condition: (m: PoolMetrics) => m.creationsPerMinute > 10, message: 'High connection creation rate - possible instability', severity: 'warning' } };

Pool Size Management

The Goldilocks Problem

The Mathematics of Pool Sizing

Database-Side Connection Limits

Static vs Dynamic Sizing Strategies

The Small Pool Counter-Intuition

Connection Proxies and Multiplexing

Monitoring Pool Health

Summary: Pool Size Management

There’s more to understand.

Pool Size Management

The Goldilocks Problem

The Mathematics of Pool Sizing

Database-Side Connection Limits

Static vs Dynamic Sizing Strategies

The Small Pool Counter-Intuition

Connection Proxies and Multiplexing

Monitoring Pool Health

Summary: Pool Size Management

There’s more to understand.