PostgreSQL Connection Pooling: Preventing Connection Exhaustion in Production
A practical guide to configuring Npgsql connection pooling in .NET and sizing pools across multiple application instances so PostgreSQL does not run out of connections. It stresses that pool limits must be calculated for the whole deployment, not a single process.
Key points
- Create one NpgsqlDataSource and reuse it across the application instead of building a new one per request.
- The total connection ceiling is instance count times Maximum Pool Size, so include scaling in your calculation.
- Split max_connections across services, background workers and admin tasks, and keep some headroom.
- Dispose connections with await using; connections that are never released drain the pool.
- Group pg_stat_activity by application_name and state to see which service is holding connections.
- Add a server-side pooler such as PgBouncer only when application-side pooling is not enough, and check session-state compatibility first.
PostgreSQL connection exhaustion in production usually shows up under traffic spikes, when background workers run alongside the API, or when several instances connect at once. A pool that respects its own limit can still overload the server, because every application instance has its own pool and the totals add up across the deployment.
The guide walks through creating one shared NpgsqlDataSource, setting Maximum Pool Size and Minimum Pool Size in the connection string, and splitting the server's max_connections budget between services. It shows how to inspect pg_stat_activity, tag sessions with application_name, and avoid leaks by disposing connections with await using. It closes by explaining when a server-side pooler such as PgBouncer helps and why transaction pooling limits session-level features.
Real-world use
- Planning the connection budget for three APIs and a background worker sharing one PostgreSQL server.
- Diagnosing 'too many connections' errors in an ASP.NET Core service that spikes under load.
- Limiting pool size in a container-based system that scales horizontally.
Why it matters
Scaling out multiplies the real connection count even when the pool settings stay the same. Calculating limits from instance count is what keeps production from hitting the server's connection ceiling.