# Error with Npgsql: "The connection pool has been exhausted, either raise MaxPoolSize (currently 100) or Timeout (currently 15 seconds)"

**URL:** <https://community.cratedb.com/t/error-with-npgsql-the-connection-pool-has-been-exhausted-either-raise-maxpoolsize-currently-100-or-timeout-currently-15-seconds/729>\
**Category:** CrateDB\
**Created:** [June 16, 2021, 6:10am UTC](https://community.cratedb.com/t/error-with-npgsql-the-connection-pool-has-been-exhausted-either-raise-maxpoolsize-currently-100-or-timeout-currently-15-seconds/729 "2021-06-16T06:10:12Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![testjob\_tyu](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/testjob_tyu/32/327_2.png) [@testjob\_tyu](https://community.cratedb.com/u/testjob_tyu)\
**Post date:** [June 16, 2021, 6:10am UTC](https://community.cratedb.com/t/error-with-npgsql-the-connection-pool-has-been-exhausted-either-raise-maxpoolsize-currently-100-or-timeout-currently-15-seconds/729/1 "2021-06-16T06:10:12Z")

</div>

I am using Npgsql connection and kubernetes service used for deployment.see below post for my configuration.

> [@How to connect NpgsqlDatabaseInfo for crate database in C#](https://community.cratedb.com/t/how-to-connect-npgsqldatabaseinfo-for-crate-database-in-c/698):
>
> I am very new to crate db. I created an REST API application in C# which connected to crate db and executing the database query. I’m using the [https://crate.io/docs/npgsql/en/latest/connect.html#connect](https://crate.io/docs/npgsql/en/latest/connect.html#connect) documentation. The datafactory related link is not working.[https://www.npgsql.org/doc/api/Npgsql.NpgsqlDatabaseInfo.html](https://www.npgsql.org/doc/api/Npgsql.NpgsqlDatabaseInfo.html) When multiple API calls are came, the time out exception occurred in NpgsqlDatabaseInfo.RegisterFactory(new CrateDbDatabaseInfoFactory()) connection. Below is my code . my…

Now i got an error check below

> The connection pool has been exhausted, either raise MaxPoolSize (currently 100) or Timeout (currently 15 seconds)

how to solve this

---

<div class="post-metadata">

**Author:** ![proddata](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/proddata/32/1379_2.png) [@proddata](https://community.cratedb.com/u/proddata)\
**Post date:** [June 16, 2021, 6:20am UTC](https://community.cratedb.com/t/error-with-npgsql-the-connection-pool-has-been-exhausted-either-raise-maxpoolsize-currently-100-or-timeout-currently-15-seconds/729/2 "2021-06-16T06:20:10Z")

</div>

This is a problem on your client / npgsql side.  
It seems like you set Max connection limit to 100 and are trying to open more than 100 connections. You either have to probably close the connections or reuse them.

---

<div class="post-metadata">

**Author:** ![testjob\_tyu](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/testjob_tyu/32/327_2.png) [@testjob\_tyu](https://community.cratedb.com/u/testjob_tyu)\
**Post date:** [June 16, 2021, 6:36am UTC](https://community.cratedb.com/t/error-with-npgsql-the-connection-pool-has-been-exhausted-either-raise-maxpoolsize-currently-100-or-timeout-currently-15-seconds/729/3 "2021-06-16T06:36:10Z")

</div>

i am always closing the connection when i open.  
is there any example for reusing the connection in c# web api?  
How do i increase the Max connection ?  
below is my connection flow

> <https://stackoverflow.com/questions/63772244/what-can-be-done-for-connection-re-use>

but still i got the

> The connection pool has been exhausted

error

---

<div class="post-metadata">

**Author:** ![testjob\_tyu](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/testjob_tyu/32/327_2.png) [@testjob\_tyu](https://community.cratedb.com/u/testjob_tyu)\
**Post date:** [June 16, 2021, 2:45pm UTC](https://community.cratedb.com/t/error-with-npgsql-the-connection-pool-has-been-exhausted-either-raise-maxpoolsize-currently-100-or-timeout-currently-15-seconds/729/4 "2021-06-16T14:45:46Z")

</div>

Could you please help on this issue?

---

<div class="post-metadata">

**Author:** ![amotl](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/amotl/32/617_2.png) [@amotl](https://community.cratedb.com/u/amotl)\
**Post date:** [June 17, 2021, 2:35pm UTC](https://community.cratedb.com/t/error-with-npgsql-the-connection-pool-has-been-exhausted-either-raise-maxpoolsize-currently-100-or-timeout-currently-15-seconds/729/5 "2021-06-17T14:35:40Z")

</div>

Dear @testjob_tyu,

thank you for sharing your observations about the behavior of the connection pool included in Npgsql. It is an important topic to configure and use a database connection pool appropriately in order to not overload the database.

### Some questions upfront

May I first ask if you observe that when having reasonable load (i.e. number of requests) on the application or, instead, experience it even in low traffic situations? This is important to tell apart because seeing that error might have different root causes in individual scenarios.

Also, I would like to humbly ask how and in which environment your Kubernetes infrastructure is operated. Is it some cloud/SaaS offering running on shared infrastructure operated by AWS, Azure, Google, Alibaba and friends or do you run and operate it completely on your own premises?

Both those details are important to know about before going deeper.

### Quick shot

Without aiming to be offensive in any way, let me cite some quotes from the discussion you shared with us [1].

> [@CodeCaster](#):
>
> Why did you create [the `GetDBConnection()`] method? Don’t outsmart the library’s and framework’s connection pooling mechanisms.

> [@TheGeneral](#):
>
> _Pooling_ is controlled by `Pooling=true` in the connection string, however it’s on by default.
> 
> - When you call `NpgsqlConnectiona.Open` the physical connection will be taken from the pool if one is exists, you don’t have to do anything.
> - On _disposing_ `NpgsqlConnection` it will be returned to the pool to be reused.
> 
> By default there is nothing you need to do or worry about.

In other words, this suggests to only use a single instance of

```auto
conn = new NpgsqlConnection(databaseConnString);

```

throughout your whole application but individually invoke `conn.Open()` and `conn.Close()` where you need a database connection. While I am not 100% sure about this, I believe the connection pooling mechanism is implemented through those methods.

According to [2], the default pool size (100) should be more than enough for handling quite a number of concurrent web requests reasonably, I don’t see a need to increase their values.

### Connection leaks?

> [@testjob\_tyu](#):
>
> i am always closing the connection when i open.

I also wanted to let you know that discussions about the same topic might just be happening at [3,4]. Maybe you can draw some inspirations from there, i.e. are you 100% sure you don’t have any connection leaks in your code?

### Further details

> [@testjob\_tyu](#):
>
> is there any example for reusing the connection in c# web api?

It will always be specific to the application / web framework which gets used. Maybe you can share more code of yours?

> [@testjob\_tyu](#):
>
> How do i increase the Max connection ?

You will have to add the respective settings to your database connection string, as outlined at [Connection String Parameters | Npgsql Documentation](https://www.npgsql.org/doc/connection-string-parameters.html#pooling). However, I strongly believe the default of 100 concurrent connections available to the database connection pool will be enough in most cases and should not be increased lightheartedly because that might cause to use too much resources on the database itself.

With kind regards,  
Andreas.

[1] [c# - what can be done for connection re use? - Stack Overflow](https://stackoverflow.com/questions/63772244/what-can-be-done-for-connection-re-use)  
[2] [Connection String Parameters | Npgsql Documentation](https://www.npgsql.org/doc/connection-string-parameters.html#pooling)  
[3] [NPGSQL connection pool issue · Issue #3829 · npgsql/npgsql · GitHub](https://github.com/npgsql/npgsql/issues/3829)  
[4] [postgresql - NPGSQL connection pool issue - Stack Overflow](https://stackoverflow.com/questions/67918873/npgsql-connection-pool-issue)

---

<div class="post-metadata">

**Author:** ![testjob\_tyu](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/testjob_tyu/32/327_2.png) [@testjob\_tyu](https://community.cratedb.com/u/testjob_tyu)\
**Post date:** [June 18, 2021, 6:22am UTC](https://community.cratedb.com/t/error-with-npgsql-the-connection-pool-has-been-exhausted-either-raise-maxpoolsize-currently-100-or-timeout-currently-15-seconds/729/6 "2021-06-18T06:22:01Z")

</div>

> [@amotl](#):
>
> May I first ask if you observe that when having reasonable load

I have two services. One for insert the data into crate database and other one is fetching the same data (some aggregation query applied).  
Inserting application is always running and it receive 600 data per minute. When the web user interface calling the fetch application, it will collect the data by fetch query. 50 query called in 10 seconds from web user interface. this is the entire situation. But some times got pool exhausted error. Not always.

> [@amotl](#):
>
> Also, I would like to humbly ask how and in which environment your Kubernetes infrastructure is operated. Is it some cloud/SaaS offering running on shared infrastructure operated by AWS, Azure, Google, Alibaba and friends or do you run and operate it completely on your own premises?

Azure kubernetes.

**For all other question i will share my code below.**  
**I am using dotnet core 3.1 console application for database insert.**  
**Code is below**

```
    public async Task InsertData(DataViewModel data, string tablename)
    {
        try
        {
      var ConnectionString = "Host=crate-internal-service;Username=crate;SSL Mode=Prefer;Database=doc"
            using (NpgsqlConnection sqlCon = new NpgsqlConnection(ConnectionString))
            {
                sqlCon.Open();
                string cmdString = $"INSERT INTO {tablename} (parameterid,deviceid,gatewayid,reading,timestamp) VALUES (@s,@d,@g,@r,@t)";
                await using (NpgsqlCommand sqlCmd = new NpgsqlCommand(cmdString, sqlCon))
                {
                    sqlCmd.Parameters.AddWithValue("s", data.parameterid);
                    sqlCmd.Parameters.AddWithValue("d", data.deviceid);
                    sqlCmd.Parameters.AddWithValue("g",data.gatewayid);
                    sqlCmd.Parameters.AddWithValue("r", data.reading);
                    sqlCmd.Parameters.AddWithValue("t", data.timestamp);
                    await sqlCmd.ExecuteNonQueryAsync();
                }
                 sqlCon.Close();
            }
        }
        catch (Exception e)
        {
            Console.WriteLine(e.Message);
        }
    }

```

**For fetching the data uisng c# dotnet core3.1 web api application**

```
public async Task<List<Aggregation>> Aggregated(Aggregation model,string tablename)

    {
  var ConnectionString = "Host=crate-internal-service;Username=crate;SSL Mode=Prefer;Database=doc"

        var result = new List<Aggregation>();
        try
        {
             using (NpgsqlConnection sqlCon = new NpgsqlConnection(SqlConnectionString))
            {
                sqlCon.Open();
                
                var cmd = $"select date_trunc('{model.groupby}','Asia/Kolkata', v.timestamp) as unixtime,{model.operation}(reading) as reading from {tablename} v(parameterid,deviceid,reading,timestamp) where parameterid IN (@pt) AND timestamp>=@st AND timestamp<=@et group by (unixtime) ORDER BY unixtime ASC";
                using (NpgsqlCommand sqlCmd = new NpgsqlCommand(cmd, sqlCon))
                {
                    sqlCmd.Parameters.AddWithValue("pt", parameteres);
                    sqlCmd.Parameters.AddWithValue("st", model.starttime);
                    sqlCmd.Parameters.AddWithValue("et", model.endtime);

                    await using (var reader = await sqlCmd.ExecuteReaderAsync())
                    {
                        while (reader.HasRows && await reader.ReadAsync())
                        {
                       
                            Aggregation item = new Aggregation
                            {
                                reading = readingValue,
                            };
                            result.Add(item);
                        }
                    }
                }
                 sqlCon.Close();
            }
        }
        catch (Exception e)
        {
            Console.WriteLine(e);
        }
        return result;
    }

```

Always calling the new NpgsqlConnection(SqlConnectionString)) , open and close . When a data come and insert into the database, connect,open and close functions called every time. Same way for fetch application.

---

<div class="post-metadata">

**Author:** ![amotl](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/amotl/32/617_2.png) [@amotl](https://community.cratedb.com/u/amotl)\
**Post date:** [June 18, 2021, 2:24pm UTC](https://community.cratedb.com/t/error-with-npgsql-the-connection-pool-has-been-exhausted-either-raise-maxpoolsize-currently-100-or-timeout-currently-15-seconds/729/7 "2021-06-18T14:24:38Z")

</div>

Dear @testjob_tyu,

thank you for sharing more details about your environment and application. Now, let’s temporarily assume it is **not** an _overload_ situation, but an _underutilization_ issue instead. In order to explain that way of thinking about the problem and the respective path of investigations further, let me elaborate a bit more about what we found out the other day.

### Prerequisite thoughts

You outlined that on the write path, you have a sustained insert operation going on. However, on the read path, there might be intermittent pauses between batches of requests? Now, if we assume the database connections serving those read requests will be put back in the connection pool and get stale while not being reused before the configured TCP timeout, the observed behavior like “connection pool exhausted” might be exactly the same as usually seen in overcommitment situations.

### Background about cloud networking infrastructure

The reason why I asked about the hosting environment is that we have been able to observe nasty issues when operating long-lived TCP connections in typical cloud environments, which is a general issue and not specifically related to CrateDB. Specifically, we encountered those issues at the first time when operating Kafka on Azure but they also apply to other cloud provider’s infrastructures in one way or another.

It is related to the load balancing component, i.e. the NAT gateway typically used within cloud infrastructures to channel outbound network connections. We already assembled a comprehensive report about this topic at [Networking robustness and resiliency on Azure and beyond (AWS, GCP, AliCloud) · Issue #10779 · crate/crate · GitHub](https://github.com/crate/crate/issues/10779), a descendant of the original RCA at [Robustness and resiliency on Azure · Issue #3109 · confluentinc/librdkafka · GitHub](https://github.com/edenhill/librdkafka/issues/3109).

### Adjusting some driver settings

Now, attaching to that, in order to rule out those issues, we will probably have to adjust/enable some TCP keepalive features/threshold values on the Npgsql client settings. Maybe playing around with these connection string parameters can help:

- `Connection Idle Lifetime=200`
- `Tcp Keepalive=true`
- `Keepalive=30`

Those resources are helpful to look up documentation about these parameters:

- [Connection String Parameters | Npgsql Documentation](https://www.npgsql.org/doc/connection-string-parameters.html#pooling)
- [Connection String Parameters | Npgsql Documentation](https://www.npgsql.org/doc/connection-string-parameters.html#timeouts-and-keepalive)
- [Keepalive | Npgsql Documentation](https://www.npgsql.org/doc/keepalive.html)

Please let me know about the outcome. If that doesn’t help, we will have to dig deeper.

With kind regards,  
Andreas.

---

<div class="post-metadata">

**Author:** ![amotl](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/amotl/32/617_2.png) [@amotl](https://community.cratedb.com/u/amotl)\
**Post date:** [June 18, 2021, 2:45pm UTC](https://community.cratedb.com/t/error-with-npgsql-the-connection-pool-has-been-exhausted-either-raise-maxpoolsize-currently-100-or-timeout-currently-15-seconds/729/8 "2021-06-18T14:45:26Z")

</div>

> [@testjob\_tyu](#):
>
> ```auto
> sqlCon.Open();
> // ...
> sqlCon.Close();
> 
> ```

This is really just a wild guess, but at [1,2], I discovered the asynchronous variants `OpenAsync()` and `CloseAsync()`. Maybe using them will improve the runtime behavior right away, specifically when it comes to proper cleanup of resources.

On the other hand, [3] also has an interesting discussion about whether to favor `Dispose()` over `Close()`. In this case, you should probably also use the asynchronous variant `DisposeAsync()`.

[1] [https://github.com/npgsql/npgsql/blob/v5.0.7/src/Npgsql/NpgsqlConnection.cs#L143-L151](https://github.com/npgsql/npgsql/blob/v5.0.7/src/Npgsql/NpgsqlConnection.cs#L143-L151)  
[2] [https://github.com/npgsql/npgsql/blob/v5.0.7/src/Npgsql/NpgsqlConnection.cs#L683-L695](https://github.com/npgsql/npgsql/blob/v5.0.7/src/Npgsql/NpgsqlConnection.cs#L683-L695)  
[3] [c# - Why does this `using` block not release the database connection? - Stack Overflow](https://stackoverflow.com/questions/59424996/why-does-this-using-block-not-release-the-database-connection)

---

<div class="post-metadata">

**Author:** ![testjob\_tyu](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/testjob_tyu/32/327_2.png) [@testjob\_tyu](https://community.cratedb.com/u/testjob_tyu)\
**Post date:** [June 20, 2021, 9:51am UTC](https://community.cratedb.com/t/error-with-npgsql-the-connection-pool-has-been-exhausted-either-raise-maxpoolsize-currently-100-or-timeout-currently-15-seconds/729/9 "2021-06-20T09:51:16Z")

</div>

Okay,I will try this tcp connection problem

---

<div class="post-metadata">

**Author:** ![proddata](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/proddata/32/1379_2.png) [@proddata](https://community.cratedb.com/u/proddata)\
**Post date:** [June 20, 2021, 6:36pm UTC](https://community.cratedb.com/t/error-with-npgsql-the-connection-pool-has-been-exhausted-either-raise-maxpoolsize-currently-100-or-timeout-currently-15-seconds/729/10 "2021-06-20T18:36:13Z")

</div>

> [@testjob\_tyu](#):
>
> `ExecuteNonQueryAsync`

From the placement of your `try-catch`-block it seems like you are only closing the connections, when there is no exception.

You might want to move it and close the `sqlCon` in a `finally`-block.

Also, are you sure all your code is non-blocking, how many open pgsql-connections do you see in cratedb?

```sql
SELECT SUM(connections['psql']['open']) as pg_sql FROM sys.nodes limit 100;

```