Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
2  
You'd have to UNION ALL 80000 tables together. Not practical. You'd be better off separating users at the application level. –  Phil May 2 '12 at 8:32
2  
what kind of 'protection' would you get from separating each user into their own schema? Schema's are usually used for namespacing... –  Jack Douglas May 2 '12 at 8:58
    
It's not clear what you're asking -- you want to put each user's tables in a separate schema, but you want to have at least one shared table (e.g., for user profile info)? Just put the shared table in a separate schema (maybe public), and have that on the search_path along with the user schema. Doesn't plan time go through the roof with 80k schemas and some multiple of that many tables, though? –  kgrittn May 2 '12 at 12:48
    
Table Inheritance to the rescue ;-) –  Chris Travers Sep 9 '12 at 8:30

1 Answer 1

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.