Re: Hope for a new PostgreSQL era?

Le Thu, 8 Dec 2011 12:27:22 +0000,
Simon Riggs <simon(at)2ndQuadrant(dot)com> a écrit :

> On Thu, Dec 8, 2011 at 11:24 AM, Craig Ringer <ringerc(at)ringerc(dot)id(dot)au>
> wrote:
>
> > Areas in which Pg seems significantly less capable include:
>
> Please can you explain the features Oracle has in these area, I'm not
> clear. Thanks.

Maybe I can answer from my own Oracle experience. I hope it will be what
Craig had in mind :)

>
>
> > - 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.

>
> > - 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 :) )

Being able of changing the backend's nice level may do something
similar I guess. I don't think Oracle's resource manager solves
the priority inversion due to locking in the database, but I'm not sure
of it.