Postgresql – Concept of Schema in PostgreSQL

Option 1

There are several reasons for this, which I'll explain below. First, here's how to do it.

• Use your choice of standard RDBMS platform.

• Set up your schema with several user-configurable fields, and make your application facilitate the configuration on a per-tenant basis.

• From the per-tenant metadata, you can create a per-tenant view of their data, which has the filters built in, and the columns named from your metadata. Any reports provided can also inherit the metadata. If they want to do M.I. off the data then provide them an extract of the transactional data, or maybe some additional MIS application on a different server if they will pay for that.

• Don't try to provide more customisation than this (i.e. no radical changes to the schema) unless the client is prepared to pay for their own private instance and maintaing a custom build.

The reasons behind this are:

• These database systems will handle the sort of volumes you describe on fairly ordinary hardware. You don't really have the sort of transaction volume that merits a NoSQL database. Unless you have some other architectural reason to want one, there's not much point in going bleeding-edge.

• They're mature, well-understood technologies.

• System management, backup/restore, replication, reporting and disaster recovery are all well sorted on RDBMS platforms.

• You can get client libraries including JDBC for all major RDBMS platforms.

• Views can be used for the per-user customisation and generatedfrom your application metadata.

• It's substantially more efficient than XML fields or EAV structures.

In SQL Server, changing the schema owner will drop all permissions. This is a "feature" buried deep in the documentation:

If the target entity is not a database and the entity is being transferred to a new owner, all permissions on the target will be dropped.

https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-authorization-transact-sql

For a schema, this apparently applies to the permissions on all objects within it.