Re: RFC: logical publication via inheritance root?

On Fri, Mar 31, 2023 at 3:17 PM Peter Smith <smithpb2250(at)gmail(dot)com> wrote:
> OK, my understanding is that TimescaleDB uses some kind of
> quasi-partitioned/inherited tables (aka hypertables? [1]) internally,
> and your proposed WIP patch provides a set_logical_root() function
> which combines with the logical replication (LR) PUBLICATION option
> "publish_via_partition_root" to help to replicate those.

Correct!

> You also mentioned pg_partman. IIUC pg_partman is a partitioning
> extension [2] that pre-dated the native PostgreSQL partitioning
> introduced in PG10 (i.e. quite a while ago). I guess it would be a
> very niche group of users that are still using pg_partman old-style
> (pre-PG10) partitions and want to migrate them but have not already
> done so.

Yeah. I've got no evidence either way, unfortunately -- on the one
hand, surely people have been able to upgrade by now? And on the
other, implementation inertia seems to override most other engineering
goals...

Probably best to ask the partman users, and not me. :D Or assume it's
a non-benefit unless someone says otherwise (even then, the partman
maintainers would need to agree it's useful and add support for this).

> Outside the scope of special TimescaleDB tables and the speculated
> pg_partman old-style table migration, will this proposed new feature
> have any other application? In other words, do you know if this
> proposal will be of any benefit to the *normal* users who just have
> native PostgreSQL inherited tables they want to replicate?

I think it comes down to why an inheritance scheme was used. If it's
because you want to group rows into named, queryable subsets (e.g. the
"cities/capitals" example in the docs [1]), I don't think this has any
utility, because I assume you'd want to replicate your subsets as-is.

But if it's because you've implemented a partitioning scheme of your
own (the docs still list reasons you might want to [2], even today),
and all you ever really do is interact with the root table, I think
this feature will give you some of the same benefits that
publish_via_partition_root gives native partition users. We're very
much in that boat, but I don't know how many others are.

Thanks!
--Jacob

[1] https://www.postgresql.org/docs/15/tutorial-inheritance.html
[2] https://www.postgresql.org/docs/15/ddl-partitioning.html#DDL-PARTITIONING-USING-INHERITANCE