Connection Pooling – NodeJS vs Database Products

connection-poolingMySQLnode.jspostgresql

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.

How are these approaches (npm package versus product supporting tools) different?
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?

Best Answer

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.

Related Solutions

Mysql Connection Pooling Similar To PostgreSQL’s PgBouncer

This started off as a comment but it's getting a bit long.

Perhaps you could provide a better description of what it is you actually want rather than simply say it's like something else.

If you want multiple clients to use the same connection, then it must be implemented in the DBMS, otherwise you have no session management. Further you need to advise how you intend dealing with issues of state spanning more than a single request/response (i.e. transactions).

It would also be helpful to know why you want it. MySQL has a very different setup cost and per-session overhead cost compared to PostgreSQL - why do you think it needs it, and why you think it would actually yeild any benefit.

Sql-server – connection pooling, transactions, nested transaction and rollback

There is no such thing as nested transactions in any released version of SQL Server. You can say BEGIN TRANSACTION as many times as you want, but a ROLLBACK affects all of them (never mind what @@TRANCOUNT says - it only reflects nesting level).

From the documentation:

It is not legal for the transaction_name parameter of a ROLLBACK TRANSACTION statement to refer to the inner transactions of a set of named nested transactions. transaction_name can refer only to the transaction name of the outermost transaction. If a ROLLBACK TRANSACTION transaction_name statement using the name of the outer transaction is executed at any level of a set of nested transactions, all of the nested transactions are rolled back. If a ROLLBACK WORK or ROLLBACK TRANSACTION statement without a transaction_name parameter is executed at any level of a set of nested transaction, it rolls back all of the nested transactions, including the outermost transaction.

A demonstration:

CREATE TABLE dbo.foo(a INT);

BEGIN TRANSACTION;
  INSERT dbo.foo(a) SELECT 1;
  BEGIN TRANSACTION;
    INSERT dbo.foo(a) SELECT 2;
    BEGIN TRANSACTION;
    ROLLBACK TRANSACTION;

SELECT tc = @@TRANCOUNT;

SELECT a FROM dbo.foo;

DROP TABLE dbo.foo;

Results:

tc    
----
0

1 row(s) affected.

a
----

0 row(s) affected.

So, there is no way to only roll back part of a "nested" transaction - it's all or nothing. If you want previous parts of this transaction to commit even if later parts fail, then stop nesting, and commit the first transaction before starting the next one.

Best Answer

Related Solutions

Mysql Connection Pooling Similar To PostgreSQL’s PgBouncer

Sql-server – connection pooling, transactions, nested transaction and rollback

Related Question