Here some real world expirience:
I was working on very large database with structure like this one.
Back then I decided to use 'Multi-tenant schema seperated architecture' based on this article
https://msdn.microsoft.com/en-us/library/aa479086.aspx
When you considering to use this design in postgres, take in account that:
- You need to write a management script that keeps all inherited tables of same type in sync, (for ex. 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. - Auto-increment sequences needs to be defined on a public schema manually and all inherited tables should manually be defined to feed from it.
- 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 continiously, so this is a real issue.
You should not use this design if your database running mostly public table SELECTs on all inherited tables, because it's running through every schema and it adds enormous overhead compared to shared table.
But if your software mostly run SELECTs inside client schema(or on public with CHECK filter condition) and public SELECTs is only for batch-infrequent operations, it's perfectly fine.