Re: [Postgres-xc-developers] Data distribution by hash - general question

[Adam D. <ada...@gm...>]

So this some kind of strategy fos situation:
Table A join Table B join Table C ?


2013/7/16 Mason Sharp <ma...@st...>

>
>
>
> On Tue, Jul 16, 2013 at 11:00 AM, Adam Dec <ada...@gm...> wrote:
>
>> So the very basic strategy for data distribution (as I understand it
>> right now) is to distribute data by hash (table with a lot of writes) using
>> primary key? Are there any other strategies/scenarios?
>>
>>
> If you have fairly static tables, use "distribute by replication" for
> them.
>
> For some other scenarios you could do a bit of denormalization and pull in
> a foreign key further. For example, for customer-order-lineitem, with
> cust_id in customer and order (as a FK), you could pull cust_id down into
> lineitem as well and distribute by it. You would have to make sure that the
> application always includes cust_id in lineitem in the WHERE clause when
> joining with order to ensure that the query gets pushed down to a single
> node.
>
>
>
>
>
>>
>> 2013/7/16 Mason Sharp <ma...@st...>
>>
>>>
>>>
>>>
>>> On Tue, Jul 16, 2013 at 9:30 AM, Adam Dec <ada...@gm...> wrote:
>>>
>>>> Yes A is like a parent and all the others are like a children (joined
>>>> with foreign key)...so using parent primary key (id) I will be sure that
>>>> the data (that are joined) will all stay at the same node? What about the
>>>> situation when table B is also joined (using foreign key) with some other
>>>> table? Is that the same?
>>>>
>>>>
>>> If B is distributed on id but also contains column "id2" that is joined
>>> with table E, then it can only push down some of the joins. It will end up
>>> needing to join on one single coordinator and shipping all of the data
>>> there (This is one of the issues that StormDB addresses in our version).
>>>
>>>
>>>
>>>>
>>>> 2013/7/16 Mason Sharp <ma...@st...>
>>>>
>>>>> Adam,
>>>>>
>>>>> Is "id" present in all of A,B,C, and D? Is A the parent and the other
>>>>> children and used as a foreign key from B,C, and D to A? If so, yes, on the
>>>>> surface it sounds like you can do that and be able to take advantage of
>>>>> pushing down joins to the local data nodes.
>>>>>
>>>>> Regards,
>>>>>
>>>>> Mason
>>>>>
>>>>>
>>>>> On Mon, Jul 15, 2013 at 9:03 AM, Adam Dec <ada...@gm...> wrote:
>>>>>
>>>>>> Hi!
>>>>>>
>>>>>> My topology: 2 machines (on each machine 1 master coordinator and 1
>>>>>> master datannode)
>>>>>>
>>>>>> Lets say that I have a table A which has
>>>>>> - one-to-many relation with table B
>>>>>> - one-to-many relation to table C
>>>>>> - one-to-one relation with table D
>>>>>>
>>>>>> I would like to distribute it in the cluster. All I have to do is to
>>>>>> put
>>>>>> DISTRIBUTE BY HASH(id); in each of the tables while creating them?
>>>>>> id - primary key
>>>>>>
>>>>>> In my example Table A is like a root of the graph. How to create such
>>>>>> a "graph of tables" to be shure
>>>>>> that when I will invoke a select with joins all the proceesin will be
>>>>>> done only on the one node.
>>>>>> I do not want to replicate all the data.
>>>>>>
>>>>>> Where can I read about data distribution in Postgres XC? Do you have
>>>>>> any examples that I could look at?
>>>>>>
>>>>>>
>>>>>> Regards,
>>>>>>
>>>>>> Adam Dec
>>>>>>
>>>>>>
>>>>>> ------------------------------------------------------------------------------
>>>>>> See everything from the browser to the database with AppDynamics
>>>>>> Get end-to-end visibility with application monitoring from AppDynamics
>>>>>> Isolate bottlenecks and diagnose root cause in seconds.
>>>>>> Start your free trial of AppDynamics Pro today!
>>>>>>
>>>>>> http://pubads.g.doubleclick.net/gampad/clk?id=48808831&iu=/4140/ostg.clktrk
>>>>>> _______________________________________________
>>>>>> Postgres-xc-developers mailing list
>>>>>> Pos...@li...
>>>>>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> Mason Sharp
>>>>>
>>>>> StormDB - http://www.stormdb.com
>>>>> The Database Cloud
>>>>> Postgres-XC Support and Services
>>>>>
>>>>
>>>>
>>>
>>>
>>> --
>>> Mason Sharp
>>>
>>> StormDB - http://www.stormdb.com
>>> The Database Cloud
>>> Postgres-XC Support and Services
>>>
>>
>>
>
>
> --
> Mason Sharp
>
> StormDB - http://www.stormdb.com
> The Database Cloud
> Postgres-XC Support and Services
>