Q:

Number of concurrent connections with Npgsql and async

I'm trying to maximize throughput to a PostgreSQL database and one huge performance leap can be obtained by moving from thread pools to asynchronous queries. Here's the async version of the code that will perform said query:
using (var conn = new NpgsqlConnection(connStr)) {
    await conn.OpenAsync();
    var id = await conn.ExecuteScalarAsync<long>(
        "my_proc",
        param_obj,
        commandType: CommandType.StoredProcedure,
        commandTimeout: 0
    );
    return id;
}

Previously, when using thread pools, I could set the maximum number of open connections at any given time by simply setting the number of threads (and if I exceeded the MaxPoolSize, I would see pool exhausted / timeout exceptions, as expected). In this case though, wouldn't the number of connections blow up rather quickly? Perhaps there's a more canonical way of doing this I'm not aware of.

A:

The fact that your code is asynchronous doesn't (by itself) changing the working of connection pooling in any way, nor will it (again, by itself) exhaust the pool any faster. The moment OpenAsync() completes (asynchronously), a connection has been allocated from the pool to your code, and will be considered busy until it's disposed. This isn't fundamentally different from the way things work in synchronous code; the only difference is that in the sync version there's a thread blocking on all the I/O, but not in the async version.
Now, it seems that in your previous sync version you were controlling the number of connections open concurrently by setting the number of threads executing database code. Since with async the thread yields as soon as there's I/O, that's obviously not relevant. Instead, you can simply control the number of times you (concurrently) invoke your code above. For example, you could run your code for 100 parameter values and then use Task.WhenAny() to wait until any one of these operations completes. When that happens, you can then execute another task (with a new parameter value), and so on.

