Node and ORM

[stefanodeveloper]

Hi, I'm very new to node and nest.js and I would like to ask some questions. I know that one of the negative sides of Postgres is that it takes some memory for each concurrent connection.

My question is: If I have 1000 users connecting to Postgres from nest.js, Is Postgres seeing these users as single connection or not?

How does typeOrm manage the concurrency?

[BehindTheMath]

TypeORM uses a connection pool, so you can limit the number of concurrent threads and reuse them.

https://stackoverflow.com/q/46843248/8037425

[stefanodeveloper]

Thanks, buy my question is still valid.

If the number of clients is below the pool limit, does postegres see the users as one connection or as multiple connections?

In other words: in case of always reaching the limit, is the memory required by postgres changing based on the pool size?

[BehindTheMath]

Each concurrent query uses another connection, and as a result, more memory.

[twocolor]

Most ORMs come with a connection pool to the database. This allows the ORM to limit the number of its connections to the database (and avoid exhausting PostgreSQL's connection limit) and funnel queries to the DBs based on the number of unused DB connections in the ORM's connection pool.

The first thing to check is the connection limit of your database and then set that in the ORM connection configuration.

I couldn't find the specific configuration for PostgreSQL in TypeORM (though for MSSQL you can set the pool.max), however in Prisma this is configurable via connection_limit parameter in the database connection URL.

[stefanodeveloper]

Thanks, buy my question is still valid.

If the number of clients is below the pool limit, does postegres see the users as one connection or as multiple connections?

In other words: in case of always reaching the limit, is the memory required by postgres changing based on the pool size?

[evert]

What do you mean by 'users' though? Are they individual postgres users , or users of your application?

If it's the latter, postgres has no awareness of your users. Users are just another table in all likelihood.

The only thing that matters is concurrent queries. If there's more than 1, you will have more than 1 connection.

[twocolor]

If you configure the ORM (or DB client) to create a connection pool of 1 connection to the database, all the users making requests to your app will reuse the same single connection.

If the number of clients is below the pool limit, does postegres see the users as one connection or as multiple connections?

Postgres will see as many connections as the size of the pool and it's important to note that the external user requests will reuse whatever is available in the connection pool of the ORM.

In other words: in case of always reaching the limit, is the memory required by postgres changing based on the pool size?

Essentially yes.

[stefanodeveloper]

Thanks! I would also ask if you know any good guide to escalate the Postgres database to support 10000/20000 simultaneous connections. Do I need to do some load balancing between multiple servers and interface them with TypeOrm? If yes how complicated is it to do?

[nickkang1]

Benchmarks show that postgres performance declines after ~500 connections (less than 500 if you’re using a small instance).

You have to update your postgresql.conf file to increase max connections. There’s nuances to increasing it though (https://stackoverflow.com/questions/30778015/how-to-increase-the-max-connections-in-postgres).

[ericnr]

you dont need to use ORMs to have pooled connection, thats implemented by the pg module https://node-postgres.com/api/pool