Re: [Postgres-xc-general] How distribution of tables works in PGXC?

[Ashutosh B. <ash...@en...>]

Hi Amul,
Please check out this video http://www.youtube.com/watch?v=g_9CYat8nkY. The
video explains various impacts the distribution/replication has in XC.


On Wed, Aug 7, 2013 at 11:50 AM, Koichi Suzuki <koi...@gm...>wrote:

> Hello Amul;
>
>
> Welcome to Postgres-XC community.
>
>
> 2013/8/7 amul sul <sul...@ya...>
>
>> Hello ALL,****
>>
>> I need little help, to migrated Postgres database to the PGXC.****
>>
>> While creating database, I want to best possible way of relations should
>> distribute and/or replicated, Primary aim to achieve to optimum performance.
>>
>
> Some description on table distribution/replication will be found in PGCon
> tutorial material available at our Wiki page
> http://postgresxc.wikia.com/wiki/Postgres-XC_Wiki.
>
>
>> ****
>>
>> But we need to consider following constrain mention in documentation. ***
>> *
>>
>> 1. In distributed tables, UNIQUE constraints must include the
>> distribution column of the table.
>>
>
> Yes, because we cannot enforce unique constraints in this case unless we
> do not have global index.   It is not practical to visit all the datanodes
> to enforce this.
>
>
>>  ****
>> 2. in UNIQUE constraint, distribution column must be included in PRIMARY
>> KEY. Other restrictions apply to PRIMARY KEY too.
>>
>
> This is by the same reason as 1.
>
>
>> ****
>> 3. REFERENCE must be the distribution column while creating Foreign key
>> in relation. In this case, we cannot add PRIMARY KEY to other column in
>> relation because PRIMARY KEY must be the distribution column as well.
>>
>
> This is also by the same reason as 1.  We may be able to support this
> without such global object management if referred column is a primary key
> or set of referred column includes a primary key.
>
>
>> ****
>> 4. With REFERENCES integrity needs to be the distribution column, it is
>> Postgres-XC's restriction that we cannot specify more than one columns with
>> REFERENCES constraint.
>>
>
> See above.
>
>
>>
> ****
>> 5. Not allowed Modifying distribution columns definition and Modifying
>> distribution column values
>>
>
> When distribution column value is altered, we need to move this row to
> somewhere else.  It is not simple so far to track this to handle following
> update statement correctly.
>
> ****
>>
>> I am not pretty clear about all those point, so need your help.****
>> My questions are as follow:****
>>
>> Is distribution on table is possible in following condition ?****
>>         1. relation(table) has Primary key and Foreign key(REFERENCE
>> column) and both are different?****
>>         2. If primary key is on ( col1, col2) and Foreign key is only on
>> (col2) is this O.K, if PRIMARY key is used as distribution column. ****
>>         3. If relation has more than one Foreign key (on col1 and col2) *
>> ***
>>
>> I think it not possible distribute table in such condition, in this case
>> is this best way to distributed by ROUND ROBIN or REPLECATE relations?
>>
>
> Round robin is not a good idea.   Make referred table "replicated" will
> fix the above problems.
>
> Hope they help.
>
> Regards;
> ---
> Koichi Suzuki
>
>
>
>> ****
>>
>> Thoughts?  Comments?****
>>
>> Thank you.****
>>
>> Regards****
>>  Amul Sul****
>>
>>
>> ------------------------------------------------------------------------------
>> Get 100% visibility into Java/.NET code with AppDynamics Lite!
>> It's a free troubleshooting tool designed for production.
>> Get down to code-level detail for bottlenecks, with <2% overhead.
>> Download for free and get started troubleshooting in minutes.
>>
>> http://pubads.g.doubleclick.net/gampad/clk?id=48897031&iu=/4140/ostg.clktrk
>> _______________________________________________
>> Postgres-xc-general mailing list
>> Pos...@li...
>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-general
>>
>>
>
>
> ------------------------------------------------------------------------------
> Get 100% visibility into Java/.NET code with AppDynamics Lite!
> It's a free troubleshooting tool designed for production.
> Get down to code-level detail for bottlenecks, with <2% overhead.
> Download for free and get started troubleshooting in minutes.
> http://pubads.g.doubleclick.net/gampad/clk?id=48897031&iu=/4140/ostg.clktrk
> _______________________________________________
> Postgres-xc-general mailing list
> Pos...@li...
> https://lists.sourceforge.net/lists/listinfo/postgres-xc-general
>
>


-- 
Best Wishes,
Ashutosh Bapat
EntepriseDB Corporation
The Postgres Database Company