Connection pooling - NodeJS or Database products?

Question

Consider the mysql package. The description covers connection pooling thus,

Rather than creating and managing connections one-by-one, this module also provides built-in connection pooling using mysql.createPool(config).

Then, there is the connection pool setting in proxysql as described here.

Similarly, in case of PostgreSQL too, there is the pg package supporting connection pooling and pgboucner pooling feature.

1. How are these approaches (npm package versus product supporting tools) different?
2. Ideally, I would like to configure connection pooling at the database level so that a developer is free from handling connections. Can this be a preferred approach?

Answer 1

You can use connection pooling on any level, but I believe that it is best done with a connection pool inside the application server code for these reasons:

• You don't need an extra process like pgBouncer that you have start and monitor.

• You don't have any more TCP connections than necessary, thus conserving resources and being faster.

This holds if you have a single application server. If several of them access the same database, connecting pooling in the application server isn't enough. It is still a goid idea if you want to limit the DB resources each one can use, but you will still need another connection pool to limit the total number of database connections.

Such a connection pool can be implemented in the database if the RDBMS supports it (PostgreSQL doesn't), or it can be an external tool like pgBouncer.

• cogitoergosum: Ok. Suppose, I have five instances of NodeJS based API end-points behind a firewall, and each one of them is invoked say, 10 times per second. With connection pooling from mysql NodeJS package, how many connections will MySQL database see? If I set-up ProxySQL to make 5 connections at max, then will MySQL see only 5 connections ever?
• Laurenz Albe: If you have several application servers, connection pooling in the application server isn't enough. I have extended the answer. I don't know MySQL well enough to go into its specifics.
• inf3rno: Looks like another solution to limit the number of instances too, so numberOfInstances x poolSize will give you the max number of connections.

Answer 2

Connection Pooling serves two particular purposes and both of them are client related. First, pooling helps reduce the overhead of establishing connections. Second, pooling helps conserve the TCP Port pool on the Client. Most providers (e.g. SQLNCLI, ODBC) implement pooling on the client side and implement at least some features to allow the developer to control or influence the functioning. IMO Server side connection pooling just wastes server resources.

• cogitoergosum: I see your point. Looking at pgbouncer and ProxySQL as clients to PostgreSQL and MySQL database respectively, my question was whether the connection pooling from NodeJS packages have any advantage or disadvantage w.r.t. the proxy clients I mentioned.
• Ray: I am not familiar with the packages or NodeJS. I was mainly responding to your question 2 where you mentioned setting up pooling at the database level.