Return to Revisions

Here is some real world experience:

I was working on a very large database with a structure like this one. Back then I decided to use 'Multi-tenant schema separated architecture' based on this article: https://msdn.microsoft.com/en-us/library/aa479086.aspx

When you are considering using this design in postgres, take into account that:

1. You need to write a management script that keeps all inherited tables of same type in sync, (for example, client1.user client2.user) and also can create a new client schema with all the tables. This includes indexes, foreign keys and other things that are not inherited and may be changed independently.
2. Auto-increment sequences need to be defined on a public schema manually and all inherited tables should manually be defined to feed from it.
3. Performance issues, when running public table SELECT on all inherited tables and there is more than few clients schemas. This can be solved using materialized views, but they cannot be updated continuously, so this is a real issue.

You should not use this design if your database is running mostly public table SELECTs on all inherited tables, because it will run through every schema and add enormous overhead compared to using a shared table. If your software mostly runs queries inside a client schema (or on public with CHECK filter conditions) and public SELECTs are only for batch/infrequent operations, it's perfectly fine.