What is connection pooling and why does it matter?

Connection pooling keeps a pool of open database connections instead of opening a new one per request. Opening a TCP+TLS+auth handshake per request is 50-150ms — devastating at scale.

ADO.NET / EF Core handles this automatically:

services.AddDbContext<AppDb>(opt => opt.UseNpgsql(connectionString));
// pool size defaults to 100 (npgsql) or 100 (SqlClient)

When you await db.Orders.ToListAsync(), EF borrows a pooled connection, runs the query, returns it to the pool. The connection stays open until idle timeout.

Production knobs (PostgreSQL example):

Host=db.example.com;
Port=5432;
Database=app;
Username=...;
Password=...;
Maximum Pool Size=50;        // tune to (cores × 2-4) typically
Connection Idle Lifetime=300;
Connection Lifetime=600;

Common bugs:

1. Pool exhaustion under spike traffic → all connections checked out, requests timeout. Fix: increase pool, or fix slow queries holding connections.

2. Long-lived contexts in singletons → connection never returned, pool starves quickly. Fix: use scoped lifetime per request.

3. Forgetting to dispose outside DI scope:

using var db = new AppDbContext(opts);   // ensures Dispose returns the connection

With Supabase / pooler endpoints (port 6543): the pooler runs server-side and multiplexes many app connections onto fewer DB connections. Useful for serverless platforms where each function spins up its own pool.

Test it: open Activity Monitor / pg_stat_activity. If you see hundreds of idle connections, your pool size is too large. If you see connection limit exceeded errors, it's too small.

Rule of thumb: max pool size ≈ (cores on the database × 2) ÷ number of app instances. Then adjust based on real metrics.