Very often applications support multiple tenants. A financial accounting solution used by a CPA is a typical example for that. However, there are other scenarios where the ability to switch between several tenants/customers is required. The following note gives an overview about the options with their pros and cons in the context of the PostgreSQL server.
Note: With ISAM tables, multi-tenancy was mostly achieved by having all tables/index making up a single clients data model in a client specific sub directory. So switching between clients was just closing all tables and opening them again with a new root directory.
1) In PostgreSQL, a schema is a namespace that contains named database objects such as tables, views, indexes, data types, functions, and operators. To access an object of a schema, you qualify its name with the schema name as a prefix: schema_name.object_name. If you need to name database, schema and object use database_name.schema_name.object_name. The default schema name in a PostgreSQL database is public.
from https://www.postgresqltutorial.com/postgresql-schema/
2) When using the "table with client_id" approach we recommend the usage of views per tenant to restrict access rights to a single tenants data by design. Otherwise the data management approach may violate GDPR/DSGVO requirements.
Note: With ISAM tables, multi-tenancy was mostly achieved by having all tables/index making up a single clients data model in a client specific sub directory. So switching between clients was just closing all tables and opening them again with a new root directory.
Problem
With a SQL Server in general the question now is how to implement that multi client/multi-tenancy. As a matter of fact, a SQL server provides two basic features to tackle that requirement:- multiple databases, where each DB relates to a client
- multiple schema in the same database, where each schema relates to one client
- additional column in each table to select relevant data per client in WHERE clause
- to work with multiple database exclusively the easiest way is to disconnect from one DB and connect to another DB.
- to work with multiple schema, each qualifier of a database object such as table, column ... needs to be prefixed with the schema qualifier. Since this is not practical, there is a special SQL command SET search_path TO <schema_name> which allows to define the schema to be searched for the name qualifier.
- store all client data in customer_all table, have a column client_name, use SET application_name TO 'clientname' to select client, create updateable view customer with WHERE client_name = current_setting('application_name') OR current_setting('application_name')='all'
Pro verus Con
The following table lists the pro / con arguments for the approaches outlined above.| User-Story | Database | Schema | Table with client_id*) |
|---|---|---|---|
| Implementation overhead | none | some | yes, data model needs change |
| Switching between clients performance | disconnect/connect takes milliseconds | SET search_path, takes no time | SET application_name takes no time |
| Process data of multiple/all clients at once | complex | complex | easy |
| General space control | yes | yes | yes |
| Client specific space control | yes | no | difficult |
| Dedicated vacuuming | yes | no | no |
| Overall backup | easy | easy | easy |
| Backups per application | easy | easy | difficult |
| Backups per client | easy | super difficult | difficult |
| Security and Isolation | very high, nothing special required | good, requires discipline in managing roles | low, possible but difficult to implement |
| Storage requirements | high | middle | low |
| Overall CPU workload | normal | in theory higher | higher due to views |
When to use
Our recommendation is as following:- in most cases it is the easiest to go with the one client, one database approach, even so it requires more space on disk this is by far the easiest way to handle multi-tenancy
- if there is no requirement for isolated backups between the clients the schema approach works well
- if there is a need to aggregate data over all clients the table with client_id approach is working well *)
// ISAM versus SQL data access path
With the ISAM emulation using the "database" approach to isolate different tenants is the best way to go. Using different schema to isolate clients works from the ISAM emulation perspective but the upsize process is super complex here since the upsizing wizard is not designed to manage different schemas.1) In PostgreSQL, a schema is a namespace that contains named database objects such as tables, views, indexes, data types, functions, and operators. To access an object of a schema, you qualify its name with the schema name as a prefix: schema_name.object_name. If you need to name database, schema and object use database_name.schema_name.object_name. The default schema name in a PostgreSQL database is public.
from https://www.postgresqltutorial.com/postgresql-schema/
2) When using the "table with client_id" approach we recommend the usage of views per tenant to restrict access rights to a single tenants data by design. Otherwise the data management approach may violate GDPR/DSGVO requirements.
