PostgreSQL schemas, select same table from all schemas

postgresqlschema

My app is used by about 80k users every month. I want to create separate PostgreSQL schemas for each user (a-la SaaS, for protection).

My app allows users to search other user's profiles, via:

SELECT user_id FROM user_table WHERE city = 'NYC';

What is the correct way to implement the same search functionality with unique schemas in place for each user?

Will performance suffer?

Best Answer

This might be a good case for using table inheritance. Create the initial table in the master schema and then make all user tables inherit it.

The key to making this work well is to see the master table as as abstract mixin rather than a typical public class inheritance. The parent table then gives you, essentially, a catalog of all child tables' contents. This becomes problematic if you also insert into the parent table so you may want to use a trigger to avoid that.

Related Solutions

Separate Schemas by Industry or by Application functionality

I understand that you want to go with single database (as it is good from management & maintenance point of view), but maybe it's too much integration.

I am assuming that:

you will have separate application for each industry,
they have not very much in common (they really cover different business aspects)

I think that one of acceptable solutions in this case would be:

a separate database for each industry
in every database, single shared public schema for shared data, including common dictionaries, all non-customer-dependent data.
one separate database user account + private schema for each customer. You might use postgres feature of table inheritance to conform to some public table.

Issues that Simon puts in his comment are really important. So you will have to enforce a very strict policy regarding GRANTs in your database.

In PostgreSQL, it is possible to achieve (as well in many other RDBMS). The key to achieving multi-tenant solution would be intelligent usage of schemas, roles, search_path setting. See http://www.postgresql.org/docs/current/static/ddl-schemas.html. Actually what I propose will in a way emulate what Oracle does. If you need help on details please ask.

Mongodb – Schema design for privacy settings in MongoDB

It's important to remember that since there are multiple ways to design schema in MongoDB for a given set of data, it's critical to consider the types of reads and writes you will be making from your application.

Assuming that you will be querying for a subset of documents that a given user can see, it's likely that you will want to structure the document collection in a way that will give you the ability to query for all "visible" documents in a single query.

Document structure such as:

{ _id: ...,
  public: true,
  groups: ['group1', 'group2',...],
  users: ['user1','user2',...],
}

You could now query for all documents visible to user X if you have a list of groups user X belongs to by querying:

db.documents.find( { $or : [
     { public : true },
     { users  : X },
     { groups : { $in : [list-of-X's-groups] } }
] } )

Best Answer

Related Solutions

Separate Schemas by Industry or by Application functionality

Mongodb – Schema design for privacy settings in MongoDB

Related Question