PostgreSQL Connection Pooling

[butschster]

Executive Summary

Problem Statement

RoadRunner currently spawns multiple PHP workers, each creating independent database connections to PostgreSQL. This architecture leads to:

• Connection explosion: 50 workers × 10 RoadRunner instances = 500 database connections
• Resource waste: Each idle PHP worker holds an idle database connection
• Performance overhead: Connection establishment takes 5-50ms per connection
• Database limits: PostgreSQL typically supports 100-200 connections before performance degrades
• No connection reuse: Workers cannot share connections during idle periods

Proposed Solution

Create a native RoadRunner plugin that acts as a PostgreSQL connection pooler and proxy. PHP workers connect to RoadRunner's local pool instead of directly to PostgreSQL. The plugin manages a shared pool of database connections, multiplexing requests from all workers through fewer backend connections.

Expected Impact

• 80% reduction in database connections (from 500 to ~100)
• 60% reduction in connection overhead latency
• Zero changes required in PHP application code
• Native integration with RoadRunner's lifecycle and monitoring

Technical Requirements

Core Functionality

Connection Pooling

• Shared connection pool accessible by all PHP workers within a RoadRunner instance
• Transaction-aware pooling that maintains transaction boundaries per worker
• Prepared statement caching across pooled connections
• Connection lifecycle management with automatic health checks and reconnection
• Multi-database support with separate pools per database

Connection Modes

1. Session Mode: Worker owns a connection for its entire session
2. Transaction Mode: Worker gets a connection only during active transactions
3. Statement Mode: Each statement can use any available connection (stateless)
4. Auto Mode: Plugin automatically selects optimal mode based on query patterns

Protocol Support

• Full PostgreSQL wire protocol v3 compatibility
• Support for all PostgreSQL data types including arrays, JSON, and custom types
• Extended query protocol for prepared statements
• COPY protocol for bulk data operations
• Notification/LISTEN support with proper routing to workers

Configuration Structure

# .rr.yaml configuration
pgpool:
  # Enable/disable the plugin
  enabled: true
  
  # Local proxy endpoint for PHP workers
  proxy:
    # Address where plugin listens for connections from PHP workers
    address: "tcp://127.0.0.1:5433"
    # Alternative Unix socket option
    # address: "unix:///var/run/rr-pgpool.sock"
    
    # Max concurrent client connections (PHP workers)
    max_clients: 100
    
    # Client connection timeout
    client_timeout: 30s
    
    # Authentication mode for PHP workers
    # Options: "none", "password", "trust"
    auth_mode: "trust"

  # PostgreSQL backend configuration
  backends:
    # Primary database configuration
    primary:
      # Connection string to actual PostgreSQL
      dsn: "postgres://user:pass@localhost:5432/dbname?sslmode=prefer"
      
      # Alternative structured configuration
      # host: localhost
      # port: 5432
      # database: dbname
      # user: username
      # password: password
      # sslmode: prefer
      
      # Connection pool settings
      pool:
        # Maximum connections to PostgreSQL
        max_connections: 25
        
        # Minimum idle connections to maintain
        min_connections: 5
        
        # Maximum connection lifetime
        max_lifetime: 1h
        
        # Maximum idle time before closing
        max_idle_time: 30m
        
        # How often to check connection health
        health_check_period: 30s
        
        # Connection acquisition timeout
        acquire_timeout: 5s
        
        # Queue size for waiting connection requests
        wait_queue_size: 100
        
      # Connection behavior
      behavior:
        # Pooling mode: "session", "transaction", "statement", "auto"
        mode: "transaction"
        
        # Reset connection state after each use
        reset_on_return: true
        
        # Lazy connection creation
        lazy_connect: true
        
        # Prepared statement cache size per connection
        statement_cache_size: 100
        
        # Query timeout (0 = no timeout)
        query_timeout: 30s

    # Read replica configuration (optional)
    replicas:
      - dsn: "postgres://user:pass@replica1:5432/dbname"
        pool:
          max_connections: 20
          min_connections: 2
        weight: 1
        
      - dsn: "postgres://user:pass@replica2:5432/dbname"
        pool:
          max_connections: 20
          min_connections: 2
        weight: 1

  # Routing configuration
  routing:
    # Enable read/write splitting
    read_write_split: true
    
    # Regex patterns for read-only queries
    read_patterns:
      - "^SELECT"
      - "^WITH .* SELECT"
      
    # Regex patterns for write queries  
    write_patterns:
      - "^INSERT"
      - "^UPDATE"
      - "^DELETE"
      - "^ALTER"
      - "^CREATE"
      - "^DROP"
      
    # Load balancing for replicas
    # Options: "round_robin", "least_connections", "random", "weighted"
    load_balance: "least_connections"
    
    # Failover behavior
    failover:
      # Automatic failover to replicas if primary fails
      enabled: true
      
      # Time to wait before retrying failed backend
      retry_interval: 10s
      
      # Max consecutive failures before marking backend as down
      max_failures: 3

  # Monitoring and metrics
  monitoring:
    # Enable metrics collection
    enabled: true
    
    # Metrics update interval
    interval: 10s
    
    # Expose metrics via RPC
    expose_rpc: true
    
    # Prometheus metrics endpoint
    prometheus:
      enabled: true
      namespace: "roadrunner"
      subsystem: "pgpool"
    
    # Metrics to collect
    collect:
      - pool_stats        # Connection pool statistics
      - query_duration    # Query execution times
      - error_rates       # Error counts by type
      - throughput        # Queries per second
      - connection_wait   # Time waiting for connections

  # Logging configuration
  logging:
    # Log level: "debug", "info", "warn", "error"
    level: "info"
    
    # Log slow queries
    slow_query:
      enabled: true
      threshold: 100ms
      
    # Log all queries (debug mode)
    log_queries: false
    
    # Include query parameters in logs
    log_params: false

  # Advanced features
  features:
    # Query caching layer
    cache:
      enabled: false
      ttl: 5m
      max_size: 100MB
      
    # Circuit breaker for backend failures
    circuit_breaker:
      enabled: true
      threshold: 5         # Failures to open circuit
      timeout: 30s        # Time before attempting reset
      
    # Rate limiting per client
    rate_limit:
      enabled: false
      requests_per_second: 1000
      burst: 100

  # Multi-database configuration
  databases:
    # Additional database pools
    analytics:
      primary:
        dsn: "postgres://user:pass@analytics:5432/analytics_db"
        pool:
          max_connections: 10
      routing:
        read_write_split: false
        
    cache_db:
      primary:
        dsn: "postgres://user:pass@cache:5432/cache_db"
        pool:
          max_connections: 5
          mode: "statement"  # Stateless queries only

PHP Integration Requirements

Connection Methods

1. Direct PostgreSQL protocol: PHP connects using standard PostgreSQL drivers to localhost:5433
2. RPC via Goridge: PHP calls pool methods through RoadRunner RPC interface
3. Environment variable injection: Plugin sets DATABASE_URL to point to local proxy

Compatibility Requirements

• PDO PostgreSQL driver compatibility
• pg_connect/pg_query function compatibility
• Laravel/Symfony/Doctrine ORM compatibility
• Connection string passthrough maintaining all PostgreSQL parameters

Performance Targets

Latency Requirements

• Connection establishment: < 1ms (local proxy)
• Query routing overhead: < 0.5ms
• Transaction begin/commit: < 1ms
• Prepared statement execution: < 0.2ms overhead

Throughput Requirements

• Minimum: 50,000 queries/second per instance
• Target: 100,000 queries/second per instance
• Connection turnover: 1000 connections/second

Resource Limits

• Memory usage: < 100MB for 100 connections
• CPU overhead: < 5% for proxy operations
• Goroutines: < 2 per client connection

Implementation Dependencies

Required Go Packages

Primary PostgreSQL Driver

github.com/jackc/pgx/v5

• Core PostgreSQL driver and toolkit
• Production-ready with extensive real-world usage
• Includes pgxpool for connection pooling
• Native PostgreSQL protocol implementation

Supporting Packages

• github.com/jackc/pgx/v5/pgxpool: Connection pooling implementation
• github.com/jackc/pgx/v5/pgproto3: PostgreSQL wire protocol messages
• github.com/jackc/pgx/v5/pgtype: PostgreSQL type handling
• github.com/jackc/puddle/v2: Generic resource pooling (used by pgxpool)

Protocol Implementation

github.com/jeroenrinzema/psql-wire (Alternative approach)

• High-level PostgreSQL server implementation
• Simplifies protocol handling
• Good for proxy implementation

RoadRunner Integration Packages

• github.com/roadrunner-server/endure/v2: Service container
• github.com/roadrunner-server/api/v4: Plugin interfaces
• github.com/roadrunner-server/goridge/v3: PHP-Go communication
• github.com/roadrunner-server/errors: Error handling
• github.com/roadrunner-server/tcplisten: TCP server utilities

Monitoring and Observability

• github.com/prometheus/client_golang: Metrics collection
• go.uber.org/zap: Structured logging
• github.com/roadrunner-server/informer/v4: RoadRunner metrics interface

Operational Requirements

Health Checks

• Connection validation: Periodic SELECT 1 or pg_is_in_recovery()
• Pool saturation monitoring: Alert when > 80% connections in use
• Backend availability: Automatic detection of database failures
• Replication lag monitoring: For read replicas

Graceful Operations

• Zero-downtime reloads: Drain existing connections before restart
• Connection draining: Wait for transactions to complete
• Gradual pool scaling: Slowly increase connections to avoid thundering herd
• Failback coordination: Controlled return to primary after recovery

Error Handling

• Connection errors: Automatic retry with exponential backoff
• Transaction failures: Proper rollback and connection reset
• Pool exhaustion: Queue requests or fail fast based on configuration
• Backend failures: Automatic failover to replicas if available

Security Considerations

• Connection encryption: TLS/SSL support for backend connections
• Authentication passthrough: Support for SCRAM-SHA-256, MD5, etc.
• Connection isolation: Prevent cross-worker data leakage
• Query sanitization: Optional SQL injection detection

Migration Path

Phase 1: Basic Pooling (MVP)

• Single backend support
• Transaction mode pooling only
• Basic pool statistics
• RPC-based query execution

Phase 2: Protocol Proxy

• PostgreSQL wire protocol implementation
• Direct connection from PHP PostgreSQL drivers
• Prepared statement support
• Connection state management

Phase 3: Advanced Features

• Read/write splitting
• Multiple backend support
• Query caching layer
• Circuit breaker implementation

Phase 4: Production Hardening

• Comprehensive monitoring
• Performance optimization
• Load testing and benchmarking
• Documentation and examples

Success Metrics

Functional Success

• ✅ All existing PHP applications work without code changes
• ✅ Full PostgreSQL feature compatibility
• ✅ Proper transaction isolation
• ✅ Prepared statement support

Performance Success

• ✅ 80% reduction in database connections
• ✅ < 1ms latency overhead for queries
• ✅ 100,000+ QPS throughput capacity
• ✅ < 100MB memory footprint

Operational Success

• ✅ Zero connection leaks over 7-day test
• ✅ Automatic recovery from database restarts
• ✅ Prometheus metrics for all pool operations
• ✅ Clear error messages for debugging

Testing Requirements

Unit Tests

• Connection pool lifecycle
• Protocol message handling
• Query routing logic
• Error handling paths

Integration Tests

• PostgreSQL compatibility suite
• Transaction isolation verification
• Prepared statement handling
• Concurrent access patterns

Load Tests

• 1000 concurrent PHP workers
• 1M queries per minute sustained
• Connection pool efficiency
• Memory leak detection

Compatibility Tests

• PDO PostgreSQL driver
• Native pg_* functions
• Laravel Eloquent ORM
• Doctrine DBAL/ORM
• Symfony components

Documentation Requirements

User Documentation

• Installation and configuration guide
• PHP connection examples
• Performance tuning guide
• Troubleshooting guide
• Migration from direct connections

Developer Documentation

• Architecture overview
• Protocol implementation details
• Contributing guidelines
• Plugin API reference
• Benchmarking methodology

Acceptance Criteria

The plugin will be considered complete when:

1. Functional: Passes all PostgreSQL compatibility tests
2. Performance: Achieves < 1ms overhead and 80% connection reduction
3. Reliable: Runs 7 days under load without memory leaks or crashes
4. Observable: Provides comprehensive metrics and logging
5. Documented: Includes complete user and developer documentation
6. Tested: Achieves > 80% code coverage with unit and integration tests

References

• GitHub Issue #919: Connection pool plugin request
• pgx documentation: https://github.com/jackc/pgx
• PostgreSQL Protocol: https://www.postgresql.org/docs/current/protocol.html
• RoadRunner Plugin Guide: https://docs.roadrunner.dev/docs/customization/plugin