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@xxxxxxxxxxxxx> wrote:
Abhay Gupta <gupta.abhay86@xxxxxxxxx> 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
