Re: Estimated resources for a 500 connections instance (VM)

On Tue, 2020-04-07 at 15:51 -0400, David Gauthier wrote:
> But for my edification, is it roughly true that 2 connections working with the DB 100%
> of the time is equivalent to 20 connections @ 10% = 200 connections @ 1 % (if you know what I mean) ?

Roughly, yes.
There is a certain overhead, as each query has to take a snapshot of the
database, which has to consider all active connections.

But the main problem is that you have no way to ensure that all those
idle connections stay idle.

Imagine that most of these sessions issue short statements
against "table1". There are also some longer SELECTs.
No problem so far.
Now along comes a statement that blocks the table for a very short
time, like a CREATE INDEX CONCURRENTLY.
Unfortunately, that ACCESS EXCLUSIVE lock has to wait for one of
the longer SELECTs to finish.
Now all those moderately short statements cannot run any more, but
they have to queue behind the ACCESS EXCLUSIVE lock. For a
while, nothing happens. Then, as soon as the CREATE INDEX CONCURRENTLY
has got its lock, done its work and finished, all hell breaks loose,
because all sessions start executing their statements at the same time.

I have seen databases servers go down because of such events, and
the problem is the high number of connections, even if they are idle
most of the time.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com