Suitable values for poolSize, poolIdleTimeout and reapIntervalMillis

[WarTech9]

This is more of a question than an issue.

I'm using node-postgres and have had some issues with the connection pool getting exhausted (I think mainly due to connection leaks). I'm trying to find out the optimal values for pool size and timeouts. Let's say I expect to have 1000 concurrent users and most users will be making requests that result in db requests. My database server has limited resources with 1 CPU and 2GB or RAM.

What would be the ideal values for these settings? I'm currently using this:

pg.defaults.poolSize = 25;
pg.defaults.reapIntervalMillis = 1000;
pg.defaults.poolIdleTimeout = 1000;

Also I'm a question about the poolIdleTimeout. The comments in the code says:

max milliseconds a client can go unused before it is removed from the pool and destroyed

Does this mean the amount of time before the connection is closed and returned to the pool, or the connection is closed and removed from the pool, thus decreasing pool size?

[charmander]

Does this mean the amount of time before the connection is closed and returned to the pool, or the connection is closed and removed from the pool, thus decreasing pool size?

I believe it refers to how long a client will stay connected and idle while available in the pool. If you acquire a client from the pool, it will never time out and be returned. So…

I think mainly due to connection leaks

… fix those! Bluebird’s resource management might help.

[WarTech9]

… fix those! Bluebird’s resource management might help.

Thanks for the tip. I believe I had one particular code path that gets hit rarely, so over time I was losing connections. Another useful tool in tracking that down was postgres' query:

SELECT * FROM pg_stat_activity

Which lists all open connections, thus helped me find ones that shouldn't be open after they've been used. While I'm at it though it would be best to make sure connection pool setup is optimal.

[abenhamdine]

so over time I was losing connections

Facing the same issue, I have tried to decrease poolIdleTimeout to 2000 ms, but it's not efficient : when you have connexion leaks in a hot path, the only way is to find & fix them.

The main problem imho is still that pg.connect dont return error when the pool is exhausted, see #1006

[brianc]

I added this to the docs subtask to document stuff about leaky connections.

[jacobbogers]

why are connections leaking?, isnt it supposed to be returned to the pool with the callback "done"?
is done() not working.???
i.e.

pool.connect((err, client, done) => {
    //do something usefull
    done();   <-- return back to pool?            
}

[charmander]

@jacobbogers Is that a bug report? You’ll need to provide more details.

[rue]

@brianc @charmander …Am I missing where poolIdleTimeout and reapIntervalMillis are actually being used?

node-pg-pool accepts a config parameter named idleTimeoutMillis, and there’s no ‘reap’ anything in either node-pg-pool or node-pool (there is an evictionRunIntervalMillis). I don’t see the node-postgres names being translated to those anywhere. Looks like they might’ve been lost when pool.js was removed, but the option passing gets a little convoluted so maybe I’m losing track somewhere.

If not, it looks like the current property should be idleTimeoutMillis (and connectionTimeoutMillis) and reap configuration is not supported at all?

[charmander]

If not, it looks like the current property should be idleTimeoutMillis (and connectionTimeoutMillis) and reap configuration is not supported at all?

Right. Is there documentation contradicting that?

[abenhamdine]

@rue you're right, I think poolIdelTimeOut, returnToHead, and reapIntervalMillis have been removed with the update to 7.x.
FI, there's still some remaining occurrences in default.js, see #1438

[charmander]

There’s a connection timeout option now to address #1222 (comment), and idleTimeoutMillis replaces poolIdleTimeout. Documentation at https://node-postgres.com/api/pool/. (As for the original issue, ignoring leaks: the ideal pool size and idle timeout depend on too many factors to prioritize in documentation, I think, and the defaults will usually not be the issue except to people who’ll know where to look.)