Does the pool support "idle" connection ?

[allan-simon]

I don't see anywhere how to set the pool so that it keeps X number of connections open at any moments to keep the pool warm in case of burst ?

[sfackler]

The pool maintains a fixed number of connections, which is specified via the pool_size field of the config: http://sfackler.github.io/r2d2/doc/v0.6.1/r2d2/config/struct.Builder.html#method.pool_size

[allan-simon]

oh then my question is reversed : how can i set the maximun number of connection my pool will reach ?

i.e is it possible to have both "set max idle connection" and "set max open connection" like in go

https://golang.org/pkg/database/sql/#DB.SetMaxIdleConns
https://golang.org/pkg/database/sql/#DB.SetMaxOpenConns

[sfackler]

pool_size corresponds to MaxOpenConns. There is no equivalent to MaxIdleConns.

[allan-simon]

I see, is it something that you think may be useful to implement ?

[sfackler]

It's not totally clear to me what purpose MaxIdleConns is supposed to serve. What's the use case for wanting to adjust it?

[allan-simon]

actually it's a pretty frequent use case, at least in the companies I've worked

you have a database used by several systems, or like in amazon, with a hard limited number of connection
so you want to have your MaxOpenConns to be sure to not reach this hard limit.
However, your services is only used in burst (for example once every hour or so) so you don't want to keep MaxOpenConns open all the times because it will be a waste of resource (as your other services may not have their burst moment at the same time) but you don't want all the connection to be garbage collected/timeout as it would meant:

1. the X first connections will be a little slower the time the connection to the db is opened
2. all the connections may already have been used in the meantime by an other service in burst time, so this way you're sure to be able to provide an access to the database.

this carefull management of number of connection opened is especially important in cloud-based services, where your postgresql instance are priced by number of max connection, and from one instance size to an other you can easily double or halfed the price

[sfackler]

At least for Amazon RDS, you're billed based on hardware and storage requirements, not active connections, right? For some workload, there is an optimal number of connections at which throughput and cost is optimal. What is the downside in maintaining exactly that number of connections at all times? What are the resources being conserved for?

In any case, I'm not so opposed to allowing the pool size to be non-fixed, but I'm not sure what the configuration parameter should be. Go's pool has SetMaxOpenConns and SetMaxIdleConns, HikariCP has minimumIdle and maximumPoolSize. c3p0 has maxPoolSize, minPoolSize, and some other parameters to configure how connections are added and dropped. Go's setup seems like it would cause a lot of thrashing if load is bouncing above and below the MaxIdleConns threshold.

[allan-simon]

The main argument I got for that is that by keeping a number > 0 (and < Max pool size) of connection at anytime, you prevent yourself from being starved by other application (while trying to not be too greedy by keeping it < Max pool size)

[allan-simon]

As for RDS, my bad, I discovered only today that it's possible to change the max number of connection (cargo cult in my team), but as it's not something that can be changed dynamically, my point above still stands

[sfackler]

I think I'm leaning towards the Hikari/c3p0 config strategy, though those will require some more functionality to be added that I've been meaning to do for a while (max idle time, for example).

[allan-simon]

ok great, thanks :)

[sfackler]

There are now three new configuration options.

• idle_timeout Connections will be destroyed if they are left idle for this long
• max_lifetime Connections will be destroyed after they have existed for this period of time
• min_idle The pool will try to keep this many connections idle at all times. This is the option you'll drop below pool_size if you want a variable sized pool

Seem like that'll work?

[allan-simon]

awesome , yes it should do the trick

[sfackler]

Released v0.6.2 with these changes.