Creating schema best practices

From: "Babay Adi, Hava" <hava(dot)babay(at)hp(dot)com>
To: "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org>
Subject: Creating schema best practices
Date: 2012-10-02 18:54:09
Message-ID: C6B1B26CA32AC64F8274F7ACE29D61732F567DE0@G9W0745.americas.hpqcorp.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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).

Thanks in advance,
Hava

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Greg Williamson 2012-10-02 22:02:23 Re: Database size stays constant but disk space keeps shrinking -- postgres 9.1
Previous Message Bruce Momjian 2012-10-02 15:58:57 Re: [ADMIN] pg_upgrade from 9.1.3 to 9.2 failed