Using inheritance in PostgreSQL

Question

To someone who has experience using inheritance in PostgreSQL: Is it worth using it, or better not to? In which situation you would use it?

To be honest, I do not fully understand the difference between the relational and OO models...

Answer 1

Probably not, there are caveats to PostgreSQL table inheritance, such as no globally unique constraints, so you lose many of the consistency guarantees. Also writing well-performing queries can be quite a challenge. As pointed out by Scott, PostgreSQL inheritance is only really useful for table partitioning where it's a performance tradeoff to begin with.

There are 2 common ways to use standard SQL idioms for class inheritance:

• Every object has an individual row in a superclass table, and subclass objects also have a row in a subclass-specific table, that refers to superclass fields by a foreign-key reference.
• Just lump all superclass and subclass fields in a single big table, and leave them be NULLs where their value is not applicable. This works particularly well in PostgreSQL, because NULL values only consume 1 bit per row, and adding/removing fields to existing tables is very fast (regardless of the amount of data). You could possibly write a trigger to verify that required fields for a specific type of class are present.

Answer 2

Its nice but be sure your understand the caveats outlined in the manual before using it. Currently the way it handles constraints is a bit rough but its on the todo list. It is particularly useful with partitioning. A more OO example would be inheriting from the people table to create an employees table.

Of course the downside is that its not portable to any other rdbms's so if you had to rehost a database on another rdbms you'd have to rewrite a bunch of stuff.