0

We have a setup where multiple Node processes write into the same database (different tables), and as a result, when using Knex, we end up with more connections to the database than desirable. So, I was thinking of using PgBouncer as a middleware for the Knex processes to connect to, but I'm unsure of how Knex's attempts at connection pooling will work with PgBouncer, which will setup its own pool of connections.

Please assume the following:

  • A 2vCPU database server
  • 10+ Node processes interacting with the database
  • PgBouncer running with a pool size of 5

Questions:

  • If I set min/max size as 1/5 in each Knex setup, will I run out of connections or will PgBouncer somehow be able to "fool" each Knex setup into believing that it has its own pool?
  • It doesn't feel like I can use a Knex pool in this scenario. Even using min/max pool sizes as 1/1 will leave me out of options if the first five Knex steups I launch claim a connection each.
  • Is there a way to make Knex drop pooling and open/close connections as needed? This is the ideal setup for me because now PgBouncer won't actually be opening/closing connections but returning them to the pool (unless I'm mistaken about this?).

What strategy should I use? What should my knexfile look like? And would I need to code differently for this? Any help or ideas are welcome!

ankush981
  • 5,159
  • 8
  • 51
  • 96
  • Why are you doing all this stuff in the first place? Why 10 node processes when node is already highly multi-threaded? (I think it is anyway, not a node user myself) Why allow only 5 connections to PostgreSQL? That is 1/20th of the default setting of max_connections, and that default is already intended for small systems. – jjanes Nov 27 '22 at 18:42
  • @jjanes These are 10 different systems integrating with 10 different APIs, hence done differently as they need to controlled individually. As for the setting, a higher pool size actually means worse performance. Here's some food for thought: https://github.com/brettwooldridge/HikariCP/wiki/About-Pool-Sizing – ankush981 Nov 28 '22 at 05:40
  • It looks like what I can do is this: use a function to create a Knex instance every time I want to run a DB query, and make sure there's a finally block doing knex.destroy() after db-related code has finished running (context: https://stackoverflow.com/questions/57813600/knex-connections-arent-directly-freed-after-a-route-has-finished-and-connectio). While this looks like horrible general practice, when using PgBouncer it makes sense no connections are actually being created and destroyed. I also intend to keep pool settings as min: 1, max: 1 for most of my Node apps. Will see how it goes. – ankush981 Nov 28 '22 at 09:44

1 Answers1

1

While it would be ridiculous to allow 32000 connections, it is also ridiculous to allow only 5. I think the lesson from your link should be not that there is a precisely defined magic number of connections, but that you need to look at the waitevents of your performing database, or just do experiments, to see what is going on and whether you have too many connections.

While repeatedly connecting to pgbouncer (which reuses its internal connection to PostgreSQL) might be less expensive than repeatedly connecting all the way through to PostgreSQL, it will still be far more expensive than just re-using an existing connection from knex's internal connection pool. If your connection load is high enough to matter, then bypassing the internal connection pool to just use pgbouncer would be a mistake. Most likely using pgbouncer at all is a mistake, as it just introduces yet another moving piece for no good reason.

Using knex pooler with min:1 and max:5 with 10 different knex app servers and a limit of 5 connections in pgbouncer would mean that only 5 of your app servers could have a connection. The rest would be forced to wait, but it isn't clear what they would be waiting for. Presumably they would wait forever, or until they caught a timeout error, or until one of other app servers exited or shutdown its pool. Pgbouncer would fool them all right, but not in a helpful way. It might make more sense to use this a min:0 (which is now the recommended setting, but still not the default), as that way an app server would at least release its final connection after idleTimeoutMillis, allowing another app to use it.

Using min:1 max:1 could be useful if pgbouncer were not used or used with a large enough pool size, but it could also break entirely. For example, if an app needs at least 2 simultaneous connections to work correctly. That would probably be a poorly written app, but poorly written apps are the rule, not the exception.

jjanes
  • 37,812
  • 5
  • 27
  • 34
  • I accept your answer from the point of view of how knex and pgbouncer will interplay, but I'm not convinced about the number-of-connections thing. The primary thing that comes out of the link I shared isn't that I should look into wait buffers but that as soon as we have more processes than the number of CPU cores, we'll take an overall hit in managing those incoming chunks of work. To me this looks like common sense, though I'm happy to stand corrected in due time. On a four-core machines, I don't think more than 10-15 connections makes sense. Anyway, thanks for your inputs, really! :-) – ankush981 Dec 14 '22 at 13:54