Re: Azure Postgresql High connection establishment time

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.

Please let me know if I still missed something as we have just started our
journey with Postgresql.

Thanks

On Mon, Sep 20, 2021 at 1:16 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Abhay Gupta <gupta(dot)abhay86(at)gmail(dot)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
>