A fact table typically contains a composite primary key made up of the keys to all the dimensions that define its granularity. if we distribute the table by this key, it cannot possibly accomplish a local join to any of the dimensions to which it is bound because those tables are distributed by their own PKs, which won't match the hash generated for the fact.

I'm guessing that if dimensions are small it won't matter much, but if the dimension is large e.g. a security master table with millions of CUSIPs and other identifiers, how can local joins be accomplished?

what is the standard approach to selecting a distribution key for facts?

3 Answers 3


Your distribution approach by the composite primary key will create a distribution with low or no skew. That's great but, as you point out, it comes with some costs in other areas. In practice even distribution is often a secondary concern. Distribution tends to be by the largest commonly used join or the most common aggregation. In the case you describe this sounds to be the security descriptor dimension.

Data distribution in a MPP database aims to optimise two metrics: minimising the movement of data and making the use of the all the available hardware performance in the cluster. Minimising data motion is achieved by co-locating data for large join and aggregate operations. Using all the hardware to get the best performance for a query requires that the data is evenly distributed and that typical queries will not be working on data on a single node. So when picking a distribution key to minimise data motion distribution it's not only important to minimise skew but also to avoid distributing by a field which is common used in query predicates.

  • so in a typical (dimensional) DW, facts are large tables and dimensions relatively small. however, facts are always joined to one or more dimensions, so it sounds to me that data movement is unavoidable but I'm guessing the optimizer is smart enough to move the smaller datasets?
    – ekkis
    Commented Apr 21, 2016 at 17:57

If the security dimension is the largest, then distribute the fact table by the security dimension key. Since it is large, it should have sufficiently entropy on its own to prevent skew.

On the other hand, one circumstance when you would not want to do this is if you are frequently selecting with the security dimension in the WHERE clause, and retrieving or processing a large number of facts. This would load all of the processing onto one node. That would lead you to either another dimension key if there is one that is sufficiently large, a composite key with another dimension, or just use a random distribution.

On the gripping hand, if you are constantly doing a large number of such queries, then skewing the workload over the nodes might not be an issue as they are all busy doing the local join on their own query - using a distribution key as a form of load balancing. I've seen this suggested, but I don't much like it myself.

  • and if I have two or more large dimensions then I'm out of luck? sounds like I have to pick one over the other
    – ekkis
    Commented Apr 21, 2016 at 17:58
  • Yes. You can only distribute by one hash function, and if that matches a join key, then the nodes will be able to do a local join on that key. Otherwise they will have to do a redistribute (or broadcast) motion. Statistics are important for this decision to be a good one. Single-node databases only use statistics if there are indexes. MPP databases use statistics even when there are no indexes, to decide when to broadcast and when to redistribute.
    – PhilHibbs
    Commented Apr 23, 2016 at 1:15
  • Another thought just occurred to me. If query performance is more important to you than load performance and space, you could simply replicate the entire table (or a column subset) with a different distribution key, and use whichever is most appropriately distributed. In a data vault model, you could have several link and/or satellite tables with different distributions, and live with the cost of redistributing or broadcasting the hub.
    – PhilHibbs
    Commented Mar 25, 2017 at 20:51
  • yes, that was my conclusion. I can't say that I'm exactly impressed with the structure. think permutations
    – ekkis
    Commented Mar 26, 2017 at 21:48
  • 1
    Postgres-XL has "distributed by replication" so every node gets a copy of the data, but I don't think that is a good idea for large tables! The inescapable fact is that you can only distribute the data once, unless you replicate it, and if you are going to replicate it then you should probably make some very careful choices about which columns you replicate by what key, so doing it yourself is the only sensible option.
    – PhilHibbs
    Commented Mar 28, 2017 at 19:07

there are many ways to decide distribution is such cases where you want to remove motion of tuples among the segments,

Assuming a table Fact having various column(including a natural key, surrogate key and surrogate key or natural key from various DIMs) Fact Table - > fct_sk, dim1_sk, dim2_sk, dim3_sk

We also have 3 DIMs having their own surrogate key populated dim1_sk, dim2_sk, dim3_sk, these surrogate keys are alos referenced in fact table described above

Scenario (1)

Surrogate key in all tables will be used as distribution key

In above scenario data from all the tables will be properly distributed among segments but in case of joins lot of motions will be going on

Scenario (2)

data for DIM tables should be distribute on their surrogate key(DIM1 on dim1_sk and DIM2 on dim2_sk and DIM3 on dim3_sk) and Fact table should be distribute on composite distribution key of all DIM's SK(dim1_sk, dim2_sk,dim3_sk)

In above scenario data may be skewed but during JOIN tuples will not need to move from one seg instance to other, local join will be achived through these method

Scenario (3)

Bigger DIM can be distribute on the basis of their sk and fact will also distribute on the same so for these join, local join operation will be performed and other DIMs will be broadcasted if it is smaller in size


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.