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

[Koichi S. <koi...@gm...>]

Hello;


2013/8/8 amul sul <sul...@ya...>

> Dear Michael-sir
>
> Thanks to you and Suzuki-san, Bapat-sir for their quick reply.
>
> Here just want to clear Parent and child relation,
> Do you mean, child table which contain Foreign key and Parent table is
> which going to be referred.
> The following eg. PRODUCTS table will be Parent and  ORDERS table is child.
>
> I hope, this is right,
>
> CREATE TABLE products (
> product_no integer PRIMARY KEY,
> name text,
> price numeric
> );
>

This is correct.   The table will be distributed by hash using the primary
key.


>
> CREATE TABLE orders (
> order_id integer,
> product_no integer REFERENCES products (product_no),
> quantity integer
> )
>

This is not supported yet.   Because product table row referred from orders
table can be located  at other datanode.

To make this work, you can distribute product table as replicated.   This
works.   Because product table is more stable than order table, impact to
the whole throughput may not be significant.

Regards;
---
Koichi Suzuki


> As can i see, more general way is the referred table(here products) should
> replicated only when we unable distribute it (using its product_no) due to
> other limitations, will definitely follow in such case.
>
> My simple understanding is, when we going to add any row in ORDERS table,
> we are going to look respective entry is present in PRODUCTS table.
> so we need to find it using it unique key in global distribution or
> locally.
>
> That's  why here we needed PRODUCTS table should distributed on its
> PRODUCT_NO column.
> and no worry about ORDERS table distribution.
>
> Is this right?
>
> But document at
> http://postgres-xc.sourceforge.net/docs/1_0_3/ddl-constraints.html
>
>
> ------------------------------------------------------------------------------
> CREATE TABLE orders ( order_id integer, product_no integer REFERENCES
> products (product_no), quantity integer
> ) DISTRIBUTE BY HASH(product_no);
> Note: The following description applies only to Postgres-XC
> Please note that column with REFERENCE must be the distribution column. In
> this case, we cannot add PRIMARY KEY to order_id because PRIMARY KEY must
> be the distribution column as well. This limitation is introduced because
> constraints are enforced only locally in each Datanode, which will be
> resolved in the future.
>
> ------------------------------------------------------------------------------
>
> This focusing to distribute ORDERS on its reference column
> (here product_no).
>
> Does this required?
> because in my case if my query is inserting row in ORDERS table, will
> always try to search refereed table i.e PRODUCTS.
> so why should distribute ORDERS table with REFERENCE column.
>
> Lets say there will be third table which referring ORDER on its order_id,
> in this case its good idea to distribute it on order_id.
> Finally picture will look like
>
> PRODUCTS --> distribute using product_no
> ORDERS -->  distribute using order_id
> THIRD_TABLE --> depends on requirement.
>
> Can i do this way, do i missing something?
>
> >>Please check out this video http://www.youtube.com/watch?v=g_9CYat8nkY (Preview) .
> The video explains various impacts the distribution/replication has in XC.
> Very informative presentation. Thank you :)
>
> >>>Welcome to Postgres-XC community.
> Thank you Suzuki-san.
>
> >>Hi Amul,
>
> Thanks and 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
>