PostgreSQL - Pros and cons of using INHERITS instead of references for modeling OOP inheritance?

Question

using references:

CREATE TABLE Users (
    id       int     primary key,
    name     text
);

CREATE TABLE Moderators (
    role     int,
    userid   int references Users(id)
);

using INHERITS:

CREATE TABLE Users (
    id       int     primary key,
    name     text
);

CREATE TABLE Moderators (
    role     int
) INHERITS (Users);

Besides the differences in query syntax, is there any difference in performance, scalability of those two?

Answer 1

The inheritance in PostgreSQL is pretty old artefact. In these days the PostgreSQL is relation database, it is not OOP database.

If your model will be OOP only, then probably you will not touch some unwanted artefacts - but when you will try mix both models, then you can touch some issues - inheritance is not well supported by reference integrity constraints and maybe you can find some other gotchas. The OOP way is not used, is not preferred - it is just historic artefact.