Re: adding partitioned tables to publications

From: Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com>
To: Amit Langote <amitlangote09(at)gmail(dot)com>
Cc: Rafia Sabih <rafia(dot)pghackers(at)gmail(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: adding partitioned tables to publications
Date: 2019-11-22 10:46:14
Message-ID: 7a23e60b-ffe8-bce1-5c44-db48d3bc163e@2ndquadrant.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On 2019-11-22 07:28, Amit Langote wrote:
> Hmm, I thought it would be more desirable to not expose a published
> partitioned table's leaf partitions to a subscriber, because it allows
> the target table to be defined more flexibly.

There are multiple different variants that we probably eventually want
to support. But I think there is value in exposing the partition
structure to the subscriber. Most notably, it allows the subscriber to
run the initial table sync per partition rather than in one big chunk --
which ultimately reflects one of the reasons partitioning exists.

The other way, exposing only the partitioned table, is also useful,
especially if you want to partition differently on the subscriber. But
without the ability to target a partitioned table on the subscriber,
this would right now only allow you to replicate a partitioned table
into a non-partitioned table. Which is valid but probably not often useful.

>> What happens when you add a leaf table directly to a publication? Is it
>> replicated under its own identity or under its ancestor partitioned
>> table? (What if both the leaf table and a partitioned table are
>> publication members?)
>
> If both a leaf partition and an ancestor belong to the same
> publication, then leaf partition changes are replicated using the
> ancestor's schema. For a leaf partition to be replicated using its
> own schema it must be published via a separate publication that
> doesn't contain the ancestor. At least that's what the current patch
> does.

Hmm, that seems confusing. This would mean that if you add a
partitioned table to a publication that already contains leaf tables,
the publication behavior of the leaf tables would change. So again, I
think this alternative behavior of publishing partitions under the name
of their root table should be an explicit option on a publication, and
then it should be ensured somehow that individual partitions are not
added to the publication in confusing ways.

So, it's up to you which aspect of this you want to tackle, but I
thought your original goal of being able to add partitioned tables to
publications and have that implicitly expand to all member partitions on
the publication side seemed quite useful, self-contained, and
uncontroversial.

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Jinbao Chen 2019-11-22 10:50:43 Re: Planner chose a much slower plan in hashjoin, using a large table as the inner table.
Previous Message imai.yoshikazu@fujitsu.com 2019-11-22 10:23:09 RE: Planning counters in pg_stat_statements (using pgss_store)