Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

2 Answers
2

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.

Yes, I was thinking about creating a bigint/guid sequence that will serve as a PK. However, I wasn't sure of the repercussions involved with PK's and FK's. If I had to create a few tables, Users, Roles, and a mapping table UserRoles. It would be, Users has has many UserRoles, and Roles has many UserRoles, which will create 2 FK's on UserRoles to Users and Roles. Can you advise me as to how this SHOULD be done with table inheritance?
–
Lee337Mar 21 '13 at 18:59

When you considering to use this design in postgres, take in account that:
1. You need to write a management script that keeps all inherited tables of same type in sync, (for ex. 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 needs 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 continiously, so this is a real issue.

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