Обсуждение: Schema Consolidation in PostgreSQL

Wei Shan wrote:
> I have a database with multiple schemas consolidated within a postgres database.
> 
> I have 2 issues when working with this setup.
> 
> 1. How can I ensure that there's no resource contention between the schemas? Is there any resource
> manager.
> 2. Can I get schema-level performance statistics? If I see that this schema is overloaded, I may move
> it to another server.

It is a bit weird to think of resource consumption by schema; wouldn't it make
more sense to assess resource consumption by user or session?

The only resources that can be measured by schema would be I/O or memory for shared buffers,
I guess.

There is pg_statio_all_tables and similar views that tell you how many blocks were
read from disk and from shared buffers.  You could aggregate that information by schema.

For measuring shared memory usage you could install the pg_buffercache extension,
out of that view you could get the information how many buffers are used by objects in
each schema.

Yours,
Laurenz Albe

Hi all,

Thanks for the replies so far.

When I say resource manager, I actually meant if there's a way to ensure the maximum resource utilization by a certain user. For example in Oracle, we can actually limit a certain user by setting a resource plan on it. Resource can be CPU.

Thanks!

Wei Shan wrote:
> When I say resource manager, I actually meant if there's a way to ensure the maximum
> resource utilization by a certain user. For example in Oracle, we can actually limit
> a certain user by setting a resource plan on it. Resource can be CPU.

There is no such thing in PostgreSQL.

You can limit the storage a user can use by keeping his data in a
table space with a certain size.

Yours,
Laurenz Albe

Jorge Torralba wrote:
> On Sun, May 10, 2015 at 3:01 AM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:
>> Wei Shan wrote:
>>> When I say resource manager, I actually meant if there's a way to ensure the maximum
>>> resource utilization by a certain user. For example in Oracle, we can actually limit
>>> a certain user by setting a resource plan on it. Resource can be CPU.

>> There is no such thing in PostgreSQL.
>> You can limit the storage a user can use by keeping his data in a table space with a certain size.

> You can to some extent do the following
> 
> ALTER USER name SET configuration_parameter { TO | = } { value | DEFAULT }

But that won't limit the resources used by a user.
A user can have several connections.

You could maybe limit memory usage somewhat by setting work_mem low for
a user and limiting their connections with
ALTER ROLE ... CONNECTION LIMIT ...

But that's a big step from something like Oracle's Resource Manager.

Yours,
Laurenz Albe