Postgres Npgsql connect slower than SQL Server

Question

why connect to postgres over internet or VPN slower than sql server ? I have test

    DateTime now0 =  System.DateTime.Now;
    string sqlconnectsrting = "server=xxx.xxx.xxx.xxx;database=pubs;uid=sa;pwd=";
    for (int i = 0; i < 1000; i++)
    {
        SqlConnection connect = new SqlConnection(sqlconnectsrting);
        connect.Open();
        connect.Close();
    }
    System.DateTime now1 = System.DateTime.Now;
    Console.WriteLine(String.Format("SQL Connect time : {0}", now1 - now0));

    now0 = System.DateTime.Now;
    string npgconnectsrting = "Server=xxx.xxx.xxx.xxx;Port=5433;User Id=postgres;Password=postgres;Database=hr_data;";
    for (int i = 0; i < 1000; i++)
    {
        NpgsqlConnection connect = new NpgsqlConnection(npgconnectsrting);
        connect.Open();
        connect.Close();
    }
    now1 = System.DateTime.Now;
    Console.WriteLine(String.Format("Postgres Connect time : {0}", now1 - now0));

When connect is localhost they are similar, but when connect is over internet. SQL connect take 1-5s but postgres take 3-7 minutes. Is there anyway to fix this?

Tuan Hoang Anh

Answer 1

3-7 minutes is definitely to long for a connect (I'm not sure if that time is for a single connect or for the 1000 connects you are trying)

I'm pretty sure there is a problem with the network.

What exactly do you mean with "over the internet"? Are those servers really located in the public internet? Are SQL Server and Postgres installed on the same machine?

If not, where are those machines located? If both DBMS are installed on different machines, I'd look into the network configuration for the Postgres server.

Apart from a general "internet" problem (e.g. slow routing) it could also be a problem with the (physical) network interface. We once had that with a faulty network card, that was resending every packet 3-4 times, which slowed down things considerably.

Answer 2

What's the setting for SSL?

SHOW ssl;

Creating an SSL-connection will be slower, even when you actualy don't use SSL. Turn it off if you don't need the extra security.

3 to 7 minutes is about 180 to 420 seconds, if you create 1000 connections, it will "only" take 0.18 to 0.42 seconds for each connection. SQL Server is not going to make a 1000 connections within 1 to 5 seconds over a network, the network will be too slow. Sounds like a connection pool that let you believe you have created a new connection.

Answer 3

When you make a connection, Npgsql tries to create minpoolsize connections. At first connecting, it runs some queries to check types and some supported features. Maybe this is slowing down your tests.

What is strange is that Npgsql only does that for the first connection, so, you should have an smooth run after that. Would you mind to test only 2 loops, instead of 1000 and see how it goes?