Thread: Azure Postgresql High connection establishment time

Hi

we are writing a python(3.9) azure function hosted on linux which is connecting to azure postgresql 11. We are using psycopg2 to connect the azure function to postgresql.

we are noticing the connection establishment is taking around 200-300 ms. Below is the statment.

conn=psycopg2.connect(conn_string)

Conn_string includes host, user, password, dbname, sslmode=require

The connection establishment time is very high as our functions are written to be very high performance.

can you please let us know if there is something we should be doing to reduce the connection establishment time.

Thanks

Abhay Gupta <gupta.abhay86@gmail.com> writes:
> we are noticing the connection establishment is taking around 200-300 ms.

Not sure whether that's out of line or not, since you've provided
no background data (e.g. is the connection across a network link?
how busy is the server? what encryption and authentication are
you using?).  However ...

> The connection establishment time is very high as our functions are written
> to be very high performance.

... if you are looking for good performance, the last thing you
should be doing is making a connection per query.  Postgres backend
processes are pretty heavyweight things.  Even after the connection
is complete, there's overhead involved in populating caches and so
forth.  You'd be well-served to use a connection pooler and/or try
to keep an application's connection open once made.

            regards, tom lane

On 9/20/21 10:37 AM, Abhay Gupta wrote:
> Hello Tom
> 
> Thanks for taking time in looking into this for us. Apologies for not 
> providing any required data.
> 
> Yes, Azure postgresql has a private endpoint attached to it so it is 
> going through a private link. Also the server has 2 vcore and 5gb. Since 
> currently we are in development phase there is no load on the server at 
> all. No encryption and in the connection string we are passing the user 
> and password. It is Ssl enabled. To negate it is Azure issue, we 
> installed postgresql 11 on our on prem server and there also we are 
> seeing a connection latency of about 150-200 ms.
> 
> We are only opening 1 connection in our application i.e. after all the 
> work is done for that user session than only we close the connection.
> 
> If you could assist in what we can check will be very helpful.

Per upstream comment take a look at connection pooling.

Two solutions that come to mind:

Pgpool-II

https://www.pgpool.net/mediawiki/index.php/Main_Page

PgBouncer

http://www.pgbouncer.org/


> 
> Thanks
> 



-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 2021-09-20 13:37:52 -0400, Abhay Gupta wrote:
> Yes, Azure postgresql has a private endpoint attached to it so it is going
> through a private link. Also the server has 2 vcore and 5gb. Since currently we
> are in development phase there is no load on the server at all. No encryption
> and in the connection string we are passing the user and password. It is Ssl
> enabled. To negate it is Azure issue, we installed postgresql 11 on our on prem
> server and there also we are seeing a connection latency of about 150-200 ms.

How are you authenticating? Are you using password hashes stored in the
database (MD5 or preferrably SCRAM-SHA-256) or are you authenticating
against an external source (e.g. active directory). If the latter, what
and how? Can you measure how long the authentication takes?

I have seen LDAP authentication against a local AD instance take over
hundred milliseconds, so I wouldn't be surprised if that was the
culprit.

        hp

PS: a local connection (ident) takes about 3.5 ms on my (not very fast)
laptop.

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"