Re: Hope for a new PostgreSQL era?

On 12/08/2011 08:53 PM, Tomas Vondra wrote:
> On 8 Prosinec 2011, 12:24, Craig Ringer 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.
>> o d
> The first thing I'd like to see is "user profiles"z- being able to set
> things like work_mem, synchronous_commit, etc. on per-user basis
> separately.
You can.

ALTER USER username SET work_mem = '100MB';

It's not a hard cap - the user can raise/lower it however they like. The
initial value can be set globally, per-user, per-database, or globally.

> I wonder if the prioritisation could be done using nice - each backend
> is a separate process, so why not to do 'nice(10)' for low priority
> processes or something like that.

Yes, to a limited degree you can prioritise queries using nice and
ionice, but it's awkward because:

- All queries run as `postgres' so you can't do per-user limiting very
easily

- The postmaster doesn't have a way to set the nice level and ionice
level when it forks a backend, nor does the backend have any way to do
it later. You can use your own user-defined C functions for this, though.

- Most importantly, even if you nice and ionice using C functions or
manually with the cmdline utilities, you can't affect the bgwriter, nor
can you affect how much data a low-priority query pushes out of cache.

--
Craig Ringer