database-connection-pool-manager

license	Apache-2.0
name	database-connection-pool-manager
description	PgBouncer, connection optimization, and pooling strategies for database performance. Activate on: connection pool, PgBouncer, database connections, pool size, connection limit, Prisma pool, Drizzle pool. NOT for: query optimization (use data-warehouse-optimizer), database schema design (use dimensional-modeler).
allowed-tools	Read,Write,Edit,Bash(npm:*,npx:*,docker:*,psql:*)
category	Backend & Infrastructure
tags	["database","connection-pool","pgbouncer","performance","postgres"]
pairs-with	[{"skill":"multi-tenant-architecture-expert","reason":"Multi-tenant apps require careful pool segmentation"},{"skill":"observability-apm-expert","reason":"Connection pool metrics are critical for performance monitoring"},{"skill":"data-warehouse-optimizer","reason":"Warehouse query patterns affect pool sizing"}]

Database Connection Pool Manager

Optimize database connection pools for throughput, latency, and resource efficiency using PgBouncer, application-level poolers, and cloud-managed pools.

Activation Triggers

Activate on: "connection pool", "PgBouncer", "database connections", "pool size", "connection limit", "too many connections", "connection timeout", "Prisma pool", "Supabase pooler"

NOT for: SQL query optimization → data-warehouse-optimizer | Schema design → dimensional-modeler | ORM selection → api-architect

Quick Start

1. Audit current connections — SELECT count(*) FROM pg_stat_activity to understand baseline
2. Choose pooling mode — transaction pooling (default), session pooling (for prepared statements)
3. Size the pool — start with connections = (cores * 2) + spindle_count per PostgreSQL docs
4. Deploy pooler — PgBouncer sidecar or Supabase/Neon built-in pooler
5. Monitor — track active/idle/waiting connections, query queue time

Core Capabilities

Domain	Technologies
External Poolers	PgBouncer 1.23+, Odyssey, PgCat
Cloud Poolers	Supabase Supavisor, Neon pooler, RDS Proxy
App-Level	Prisma connection pool, Drizzle pool, node-postgres Pool
Monitoring	pg_stat_activity, PgBouncer SHOW commands, Prometheus
Databases	PostgreSQL 16+, MySQL 8.4+, CockroachDB

Architecture Patterns

PgBouncer Transaction Pooling

App Instances (100 connections)
    ↓
PgBouncer (pool_mode = transaction)
    max_client_conn = 200
    default_pool_size = 20
    reserve_pool_size = 5
    ↓
PostgreSQL (max_connections = 30)

Key: 200 app connections share 20 actual database connections. Each connection is released back to the pool at transaction end.

Pool Sizing Formula

Optimal pool size = ((core_count * 2) + effective_spindle_count)

Example (8-core server, SSD):
  pool_size = (8 * 2) + 1 = 17

For serverless (many short-lived functions):
  pgbouncer.default_pool_size = 20
  pgbouncer.min_pool_size = 5
  app.max_pool_size = 5  (per function instance)
  total_functions * 5 <= pgbouncer.max_client_conn