Re: Persistent Connections - Mailing list pgsql-general

From Tim Cross
Subject Re: Persistent Connections
Date
Msg-id 87mu4sowbz.fsf@gmail.com
Whole thread Raw
In response to Re: Persistent Connections  ("Peter J. Holzer" <hjp-pgsql@hjp.at>)
List pgsql-general
Peter J. Holzer <hjp-pgsql@hjp.at> writes:

> On 2020-06-24 13:55:00 -0400, Bee.Lists wrote:
>> On Jun 24, 2020, at 6:47 AM, Peter J. Holzer <hjp-pgsql@hjp.at> wrote:
> Does "I have 37 queries" mean you have seen 37 queries of this type in
> some time window (e.g. the last day or hour) or does it mean you are
> currently seeing 37 connections where the last query was of this type?
>
> If it's the latter, you very obviously have at least 37 (more likely
> 37 + 5 = 42) connections. So you web app is configured to open dozens of
> connections concurrently. You might want to look into that.
>

I just had another thought. Based on a VERY brief scan of the Sequel
API, I suspect it uses a connection pool by default. So it is quite
likely that the expectations on when the connections are closed is
incorrect. It could easily be that the web app creates a connection pool
as soon as it is started and keeps that pool open until either the web
server is closed down or a pool timeout kicks in (some connection pools
use a max lifetime setting for connections and will close a connection
after that period, replacing it with a new connection).

It is also quite likely that the Sequel GEM creates a connection pool
with a default number of connections if not explicitly defined by the
developer. This default could be close to or even exceed the number set
for max connections within PG (especially as the OP has indicated it is
very small).

I have to say, I do hate ORMs. They always reduce the flexibility and
power offered by SQL, tend to result in code where more processing is
done in the client which would have been faster and more efficiently
done by the SQL engine and hides details which make troubleshooting even
harder. However, the worst issue is that it also results in developers
not understanding the power of the underlying RDMS and encourages poor
DB schema design. Those who support such technologies typically point to
the benefits of database neutrality such systems can provide. In over 30
years of DB work, I have yet to see such neutrality actually work. It is
a pipe dream. 
-- 
Tim Cross



pgsql-general by date:

Previous
From: Tim Cross
Date:
Subject: Re: Persistent Connections
Next
From: Sri Linux
Date:
Subject: Need help with PITR for PostgreSQL 9.4.5