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

Answer 1

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.