sequelize - connection pool size

This is a very broad question but here is very broad overview

Whenever we connection to db server, it actually forks a new process to fulfill that request. As you can expect, this is expensive. So pool allows us to keep the number of processes active in db server. max means that no matter how many request your app(node) gets, it will not open up a new process with the db server.

and if 6users want to get the DB, 5connections are all allocated to the individual user, and since there is only 5 connections, the 6th user has to wait.

In the above case, only 5 parallel request can run with the db server (not the app server)

Here is a good link to read

Here is an example demonstrating the effect of pool.max and pool.idle options.

Environment:

• "sequelize": "^5.21.3"
• node: v12.16.1
• PostgreSQL: 9.6

Client code:

db.ts:

const sequelize = new Sequelize({  dialect: 'postgres',  host: envVars.POSTGRES_HOST,  username: envVars.POSTGRES_USER,  password: envVars.POSTGRES_PASSWORD,  database: envVars.POSTGRES_DB,  port: Number.parseInt(envVars.POSTGRES_PORT, 10),  define: {    freezeTableName: true,    timestamps: false,  },  pool: {    max: 5,    min: 0,    idle: 10 * 1000,  },});export { sequelize };

pool_test.ts:

import { sequelize } from '../../db';for (let i = 0; i < 100; i++) {  sequelize.query('select pg_sleep(1);');}

Run a PostgreSQL server by docker container:

CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                    NAMES3c9c0fd1bf53        postgres:9.6        "docker-entrypoint.s…"   5 months ago        Up 27 hours         0.0.0.0:5430->5432/tcp   node-sequelize-examples_pg_1

Run the test code:

DEBUG=sequelize* npx ts-node ./pool_test.ts

Debug logs:

  sequelize:pool pool created with max/min: 5/0, no replication +0ms  sequelize:connection:pg connection acquired +0ms  sequelize:connection:pg connection acquired +38ms  sequelize:connection:pg connection acquired +3ms  sequelize:connection:pg connection acquired +0ms  sequelize:connection:pg connection acquired +1ms  sequelize:connection:pg connection acquired +1ms  sequelize:pool connection acquired +97ms  sequelize:sql:pg Executing (default): select pg_sleep(1); +0msExecuting (default): select pg_sleep(1);  sequelize:pool connection acquired +2ms  sequelize:pool connection acquired +0ms  sequelize:pool connection acquired +0ms  sequelize:pool connection acquired +0ms  sequelize:sql:pg Executing (default): select pg_sleep(1); +2msExecuting (default): select pg_sleep(1);  sequelize:sql:pg Executing (default): select pg_sleep(1); +2msExecuting (default): select pg_sleep(1);  sequelize:sql:pg Executing (default): select pg_sleep(1); +0msExecuting (default): select pg_sleep(1);  sequelize:sql:pg Executing (default): select pg_sleep(1); +0msExecuting (default): select pg_sleep(1);  sequelize:sql:pg Executed (default): select pg_sleep(1); +1s  sequelize:pool connection released +1s  sequelize:pool connection acquired +1ms  sequelize:sql:pg Executed (default): select pg_sleep(1); +2ms  sequelize:sql:pg Executed (default): select pg_sleep(1); +0ms  sequelize:sql:pg Executed (default): select pg_sleep(1); +0ms  sequelize:sql:pg Executed (default): select pg_sleep(1); +0ms  sequelize:sql:pg Executing (default): select pg_sleep(1); +1msExecuting (default): select pg_sleep(1);  sequelize:pool connection released +1ms  sequelize:pool connection released +0ms  sequelize:pool connection released +0ms  sequelize:pool connection released +0ms  sequelize:pool connection acquired +1ms  sequelize:pool connection acquired +0ms  sequelize:pool connection acquired +0ms  sequelize:pool connection acquired +0ms

Enter the docker container, check the connection process:

root@3c9c0fd1bf53:/# date '+%A %W %Y %X' && ps aux | grep "postgres: testuser"Thursday 31 2020 09:51:34 AMpostgres 13615  0.0  0.7 289496 16064 ?        Ss   08:29   0:00 postgres: testuser node-sequelize-examples [local] idlepostgres 14335  0.0  0.5 288384 11248 ?        Ss   09:51   0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45704) SELECTpostgres 14336  0.0  0.5 288384 11248 ?        Ss   09:51   0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45706) SELECTpostgres 14337  0.0  0.5 288384 11252 ?        Ss   09:51   0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45708) SELECTpostgres 14338  0.0  0.5 288384 11248 ?        Ss   09:51   0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45710) SELECTpostgres 14339  0.0  0.5 288384 11248 ?        Ss   09:51   0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45712) SELECTpostgres 86437  0.0  0.6 288804 13704 ?        Ss   00:57   0:00 postgres: testuser node-sequelize-examples [local] idle

As you can see, there are 5(pool.max) connection processes.

After the connection processes IDLE 10(pool.idle) seconds. The connection processes will be destroyed.

root@3c9c0fd1bf53:/# date '+%A %W %Y %X' && ps aux | grep "postgres: testuser"Thursday 31 2020 09:53:48 AMpostgres 13615  0.0  0.7 289496 16064 ?        Ss   08:29   0:00 postgres: testuser node-sequelize-examples [local] idlepostgres 14352  0.0  0.5 288384 11248 ?        Ss   09:53   0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45716) idlepostgres 14353  0.0  0.5 288384 11252 ?        Ss   09:53   0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45718) idlepostgres 14354  0.0  0.5 288384 11248 ?        Ss   09:53   0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45720) SELECTpostgres 14355  0.0  0.5 288384 11248 ?        Ss   09:53   0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45722) idlepostgres 14356  0.0  0.5 288384 11248 ?        Ss   09:53   0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45724) SELECTroot     14440  0.0  0.0  12784   972 pts/3    S+   09:53   0:00 grep postgres: testuserpostgres 86437  0.0  0.6 288804 13704 ?        Ss   00:57   0:00 postgres: testuser node-sequelize-examples [local] idleroot@3c9c0fd1bf53:/# date '+%A %W %Y %X' && ps aux | grep "postgres: testuser"Thursday 31 2020 09:53:49 AMpostgres 13615  0.0  0.7 289496 16064 ?        Ss   08:29   0:00 postgres: testuser node-sequelize-examples [local] idlepostgres 14352  0.0  0.5 288384 11248 ?        Ss   09:53   0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45716) idlepostgres 14353  0.0  0.5 288384 11252 ?        Ss   09:53   0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45718) idlepostgres 14354  0.0  0.5 288384 11248 ?        Ss   09:53   0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45720) idlepostgres 14355  0.0  0.5 288384 11248 ?        Ss   09:53   0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45722) idlepostgres 14356  0.0  0.5 288384 11248 ?        Ss   09:53   0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45724) idlepostgres 86437  0.0  0.6 288804 13704 ?        Ss   00:57   0:00 postgres: testuser node-sequelize-examples [local] idleroot@3c9c0fd1bf53:/# date '+%A %W %Y %X' && ps aux | grep "postgres: testuser"Thursday 31 2020 09:53:55 AMpostgres 13615  0.0  0.7 289496 16064 ?        Ss   08:29   0:00 postgres: testuser node-sequelize-examples [local] idlepostgres 14352  0.0  0.5 288384 11248 ?        Ss   09:53   0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45716) idlepostgres 14353  0.0  0.5 288384 11252 ?        Ss   09:53   0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45718) idlepostgres 14354  0.0  0.5 288384 11248 ?        Ss   09:53   0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45720) idlepostgres 14355  0.0  0.5 288384 11248 ?        Ss   09:53   0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45722) idlepostgres 14356  0.0  0.5 288384 11248 ?        Ss   09:53   0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45724) idleroot     14446  0.0  0.0  12784   932 pts/3    S+   09:53   0:00 grep postgres: testuserpostgres 86437  0.0  0.6 288804 13704 ?        Ss   00:57   0:00 postgres: testuser node-sequelize-examples [local] idleroot@3c9c0fd1bf53:/# date '+%A %W %Y %X' && ps aux | grep "postgres: testuser"Thursday 31 2020 09:53:58 AMpostgres 13615  0.0  0.7 289496 16064 ?        Ss   08:29   0:00 postgres: testuser node-sequelize-examples [local] idleroot     14449  0.0  0.0  12784   940 pts/3    S+   09:53   0:00 grep postgres: testuserpostgres 86437  0.0  0.6 288804 13704 ?        Ss   00:57   0:00 postgres: testuser node-sequelize-examples [local] idle

client debug logs:

...  sequelize:pool connection released +25ms  sequelize:pool connection destroy +10s  sequelize:pool connection destroy +0ms  sequelize:pool connection destroy +0ms  sequelize:pool connection destroy +0ms  sequelize:pool connection destroy +1ms

If you change pool.max to 10, check the count of connection processes:

root@3c9c0fd1bf53:/# date '+%A %W %Y %X' && ps aux | grep "postgres: testuser"Thursday 31 2020 09:56:51 AMpostgres 13615  0.0  0.7 289496 16064 ?        Ss   08:29   0:00 postgres: testuser node-sequelize-examples [local] idlepostgres 14457  0.0  0.5 288384 11248 ?        Ss   09:56   0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45728) SELECTpostgres 14458  0.0  0.5 288384 11252 ?        Ss   09:56   0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45730) SELECTpostgres 14459  0.0  0.5 288384 11252 ?        Ss   09:56   0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45732) SELECTpostgres 14460  0.0  0.5 288384 11248 ?        Ss   09:56   0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45734) SELECTpostgres 14461  0.0  0.5 288384 11248 ?        Ss   09:56   0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45736) SELECTpostgres 14462  0.0  0.5 288384 11248 ?        Ss   09:56   0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45738) SELECTpostgres 14463  0.0  0.5 288384 11244 ?        Ss   09:56   0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45740) SELECTpostgres 14464  0.0  0.5 288388 11244 ?        Ss   09:56   0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45742) SELECTpostgres 14465  0.0  0.5 288388 11244 ?        Ss   09:56   0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45744) SELECTpostgres 14466  0.0  0.5 288388 11248 ?        Ss   09:56   0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45746) SELECTroot     14472  0.0  0.0  12784   944 pts/3    S+   09:56   0:00 grep postgres: testuserpostgres 86437  0.0  0.6 288804 13704 ?        Ss   00:57   0:00 postgres: testuser node-sequelize-examples [local] idle