Re: Creating schema best practices

On Wed, Oct 3, 2012 at 10:58 AM, Babay Adi, Hava <hava(dot)babay(at)hp(dot)com> wrote:

> Thanks Craig for the useful information.****
>
> ** **
>
> On the same regard – Some of the mentioned modules in the mentioned
> application use a set of tables which is logically separate (there are no
> join statements with tables of other modules). What are the pros\cons of
> using a separate database instead of a separate schema for maintaining such
> tables?****
>
> ** **
>
> I understand that resources are shared among multiple databases on the
> same cluster, so in terms of performance, are there resources that are
> dedicated for each database and would benefit performance?****
>
> ** **
>
> I’d appreciate a best practice also regarding to using database vs schema.
>

Best practice is more about opinion than anything else.

Regarding multiple databases: it depends entirely on your needs. If you
separate your table into two databases, then your application will have to
make two connections rather than one. That might be a performance issue
depending on how many connections per second you get.

When you do backups, you'll have to do two instead of one. It's hard to
see why two databases would be better than one in your case.

Everything (database, schema, table, metadata, ....) is managed by the same
database cluster, so there's no performance advantage to building separate
databases. If you have several file systems on separate disks, you can
improve performance by using them, but you don't need separate databases
for that. You can create tablespaces and use that to assign tables or
schemas to a particular file system.

Craig