PostgreSQL – How to Divide Database into Schemas

performancepostgresqlschema

My Postgres database will be focused on multidisciplinary organizations. The project axis is: a person could belongs to diverses disciplines (ex: arts, sports) for the same organization, and this organization needs an unique central Web app.

My first intent was modeling only one database but the size was enormous and the relations so complex and redundant, then my second and current approach is as follows:

  • People_Schema containing general tables like tbl_person, tbl_city, tbl_ocupation, etc.
  • Arts_Schema containing tables like tbl_art_center, tbl_enrollment(art_center_id FK, person_id FK), etc.
  • Sports_Schema containing tables like tbl_sport, tbl_team, tbl_team_member(team_id FK, person_id FK), etc.
  • And among others schemas in future growing.

The question is: Does this last approach is a good idea? or Does will be a hell for join queries between schemas or others painful implications that I have not seen?, considering multi-tenant expectatives, RESTfull APIs, DreamFactory Backend as a Service and Amazon EC2 Server.

Edit:
My approach was inspired by this statement taken from official Postgres docs: If the projects or users are interrelated and should be able to use each other's resources, they should be put in the same database but possibly into separate schemas.

Best Answer

schemas is used for organizational structure and namespacing. By namespacing I mean that you can have similarly named table in few places. And another feature of using schema is additional access control for that schema. You will have joins depending on your data model. You are using schemas implicitly already. There is a "search path" defined in PostgreSQL database.

So every time you type create table X, you use default schema. And for queries you should use table aliases so you might add only schema name to every table but if you use alias your queries should be the same with or without schema.

I don't have experience with above feature (multitenant db and amazon and ...)

Related Question