1

Specific example of what I'm asking:

Say that a SaaS app has an Order table with CustomerID and a child OrderItem table.

We want to partition both tables and we want to use CustomerID as the partition key.

The issue is that the OrderItem table does not have a CustomerID column (its only foreign key is to the Order table).

Would it be a best-practice to denormalize the OrderItem table and add a CustomerID column to it for the sake of partitioning?

Improve this question
2
  • 3
    You can not Partition by something that is not included in the table. So yes if you wish to partition by CustomerID it will need to be in the table. Whether this is a best practice or not depends a ton on your table structure and whether or not you truly need partitioning.
    – Zane
    Commented May 20, 2014 at 15:31
  • 3
    Agreed with @Zane. Usually people partition by things that are more predictable in terms of growth and distribution, such as OrderDate. Sure orders by day or month will fluctuate, but partitioning by CustomerID is like partitioning by first letter of last name - your Q and X partitions are going to be pretty quiet, while your J and S partitions will be large and busy. Also, usually, partitioning allows you to do things like archive data (say, orders over 6 months old move to some near- or off-line resource). What maintenance advantage does partitioning by CustomerID buy you?
    – Aaron Bertrand
    Commented May 20, 2014 at 15:33

1 Answer 1

Reset to default
3

Yes, if you want to partition by CustomerID, then CustomerID must exist in the table (even if it is redundant).

But first, IMHO, you need to ask yourself a couple of questions:

  1. Do I need partitioning? What am I using it for? What problem am I solving?
  2. If the answer to 1. is yes, then am I partitioning on the right column? How - specifically - will partitioning on CustomerID help in my environment?

I find that a lot of people turn to partitioning as a knee-jerk reaction to a performance problem of some kind that partitioning simply can't (completely) solve.

Similar to indexed views - people create views on top of views on top of views, then assume that all they need to do is create an index on each view and that will speed everything up. Then they actually learn the hard way about what indexed views are, how they work, and the much more limited set of scenarios that comprise their "sweet spot."

Improve this answer
1
  • Thanks Aaron. We are looking at partitioning to help with lock contention / deadlocks. We have a SaaS (multi-tenant) app; so partitioning by customer feels like the natural approach (more or less isolating each customer into their own filegroup, where one customer's locks won't affect another customer).
    – Dave Clausen
    Commented May 21, 2014 at 20:33

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.