Good technique for connections with PostgreSQL

Question

I am using Npgsql to access PostgreSQL via .NET. I am concerned about the right way to perform connections to the database, since in my opinion this is an expensive operation to open a connection and then close it every single time I want to perform some transaction.

So here is the 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 PostgreSQL class.

My question:

Is this approach right? Or should I open and close connection every single time I want to make a transaction - open as late as possible and close as soon as possible?

If I should open and close connections every single time - should I write a queue that would limit the amount of concurrent connections? Or PostgreSQL will handle it itself - and, theoretically, I can open 200 connections and it will be alright.

Please share your experience with me ^^

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

Answer 1

PostgreSQL supports connections pooling (pool size is customizable) so the common pattern:

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

should be the better choice.

Answer 2

In my opinion you should open a connection at the moment you need it, and close it right after it. This will prevent a lot of connections on the server to be kept alive.

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