package pool

import (
	"context"
	"log"
	"time"

	"github.com/jackc/pgx/v5/pgxpool"
)

type PoolConfig struct {
	DSN              string
	MaxConns         int32
	MinConns         int32
	MaxConnLifetime  time.Duration
	MaxConnIdleTime  time.Duration
	HealthCheckPeriod time.Duration
}

func New(ctx context.Context, cfg PoolConfig) (*pgxpool.Pool, error) {
	poolCfg, err := pgxpool.ParseConfig(cfg.DSN)
	if err != nil {
		return nil, err
	}

	poolCfg.MaxConns = cfg.MaxConns
	poolCfg.MinConns = cfg.MinConns
	poolCfg.MaxConnLifetime = cfg.MaxConnLifetime
	poolCfg.MaxConnIdleTime = cfg.MaxConnIdleTime
	poolCfg.HealthCheckPeriod = cfg.HealthCheckPeriod

	// Pre-warm connections to avoid cold-start latency
	pool, err := pgxpool.NewWithConfig(ctx, poolCfg)
	if err != nil {
		return nil, err
	}

	// Validate initial connectivity
	if err := pool.Ping(ctx); err != nil {
		pool.Close()
		return nil, err
	}

	log.Printf("Pool initialized: max=%d, min=%d, health=%v", cfg.MaxConns, cfg.MinConns, cfg.HealthCheckPeriod)
	return pool, nil
}

type ObservablePool struct {
	*pgxpool.Pool
	metrics *PoolMetrics
}

type PoolMetrics struct {
	Act

iveConns prometheus.Gauge IdleConns prometheus.Gauge WaitCount prometheus.Counter WaitDuration prometheus.Histogram }

func (p *ObservablePool) Acquire(ctx context.Context) (*pgxpool.Conn, error) { start := time.Now() conn, err := p.Pool.Acquire(ctx) if err != nil { p.metrics.WaitCount.Inc() return nil, err } p.metrics.WaitDuration.Observe(time.Since(start).Seconds()) return conn, nil }

// Periodic stats emission (run in background goroutine) func (p *ObservablePool) EmitStats(ctx context.Context, interval time.Duration) { ticker := time.NewTicker(interval) defer ticker.Stop() for { select { case <-ctx.Done(): return case <-ticker.C: stats := p.Pool.Stat() p.metrics.ActiveConns.Set(float64(stats.TotalConns() - stats.IdleConns())) p.metrics.IdleConns.Set(float64(stats.IdleConns())) } } }

### 3. Architecture for Extreme Scale

When application-level pools cannot handle thousands of concurrent instances, introduce a **connection proxy**:

- **PgBouncer** (PostgreSQL): Transaction-level pooling, supports `pool_mode = transaction`, reduces DB connections to a fixed number regardless of app instances.
- **ProxySQL** (MySQL): Query routing, connection multiplexing, and read/write splitting.
- **Cloud-Managed**: AWS RDS Proxy, Azure Database for PostgreSQL Flexible Server connection pooling, Google Cloud SQL Proxy.

Proxy architecture:

[App Instances] → [Pool Manager] → [Proxy (PgBouncer/ProxySQL)] → [Database]

The proxy maintains a fixed connection set to the database, while application pools connect to the proxy. This decouples app scaling from database limits and enables connection multiplexing at the protocol level.

### 4. Graceful Degradation & Circuit Breaking

At scale, pools must fail fast and recover safely:

```go
func ExecuteWithBackoff(ctx context.Context, pool *ObservablePool, query string, args ...interface{}) error {
	for attempt := 0; attempt < 3; attempt++ {
		conn, err := pool.Acquire(ctx)
		if err != nil {
			if errors.Is(err, context.DeadlineExceeded) {
				return fmt.Errorf("pool exhausted: %w", err)
			}
			time.Sleep(time.Duration(attempt+1) * 100 * time.Millisecond)
			continue
		}
		defer conn.Release()

		_, err = conn.Exec(ctx, query, args...)
		if err == nil {
			return nil
		}
		// Log and retry on transient DB errors
		log.Printf("Transient error: %v", err)
	}
	return fmt.Errorf("max retries exceeded")
}

This pattern prevents thundering herds, respects context deadlines, and releases connections deterministically.

---

Pitfall Guide

1. Connection Leaks & Missing Returns
   Failing to call conn.Release() or defer it incorrectly leaves connections in the Acquired state indefinitely. At scale, this exhausts the pool silently. Always use defer conn.Release() immediately after acquisition, and validate with pool metrics.

2. Static Pool Sizing in Dynamic Environments
   Hardcoding max_connections without considering replica count, autoscaling policies, or proxy layers guarantees exhaustion. Use (DB_MAX / APP_INSTANCES) * SAFETY_FACTOR as a baseline, and prefer transaction-level pooling via proxies.

3. Ignoring Database-Side Connection Limits & Overhead
   Databases track max_connections, but also superuser_reserved_connections, work_mem, and shared_buffers. A pool that respects max_connections can still OOM the database if each connection consumes excessive memory. Align pool sizing with SHOW settings and use pg_stat_activity or SHOW PROCESSLIST for validation.

4. Health Checks That Cause Thundering Herds
   Aggressive HealthCheckPeriod (e.g., < 5s) with large pools triggers simultaneous validation queries, spiking CPU and I/O. Set health checks to 10–30s, use lightweight SELECT 1 queries, and stagger checks across pool instances using jitter.

5. TLS/SSL Handshake Overhead in Pooled Connections
   If sslmode=verify-full is used without connection reuse, each acquisition re-negotiates TLS. Ensure sslmode is configured once at pool creation, not per-query. For cloud databases, use IAM auth or certificate rotation strategies that don't force pool recreation.

6. Poor Observability & Missing Pool Metrics
   Monitoring only database-side metrics blinds you to pool bottlenecks. Track: acquire_wait_time, idle_conns, total_conns, max_conns_reached, and evictions. Alert on wait_count > threshold and acquire_latency > p95. Without these, you react to failures instead of preventing them.

---

Production Bundle

Checklist

Pre-Deployment

• Database max_connections and reserved_connections documented
• Pool max_conns calculated: (DB_LIMIT / EXPECTED_INSTANCES) * 0.7
• Proxy layer evaluated (PgBouncer/ProxySQL/Cloud Proxy)
• TLS/SSL mode verified for connection reuse
• Health check interval set ≥ 10s with jitter
• Context timeouts applied to all pool acquisitions

Runtime

• Pool metrics exported to monitoring stack (Prometheus/Datadog)
• Backpressure circuit breaker configured
• Graceful shutdown implemented (pool.Close() on SIGTERM)
• Connection leak detection enabled (idle timeout + max lifetime)
• Load test validated against peak RPS + 30% headroom

Disaster Recovery

• Failover tested (primary → replica pool routing)
• Pool recreation strategy documented (config reload vs restart)
• Database connection limit increase runbook available
• Monitoring alerts routed to on-call with runbook links

Decision Matrix

Factor	App-Level Pool	Lightweight Proxy (PgBouncer/ProxySQL)	Cloud-Managed Proxy	Serverless Adapter
Scale	≤ 50 instances	50–500 instances	500+ instances	Event-driven/ephemeral
Latency Impact	Low (local)	Low (same VPC)	Medium (managed hop)	High (cold start)
Cost	Free	Low (compute)	Medium (AWS/Azure/GCP fee)	Pay-per-use
Complexity	Low	Medium	Low	High
Team Expertise	Basic	Network/DB ops	Cloud-native	Framework-specific
Best For	Monoliths, small microservices	High-concurrency microservices	Enterprise cloud workloads	Lambda/Cloud Functions

Rule of Thumb: Use app-level pools for ≤ 10 instances. Introduce a proxy at 10–50 instances. Mandate cloud-managed or dedicated proxies beyond 50 instances or when serverless is involved.

Config Template

# pool-config.yaml
database:
  dsn: "postgres://user:pass@db-host:5432/appdb?sslmode=verify-full"
  pool:
    max_conns: 25
    min_conns: 5
    max_conn_lifetime: "30m"
    max_conn_idle_time: "5m"
    health_check_period: "15s"
    acquire_timeout: "2s"
  proxy:
    enabled: true
    host: "pgbouncer.internal"
    port: 6432
    pool_mode: "transaction"
    server_reset_query: "DISCARD ALL"
  observability:
    metrics_port: 9090
    log_level: "warn"
    trace_sampling: 0.1
  resilience:
    circuit_breaker:
      threshold: 10
      timeout: "30s"
    retry:
      max_attempts: 3
      backoff_base: "100ms"
      jitter: true

Quick Start

1. Define Limits: Query your database for max_connections and calculate safe per-instance pool size.
2. Deploy Proxy: Install PgBouncer (or enable cloud proxy). Configure pool_mode = transaction and max_client_conn ≥ expected app instances × pool max.
3. Initialize Pool: Use the provided Go template. Set max_conns to 20–25% of DB limit per instance. Enable max_conn_lifetime to rotate connections safely.
4. Instrument: Expose pool stats via /metrics. Alert on acquire_wait_count and p95_acquire_latency > 500ms.
5. Validate: Run load tests with wrk or k6. Simulate 2x peak traffic. Verify no connection refused errors, stable CPU/memory, and graceful degradation under pool exhaustion.

Connection pooling at scale is no longer about keeping connections alive—it's about orchestrating them dynamically, observing them relentlessly, and designing for failure. Implement the right layer for your scale, instrument everything, and let metrics drive capacity decisions. The database will thank you with predictable latency and zero connection storms.