Choosing the right Partition Key

• The partition key portion of a table's primary key determines the logical partitions in which a table's data is stored.

• Provisioned I/O capacity for the table is divided evenly among these physical partitions.

  • So a partition key design that doesn't distribute I/O requests evenly can create "hot" partitions that result in throttling and use your provisioned I/O capacity inefficiently.

Best Practices

• Use high-cardinality attributes.

  • These are attributes that have distinct values for each item, like e-mailid, employee_no, customerid, sessionid, orderid, and so on.

• Use composite attributes.

  • Try to combine more than one attribute to form a unique key, if that meets your access pattern. For example, consider an orders table with customerid+productid+countrycode as the partition key and order_date as the sort key.

• Cache the popular items when there is a high volume of read traffic using DAX.

  • The cache acts as a low-pass filter, preventing reads of unusually popular items from swamping partitions.

• Add random numbers or digits from a predetermined range for write-heavy use cases.

  • Suppose that you expect a large volume of writes for a partition key (for example, greater than 1000 1 K writes per second).

    • In this case, use an additional prefix or suffix (a fixed number from predetermined range, say 1–10) and add it to the partition key.