Good PostgreSQL Connection Technique

Question

I am using Npgsql to access PostgreSQL through .NET. I am worried about the correct way to make connections to the database, because, in my opinion, this is an expensive operation to open a connection and then close it every time I want to perform some transaction.

So here is a general idea:

 public class PostgreSQL { private NpgsqlConnection conn; // <- one connection for this object, open all the time public PostgreSQL(string connString) { conn = new NpgsqlConnection(connString); conn.Open(); } // ...here making some queries... public void Close() => conn.Close(); // <- use this in the very end of the program } 

As you can see above, I have one connection for an instance of the PostgreSQL class.

My question is :

Is this approach right? Or do I need to open and close the connection every time I want to complete a transaction - open and close as soon as possible?

If I have to open and close connections every time - should I write a queue that would limit the number of parallel connections? Or PostgreSQL will handle this on its own - and, theoretically, I can open 200 connections, and everything will be fine.

Please share your experience with me ^^

EDIT: I will run 100-200 queries per second.

Answer 1

In my opinion, you should open the connection at the moment you need it, and close it immediately after it. This will prevent maintaining a large number of connections on the server.

In my experience, opening a connection doesn’t take much time (a few milliseconds, usually part of your execution time), so you don’t have to worry too much.

Answer 2

PostgreSQL supports pooling (the size of the pool is customizable ), so the general pattern is:

 using (NpgsqlConnection conn = new NpgsqlConnection(...)) { ... } 

should be a better choice.