PostgreSQL Connection Pools: Avoiding Too Many Open Connections

A PostgreSQL database can become slow even when individual SQL queries are reasonably efficient. One possible cause is connection pressure: too many clients are opening database connections, or connections are remaining occupied longer than expected.

This problem often appears in ASP.NET Core applications that handle many concurrent requests. Each application instance may create its own database connections, background workers may use separate connections, and monitoring tools may add more connections to the same PostgreSQL server. As the number of connections grows, the database must manage additional sessions and associated resources.

Connection pooling helps reduce the cost of repeatedly opening and closing database connections. However, pooling does not automatically prevent connection exhaustion. An application can still create too many concurrent connections if its pool sizes and deployment scale are not considered together.

This article explains how PostgreSQL connections and client-side pools work, how to configure pooling in .NET applications, how to diagnose connection exhaustion, and how to choose pool settings that fit a production workload.

How PostgreSQL Connections Work

A PostgreSQL client normally establishes a database connection before executing SQL commands. Establishing a connection involves network communication, authentication, session initialization, and server-side resource allocation.

PostgreSQL traditionally uses a process-based architecture in which each active client connection is handled by a server process. Maintaining a large number of connections therefore has operational costs, even when some sessions are mostly idle.

Consider an ASP.NET Core API that uses Entity Framework Core with Npgsql, the PostgreSQL provider for .NET. When a request queries the database, the application needs an available database connection. After the operation finishes, the connection can be returned to a client-side pool for reuse.

The application does not necessarily need to establish a new physical database connection for every request.

What Is Connection Pooling?

Connection pooling maintains a collection of reusable database connections. Instead of creating a new physical connection every time an application needs database access, the client library can reuse an existing connection from the pool.

In a typical .NET application, Npgsql manages PostgreSQL connection pools. EF Core obtains connections through the configured provider, while Npgsql handles the underlying connection reuse.

The general lifecycle looks like this:

1. An application operation requests a database connection.
2. Npgsql checks whether a suitable connection is available in the pool.
3. If a connection is available, it is reused.
4. If the pool has capacity but no reusable connection is available, another physical connection may be opened.
5. If the pool has reached its configured limit, the application must wait for a connection to become available or encounter a timeout.
6. When the application closes or disposes the connection, it is normally returned to the pool rather than physically disconnected.

The last step is important. Calling Dispose() on a pooled connection generally releases it back to the pool. It does not necessarily close the underlying PostgreSQL session immediately.

Configure Connection Pooling in Npgsql

Npgsql enables connection pooling by default. You can configure the pool through the connection string used by your application.

For example, an ASP.NET Core application can store its PostgreSQL connection string in configuration:

{
  "ConnectionStrings": {
    "DefaultConnection": "Host=localhost;Port=5432;Database=shopdb;Username=app_user;Password=local-dev-password;Pooling=true;Minimum Pool Size=0;Maximum Pool Size=50;Timeout=15;Command Timeout=30"
  }
}

The values shown are illustrative starting points, not universal production recommendations.

Important settings include:

Setting	Purpose
Pooling	Enables or disables connection pooling
Minimum Pool Size	Controls the minimum number of connections retained by the pool
Maximum Pool Size	Limits the number of physical connections managed by that pool
Timeout	Limits how long opening a connection may wait for a connection to become available or for connection establishment to complete
Command Timeout	Limits how long a database command may execute before a command timeout occurs

Connection-string option names and behavior depend on the Npgsql version. Use the settings supported by the version installed in your application.

For production deployments, keep credentials in a managed secret store or secure configuration provider. Do not commit real database passwords to source control.

Register the DbContext

Configure EF Core to use the PostgreSQL provider:

using Microsoft.EntityFrameworkCore;

var builder = WebApplication.CreateBuilder(args);

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

builder.Services.AddDbContext<AppDbContext>(options =>
{
    options.UseNpgsql(connectionString);
});

var app = builder.Build();

app.MapGet("/", () => "Application is running.");

app.Run();

The AddDbContext registration uses dependency injection to manage the lifetime of AppDbContext. By default, the context is scoped to the request in a typical ASP.NET Core application.

A DbContext is not thread-safe. Do not share the same context instance between concurrent operations. If independent operations require separate contexts, create them through IDbContextFactory<TContext> or another suitable lifetime strategy.

Connection pooling and DbContext pooling are separate features. Npgsql connection pooling manages physical database connections, while EF Core context pooling reuses initialized context instances to reduce application-side allocation and initialization costs.

Understand the Connection Budget

The most important connection-pooling mistake is configuring each application instance independently without considering the total deployment.

Suppose an API has four application instances and each instance uses one Npgsql pool with a maximum size of 50.

The theoretical maximum number of connections for those pools is:

[
4 \times 50 = 200
]

If the deployment scales to 12 instances, the same configuration can allow:

[
12 \times 50 = 600
]

These figures represent the combined pool capacity, not the number of connections that will necessarily be open at all times. Minimum pool sizes, connection-string differences, separate data sources, background workers, and other clients can change the total.

PostgreSQL also has a server-side connection limit. That limit must accommodate application connections, administrative access, monitoring, scheduled jobs, and other consumers.

A safe design leaves capacity for operational access and unexpected workloads rather than assigning the entire connection budget to application pools.

Calculate a Practical Pool Size

Start with the database's usable connection budget and the maximum number of application instances expected during normal operation and scaling events.

For example, assume the database can safely allocate 300 connections to the workload after reserving capacity for administration and other clients.

If the application can scale to eight instances, allocating a maximum of 50 connections per instance would permit up to 400 application connections. That exceeds the available budget.

A more appropriate initial configuration might use a smaller pool limit per instance, followed by load testing and measurement.

Do not calculate pool size from request concurrency alone. An application can process many HTTP requests while using relatively few database connections if each request performs short database operations and releases connections promptly.

The key variables include:

• The number of application instances.
• The number of distinct connection pools.
• The duration of database operations.
• The number of concurrent operations requiring connections.
• Background processing and scheduled jobs.
• Other services sharing the database.
• The available PostgreSQL connection budget.

Diagnose Connection Exhaustion

When PostgreSQL connections are exhausted, application requests may wait for a pool connection, time out while opening a connection, or fail because the server cannot accept additional sessions.

Start by inspecting the current PostgreSQL sessions:

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

This query groups database sessions by application name and connection state. It helps reveal which clients are consuming connections and whether many sessions are idle or actively executing work.

You can also inspect the server's configured connection limit:

SHOW max_connections;

The configured limit is not the same as the number of connections available to an application. Reserved connections, other clients, and operational requirements must be considered.

Identify long-running queries

A large number of active connections may indicate slow database operations or queries that hold connections for too long.

Use a diagnostic query such as:

SELECT
    pid,
    application_name,
    state,
    now() - query_start AS query_duration,
    wait_event_type,
    wait_event,
    LEFT(query, 200) AS query_text
FROM pg_stat_activity
WHERE state = 'active'
  AND query_start IS NOT NULL
ORDER BY query_start;

Review the output carefully. The query text may contain sensitive information, so restrict access to diagnostic results.

Long-running statements, lock waits, and excessive transaction duration can keep connections occupied. Increasing the pool size may postpone the symptom without correcting the underlying problem.

Common Connection-Pooling Mistakes

Opening connections without disposing them

Connections should be disposed promptly when database work is complete. In ordinary ADO.NET usage, use using or await using as appropriate.

EF Core normally manages connection opening and closing for its operations, but manually opened connections must be closed reliably.

Holding connections during unrelated work

Avoid keeping a database transaction or connection open while waiting for an external HTTP API, processing a large file, or performing unrelated calculations.

Long-lived operations reduce the number of connections available to other requests.

Increasing the pool size without checking PostgreSQL capacity

A larger pool can allow more requests to compete for database resources simultaneously. If the database is already overloaded, this can worsen latency.

First determine whether the bottleneck is connection acquisition, slow queries, lock contention, or insufficient database capacity.

Confusing idle connections with leaked connections

Idle sessions are not automatically a problem. Pooled connections may remain open so they can be reused.

Investigate whether the number of connections is within the expected budget, whether idle sessions are accumulating unexpectedly, and whether application requests are waiting for available pool capacity.

Best Practices for Production Applications

1. Set an explicit connection budget. Calculate the maximum connections across all application instances, background workers, and separate pools.
2. Keep pool limits proportional to deployment scale. Revisit settings when changing autoscaling limits or adding application services.
3. Release connections promptly. Avoid holding transactions open while performing non-database work.
4. Monitor pool wait time and database sessions. Track connection acquisition failures, open connections, query duration, and request latency.
5. Tune queries before increasing concurrency. Faster queries can reduce connection occupancy and improve throughput without opening more connections.
6. Use connection multiplexing only when its limitations are understood. A pooler such as PgBouncer can reduce backend connection pressure, but transaction pooling changes session behavior and is not compatible with every session-dependent feature.
7. Test peak conditions. Load-test expected concurrency and scaling events rather than relying only on development measurements.
8. Protect database credentials and diagnostic output. Restrict access to connection strings, SQL logs, and session details.

Advantages and Disadvantages

Advantages	Disadvantages
Reuses connections instead of repeatedly establishing them.	Incorrect limits can cause connection acquisition timeouts.
Reduces connection setup overhead.	Idle pooled connections still consume server resources.
Helps applications handle concurrent requests efficiently.	Multiple application instances can multiply total pool capacity.
Works with EF Core and Npgsql without requiring manual pool management.	Connection pooling does not fix slow queries or long transactions.
Provides configurable limits and timeouts.	Requires monitoring and adjustment as workloads change.

Summary

PostgreSQL connection pooling helps .NET applications reuse database connections efficiently, but it must be configured with the entire deployment in mind. Npgsql manages the physical connection pool, while EF Core manages its context lifecycle and uses connections when database operations require them.

The most important steps are to calculate the combined connection budget, configure sensible pool limits, release connections promptly, and monitor PostgreSQL sessions alongside application latency.

When connection pressure appears, investigate query duration, lock waits, pool acquisition delays, and deployment scale before increasing the maximum pool size. A well-managed connection pool supports reliable application throughput without overwhelming the database.