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.

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
Practically the scenarios are managed as following:
  • 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-StoryDatabaseSchemaTable with client_id*)
Implementation overheadnonesomeyes, data model needs change
Switching between clients
performance
disconnect/connect takes
milliseconds
SET search_path, takes no timeSET application_name takes no time
Process data of multiple/all
clients at once
complexcomplexeasy
General space controlyesyesyes
Client specific space controlyesnodifficult
Dedicated vacuumingyesnono
Overall backupeasyeasyeasy
Backups per applicationeasyeasydifficult
Backups per clienteasysuper difficultdifficult
Security and Isolationvery high, nothing special requiredgood, requires discipline in managing roleslow, possible but difficult to implement
Storage requirementshighmiddlelow
Overall CPU workloadnormalin theory higherhigher 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.