Sequelize + postgres, how to calculate Connection Pool size?

Question

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?

Did you ever get a grasp on this? Been down the exact same rabbit hole(s) myself, ha. :) — ntschier
– ntschier, Commented Feb 8, 2022 at 17:24

Laurenz Albe · Accepted Answer · 2021-02-05 17:48:01Z

3

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.

answered Feb 5, 2021 at 17:48

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Sequelize + postgres, how to calculate Connection Pool size?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related