PostgreSQL Connection Pooling: How to Prevent Connection Exhaustion in Production

A PostgreSQL application can perform well during development and still experience connection failures after deployment. The problem often appears when traffic increases, background workers run concurrently, or multiple application instances attempt to connect to the database at the same time.

PostgreSQL has a finite connection capacity. Each client connection consumes server resources, and opening connections repeatedly introduces additional overhead. When the number of connections approaches the configured limit, new requests may fail with errors such as too many connections.

Connection pooling helps address this problem by reusing established connections instead of creating a new database connection for every operation. However, a pool that is configured incorrectly can still exhaust database resources or introduce delays when requests wait for available connections.

In this article, we will examine how PostgreSQL connection pooling works, implement pooling in a .NET application using Npgsql, configure pool limits, investigate connection exhaustion, and establish practices for production deployments.

What Is PostgreSQL Connection Pooling?

A database connection represents a session between an application and PostgreSQL. Establishing a connection involves network communication, authentication, and server-side resource allocation.

If an application opens and closes a database connection for every individual query, the repeated connection setup can introduce unnecessary overhead.

Connection pooling maintains a collection of reusable connections. When an application needs database access, it requests a connection from the pool. After completing the operation, it returns the connection so another operation can reuse it.

The typical workflow is:

An application requests a database connection.
The connection pool provides an available connection or creates one if the pool has capacity.
The application executes database operations.
The application disposes of the connection object, returning the underlying connection to the pool when appropriate.
Another operation can reuse that connection.

The important distinction is that disposing of a pooled connection object generally returns the underlying connection to the pool rather than closing the physical database connection immediately.

Why Connection Pooling Matters

Connection pooling can improve application performance and reduce unnecessary database connection creation.

It also helps control the number of connections created by an application instance. However, a pool limit applies to that pool, not automatically to every application instance across the entire deployment.

For example, suppose an API runs in eight application instances, and each instance has a maximum pool size of 30 connections.

The combined theoretical maximum is:

8 instances × 30 connections = 240 connections

If PostgreSQL can safely accommodate fewer connections than that, the deployment may still exceed database capacity even though every individual pool respects its configured limit.

This is why pool sizing must account for the entire application environment.

Understand Connection Pooling in .NET

Npgsql is a commonly used .NET data provider for PostgreSQL. It provides connection pooling through its data source and connection management functionality.

For most applications, the recommended approach is to create and reuse a configured NpgsqlDataSource. It manages connection pooling and provides a central place to configure database access.

Create a .NET console application:

dotnet new console -n PostgresPoolingDemo
cd PostgresPoolingDemo

Install the Npgsql package:

dotnet add package Npgsql

Configure the database connection string through an environment variable rather than hardcoding database credentials into the source code.

For example, in PowerShell:

$env:ConnectionStrings__Postgres = "Host=localhost;Port=5432;Database=appdb;Username=appuser;Password=your-password"

Use a secure credential appropriate for your environment. The password shown here is a placeholder.

Configure an Npgsql Data Source

The following example creates a shared data source, opens a connection, executes a query, and disposes of the connection when the operation finishes.

using Npgsql;

var connectionString =
    Environment.GetEnvironmentVariable("ConnectionStrings__Postgres")
    ?? throw new InvalidOperationException(
        "The PostgreSQL connection string is not configured.");

var builder = new NpgsqlDataSourceBuilder(connectionString);

await using var dataSource = builder.Build();

await using var connection =
    await dataSource.OpenConnectionAsync();

await using var command = new NpgsqlCommand(
    "SELECT CURRENT_TIMESTAMP;",
    connection);

var result = await command.ExecuteScalarAsync();

Console.WriteLine($"Database time: {result}");

The NpgsqlDataSource owns the configuration and connection pool for the application. The application should generally create it once and reuse it rather than creating a new data source for every request.

The connection and command are disposed of with await using. This ensures that their resources are released even when an exception occurs.

With pooling enabled, disposing of the connection normally returns it to the pool.

This pattern is particularly important in web applications, where many requests may need database access concurrently.

Configure Maximum Pool Size

Npgsql provides connection-string settings for controlling pooling behavior. Two important settings are Maximum Pool Size and Minimum Pool Size.

For example:

Host=localhost;
Port=5432;
Database=appdb;
Username=appuser;
Password=your-password;
Pooling=true;
Minimum Pool Size=2;
Maximum Pool Size=20;
Timeout=15;
Command Timeout=30;

This configuration illustrates a maximum pool size of 20 connections and a minimum pool size of two.

The settings serve different purposes:

Pooling=true enables pooling.
Minimum Pool Size=2 establishes the minimum number of connections the pool attempts to maintain according to its lifecycle and usage.
Maximum Pool Size=20 limits the number of connections maintained by that pool.
Timeout=15 sets the connection-opening timeout in seconds.
Command Timeout=30 sets the default command timeout in seconds.

These values are examples, not universal recommendations. Choose limits based on the workload, database capacity, and number of application instances.

A connection-opening timeout and a command timeout also solve different problems. The first controls how long the application waits to establish or obtain a connection. The second controls how long a command can execute before timing out under the configured behavior.

Calculate a Safe Pool Size

The maximum pool size should not be selected in isolation.

Start by identifying the database's effective connection budget. PostgreSQL's max_connections setting defines the maximum number of concurrent connections the server accepts, subject to reserved connection slots and other configuration details.

Not every available connection should be allocated to one application. Database administration, migrations, monitoring, maintenance, and other services may also need connections.

Suppose a PostgreSQL server is configured for 200 connections. Your deployment includes three application groups, a background worker, and administrative processes.

An illustrative allocation could look like this:

Consumer	Planned connection budget
Order API	60
Inventory API	40
Reporting API	25
Background workers	25
Administration and monitoring	20
Remaining headroom	30
Total	200

This is a planning example, not a guarantee that the workload will run safely with these limits.

For each application group, account for the maximum number of simultaneously running instances and the maximum pool size per instance.

For example, if the Order API can scale to six instances, a pool maximum of 10 per instance creates a theoretical capacity of 60 connections. If it can scale to 12 instances, the same setting permits up to 120 connections across that service.

Horizontal scaling therefore changes the database connection budget even when the application configuration remains unchanged.

Diagnose PostgreSQL Connection Exhaustion

Connection exhaustion usually appears when the application cannot obtain a database connection or PostgreSQL refuses new connections.

Common symptoms include:

too many connections for role
remaining connection slots are reserved
Requests waiting for database access
Increased response latency during traffic spikes
Connection-opening timeouts
A growing number of idle or long-running database sessions

Before changing pool settings, inspect the database's current connection usage.

Check the Connection Limit and Current Usage

Run this query in PostgreSQL:

SELECT
    current_setting('max_connections')::int AS max_connections,
    COUNT(*) AS current_connections,
    COUNT(*) FILTER (
        WHERE state = 'active'
    ) AS active_connections,
    COUNT(*) FILTER (
        WHERE state = 'idle'
    ) AS idle_connections,
    COUNT(*) FILTER (
        WHERE state = 'idle in transaction'
    ) AS idle_in_transaction
FROM pg_stat_activity;

This query reports the configured connection limit and categorizes the sessions currently visible in pg_stat_activity.

The results help distinguish several situations:

A high number of active sessions may indicate heavy concurrency or slow queries.
A high number of idle sessions may indicate that connections are being retained without current work.
Idle-in-transaction sessions may indicate application code that starts transactions but leaves them open while waiting or performing unrelated work.

Idle connections are not necessarily a problem. Connection pools intentionally retain connections for reuse. The more important questions are whether the connection count approaches the server's capacity and whether sessions are being held longer than necessary.

Also remember that monitoring permissions and managed database environments can affect which sessions are visible.

Identify Which Application Owns the Connections

Set an appropriate PostgreSQL application_name for each service so its sessions can be identified more easily.

For example, a connection string may include:

Application Name=OrderApi

Then group sessions by application:

SELECT
    application_name,
    state,
    COUNT(*) AS connection_count
FROM pg_stat_activity
GROUP BY application_name, state
ORDER BY connection_count DESC;

This helps identify whether one service or worker is consuming a disproportionate share of the connection budget.

If a service has an unexpectedly large number of connections, inspect its instance count, pool configuration, request concurrency, and connection disposal behavior.

Prevent Connection Leaks in Application Code

One common mistake is keeping connections open longer than necessary.

Consider this pattern:

var connection = await dataSource.OpenConnectionAsync();

// Perform a database operation.

// Connection is never disposed.

If the application repeatedly opens connections without releasing them, the pool can eventually run out of available connections.

Use await using to ensure that the connection is disposed of when the operation completes:

await using var connection =
    await dataSource.OpenConnectionAsync();

await using var command = new NpgsqlCommand(
    "SELECT COUNT(*) FROM orders;",
    connection);

var count = await command.ExecuteScalarAsync();

Console.WriteLine($"Order count: {count}");

The connection is returned to the pool after the scope ends.

In an ASP.NET Core application, register a shared data source through dependency injection:

var builder = WebApplication.CreateBuilder(args);

var connectionString =
    builder.Configuration.GetConnectionString("Postgres")
    ?? throw new InvalidOperationException(
        "PostgreSQL connection string is missing.");

builder.Services.AddNpgsqlDataSource(connectionString);

var app = builder.Build();

app.MapGet("/orders/count", async (NpgsqlDataSource dataSource) =>
{
    await using var connection =
        await dataSource.OpenConnectionAsync();

    await using var command = new NpgsqlCommand(
        "SELECT COUNT(*) FROM orders;",
        connection);

    var count = await command.ExecuteScalarAsync();

    return Results.Ok(new { Count = count });
});

app.Run();

The example uses the Npgsql dependency-injection registration extension. Ensure the installed Npgsql version supports the API shown.

In a larger application, keep database operations in an appropriate data-access layer rather than placing every query directly in endpoint handlers.

Understand Application Pooling and External Poolers

There are two different layers of connection pooling to consider.

Application-Side Pooling

A driver such as Npgsql can reuse database connections within an application process.

This is useful for long-running applications, including web APIs and background workers. It reduces repeated connection establishment and places a limit on connections created by each pool.

However, each independent application process can have its own pool. Multiple containers or virtual machines therefore multiply the total possible connection count.

Server-Side Pooling

A server-side pooler such as PgBouncer sits between application clients and PostgreSQL.

Application instances
        |
        v
Application-side connection pools
        |
        v
Optional server-side pooler
        |
        v
PostgreSQL

A server-side pooler can accept many client connections while maintaining a smaller number of connections to PostgreSQL, depending on its configuration and pooling mode.

This is particularly useful when many short-lived application instances create connections during traffic spikes.

However, server-side pooling introduces its own configuration and operational requirements. Transaction pooling, for example, does not preserve all session-level state across transactions. Applications that depend on session variables, temporary tables, session-level advisory locks, or other persistent session behavior must verify compatibility with the chosen pooling mode.

Do not add an external pooler automatically. First understand whether application-side pooling is sufficient for the workload.

Common Mistakes and Troubleshooting

Increasing the Pool Size Without Checking Database Capacity

Raising Maximum Pool Size may reduce waiting within one application instance, but it can also increase total database connections beyond a safe limit.

Calculate the maximum connection demand across every service and instance before changing the setting.

Creating a New Data Source for Every Request

A data source manages the pool. Repeatedly creating new data sources can create unnecessary pools and complicate connection management.

Create a data source once per application process for a given configuration and reuse it.

Holding Connections During Unrelated Work

Do not keep a database connection open while waiting for a user, calling a slow external API, or performing lengthy CPU-bound work unless the database operation genuinely requires that connection to remain open.

Acquire connections shortly before database work and release them as soon as possible.

Ignoring Long-Running Transactions

An open transaction can hold locks and interfere with database maintenance. Review transaction boundaries and ensure that transactions are committed or rolled back promptly.

Use database monitoring and appropriate transaction timeouts to identify sessions that remain idle inside transactions.

Treating Every Timeout as a Pooling Problem

A request may time out because the application cannot acquire a connection, because the database query is slow, or because the database is overloaded.

Measure connection wait time, query duration, active connections, and pool utilization separately where possible. This makes it easier to identify the actual bottleneck.

Production Best Practices

Create and reuse a shared NpgsqlDataSource per application process and connection configuration.
Dispose of connections, commands, readers, and transactions correctly.
Calculate pool limits across all instances, not just one process.
Reserve connection capacity for administration, monitoring, and maintenance.
Set realistic connection and command timeouts.
Monitor connection counts, active sessions, idle sessions, and idle-in-transaction sessions.
Use meaningful application_name values to identify connection consumers.
Keep database transactions short and avoid holding connections during unrelated work.
Evaluate PgBouncer or another managed pooling option when connection demand exceeds what direct application pooling can safely support.
Load credentials through appropriate configuration or secret-management systems.
Test behavior under concurrent load and during application scaling events.

Advantages and Disadvantages of Connection Pooling

Aspect	Advantages	Disadvantages
Connection reuse	Reduces repeated connection establishment overhead.	Connections remain allocated for reuse and consume database resources.
Concurrency control	Limits the number of connections created by an individual pool.	Limits must account for every application instance.
Performance	Can reduce connection setup delays.	Requests may wait when all pool connections are busy.
Scalability	Works well with persistent application processes.	Horizontal scaling can multiply total connection demand.
Operations	Makes connection behavior easier to control.	Requires monitoring and deliberate pool sizing.
External poolers	Can reduce the number of direct database connections.	Introduces configuration and session-compatibility considerations.

Conclusion

PostgreSQL connection pooling is an important part of building reliable database-backed applications. It allows applications to reuse established connections, reduce repeated connection setup, and control how many connections each application process can create.

However, pooling does not remove PostgreSQL's connection limits. A configuration that works for one application instance may become unsafe when the service scales to multiple instances or adds background workers.

The most reliable approach combines a shared Npgsql data source, correct connection disposal, short transaction boundaries, carefully calculated pool limits, and database monitoring. When application-side pooling is insufficient, a server-side pooler can provide another layer of connection management.

By treating connection pooling as a deployment-wide capacity concern rather than a single connection-string setting, developers can reduce connection exhaustion and make PostgreSQL applications more predictable under production workloads.