dotnet-cqrs/docs/event-streaming/storage/connection-pooling.md

# Connection Pooling

Optimizing PostgreSQL connection pooling for event streaming.

## Overview

Connection pooling is critical for PostgreSQL performance in event streaming scenarios. Npgsql (the .NET PostgreSQL driver) provides built-in connection pooling that significantly improves throughput and reduces latency.

## Default Pooling Behavior

Npgsql enables connection pooling by default:

```csharp
// Connection pooling enabled automatically
var connectionString = "Host=localhost;Database=eventstore;Username=postgres;Password=postgres";

builder.Services.AddPostgresEventStreaming(connectionString);

// Pool created automatically with defaults:
// - Minimum Pool Size: 1
// - Maximum Pool Size: 100
// - Connection Idle Lifetime: 300 seconds
```

## Pool Configuration

### Connection String Parameters

| Parameter | Default | Description |
|-----------|---------|-------------|
| `Minimum Pool Size` | 1 | Minimum connections maintained |
| `Maximum Pool Size` | 100 | Maximum connections allowed |
| `Connection Idle Lifetime` | 300 | Seconds before idle connection closed |
| `Connection Pruning Interval` | 10 | Seconds between pruning checks |
| `Enlist` | true | Auto-enlist in TransactionScope |

### Basic Configuration

```csharp
var connectionString =
    "Host=localhost;" +
    "Database=eventstore;" +
    "Username=postgres;" +
    "Password=postgres;" +
    "Minimum Pool Size=10;" +      // Keep 10 warm connections
    "Maximum Pool Size=100";        // Allow up to 100 connections

builder.Services.AddPostgresEventStreaming(connectionString);
```

### High-Throughput Configuration

For high-traffic scenarios:

```csharp
var connectionString =
    "Host=localhost;Database=eventstore;Username=postgres;Password=postgres;" +
    "Minimum Pool Size=20;" +              // More warm connections
    "Maximum Pool Size=200;" +              // Higher ceiling
    "Connection Idle Lifetime=300;" +       // 5 minutes
    "Connection Pruning Interval=10;" +     // Check every 10 seconds
    "Command Timeout=30;" +                 // 30-second query timeout
    "Timeout=15";                           // 15-second connection timeout
```

### Low-Latency Configuration

For latency-sensitive applications:

```csharp
var connectionString =
    "Host=localhost;Database=eventstore;Username=postgres;Password=postgres;" +
    "Minimum Pool Size=50;" +              // Many warm connections
    "Maximum Pool Size=100;" +              // Limit overhead
    "Connection Idle Lifetime=600;" +       // Keep connections longer
    "No Reset On Close=true";               // Skip connection reset
```

## Sizing the Pool

### Calculate Pool Size

**Formula:**
```
Pool Size = (Number of Workers) × (Concurrent Operations per Worker) × 1.2
```

**Example:**
```
Workers: 4
Concurrent operations: 10
Pool size: 4 × 10 × 1.2 = 48

Use: Minimum Pool Size = 50, Maximum Pool Size = 100
```

### Per-Service Sizing

Different services may need different pool sizes:

```csharp
// Write-heavy service (event publishing)
services.AddPostgresEventStreaming(
    "Host=localhost;Database=eventstore;..." +
    "Minimum Pool Size=30;Maximum Pool Size=100");

// Read-heavy service (projections)
services.AddPostgresEventStreaming(
    "Host=localhost;Database=eventstore;..." +
    "Minimum Pool Size=50;Maximum Pool Size=200");
```

## Monitoring Connection Pool

### Log Pool Statistics

```csharp
using Npgsql;

// Get pool statistics
var stats = NpgsqlConnection.GetPoolStatistics();

foreach (var (connectionString, poolStats) in stats)
{
    _logger.LogInformation(
        "Pool stats: Total={Total}, Idle={Idle}, Busy={Busy}",
        poolStats.Total,
        poolStats.Idle,
        poolStats.Busy);
}
```

### Metrics Integration

```csharp
public class ConnectionPoolMetrics : BackgroundService
{
    protected override async Task ExecuteAsync(CancellationToken ct)
    {
        while (!ct.IsCancellationRequested)
        {
            var stats = NpgsqlConnection.GetPoolStatistics();

            foreach (var (_, poolStats) in stats)
            {
                _metrics.RecordGauge("postgres.pool.total", poolStats.Total);
                _metrics.RecordGauge("postgres.pool.idle", poolStats.Idle);
                _metrics.RecordGauge("postgres.pool.busy", poolStats.Busy);
            }

            await Task.Delay(TimeSpan.FromSeconds(10), ct);
        }
    }
}
```

## Best Practices

### ✅ DO

**1. Set appropriate minimum pool size:**
```csharp
// ✅ Good - Warm connections ready
"Minimum Pool Size=20;Maximum Pool Size=100"
```

**2. Use connection pooling:**
```csharp
// ✅ Good - Reuse connections
using var connection = new NpgsqlConnection(connectionString);
await connection.OpenAsync();
// Connection returned to pool on dispose
```

**3. Dispose connections properly:**
```csharp
// ✅ Good - using statement
using (var connection = new NpgsqlConnection(connectionString))
{
    await connection.OpenAsync();
    // Use connection
}  // Returned to pool

// Or
await using var connection = new NpgsqlConnection(connectionString);
```

**4. Set timeouts:**
```csharp
// ✅ Good - Prevent hung connections
"Command Timeout=30;Timeout=15"
```

**5. Monitor pool exhaustion:**
```csharp
try
{
    using var connection = new NpgsqlConnection(connectionString);
    await connection.OpenAsync();
}
catch (NpgsqlException ex) when (ex.Message.Contains("pool"))
{
    _logger.LogError("Connection pool exhausted!");
    _metrics.IncrementCounter("postgres.pool.exhausted");
}
```

### ❌ DON'T

**1. Don't create new connection per operation:**
```csharp
// ❌ Bad - Exhausts pool
for (int i = 0; i < 1000; i++)
{
    using var connection = new NpgsqlConnection(connectionString);
    await connection.OpenAsync();
    // Process
}  // 1000 connections created!
```

**2. Don't set pool size too low:**
```csharp
// ❌ Bad - Will bottleneck
"Maximum Pool Size=5"  // Too small for high throughput
```

**3. Don't forget to dispose:**
```csharp
// ❌ Bad - Connection leak
var connection = new NpgsqlConnection(connectionString);
await connection.OpenAsync();
// Never disposed - pool exhaustion!
```

**4. Don't disable pooling:**
```csharp
// ❌ Bad - Poor performance
"Pooling=false"  // Creates new connection every time
```

## Troubleshooting

### Pool Exhaustion

**Symptoms:**
```
Npgsql.NpgsqlException: The connection pool has been exhausted
```

**Solutions:**

1. **Increase pool size:**
```csharp
"Maximum Pool Size=200"  // Increase limit
```

2. **Fix connection leaks:**
```csharp
// Check for connections not being disposed
// Use using statements or await using
```

3. **Reduce concurrency:**
```csharp
// Limit parallel operations
var semaphore = new SemaphoreSlim(50);  // Max 50 concurrent

await semaphore.WaitAsync();
try
{
    // Database operation
}
finally
{
    semaphore.Release();
}
```

### Connection Timeouts

**Symptoms:**
```
Npgsql.NpgsqlException: Timeout during connection attempt
```

**Solutions:**

1. **Increase connection timeout:**
```csharp
"Timeout=30"  // 30 seconds to establish connection
```

2. **Increase minimum pool size:**
```csharp
"Minimum Pool Size=20"  // More warm connections
```

3. **Check database server load:**
```sql
-- Check active connections
SELECT COUNT(*) FROM pg_stat_activity;

-- Check slow queries
SELECT pid, now() - query_start as duration, query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY duration DESC;
```

### Slow Queries

**Symptoms:**
Commands take longer than expected.

**Solutions:**

1. **Set command timeout:**
```csharp
"Command Timeout=30"  // Cancel after 30 seconds
```

2. **Optimize queries:**
```sql
-- Add indexes
CREATE INDEX idx_events_stream_offset ON events(stream_name, offset);

-- Analyze query plans
EXPLAIN ANALYZE
SELECT * FROM events WHERE stream_name = 'orders' AND offset > 1000;
```

3. **Use read replicas:**
```csharp
// Write to primary
var writeConnectionString = "Host=primary.db;...";

// Read from replica
var readConnectionString = "Host=replica.db;...";
```

## Advanced Configuration

### Multiple Pools

Use different pools for different purposes:

```csharp
// Write pool
services.AddSingleton<IWriteEventStore>(sp =>
{
    var connectionString = "Host=primary;...;Maximum Pool Size=100";
    return new PostgresEventStreamStore(connectionString);
});

// Read pool
services.AddSingleton<IReadEventStore>(sp =>
{
    var connectionString = "Host=replica;...;Maximum Pool Size=200";
    return new PostgresEventStreamStore(connectionString);
});
```

### Connection Multiplexing

PostgreSQL supports connection multiplexing for commands:

```csharp
// Configure multiplexing
var connectionString =
    "Host=localhost;Database=eventstore;..." +
    "Multiplexing=true;" +              // Enable multiplexing
    "Maximum Pool Size=10";              // Fewer connections needed
```

**Benefits:**
- Fewer physical connections
- Better for serverless/container environments
- Reduced database connection overhead

**Limitations:**
- Not compatible with all features (transactions, LISTEN/NOTIFY)
- Higher latency for individual commands

## See Also

- [PostgreSQL Storage](postgresql-storage.md)
- [Database Schema](database-schema.md)
- [Performance Best Practices](../../best-practices/performance.md)
- [Npgsql Documentation](https://www.npgsql.org/doc/connection-string-parameters.html)