Long connection time with Azure Postgres

Question

I have Azure Database for PostgreSQL service (PaaS). When I'm trying to query it with psql then even simple SELECT query from one table takes ~1.5s. When I'm in postgres console then there is no issue, queries execute under 100ms. What can cause this delay in connection time?

for I in {1..15} ; do /usr/bin/time -f "%es" psql -U postgres@user  -h  my-server.postgres.database.azure.com database_name -c "SELECT count(*) FROM table_name" > /dev/null ; done
1.86s
1.95s
1.60s
1.03s
1.59s
1.58s
1.60s
1.68s
1.60s
1.60s
1.62s
1.63s
1.61s
1.83s
1.70s

FYI: There are 11 entries in the table

UPDATE Below test proof this is connection delay not execution query itself on the postgres

$ psql -t -U postgres@resource-group  -h  postgresql-project.postgres.database.azure.com stage_table <<EOF
SELECT count(*), clock_timestamp() FROM my_table;
SELECT count(*), clock_timestamp() FROM my_table;
SELECT count(*), clock_timestamp() FROM my_table;
SELECT count(*), clock_timestamp() FROM my_table;
SELECT count(*), clock_timestamp() FROM my_table;
SELECT count(*), clock_timestamp() FROM my_table;
SELECT count(*), clock_timestamp() FROM my_table;
SELECT count(*), clock_timestamp() FROM my_table;
SELECT count(*), clock_timestamp() FROM my_table;
SELECT count(*), clock_timestamp() FROM my_table;
SELECT count(*), clock_timestamp() FROM my_table;
SELECT count(*), clock_timestamp() FROM my_table;
SELECT count(*), clock_timestamp() FROM my_table;
SELECT count(*), clock_timestamp() FROM my_table;
SELECT count(*), clock_timestamp() FROM my_table;
EOF
    17 | 2019-07-23 13:26:11.475655+00
    17 | 2019-07-23 13:26:11.506887+00
    17 | 2019-07-23 13:26:11.553769+00
    17 | 2019-07-23 13:26:11.585022+00
    17 | 2019-07-23 13:26:11.616273+00
    17 | 2019-07-23 13:26:11.64752+00
    17 | 2019-07-23 13:26:11.678787+00
    17 | 2019-07-23 13:26:11.710046+00
    17 | 2019-07-23 13:26:11.74129+00
    17 | 2019-07-23 13:26:11.772524+00
    17 | 2019-07-23 13:26:11.803794+00
    17 | 2019-07-23 13:26:11.835042+00
    17 | 2019-07-23 13:26:11.86629+00
    17 | 2019-07-23 13:26:11.897517+00
    17 | 2019-07-23 13:26:11.944397+00
$

I was also read about accelerated networking on azure but this looks like only applying to VM itself. Can I enable this acceleration on PostgreSQL service? https://learn.microsoft.com/en-us/azure/virtual-network/create-vm-accelerated-networking-cli

Azure databases has performance issue with establishing new connections. After that things look normal. When I tried connecting with SSL off, I still see SSL is on, this clearly indicates they have proxies in between the client and database. Proxy can introduce latency in the queries and connection establishment. — Sirisha Chamarthi, Sep 20 '19 at 03:52

Laurenz Albe · Answer 1 · 2019-08-05T05:41:18.030

The delay is the long time it takes to establish a database connection.

1.5 seconds is rather lame. But it need not be a killer, because you should use a connection pool and keep the database sessions open.

To be certain, you can compare the result with

psql -U postgres@user -h  my-server.postgres.database.azure.com database_name <<EOF
SELECT count(*), clock_timestamp() FROM table_name;
SELECT count(*), clock_timestamp() FROM table_name;
SELECT count(*), clock_timestamp() FROM table_name;
SELECT count(*), clock_timestamp() FROM table_name;
SELECT count(*), clock_timestamp() FROM table_name;
SELECT count(*), clock_timestamp() FROM table_name;
SELECT count(*), clock_timestamp() FROM table_name;
SELECT count(*), clock_timestamp() FROM table_name;
SELECT count(*), clock_timestamp() FROM table_name;
SELECT count(*), clock_timestamp() FROM table_name;
SELECT count(*), clock_timestamp() FROM table_name;
SELECT count(*), clock_timestamp() FROM table_name;
SELECT count(*), clock_timestamp() FROM table_name;
SELECT count(*), clock_timestamp() FROM table_name;
SELECT count(*), clock_timestamp() FROM table_name;
EOF

That should be faster. The difference would be the connect time.

Adding MSDN link for same question: https://social.msdn.microsoft.com/Forums/en-US/a4bffb9d-0114-4e05-8a30-81f65fb2cd9b/high-login-latency?forum=AzureDatabaseforPostgreSQL — Mike Ubezzi, Jul 25 '19 at 17:14
Are you running on an Azure VM or are you using the Azure Database for PostgreSQL PaaS service? — Mike Ubezzi, Jul 25 '19 at 17:15

Long connection time with Azure Postgres

1 Answers1

Linked