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.1PostgreSQL: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 NAMES
3c9c0fd1bf53 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); +0ms
Executing (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); +2ms
Executing (default): select pg_sleep(1);
sequelize:sql:pg Executing (default): select pg_sleep(1); +2ms
Executing (default): select pg_sleep(1);
sequelize:sql:pg Executing (default): select pg_sleep(1); +0ms
Executing (default): select pg_sleep(1);
sequelize:sql:pg Executing (default): select pg_sleep(1); +0ms
Executing (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); +1ms
Executing (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 AM
postgres 13615 0.0 0.7 289496 16064 ? Ss 08:29 0:00 postgres: testuser node-sequelize-examples [local] idle
postgres 14335 0.0 0.5 288384 11248 ? Ss 09:51 0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45704) SELECT
postgres 14336 0.0 0.5 288384 11248 ? Ss 09:51 0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45706) SELECT
postgres 14337 0.0 0.5 288384 11252 ? Ss 09:51 0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45708) SELECT
postgres 14338 0.0 0.5 288384 11248 ? Ss 09:51 0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45710) SELECT
postgres 14339 0.0 0.5 288384 11248 ? Ss 09:51 0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45712) SELECT
postgres 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 AM
postgres 13615 0.0 0.7 289496 16064 ? Ss 08:29 0:00 postgres: testuser node-sequelize-examples [local] idle
postgres 14352 0.0 0.5 288384 11248 ? Ss 09:53 0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45716) idle
postgres 14353 0.0 0.5 288384 11252 ? Ss 09:53 0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45718) idle
postgres 14354 0.0 0.5 288384 11248 ? Ss 09:53 0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45720) SELECT
postgres 14355 0.0 0.5 288384 11248 ? Ss 09:53 0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45722) idle
postgres 14356 0.0 0.5 288384 11248 ? Ss 09:53 0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45724) SELECT
root 14440 0.0 0.0 12784 972 pts/3 S+ 09:53 0:00 grep postgres: testuser
postgres 86437 0.0 0.6 288804 13704 ? Ss 00:57 0:00 postgres: testuser node-sequelize-examples [local] idle
root@3c9c0fd1bf53:/# date '+%A %W %Y %X' && ps aux | grep "postgres: testuser"
Thursday 31 2020 09:53:49 AM
postgres 13615 0.0 0.7 289496 16064 ? Ss 08:29 0:00 postgres: testuser node-sequelize-examples [local] idle
postgres 14352 0.0 0.5 288384 11248 ? Ss 09:53 0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45716) idle
postgres 14353 0.0 0.5 288384 11252 ? Ss 09:53 0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45718) idle
postgres 14354 0.0 0.5 288384 11248 ? Ss 09:53 0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45720) idle
postgres 14355 0.0 0.5 288384 11248 ? Ss 09:53 0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45722) idle
postgres 14356 0.0 0.5 288384 11248 ? Ss 09:53 0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45724) idle
postgres 86437 0.0 0.6 288804 13704 ? Ss 00:57 0:00 postgres: testuser node-sequelize-examples [local] idle
root@3c9c0fd1bf53:/# date '+%A %W %Y %X' && ps aux | grep "postgres: testuser"
Thursday 31 2020 09:53:55 AM
postgres 13615 0.0 0.7 289496 16064 ? Ss 08:29 0:00 postgres: testuser node-sequelize-examples [local] idle
postgres 14352 0.0 0.5 288384 11248 ? Ss 09:53 0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45716) idle
postgres 14353 0.0 0.5 288384 11252 ? Ss 09:53 0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45718) idle
postgres 14354 0.0 0.5 288384 11248 ? Ss 09:53 0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45720) idle
postgres 14355 0.0 0.5 288384 11248 ? Ss 09:53 0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45722) idle
postgres 14356 0.0 0.5 288384 11248 ? Ss 09:53 0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45724) idle
root 14446 0.0 0.0 12784 932 pts/3 S+ 09:53 0:00 grep postgres: testuser
postgres 86437 0.0 0.6 288804 13704 ? Ss 00:57 0:00 postgres: testuser node-sequelize-examples [local] idle
root@3c9c0fd1bf53:/# date '+%A %W %Y %X' && ps aux | grep "postgres: testuser"
Thursday 31 2020 09:53:58 AM
postgres 13615 0.0 0.7 289496 16064 ? Ss 08:29 0:00 postgres: testuser node-sequelize-examples [local] idle
root 14449 0.0 0.0 12784 940 pts/3 S+ 09:53 0:00 grep postgres: testuser
postgres 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 AM
postgres 13615 0.0 0.7 289496 16064 ? Ss 08:29 0:00 postgres: testuser node-sequelize-examples [local] idle
postgres 14457 0.0 0.5 288384 11248 ? Ss 09:56 0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45728) SELECT
postgres 14458 0.0 0.5 288384 11252 ? Ss 09:56 0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45730) SELECT
postgres 14459 0.0 0.5 288384 11252 ? Ss 09:56 0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45732) SELECT
postgres 14460 0.0 0.5 288384 11248 ? Ss 09:56 0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45734) SELECT
postgres 14461 0.0 0.5 288384 11248 ? Ss 09:56 0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45736) SELECT
postgres 14462 0.0 0.5 288384 11248 ? Ss 09:56 0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45738) SELECT
postgres 14463 0.0 0.5 288384 11244 ? Ss 09:56 0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45740) SELECT
postgres 14464 0.0 0.5 288388 11244 ? Ss 09:56 0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45742) SELECT
postgres 14465 0.0 0.5 288388 11244 ? Ss 09:56 0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45744) SELECT
postgres 14466 0.0 0.5 288388 11248 ? Ss 09:56 0:00 postgres: testuser node-sequelize-examples 172.18.0.1(45746) SELECT
root 14472 0.0 0.0 12784 944 pts/3 S+ 09:56 0:00 grep postgres: testuser
postgres 86437 0.0 0.6 288804 13704 ? Ss 00:57 0:00 postgres: testuser node-sequelize-examples [local] idle