I am confused about pgbouncer pool size configuration and ORM(like sequelize.js), query builder(like knex.js) library pool size configuration. The architecture like this:
Application code => pgbouncer => postgresql
pgbouncer.ini:
;; ...
;; Default pool size. 20 is good number when transaction pooling
;; is in use, in session pooling it needs to be the number of
;; max clients you want to handle at any moment
;default_pool_size = 20
;; ...
sequelize connection pool configuration:
const sequelize = new Sequelize(/* ... */, {
// ...
pool: {
max: 5,
min: 0,
acquire: 30000,
idle: 10000
}
});
knex.js connection pool configuration:
var knex = require('knex')({
client: 'mysql',
connection: {
host : '127.0.0.1',
user : 'your_database_user',
password : 'your_database_password',
database : 'myapp_test'
},
pool: { min: 0, max: 7 }
});
What happened if I use sequelize.js connection pool configuration and pgbouncer connection pool size configuration together? Which configuration does the database server use? Should I use only one of them? Thanks.
It rarely makes sense to daisy chain connection pools together. So there is probably no point in using pgbouncer in addition to the built-in ones. The database server doesn't know about your connection poolers except to the extent the pool manager sends its own explicit commands to the database, and it has its own configuration file which it uses (postgresql.conf).
If you have for example 3 application processes running knex or sequelize, then you should setup pgbouncer poolsize to be 3 times bigger than single knex / sequelize pool uses.
Then you also need to make sure that postgres server also has enough connections configured to handle pgbouncer connections.
Though as @jjanes said. There is no reason to use pgbouncer with knex / sequelize, because they already provide pooling. I suppose pgbouncer is meant to be used with frameworks, which doesn't support pooling. For example if PHP or cgi stript reinitializes on every page load and makes calls to database.