Will table inheritance in PostgreSql suit this situation?

Question

Let's start off by saying, I'm new to PSQL and coming from a MSSQL background. I'm starting to design a DB in PSQL.

So my main aim is to have a Main schema in this DB, which I am going to use to control certain services with, and have multiple schema's linked to this, which is going to be the different clients data.

I have read up on table inheritance, but have never came across anyone using inheritance with an effect similar to that of DB Migrations. What I want to achieve is basically have a default schema, with the clients schema's all inheriting from the default. This to me gives control of creating/dropping/altering objects to all schema's by just altering a single schema.

Could this be viable? Has anyone else had any experience doing something similar? Does anyone have any suggestion?

UPDATE

This is what I've done. However, I was under the impression that, if you look at the below code, schema 'client1' will be all I need to do, however I was wrong. :D

So anyway, below is the script I have done, which should take care of everything I need for now - I am going to use the script for the 'client2' schema. Good or Bad?

-- public schema --
CREATE TABLE public.Users (
   Id bigserial primary key,
   Login text unique,
   check (false) NO INHERIT 
);

CREATE TABLE public.Roles (
   Id bigserial primary key,
   RoleName text unique,
   check (false) NO INHERIT 
);

CREATE TABLE public.UserRoles (
   Id bigserial primary key,
   UserId bigint references users(id),
   RoleId bigint references roles(id),
   unique (UserId, RoleId),
   check (false) NO INHERIT 
);


-- client1 schema --
CREATE TABLE client1.Users (
)
INHERITS(public.Users);

CREATE TABLE client1.Roles (
)
INHERITS(public.Roles);

CREATE TABLE client1.UserRoles (
)
INHERITS(public.UserRoles);


-- client2 schema --
CREATE TABLE client2.Users (
  primary key (id),
  Unique (Login)
)
INHERITS(public.Users);

CREATE TABLE client2.Roles (
  primary key (id),
  Unique (RoleName)
)
INHERITS(public.Roles);

CREATE TABLE client2.UserRoles (
  primary key (id),
  foreign key (UserId) REFERENCES client2.Users(id),
  foreign key (RoleId) REFERENCES client2.Roles(id),
  unique (UserId, RoleId)
)
INHERITS(public.UserRoles);

Answer 1

It is viable provided that you note that keys and indexes must be declared on each child table.

In fact this is pretty close to an ideal use for table inheritance. Just make sure you are familiar with the gotchas, and that you pay close attention to foreign key creation in the user schemas.

One caveat is that you can't cascade CREATE statements into all the schemas. This would have to be scripted. You could however create plpgsql functions to do this. Just note that such statements usually are not parameterized so you need to use quote_ident() and quote_literal as appropriate.

As per request here's an example. In the master schema. Note the key and unique constraints here are for documentation purposes only.

CREATE SCHEMA master;
SET SEARCH_PATH="master;

CREATE TABLE users (
   id bigserial not null unique,
   login text primary key,
   check (false) NO INHERIT -- 9.2 ONLY, prevents any rows in this table
);

CREATE TABLE roles (
   id bigserial not null unique,
   role_name text primary key,
   check (false) NO INHERIT -- 9.2 ONLY, prevents any rows in this table
);

CREATE TABLE user_roles (
   user_id bigint references users(id),
   role_id bigint references roles(id),
   primary key (user_id, role_id)
   check (false) NO INHERIT -- 9.2 ONLY, prevents any rows in this table
);

RESET SEARCH_PATH;

Naturally if not on PostgreSQL 9.2 drop the check constraints since they would be inherited in prior versions and then no tables could have rows.

Then in a child schema:

CREATE SCHEMA customer123;
SET SEARCH_PATH 'customer123';

CREATE TABLE users(unique (id), primary key(login)) INHERITS (master.users);
CREATE TABLE roles(unique (id), prikary key(role_name)) INHERITS (master.roles);
CREATE TABLE user_roles(
     foreign key users_id REFERENCES customer123.users(id),
     foreign key role_id REFERENCES customer123.roles(id),
     primary key (users_id, role_id)
);
RESET SEARCH_PATH; 

Note in this case you must carefully control access to the master schema because:

SELECT tableoid, login from master.users;

gives you every user account of every tenant plus enough info to determine which tenant it is.

Answer 2

Here is some real world experience:

I was working on a very large database with a structure like this one. Back then I decided to use 'Multi-tenant schema separated architecture' based on this article: https://msdn.microsoft.com/en-us/library/aa479086.aspx

When you are considering using this design in postgres, take into account that:

1. You need to write a management script that keeps all inherited tables of same type in sync, (for example, 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.
2. Auto-increment sequences need to be defined on a public schema manually and all inherited tables should manually be defined to feed from it.
3. 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 continuously, so this is a real issue.

You should not use this design if your database is running mostly public table SELECTs on all inherited tables, because it will run through every schema and add enormous overhead compared to using a shared table. If your software mostly runs queries inside a client schema (or on public with CHECK filter conditions) and public SELECTs are only for batch/infrequent operations, it's perfectly fine.