Thanks again for the helpful information. Another question on the same regard – how commonly used are PostgreSQL schemas? I want to ensure that I choose a solution that is common and wildly used.
From: Craig James [mailto:cjames@emolecules.com]
Sent: יום ד 03 אוקטובר 2012 22:49
To: Babay Adi, Hava
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Creating schema best practices
On Wed, Oct 3, 2012 at 10:58 AM, Babay Adi, Hava <hava.babay@hp.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