Re: [Postgres-xc-general] the cluster cost for normalized tables

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

On Thu, Apr 19, 2012 at 9:05 PM, Ashutosh Bapat <
ash...@en...> wrote:

> HI Michael,
> The distribution of data depends upon the distribution strategy used. In
> Postgres-XC, we distribute data based on the hash/modulo of the given
> column. It's usually advisable to choose the same distribution for the
> tables which have equi-joins on their distribution columns.
>
Some addition on that. in XC it is possible to choose the distribution
strategy at table creation by extending the DDL CREATE TABLE. Now XC
supports replication, round robin and hash on a key (useful for a primary
key).
Distribution is tuple-based, meaning that for a given strategy all the
entire tuple is distributed, or replicated.

As a global guideline, we generally recommend to replicate the tables that
are not much updated but used a lot in joins like an item table in an
amazon-type application, and distribute tables that are heavily updates,
let's say a user table.


> Choosing the right distribution for the tables involved is an art. We need
> the knowledge of table definitions and set of queries to decide the exact
> distribution. If the queries are such that they join on collocated data,
> the performance is greatly improved.
>
True. Design phase is the key of performance.


>
> On Thu, Apr 19, 2012 at 4:56 PM, Michael Vitale <mic...@ar...> wrote:
>
>>  Hi you most honorable cluster folks!
>>
>> Our company is moving from Oracle to PostgreSQL.  We initially thought we
>> would be moving to MySQL Cluster, but an investigation of how clustering
>> works in MySQL Cluster revealed that performance would suffer substantially
>> since it is predicated on keys that segregate SQL-requested data to
>> specific nodes and not to all or most of the nodes.  A highly normalized
>> database would suffer in this situation where a result set would normally
>> consist of rows gathered from most, if not all, of the back-end nodes.
>>
>> Do you all have the same problem with Clustered PostgreSQL (Postgres-XC)?
>>
>> Respectfully Yours,
>>
>> Michael Vitale
>> ARIN DBA
>> mic...@ar...
>> 703-227-9885
>>
>>
>>
>> ------------------------------------------------------------------------------
>> For Developers, A Lot Can Happen In A Second.
>> Boundary is the first to Know...and Tell You.
>> Monitor Your Applications in Ultra-Fine Resolution. Try it FREE!
>> http://p.sf.net/sfu/Boundary-d2dvs2
>> _______________________________________________
>> Postgres-xc-general mailing list
>> Pos...@li...
>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-general
>>
>>
>
>
> --
> Best Wishes,
> Ashutosh Bapat
> EntepriseDB Corporation
> The Enterprise Postgres Company
>
>
>
> ------------------------------------------------------------------------------
> For Developers, A Lot Can Happen In A Second.
> Boundary is the first to Know...and Tell You.
> Monitor Your Applications in Ultra-Fine Resolution. Try it FREE!
> http://p.sf.net/sfu/Boundary-d2dvs2
> _______________________________________________
> Postgres-xc-general mailing list
> Pos...@li...
> https://lists.sourceforge.net/lists/listinfo/postgres-xc-general
>
>


-- 
Michael Paquier
http://michael.otacoo.com