Set minimum idle connections in SQL pool

Question

Background: I want to reduce the response time when working with SQL databases in my Go applications.

Golang provides an SQL package with a connection pool. It includes the following configuration options:

func (db *DB) SetConnMaxIdleTime(d time.Duration)
func (db *DB) SetConnMaxLifetime(d time.Duration)
func (db *DB) SetMaxIdleConns(n int)
func (db *DB) SetMaxOpenConns(n int)

However, there is nothing like SetMinIdleConns to ensure that there are always some open connections that can be used when a request comes in. As a result, my application has good response times under load, but the first request after some idle time always has some delay because it needs to open a new connection to the database.

Question: Is there a way to fix this with the Go standard library or is there some alternative connection pooling library for Go with this feature?

Workarounds and already tried:

I tried setting ConnMaxIdleTime and ConnMaxLifetime to very high values, but then the SQL server closes them and I get even higher delays or even errors on the first call after a long idle time.
Obviously, I could create a background task that periodically uses the database. However, this does not seem like a clean solution.
I am considering porting a connection pool library from another language to Go.

An occasional delay of a couple milliseconds doesn't seem significant enough to warrant the time to implement and maintain a custom connection pool and the resources to maintain unused connections at all times. Are you sure you actually need this? — Adrian, Aug 04 '21 at 17:22
A few milliseconds would be fine, but its usually a lot longer (see e.g. https://stackoverflow.com/questions/57094912/long-connection-time-with-azure-postgres). And it's not only occasionally. It's happening on a demo system which is maybe used once a week. And in a demo for selling a product, the first impression is especially important. — Peter Zeller, Aug 04 '21 at 21:53

score 0 · Accepted Answer · answered Sep 28 '21 at 09:20

The pgx/pgxpool library provides a connection pool for Postgres, which allows to configure the minimum number of connections:

    connConf, err := pgxpool.ParseConfig(connStr)
    // ... (error handling)
    connConf.MaxConns = 50
    // Set minimum number of connections:
    connConf.MinConns = 10
    pool, err := pgxpool.ConnectConfig(context.TODO(), connConf)

Set minimum idle connections in SQL pool

1 Answers1

Linked