On Sat, 2020-06-06 at 11:56 +0800, Vasu Madhineni wrote: > > > On Fri, Jun 5, 2020 at 2:57 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote: > > > > On Thu, 2020-06-04 at 23:52 +0800, Vasu Madhineni wrote: > > > > > We are planning a POC on multitenant architecture in Postgres, Could you please > > > > > help us with steps for multitenant using schema for each application model. > > > > > > > > For few tenants, you can keep identical tables in several schemas and > > > > set "search_path" to select a tenant. > > > > > > > > With many tenants, you are better off with one table that holds the > > > > data for all clients. You can use Row Level Security to have each > > > > tenant see only his or her data, and it might be a good idea to > > > > use list partitioning on the tenant ID. > > Our environment is medical clinical data, so each clinic as a tenant. > Approximately 500+ tenants with 6TB data.
The important number to base this decision on would be the number of tables you'd expect in the database. It shouldn't be too many.
If the database grows large, you may be better off sharding the database together with partitioning it across schemas. Several smaller databases are easier to back up and make scaling easier.
Of course that requires your application to be part of the solution: it needs to know which database to use for which tenant.