Re: Creating schema best practices

On Tue, Oct 2, 2012 at 11:54 AM, Babay Adi, Hava <hava(dot)babay(at)hp(dot)com> wrote:

> Dear list,****
>
> ** **
>
> I’m new to PostgreSQL, planning now a migration to PostgreSQL and would
> appreciate your help. ****
>
> ** **
>
> One aspect of the migration is re-thinking our DB structure.****
>
> ** **
>
> The application considered contains several modules (let’s say ten), each
> one uses and manages a small number of tables (maximum 10 tables per
> module). Today all tables are located on the same DB, which makes
> management a bit uncomfortable. What comes to mind is grouping each
> module’s tables on a separate schema. From you experience, is there any
> performance impact for grouping tables into schemas? In general, what is
> the best practice for grouping tables in schemas vs. locating several
> tables (that might be logically separated) into the same schema? Is there
> any advantage \ disadvantage of using schemas vs naming standards that
> includes prefix for each module’s tables?****
>
> ** **
>
> In the considered application there are no name duplications among tables.
> In addition, there are there are no queries that involve tables managed by
> different modules. In addition, since all modules are owned by the same
> application, currently there is no interest in limiting access for tables
> (it is all or nothing).
>

Since you have no security needs and no naming conflicts, the answer to
your question is that it's purely for your convenience. Schemas are
primarily useful for separating namespaces and for security. Separating
your modules into schemas will have no impact on performance.

pg_dump and pg_restore have support for single-schema dumps, which might
make your backup procedures easier.

If I were in your shoes, I'd use schemas just to make management easier.
Renaming all of your tables with a module prefix is a waste of time --
that's what schemas are for.

Craig