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
- Audit current connections —
SELECT count(*) FROM pg_stat_activity to understand baseline
- Choose pooling mode — transaction pooling (default), session pooling (for prepared statements)
- Size the pool — start with
connections = (cores * 2) + spindle_count per PostgreSQL docs
- Deploy pooler — PgBouncer sidecar or Supabase/Neon built-in pooler
- 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
Prisma with PgBouncer
// schema.prisma — pgbouncer mode disables prepared statements
datasource db {
provider = "postgresql"
url = env("DATABASE_URL") // pooler:6543/db?pgbouncer=true
directUrl = env("DIRECT_DATABASE_URL") // direct:5432/db (for migrations)
}
// Connection limit per Prisma instance
generator client {
provider = "prisma-client-js"
}
// At runtime
const prisma = new PrismaClient({
datasources: {
db: { url: process.env.DATABASE_URL },
},
// connection_limit set via URL param: ?connection_limit=5
});
Anti-Patterns
- One connection per request — never open/close connections per HTTP request; use a pool
- Oversized pools — more connections != more throughput; past the optimal size, context switching kills performance
- Session pooling by default — use transaction pooling unless you need prepared statements or session variables
- No connection timeout — always set
idle_timeout and server_idle_timeout to reclaim stale connections
- Ignoring pool exhaustion — monitor
cl_waiting in PgBouncer; if clients wait, pool is undersized or queries are too slow
Quality Checklist
1---2license: Apache-2.03name: database-connection-pool-manager4description: "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)."5allowed-tools: Read,Write,Edit,Bash(npm:*,npx:*,docker:*,psql:*)6category: Backend & Infrastructure7tags:8 - database9 - connection-pool10 - pgbouncer11 - performance12 - postgres13pairs-with:14 - skill: multi-tenant-architecture-expert15 reason: Multi-tenant apps require careful pool segmentation16 - skill: observability-apm-expert17 reason: Connection pool metrics are critical for performance monitoring18 - skill: data-warehouse-optimizer19 reason: Warehouse query patterns affect pool sizing20metadata:21 source: https://skillmd.com/skills/curiositech/database-connection-pool-manager22---2324# Database Connection Pool Manager2526Optimize database connection pools for throughput, latency, and resource efficiency using PgBouncer, application-level poolers, and cloud-managed pools.2728## Activation Triggers2930**Activate on:** "connection pool", "PgBouncer", "database connections", "pool size", "connection limit", "too many connections", "connection timeout", "Prisma pool", "Supabase pooler"3132**NOT for:** SQL query optimization → `data-warehouse-optimizer` | Schema design → `dimensional-modeler` | ORM selection → `api-architect`3334## Quick Start35361. **Audit current connections** — `SELECT count(*) FROM pg_stat_activity` to understand baseline372. **Choose pooling mode** — transaction pooling (default), session pooling (for prepared statements)383. **Size the pool** — start with `connections = (cores * 2) + spindle_count` per PostgreSQL docs394. **Deploy pooler** — PgBouncer sidecar or Supabase/Neon built-in pooler405. **Monitor** — track active/idle/waiting connections, query queue time4142## Core Capabilities4344| Domain | Technologies |45|--------|-------------|46| **External Poolers** | PgBouncer 1.23+, Odyssey, PgCat |47| **Cloud Poolers** | Supabase Supavisor, Neon pooler, RDS Proxy |48| **App-Level** | Prisma connection pool, Drizzle pool, node-postgres Pool |49| **Monitoring** | pg_stat_activity, PgBouncer SHOW commands, Prometheus |50| **Databases** | PostgreSQL 16+, MySQL 8.4+, CockroachDB |5152## Architecture Patterns5354### PgBouncer Transaction Pooling5556```57App Instances (100 connections)58 ↓59PgBouncer (pool_mode = transaction)60 max_client_conn = 20061 default_pool_size = 2062 reserve_pool_size = 563 ↓64PostgreSQL (max_connections = 30)65```6667Key: 200 app connections share 20 actual database connections. Each connection is released back to the pool at transaction end.6869### Pool Sizing Formula7071```72Optimal pool size = ((core_count * 2) + effective_spindle_count)7374Example (8-core server, SSD):75 pool_size = (8 * 2) + 1 = 177677For serverless (many short-lived functions):78 pgbouncer.default_pool_size = 2079 pgbouncer.min_pool_size = 580 app.max_pool_size = 5 (per function instance)81 total_functions * 5 <= pgbouncer.max_client_conn82```8384### Prisma with PgBouncer8586```typescript87// schema.prisma — pgbouncer mode disables prepared statements88datasource db {89 provider = "postgresql"90 url = env("DATABASE_URL") // pooler:6543/db?pgbouncer=true91 directUrl = env("DIRECT_DATABASE_URL") // direct:5432/db (for migrations)92}9394// Connection limit per Prisma instance95generator client {96 provider = "prisma-client-js"97}9899// At runtime100const prisma = new PrismaClient({101 datasources: {102 db: { url: process.env.DATABASE_URL },103 },104 // connection_limit set via URL param: ?connection_limit=5105});106```107108## Anti-Patterns1091101. **One connection per request** — never open/close connections per HTTP request; use a pool1112. **Oversized pools** — more connections != more throughput; past the optimal size, context switching kills performance1123. **Session pooling by default** — use transaction pooling unless you need prepared statements or session variables1134. **No connection timeout** — always set `idle_timeout` and `server_idle_timeout` to reclaim stale connections1145. **Ignoring pool exhaustion** — monitor `cl_waiting` in PgBouncer; if clients wait, pool is undersized or queries are too slow115116## Quality Checklist117118- [ ] Pool size calculated based on CPU cores, not arbitrary numbers119- [ ] PgBouncer or equivalent deployed for serverless/high-connection workloads120- [ ] Transaction pooling mode used (session pooling only when required)121- [ ] `idle_timeout` set to reclaim unused connections (default: 300s)122- [ ] Application `connection_limit` per instance is <= pool_size / instance_count123- [ ] Migrations run on direct connection, not through pooler124- [ ] Connection pool metrics exported (active, idle, waiting, total)125- [ ] Alert configured for pool exhaustion (waiting > 0 for > 10s)126- [ ] Prepared statements disabled when using transaction pooling127- [ ] Load tested: pool handles 2x expected concurrent connections