Azure Function creating too many connections to PostgreSQL

Question

I have an Azure Durable Function that interacts with a PostgreSQL database, also hosted in Azure.

The PostgreSQL database has a connection limit of 50, and furthermore, my connection string limits the connection pool size to 40, leaving space for super user / admin connections.

Nonetheless, under some loads I get the error

53300: remaining connection slots are reserved for non-replication superuser connections

This documentation from Microsoft seemed relevant, but it doesn't seem like I can make a static client, and, as it mentions,

because you can still run out of connections, you should optimize connections to the database.

I have this method

private IDbConnection GetConnection()
{
    return new NpgsqlConnection(Environment.GetEnvironmentVariable("PostgresConnectionString"));
}

and when I want to interact with PostgreSQL I do like this

using (var connection = GetConnection())
{
    connection.Open();
    return await connection.QuerySingleAsync<int>(settings.Query().Insert, settings);
}

So I am creating (and disposing) lots of NpgsqlConnection objects, but according to this, that should be fine because connection pooling is handled behind the scenes. But there may be something about Azure Functions that invalidates this thinking.

I have noticed that I end up with a lot of idle connections (from pgAdmin): Based on that I've tried fiddling with Npgsql connection parameters like Connection Idle Lifetime, Timeout, and Pooling, but the problem of too many connections seems to persist to one degree or another. Additionally I've tried limiting the number of concurrent orchestrator and activity functions (see this doc), but that seems to partially defeat the purpose of Azure Functions being scalable. It does help - I get fewer of the too many connections error). Presumably If I keep testing it with lower numbers I may even eliminate it, but again, that seems like it defeats the point, and there may be another solution.

How can I use PostgreSQL with Azure Functions without maxing out connections?

Answer 1

I don't have a good solution, but I think I have the explanation for why this happens.

Why is Azure Function App maxing out connections?

Even though you specify a limit of 40 for the pool size, it is only honored on one instance of the function app. Note that that a function app can scale out based on load. It can process several requests concurrently in the same function app instance, plus it can also create new instances of the app. Concurrent requests in the same instance will honor the pool size setting. But in the case of multiple instances, each instance ends up using a pool size of 40.

Even the concurrency throttles in durable functions don't solve this issue, because they only throttle within a single instance, not across instances.

How can I use PostgreSQL with Azure Functions without maxing out connections?

Unfortunately, function app doesn't provide a native way to do this. Note that the connection pool size is not managed by the function runtime, but by npgsql's library code. This library code running on different instances can't talk to each other.

Note that, this is the classic problem of using shared resources. You have 50 of these resources in this case. The most effective way to support more consumers would be to reduce the time each consumer uses the resource. Reducing the Connection Idle Lifetime substantially is probably the most effective way. Increasing Timeout does help reduce errors (and is a good choice), but it doesn't increase the throughput. It just smooths out the load. Reducing Maximum Pool size is also good.

Think of it in terms of locks on a shared resource. You would want to take the lock for the minimal amount of time. When a connection is opened, it's a lock on one of the 50 total connections. In general, SQL libraries do pooling, and keep the connection open to save the initial setup time that is involved in each new connection. However, if this is limiting the concurrency, then it's best to kill idle connections asap. In a single instance of an app, the library does this automatically when max pool size is reached. But in multiple instances, it can't kill another instance's connections.

One thing to note is that reducing Maximum Pool Size doesn't necessarily limit the concurrency of your app. In most cases, it just decreases the number of idle connections - at the cost of - paying the initial setup time when a new connection will need to be established at a later time.

Update

WEBSITE_MAX_DYNAMIC_APPLICATION_SCALE_OUT might be useful. You can set this to 5, and pool size to 8, or similar. I would go this way if reducing Maximum Pool Size and Connection Idle Lifetime is not helping.