Selecting from many partition keys in Azure Table Storage

Question

In Azure Table Storage, I store news updates and the partition key is the keyword assigned to a news category e.g. "politics", "sports", etc.

When the user logs in, I want to be able to select records based on user's interests -- which are persisted in another database. So the user may be interested in "politics" and "sports". Clearly, we may have a large number of categories that the user is interested. It could be 20+ categories.

How can I query my table so that I can get any news updates in those categories? Specifically, the issue is the limit on number of conditions I can specify in a SELECT statement against Azure Table Storage. I don't think I can specify more than 15 conditions e.g. partitionKey = x or partitionKey = y, etc.

Typically, in a NoSQL database, the solution is to de-normalize but in this case, this would be a terrible alternative. Say I use user's Id as my partition key. If I have 1 million users, it would make no sense to create 1 million copies of the same record using different partition keys so that each user can easily get their own updates.

How do I handle this scenario using Azure Table Storage?

Avunculate

Answer 1

The recommended way to handle this problem is to create a separate query for each range of interest. That's going to give you the best performance as well, as each query can avoid crossing a partition boundary. If you were to write the query so that it filtered on multiple partition keys, your performance would be compromised, as you would be crossing multiple partition boundaries.

See https://azure.microsoft.com/en-us/documentation/articles/storage-table-design-guide/#design-for-querying. It doesn't discuss your scenario, but gives guidelines for optimizing queries.

Also see https://azure.microsoft.com/en-us/documentation/articles/storage-scalability-targets/#partitions-in-azure-storage for a bit more information about partitions for Azure Tables.

Paraldehyde