Re: Hope for a new PostgreSQL era?

Le Thu, 8 Dec 2011 17:54:20 +0100,
"Tomas Vondra" <tv(at)fuzzy(dot)cz> a écrit :

> On 8 Prosinec 2011, 16:11, Marc Cousin wrote:
> >> > - admission control, queuing and resource limiting to optimally
> >> > load a machine. Some limited level is possible with external
> >> > pooling, but only by limiting concurrent workers.
> >
> > Oracle has natively two ways of handling inbound connections:
> > - Dedicated, which is very similar to the PostgreSQL way of
> > accepting connections: accept(), fork() and so on
> > - Shared, which is based on processes listening and handling the
> > connections (called dispatchers) and processes doing the real work
> > (called workers, obviously). All of this works internally with
> > some sort of queuing and storing results in shared memory (I don't
> > remember the details of it)
> >
> > The advantage of this second architecture being of course that you
> > can't have more than N workers hitting your database
> > simultaneously. So it's easier to keep the load on the server to a
> > reasonable value.
>
> Which is exactly what pgbouncer and other connection pools are for ...
Yep. But with some limitations (not that important, but they exist) as
detailed in another message.

I like the pgbouncer approach as it is much simpler, but it has the
limitation that the real sessions aren't in the database anymore, so
context is lost, etc…

>
> >> > - prioritisation of queries or users. It's hard to say "prefer
> >> > this query over this one, give it more resources" or "user A's
> >> > work always preempts user B's" in Pg.
> >>
> > It's called the resource manager in Oracle. You define 'resource
> > plans', 'consumer groups', etc… and you get some sort of QoS for
> > your queries. It's mostly about CPU resource allocation if I
> > remember correctly (I never used it, except during training :) )
>
> And it's damn difficult to get it working properly ... the simpler the
> better here.

Yep, it's very hard and ugly to use. It's by the way the reason I used
it only in training, not in production situations (in production, when
it doesn't work, you have to debug the damn thing, not just throw it
away :) )