Repository: jackc/pgx (go)

Issue:
Feature request: MinIdleConns
**Is your feature request related to a problem? Please describe.**

Right now pgxpool provides MinConns, which addresses the situation where you want your application to be able to always have at least some connections ready for when a new request comes in, if you care about tail latencies and don't want to sometimes introduces a DB connection in the middle of trying to serve the request (which matters when you have a remote DB and TLS is involved, which can sometimes be hundreds of milliseconds).

However, this does not address the situation that you have a fairly active server, so may have a good number of connections open at peak time, so MinConns does not kick in. But it may not have many idle connections when handling its traffic. This exposes one to the situation of potentially not having connections available to handle new requests again.

Of course one could set MinConns so high that it is above both your off-peak and on-peak usage, but that would be a waste of resources.

**Describe the solution you'd like**
I believe something like `MinIdleConns` would solve this. The pool could always try to maintain at least `MinIdleConns` extra connections on top of the current amount of in-use connections. This would mean the application is always prepared to handle a burst of `MinIdleConns` simultaneous requests on top of whatever load it already has. Since this would be configurable, it would allow application owners to adjust it for whatever amount of burst traffic they want to be ready for.

**Additional context**
I'm happy to try and implement this if I can get an OK from a maintainer that it seems like a reasonable architecture and I'm not missing something.

Pull request:
Add MinIdleConns
Resolves #2278. (It was a quick implementation so I just went ahead and did it while my attention was here.)

Patch:
diff --git a/pgxpool/pool.go b/pgxpool/pool.go
index 270b7617..e22ed289 100644
--- a/pgxpool/pool.go
+++ b/pgxpool/pool.go
@@ -17,6 +17,7 @@ import (
 
 var defaultMaxConns = int32(4)
 var defaultMinConns = int32(0)
+var defaultMinIdleConns = int32(0)
 var defaultMaxConnLifetime = time.Hour
 var defaultMaxConnIdleTime = time.Minute * 30
 var defaultHealthCheckPeriod = time.Minute
@@ -87,6 +88,7 @@ type Pool struct {
 	afterRelease          func(*pgx.Conn) bool
 	beforeClose           func(*pgx.Conn)
 	minConns              int32
+	minIdleConns          int32
 	maxConns              int32
 	maxConnLifetime       time.Duration
 	maxConnLifetimeJitter time.Duration
@@ -144,6 +146,13 @@ type Config struct {
 	// to create new connections.
 	MinConns int32
 
+	// MinIdleConns is the minimum number of idle connections in the pool. You can increase this to ensure that
+	// there are always idle connections available. This can help reduce tail latencies during request processing,
+	// as you can avoid the latency of establishing a new connection while handling requests. It is superior
+	// to MinConns for this purpose.
+	// Similar to MinConns, the pool might temporarily dip below MinIdleConns after connection closes.
+	MinIdleConns int32
+
 	// HealthCheckPeriod is the duration between checks of the health of idle connections.
 	HealthCheckPeriod time.Duration
 
@@ -189,6 +198,7 @@ func NewWithConfig(ctx context.Context, config *Config) (*Pool, error) {
 		afterRelease:          config.AfterRelease,
 		beforeClose:           config.BeforeClose,
 		minConns:              config.MinConns,
+		minIdleConns:          config.MinIdleConns,
 		maxConns:              config.MaxConns,
 		maxConnLifetime:       config.MaxConnLifetime,
 		maxConnLifetimeJitter: config.MaxConnLifetimeJitter,
@@ -271,7 +281,8 @@ func NewWithConfig(ctx context.Context, config *Config) (*Pool, error) {
 	}
 
 	go func() {
-		p.createIdleResources(ctx, int(p.minConns))
+		targetIdleResources := max(int(p.minConns), int(p.minIdleConns))
+		p.createIdleResources(ctx, targetIdleResources)
 		p.backgroundHealthCheck()
 	}()
 
@@ -334,6 +345,17 @@ func ParseConfig(connString string) (*Config, error) {
 		config.MinConns = defaultMinConns
 	}
 
+	if s, ok := config.ConnConfig.Config.RuntimeParams["pool_min_idle_conns"]; ok {
+		delete(connConfig.Config.RuntimeParams, "pool_min_idle_conns")
+		n, err := strconv.ParseInt(s, 10, 32)
+		if err != nil {
+			return nil, fmt.Errorf("cannot parse pool_min_idle_conns: %w", err)
+		}
+		config.MinIdleConns = int32(n)
+	} else {
+		config.MinIdleConns = defaultMinIdleConns
+	}
+
 	if s, ok := config.ConnConfig.Config.RuntimeParams["pool_max_conn_lifetime"]; ok {
 		delete(connConfig.Config.RuntimeParams, "pool_max_conn_lifetime")
 		d, err := time.ParseDuration(s)
@@ -472,7 +494,9 @@ func (p *Pool) checkMinConns() error {
 	// TotalConns can include ones that are being destroyed but we should have
 	// sleep(500ms) around all of the destroys to help prevent that from throwing
 	// off this check
-	toCreate := p.minConns - p.Stat().TotalConns()
+
+	// Create the number of connections needed to get to both minConns and minIdleConns
+	toCreate := max(p.minConns-p.Stat().TotalConns(), p.minIdleConns-p.Stat().IdleConns())
 	if toCreate > 0 {
 		return p.createIdleResources(context.Background(), int(toCreate))
 	}


Test patch:
diff --git a/pgxpool/common_test.go b/pgxpool/common_test.go
index b2797027..009616a1 100644
--- a/pgxpool/common_test.go
+++ b/pgxpool/common_test.go
@@ -147,6 +147,7 @@ func assertConfigsEqual(t *testing.T, expected, actual *pgxpool.Config, testName
 	assert.Equalf(t, expected.MaxConnIdleTime, actual.MaxConnIdleTime, "%s - MaxConnIdleTime", testName)
 	assert.Equalf(t, expected.MaxConns, actual.MaxConns, "%s - MaxConns", testName)
 	assert.Equalf(t, expected.MinConns, actual.MinConns, "%s - MinConns", testName)
+	assert.Equalf(t, expected.MinIdleConns, actual.MinIdleConns, "%s - MinIdleConns", testName)
 	assert.Equalf(t, expected.HealthCheckPeriod, actual.HealthCheckPeriod, "%s - HealthCheckPeriod", testName)
 
 	assertConnConfigsEqual(t, expected.ConnConfig, actual.ConnConfig, testName)
diff --git a/pgxpool/pool_test.go b/pgxpool/pool_test.go
index 90428931..4cc7f47e 100644
--- a/pgxpool/pool_test.go
+++ b/pgxpool/pool_test.go
@@ -43,10 +43,11 @@ func TestConnectConfig(t *testing.T) {
 func TestParseConfigExtractsPoolArguments(t *testing.T) {
 	t.Parallel()
 
-	config, err := pgxpool.ParseConfig("pool_max_conns=42 pool_min_conns=1")
+	config, err := pgxpool.ParseConfig("pool_max_conns=42 pool_min_conns=1 pool_min_idle_conns=2")
 	assert.NoError(t, err)
 	assert.EqualValues(t, 42, config.MaxConns)
 	assert.EqualValues(t, 1, config.MinConns)
+	assert.EqualValues(t, 2, config.MinIdleConns)
 	assert.NotContains(t, config.ConnConfig.Config.RuntimeParams, "pool_max_conns")
 	assert.NotContains(t, config.ConnConfig.Config.RuntimeParams, "pool_min_conns")
 }