Thread: PostgreSQL inheritance vs https://wiki.postgresql.org/wiki/Don%27t_Do_This

Hello All

in the wiki above and specifically in this commit :

https://wiki.postgresql.org/index.php?title=Don%27t_Do_This&type=revision&diff=33210&oldid=33082

someone added this section about inheritance :

"

Don't use table inheritance

Don't use table inheritance. If you think you want to, use foreign keys instead.

Why not?

Table inheritance was a part of a fad wherein the database was closely coupled to object-oriented code. It turned out that coupling things that closely didn't actually produce the desired results.

When should you?

Never …almost. Now that table partitioning is done natively, that common use case for table inheritance has been replaced by a native feature that handles tuple routing, etc., without bespoke code.

One of the very few exceptions would be temporal_tables extension if you are in a pinch and want to use that for row versioning in place of a lacking SQL 2011 support. Table inheritance will provide a small shortcut instead of using UNION ALL to get both historical as well as current rows. Even then you ought to be wary of caveats while working with parent table.

"

I believe this text is false on too many accounts. So, what's the consensus about Inheritance in PostgreSQL, I am going to give a talk on it in November and I wouldn't like to advertise/promote/teach something that the community has decided to abandon or drop. Actually I proposed several topics and they chose this one (Inheritance).

On 2023-Oct-23, Achilleas Mantzios - cloud wrote:

> I believe this text is false on too many accounts. So, what's the consensus
> about Inheritance in PostgreSQL, I am going to give a talk on it in November
> and I wouldn't like to advertise/promote/teach something that the community
> has decided to abandon or drop. Actually I proposed several topics and they
> chose this one (Inheritance).

There are several things wrong with table inheritance; see [1] in our
docs, in addition to the things that the wiki page notes.  I think it's
good advice to stay clear of it, and if you're giving a talk, do not
talk about inheritance except to mention its drawbacks.  Regarding your
talk, I would strongly suggest to ask them to choose another topic from
your list.

[1] https://www.postgresql.org/docs/16/ddl-inherit.html#DDL-INHERIT-CAVEATS

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/
"Investigación es lo que hago cuando no sé lo que estoy haciendo"
(Wernher von Braun)

> On Oct 23, 2023, at 04:45, Achilleas Mantzios - cloud <a.mantzios@cloud.gatewaynet.com> wrote:
> I believe this text is false on too many accounts. So, what's the consensus about Inheritance in PostgreSQL, I am
goingto give a talk on it in November and I wouldn't like to advertise/promote/teach something that the community has
decidedto abandon or drop. Actually I proposed several topics and they chose this one (Inheritance).  

Is the topic literally table inheritance, or really table partitioning?  Often, people who are not deeply experienced
withPostgreSQL will use the term "inheritance" to mean table partitioning, since before version 10, inheritance was the
onlymechanism for doing partitioning.  If what they really mean is partitioning, then you should focus on declarative
partitioning(the newer scheme introduced in version 10), and if you mention table inheritance at all, it should be just
tomention it's an obsolete way of doing partitioning. 

If they really did mean table inheritance, that's a pretty esoteric feature to focus on at this point.  I strongly
suspectthey meant partitioning, and are using the term "inheritance" loosely. 

Achilleas Mantzios - cloud <a.mantzios@cloud.gatewaynet.com> writes:
> someone added this section about inheritance :
>     Don't use table inheritance

That's one person's opinion.  (Well, they're not alone in it, but
it's just an opinion not a considered community position.)

> I believe this text is false on too many accounts. So, what's the 
> consensus about Inheritance in PostgreSQL, I am going to give a talk on 
> it in November and I wouldn't like to advertise/promote/teach something 
> that the community has decided to abandon or drop.

There's zero chance we'd remove table inheritance.  In the first
place, we have too much concern for backwards compatibility, and
in the second place, table partitioning is built on top of it.
Probably no one is going to work hard on adding more features
directly concerned with non-partitioned inheritance, but it's
not going anywhere either.

You should certainly make the point that if your problem looks like
partitioning, you should use partitioning rather than some hand-rolled
use of plain inheritance.  But there are valid use-cases where you
actually do want more columns in the child tables than the parent.

            regards, tom lane

Στις 23/10/23 17:35, ο/η Tom Lane έγραψε:
> Achilleas Mantzios - cloud <a.mantzios@cloud.gatewaynet.com> writes:
>> someone added this section about inheritance :
>>      Don't use table inheritance
> That's one person's opinion.  (Well, they're not alone in it, but
> it's just an opinion not a considered community position.)
>
>> I believe this text is false on too many accounts. So, what's the
>> consensus about Inheritance in PostgreSQL, I am going to give a talk on
>> it in November and I wouldn't like to advertise/promote/teach something
>> that the community has decided to abandon or drop.
> There's zero chance we'd remove table inheritance.  In the first
> place, we have too much concern for backwards compatibility, and
> in the second place, table partitioning is built on top of it.
> Probably no one is going to work hard on adding more features
> directly concerned with non-partitioned inheritance, but it's
> not going anywhere either.
>
> You should certainly make the point that if your problem looks like
> partitioning, you should use partitioning rather than some hand-rolled
> use of plain inheritance.  But there are valid use-cases where you
> actually do want more columns in the child tables than the parent.

Thanks Tom,

also data departmentalization, consolidation, multi-tenancy are fine use 
cases (same # and types of columns), you can't have writable VIEWs with 
UNION, for me inheritance is a fantastic feature that I use with success 
for some very important projects.

Yes I make clear in the talk, that table partitioning is better done  
using declarative partitioning introduced in v10, but mysql and mongodb 
ppl (majority in the event) are not interested in pgsql partitioning as 
a top feature, I guess they have their own solutions for years.

Here is a cool use case for heavy DDL (ETL) on big tables :

  https://www.percona.com/blog/performing-etl-using-inheritance-in-postgresql/

nothing that could not be done via VIEWs and rules, but still more elegant.

I find PostgreSQL inheritance a great feature. The caveats are the same 
since a long time, nothing changed in that regard, but as you say, the 
implementation/limitations exist in native table partitioning as well. 
In fact I wonder how some enterprise ppl live without it. If you are 
interested I'd happily share the doc with the list.

>
>             regards, tom lane

-- 
Achilleas Mantzios
  IT DEV - HEAD
  IT DEPT
  Dynacom Tankers Mgmt

Στις 23/10/23 17:01, ο/η Alvaro Herrera έγραψε:
> On 2023-Oct-23, Achilleas Mantzios - cloud wrote:
>
>> I believe this text is false on too many accounts. So, what's the consensus
>> about Inheritance in PostgreSQL, I am going to give a talk on it in November
>> and I wouldn't like to advertise/promote/teach something that the community
>> has decided to abandon or drop. Actually I proposed several topics and they
>> chose this one (Inheritance).
> There are several things wrong with table inheritance; see [1] in our
> docs, in addition to the things that the wiki page notes.  I think it's
> good advice to stay clear of it, and if you're giving a talk, do not
> talk about inheritance except to mention its drawbacks.  Regarding your
> talk, I would strongly suggest to ask them to choose another topic from
> your list.

Yes, those limitations are known, and must be dealt. The wiki IMHO is 
aggressive and insulting plus the advice to use FK instead of 
inheritance is at least laughable. I don't think there should be such an 
official text on the official PostgreSQL site.

Thanks!

> [1] https://www.postgresql.org/docs/16/ddl-inherit.html#DDL-INHERIT-CAVEATS
>
-- 
Achilleas Mantzios
  IT DEV - HEAD
  IT DEPT
  Dynacom Tankers Mgmt

On 2023-Oct-23, Achilleas Mantzios wrote:

> I find PostgreSQL inheritance a great feature. The caveats are the same
> since a long time, nothing changed in that regard, but as you say, the
> implementation/limitations exist in native table partitioning as well.

For partitioning, many of the limitations have actually been fixed.

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/
"La persona que no quería pecar / estaba obligada a sentarse
 en duras y empinadas sillas    / desprovistas, por cierto
 de blandos atenuantes"                          (Patricio Vogel)

Στις 23/10/23 17:07, ο/η Christophe Pettus έγραψε:
>
>> On Oct 23, 2023, at 04:45, Achilleas Mantzios - cloud <a.mantzios@cloud.gatewaynet.com> wrote:
>> I believe this text is false on too many accounts. So, what's the consensus about Inheritance in PostgreSQL, I am
goingto give a talk on it in November and I wouldn't like to advertise/promote/teach something that the community has
decidedto abandon or drop. Actually I proposed several topics and they chose this one (Inheritance).
 
> Is the topic literally table inheritance, or really table partitioning?  Often, people who are not deeply experienced
withPostgreSQL will use the term "inheritance" to mean table partitioning, since before version 10, inheritance was the
onlymechanism for doing partitioning.  If what they really mean is partitioning, then you should focus on declarative
partitioning(the newer scheme introduced in version 10), and if you mention table inheritance at all, it should be just
tomention it's an obsolete way of doing partitioning.
 
>
> If they really did mean table inheritance, that's a pretty esoteric feature to focus on at this point.  I strongly
suspectthey meant partitioning, and are using the term "inheritance" loosely.
 

Hello Christophe, they mean inheritance on the data design.  They wont 
be pgsql ppl, but mysql, mongodb in their majority I suspect, so they 
want to hear where PgSQL can have an advantage in the enterprise.

There are many blogs from top PgSQL companies (cybertec, percona, etc) 
who have praised inheritance at some point. For me (segregation , 
re-consolidation, look at the data from the POV of one subsidiary, then 
the next, then the top parent company is hard to live without )

Thank you!


-- 
Achilleas Mantzios
  IT DEV - HEAD
  IT DEPT
  Dynacom Tankers Mgmt

Στις 23/10/23 19:54, ο/η Alvaro Herrera έγραψε:
> On 2023-Oct-23, Achilleas Mantzios wrote:
>
>> I find PostgreSQL inheritance a great feature. The caveats are the same
>> since a long time, nothing changed in that regard, but as you say, the
>> implementation/limitations exist in native table partitioning as well.
> For partitioning, many of the limitations have actually been fixed.
sorry that I said that. That's great!
>
-- 
Achilleas Mantzios
  IT DEV - HEAD
  IT DEPT
  Dynacom Tankers Mgmt

On 10/23/23 09:51, Achilleas Mantzios wrote:
> Στις 23/10/23 17:01, ο/η Alvaro Herrera έγραψε:
>> On 2023-Oct-23, Achilleas Mantzios - cloud wrote:
>>
>>> I believe this text is false on too many accounts. So, what's the 
>>> consensus
>>> about Inheritance in PostgreSQL, I am going to give a talk on it in 
>>> November
>>> and I wouldn't like to advertise/promote/teach something that the 
>>> community
>>> has decided to abandon or drop. Actually I proposed several topics 
>>> and they
>>> chose this one (Inheritance).
>> There are several things wrong with table inheritance; see [1] in our
>> docs, in addition to the things that the wiki page notes.  I think it's
>> good advice to stay clear of it, and if you're giving a talk, do not
>> talk about inheritance except to mention its drawbacks.  Regarding your
>> talk, I would strongly suggest to ask them to choose another topic from
>> your list.
> 
> Yes, those limitations are known, and must be dealt. The wiki IMHO is 
> aggressive and insulting plus the advice to use FK instead of 
> inheritance is at least laughable. I don't think there should be such an 
> official text on the official PostgreSQL site.

I don't know enough to say either way. What I do know is that this is 
the Wiki and you can sign up to edit it.

> 
> Thanks!
> 
>> [1] 
>> https://www.postgresql.org/docs/16/ddl-inherit.html#DDL-INHERIT-CAVEATS
>>

-- 
Adrian Klaver
adrian.klaver@aklaver.com

On Mon, 2023-10-23 at 19:49 +0300, Achilleas Mantzios wrote:
> Tom Lane wrote:
> > But there are valid use-cases where you
> > actually do want more columns in the child tables than the parent.
>
> also data departmentalization, consolidation, multi-tenancy are fine use
> cases (same # and types of columns), you can't have writable VIEWs with
> UNION, for me inheritance is a fantastic feature that I use with success
> for some very important projects.

Yes, there are valid and interesting use cases, although you have to
search for them.  I am sure it can make an interesting talk.

Yours,
Laurenz Albe

Στις 23/10/23 20:06, ο/η Laurenz Albe έγραψε:
> On Mon, 2023-10-23 at 19:49 +0300, Achilleas Mantzios wrote:
>> Tom Lane wrote:
>>>   But there are valid use-cases where you
>>> actually do want more columns in the child tables than the parent.
>> also data departmentalization, consolidation, multi-tenancy are fine use
>> cases (same # and types of columns), you can't have writable VIEWs with
>> UNION, for me inheritance is a fantastic feature that I use with success
>> for some very important projects.
> Yes, there are valid and interesting use cases, although you have to
> search for them.  I am sure it can make an interesting talk.

Thank you, I hope so.

In my job, family owned business,  shipping, different types of vessels, 
different business for each type of vessel  both conceptually / 
functionally and materially , multi-tenancy (ok the illusion of many 
different systems) + the need of top view of the organization, it fitted 
like a globe.

>
> Yours,
> Laurenz Albe

-- 
Achilleas Mantzios
  IT DEV - HEAD
  IT DEPT
  Dynacom Tankers Mgmt

Στις 23/10/23 19:58, ο/η Achilleas Mantzios έγραψε:
> Στις 23/10/23 19:54, ο/η Alvaro Herrera έγραψε:
>> On 2023-Oct-23, Achilleas Mantzios wrote:
>>
>>> I find PostgreSQL inheritance a great feature. The caveats are the same
>>> since a long time, nothing changed in that regard, but as you say, the
>>> implementation/limitations exist in native table partitioning as well.
>> For partitioning, many of the limitations have actually been fixed.

Seeing here : 
https://www.postgresql.org/docs/16/ddl-partitioning.html#DDL-PARTITIONING-DECLARATIVE

I am seeing the same obstacle to have a global unique index on the whole 
partitioned table, without including partition keys of any sort. This is 
the same since 11 or 12 IIRC. IMHO the most restrictive limitation. (and 
surely too hard to solve)

> sorry that I said that. That's great!
>>
-- 
Achilleas Mantzios
  IT DEV - HEAD
  IT DEPT
  Dynacom Tankers Mgmt

Hello All

in the wiki above and specifically in this commit :

https://wiki.postgresql.org/index.php?title=Don%27t_Do_This&type=revision&diff=33210&oldid=33082

someone added this section about inheritance :

"

Don't use table inheritance

Don't use table inheritance. If you think you want to, use foreign keys instead.

Why not?

Table inheritance was a part of a fad wherein the database was closely coupled to object-oriented code. It turned out that coupling things that closely didn't actually produce the desired results.

When should you?

Never …almost. Now that table partitioning is done natively, that common use case for table inheritance has been replaced by a native feature that handles tuple routing, etc., without bespoke code.

One of the very few exceptions would be temporal_tables extension if you are in a pinch and want to use that for row versioning in place of a lacking SQL 2011 support. Table inheritance will provide a small shortcut instead of using UNION ALL to get both historical as well as current rows. Even then you ought to be wary of caveats while working with parent table.

"

I believe this text is false on too many accounts. So, what's the consensus about Inheritance in PostgreSQL, I am going to give a talk on it in November and I wouldn't like to advertise/promote/teach something that the community has decided to abandon or drop. Actually I proposed several topics and they chose this one (Inheritance).

-- 
Achilleas Mantzios IT DEV - HEAD IT DEPT Dynacom Tankers Mgmt