Re: RFC: logical publication via inheritance root?

On Wed, Mar 1, 2023 at 9:47 AM Jacob Champion <jchampion(at)timescale(dot)com> wrote:
>
> Hi,
>
> I'm going to register this in CF for feedback.
>
> Summary for potential reviewers: we don't use declarative partitions in
> the Timescale partitioning scheme, but it'd be really nice to be able to
> replicate between our tables and standard tables, or between two
> Timescale-partitioned tables with different layouts. This patch lets
> extensions (or savvy users) upgrade an existing inheritance relationship
> between two tables into a "logical partition" relationship, so that they
> can be handled with the publish_via_partition_root machinery.
>
> I hope this might also help pg_partman users migrate between old- and
> new-style partition schemes, but that's speculation.
>

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.

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. Also, the pg_partman README [3] says since v4.0.0 there is
extensive support for native PostgreSQL partitions, so perhaps
existing LR already works for those.

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 haven’t
yet looked at the WIP patch TAP tests – so apologies for my question
if the benefits to normal users are self-evident from your test cases.

------
[1] https://docs.timescale.com/use-timescale/latest/hypertables/about-hypertables/
[2] https://www.crunchydata.com/blog/native-partitioning-with-postgres
[3] https://github.com/pgpartman/pg_partman

Kind Regards,
Peter Smith.
Fujitsu Australia