Azure Function creating too many connections to PostgreSQL

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.

This is where Dependency Injection can be really helpful. You can create a singleton client and it will do the job perfectly. If you want to know more about service lifetimes you can read it here in docs

1. First add this nuget Microsoft.Azure.Functions.Extensions.DependencyInjection

2. Now add a new class like below and resolve your client.

[assembly: FunctionsStartup(typeof(Kovai.Serverless360.Functions.Startup))]

namespace MyFunction{    class Startup : FunctionsStartup    {        public override void Configure(IFunctionsHostBuilder builder)        {            ResolveDependencies(builder);        }    }    public void ResolveDependencies(IFunctionsHostBuilder builder)    {        var conStr = Environment.GetEnvironmentVariable("PostgresConnectionString");        builder.Services.AddSingleton((s) =>        {            return new NpgsqlConnection(conStr);        }    }}

Now you can easily consume it from any of your function

public FunctionA    {        private readonly NpgsqlConnection _connection;        public FunctionA(NpgsqlConnection conn)        {            _connection = conn;        }        public async Task<HttpResponseMessage> Run()        {            //do something with your _connection        }    }

Here's an example of using a static HttpClient, something which you should consider so that you don't need to explicitly manage connections, rather allow your client to do it:

public static class PeriodicHealthCheckFunction{    private static HttpClient _httpClient = new HttpClient();    [FunctionName("PeriodicHealthCheckFunction")]    public static async Task Run(        [TimerTrigger("0 */5 * * * *")]TimerInfo healthCheckTimer,        ILogger log)    {        string status = await _httpClient.GetStringAsync("https://localhost:5001/healthcheck");        log.LogInformation($"Health check performed at: {DateTime.UtcNow} | Status: {status}");    }}