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

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]https://www.postgresql.org/docs/16/ddl-inherit.html#DDL-INHERIT-CAVEATS 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 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).

Is the topic literally table inheritance, or really table partitioning? Often, people who are not deeply experienced with PostgreSQL will use the term "inheritance" to mean table partitioning, since before version 10, inheritance was the only mechanism 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 to mention 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 suspect they 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 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).

Is the topic literally table inheritance, or really table partitioning? Often, people who are not deeply experienced with PostgreSQL will use the term "inheritance" to mean table partitioning, since before version 10, inheritance was the only mechanism 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 to mention 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 suspect they 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

Here is the presentation, it was done under "Percona University Athens".
It went ok, I think, although people didn't seem to understand much, as
the audience was MySQL/PostgreSQL/Mongo/Oracle mix. However some young
people seemed to be interested as they asked questions afterwards.

https://docs.google.com/presentation/d/15LmNmjSMAXzHDSlm0MgvHuhSPnmVijsytdPJOz3l13g/edit?usp=sharing

It was an interesting event, I had the chance to speak to top MySQL
hackers (including the founders of Percona, TiDB, FerretDB). The event
was relatively friendly to PostgreSQL as well, I have to say. PostgreSQL
was presented as catching up MySQL in terms of usage/growth.

In some future similar events I'd love to prepare a better and more
current presentation for the local Greek community!

Στις 23/10/23 14:45, ο/η Achilleas Mantzios - cloud έγραψε:

Hello All

in the wiki above and specifically in this commit :

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

someone added this section about inheritance :

"

Don't use table inheritance

Don't use table inheritance
<https://www.postgresql.org/docs/current/tutorial-inheritance.html&gt;.
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
<https://github.com/arkhipov/temporal_tables&gt; 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
<http://clarkdave.net/2015/02/historical-records-with-postgresql-and-temporal-tables-and-sql-2011/#using-inheritance&gt;
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