Add connection pool configuration options

[sgaunet]

Problem

The database connection in internal/app/client.go:26 is opened without configuring connection pool settings, using PostgreSQL driver defaults which may not be optimal for all use cases.

Current implementation:

func NewPostgreSQLClient(connectionString string) (*PostgreSQLClientImpl, error) {
    db, err := sql.Open("postgres", connectionString)
    if err != nil {
        return nil, fmt.Errorf("failed to open database: %w", err)
    }
    
    // No pool configuration!
    
    return &PostgreSQLClientImpl{
        db:               db,
        connectionString: connectionString,
    }, nil
}

Issues

Without explicit pool configuration:

1. Unknown defaults: Behavior depends on database/sql package defaults
2. No tuning: Can't optimize for high-concurrency or low-resource scenarios
3. Resource exhaustion: May create too many connections or hold too many idle connections
4. No timeouts: Connection lifetime and idle timeout not controlled

Default Behavior (database/sql)

From Go's database/sql:

• MaxOpenConns: Unlimited (can exhaust database connections)
• MaxIdleConns: 2 (may be too low for high-concurrency)
• ConnMaxLifetime: Unlimited (connections never recycled)
• ConnMaxIdleTime: Unlimited (idle connections held forever)

Proposed Solution

Add configurable connection pool settings:

type PoolConfig struct {
    MaxOpenConns    int           // Maximum open connections (default: 10)
    MaxIdleConns    int           // Maximum idle connections (default: 5)
    ConnMaxLifetime time.Duration // Maximum connection lifetime (default: 1h)
    ConnMaxIdleTime time.Duration // Maximum idle time (default: 10m)
}

func NewPostgreSQLClient(connectionString string, poolConfig *PoolConfig) (*PostgreSQLClientImpl, error) {
    db, err := sql.Open("postgres", connectionString)
    if err != nil {
        return nil, fmt.Errorf("failed to open database: %w", err)
    }
    
    // Apply pool configuration with sensible defaults
    config := poolConfig
    if config == nil {
        config = &PoolConfig{
            MaxOpenConns:    10,
            MaxIdleConns:    5,
            ConnMaxLifetime: 1 * time.Hour,
            ConnMaxIdleTime: 10 * time.Minute,
        }
    }
    
    db.SetMaxOpenConns(config.MaxOpenConns)
    db.SetMaxIdleConns(config.MaxIdleConns)
    db.SetConnMaxLifetime(config.ConnMaxLifetime)
    db.SetConnMaxIdleTime(config.ConnMaxIdleTime)
    
    return &PostgreSQLClientImpl{
        db:               db,
        connectionString: connectionString,
    }, nil
}

Environment Variable Configuration

Support environment-based configuration:

POSTGRES_MCP_MAX_OPEN_CONNS=20
POSTGRES_MCP_MAX_IDLE_CONNS=10
POSTGRES_MCP_CONN_MAX_LIFETIME=3600  # seconds
POSTGRES_MCP_CONN_MAX_IDLE_TIME=600  # seconds

Recommended Defaults

For MCP server use case (moderate concurrency):

• MaxOpenConns: 10 (limit concurrent database operations)
• MaxIdleConns: 5 (keep some connections warm)
• ConnMaxLifetime: 1 hour (recycle connections periodically)
• ConnMaxIdleTime: 10 minutes (release idle connections)

Impact

• Severity: MEDIUM
• Type: Enhancement
• Location: internal/app/client.go:26
• Benefits: Better resource management, predictable performance

Checklist

• Add PoolConfig struct for connection pool settings
• Implement pool configuration in NewPostgreSQLClient
• Add environment variable support for pool settings
• Set sensible defaults for MCP use case
• Update App.New() to pass pool configuration
• Add documentation for pool configuration in README
• Add tests verifying pool settings are applied correctly
• Consider adding pool metrics/monitoring