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