Add Pool Idle Timeout to control connection pool

[David-Engel]

From an internal email thread asking about ADO.NET connection pooling, we have this feature request from an ISV:

Is your feature request related to a problem? Please describe.

Right now, connections returned to the pool are automatically managed and closed/disposed from the pool after 4-8 minutes of idle time. This request is to be able to configurable the amount of time a connection can be idle in the pool before it is closed. "Pool Idle Timeout".

Describe the solution you'd like

A new connection string setting, "Pool Idle Timeout" which defines the number of seconds a connection can be idle in the pool before it is automatically closed.

Describe alternatives you've considered

Letting the 4-8 minute auto close do its thing. Using ClearPool() and ClearAllPools().

[roji]

FYI Npgsql has this option under the name Connection Idle Lifetime.

[saurabh500]

@David-Engel wouldnt simply increasing the Min Pool Size help?
Also I was wondering what guidance would the application author use to set the connection lifetime?
@roji how do customers of Npgsql know what value to set for this connection string param?

[roji]

@saurabh500 yeah, Connection Idle Lifetime and Pruning Interval are what control Npgsql's connection pool pruning mechanism; once you have more than Min Pool Size idle connections in the pool, these two parameters determine how/when connections get pruned. The default value for this is 300 (5 minutes), but users can e.g. dial it down if they want more aggressive pruning; that would reduce the overall idle connection strain on the server, but can cause more thrashing if new physical connections need to be created because the previous ones were pruned etc.

Note that Npgsql doesn't track specific connections for pruning reasons. In other words, we don't care if a single connection was idle for 5 minutes, or different connections came in and out of the pool but 1 was always idle in there. We simply sample the pool status every given interval, and if we see a constant idle situation for Connection IdleL Lifetime, we start pruning etc.

[David-Engel]

@saurabh500
I've forgotten any additional context from the customer, at this point. But idle timeout is a pretty common knob in pooling libraries to control how aggressively idle connections are pruned.

Examples:
HilariCP
Apache Commons CP
C3PO