CODEWITHSUNDEEP
node.jspostgresqlsequelize.jsdatabase-performance
gregs
gregs

Reputation: 49

Sequelize + postgres, how to calculate Connection Pool size?

In our nodejs app, we started to have SequelizeConnectionAcquireTimeoutError errors. Currently we are using default sequelize connection settings:

{
    max: 5,
    min: 0,
    acquire: 30000,
    idle: 10000
}

How to choose the best value of max pool?

I search lots of similar questions ant websites but I could not find a specific answer to this question. I use sequelize in connection with postgres, The only, most sensible information I found on the Pg wiki: https://wiki.postgresql.org/wiki/Number_Of_Database_Connections

Where is the paragraph "How to Find the Optimal Database Connection Pool Size", and answer (in short):

A formula which has held up pretty well across a lot of benchmarks for years is that for optimal throughput the number of active connections should be somewhere near ((core_count * 2) + effective_spindle_count).

I have found very similar topic on stack overflow which can help provide context of my problem - but without answer. Nodejs, Optimal parameters for sequelize connection pool?

Upvotes: 2

Views: 2459

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247625

The formula from the PostgreSQL Wiki assumes that the sessions in the connection pool are never idle, so the limit may be too low. I have suggested an improved formula in my blog.

The problem is that with PostgreSQL versions before v14, it is difficult to estimate how much of your transaction time is spent waiting for the client. You will have to experiment.

The size of the connection pool should be small enough that the database is not overloaded even if all connecrions are in use. There should never be more active connections than you have cores in the database machine.

Upvotes: 2

Related Questions