Re: [Postgres-xc-general] Composite table types, replicate + distribute.

[Michael P. <mic...@gm...>]

On Wed, Jul 4, 2012 at 2:31 PM, Joseph Glanville <
jos...@or...> wrote:

> Hey guys,
>
> This is more of a feature request/question regarding how HA could be
> implemented with PostgreXC in the future.
>
> Could it be possible to have a composite table type which could
> replicate to X nodes and distribute to Y nodes in such a way that
> atleast X copies of every row is maintained but the table is shareded
> across Y data nodes.
>
The answer is yes. It is possible.

>
> For example in a cluster of 6 nodes one would be able configure at
> table with REPLICATION 2, DISTRIBUTE 3 BY HASH etc (I can't remember
> what the table definitions look like) as such that the table would be
> replicated to 2 sets of 3 nodes.
>
As you seem to be aware of, now XC only supports horizontal partitioning,
meaning that tuples are present on each node in a complete form with all
the column data.
So let's call call your feature partial horizontal partitioning... Or
something like this.


> This is interesting becaues it can provide a flexible tradeoff between
> full write scalability (current PostgresXC distribute) and full read
> scalability (PostgresXC replicate or other slave solutions)
> What is most useful about this setup is using PostgresXC this can be
> maintained transparently without middleware and configured to be fully
> sync multi-master etc.
>
Do you have some example of applications that may require that?


>
> Are there significant technical challenges to the above and is this
> something the PostgresXC team would be interested in?
>
The code would need to be changed at many places and might require some
effort especially for cursors and join determination at planner side.

Another critical choice I see here is related to the preferential strategy
for node choice.
For example, in your case, the table is replicated on 3 nodes, and
distributed on 3 nodes by hash.
When a simple read query arrives at XC level, we need to make XC aware of
which set of nodes to choose in priority.
A simple session parameter which is table-based could manage that though,
but is it user-friendly?
A way to choose the set of nodes automatically would be to evaluate with a
global system of statistics the load on each table of read/write operations
for each set of nodes and choose the set of nodes the less loaded at the
moment query is fired when planning it. This is largely more complicated
however.
-- 
Michael Paquier
http://michael.otacoo.com