Is it safe to use inheritance for meta-data migration?

Question

Is it safe to create a template schema for metadata so that other schemas which contain data can inherit from it?

Advantage: Migrations will be seamless for multi tenant scenarios.

Disadvantages: ?

Example:

hello=# CREATE SCHEMA template;

hello=# CREATE TABLE template.cities (
name       text,
population real,
altitude   int;

hello=# CREATE SCHEMA us;

hello=# CREATE TABLE us.cities () INHERITS (template.cities);

hello=# \d us.cities;
                   Table "us.cities"
   Column   |     Type     | Collation | Nullable | Default 
------------+--------------+-----------+----------+---------
 name       | text         |           |          | 
 population | real         |           |          | 
 altitude   | integer      |           |          | 
Inherits: template.cities

hello=# CREATE SCHEMA eu;

hello=# CREATE TABLE eu.cities () INHERITS (template.cities);

hello=# \d eu.cities;
                   Table "eu.cities"
   Column   |     Type     | Collation | Nullable | Default 
------------+--------------+-----------+----------+---------
 name       | text         |           |          | 
 population | real         |           |          | 
 altitude   | integer      |           |          | 
Inherits: template.cities

hello=# ALTER TABLE cities ADD COLUMN state varchar(30);

hello=# \d us.cities;
                          Table "us.cities"
   Column   |         Type          | Collation | Nullable | Default 
------------+-----------------------+-----------+----------+---------
 name       | text                  |           |          | 
 population | real                  |           |          | 
 altitude   | integer               |           |          | 
 state      | character varying(30) |           |          | 
Inherits: template.cities

Answer 1

I think inheritance is a good approach to this problem.

I can think of two down sides:

• It is possible to create additional columns to the inheritance children. If you control DDL, you can probably prevent that.

• You still have to create and modify indexes on all inheritance children individually.

If you are using PostgreSQL v11 or later, you could prevent both problems by using partitioning. The individual tables would then be partitions of the “template” table. This way, you can create indexes centrally by creating a partitioned index on the template table. The disadvantage (that may make this solution impossible) is that you need a partitioning key column in the table.