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

Στις 23/10/23 17:35, ο/η Tom Lane έγραψε:
> Achilleas Mantzios - cloud <a(dot)mantzios(at)cloud(dot)gatewaynet(dot)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