Are queries against Azure Table Storage indexed when using a partial RowKey?

Question

I understand from the MS PDC presentations that the PartitionKey is used to load balance the table across multiple servers, but nobody seems to give any advice on whether the PartitionKey is used as an index WITHIN a single server.

Likewise, everyone will tell you that specifying the PartitionKey AND the RowKey gets you great performance, but nobody seems to tell you if the RowKey is being used to improve performance WITHIN a PartitionKey.

Here are some sample queries to help me frame the questions. Assume the entire table contains 100,000,000 rows.

1. PartionKey="123" and OtherField="def"
2. PartitionKey="123" and RowKey >= "aaa" and RowKey < "aac"

Here are my questions:

• If I have only 10 rows in each Partition, would Query 1 be fast?
• If I have 1,000,000 rows in each Partition, would Query 2 be fast?

Answer 1

In ATS, PartitionKey is used as a distribution lookup, not an index. From the level of working with ATS, just consider PartitionKey and "server"/node to share a 1:1 relationship. (Behind the scenes this isn't true, but concepts such as optimizing PartitionKeys that happen to reside on the same physical/virtual node are abstracted several levels from what a consumer of Azure has to deal with. Those details are purely internal to the overall Azure infrastructure and in the case of ATS, its best to assume that is an optimal as it can be ... aka "dont worry about it")

In the context of a DBMS vs ATS, RowKey is the closest thing to an "index" in that it assists in finding data across a similar node. To directly answer one of your question, RowKey is the index within the PartitionKey.

Stepping outside the box a bit, however, PartitionKey can give you perf gains closer to how you think of a traditional index, but only because of the distributed nature of how your data is spread across ATS nodes. You should optimize layout 1st to the PartitionKey, then to the RowKey. (aka, if you only have one keyable value, make it the PartKey)

In general rule, queries are going to perform in this order, from most efficient to least efficient

1. PartitionKey=x and RowKey=y (and OtherProp = z)

because the lookup gets to the right node and then to an indexed prop on the partition

2. PartitionKey=x (and OtherProp =z)

because you get to the proper node, but then to the ATS equvi. of a full table scan

3. OtherProp = z

because you have to a partition scan, then a table scan

---

With that, to your direct questions

1. I don't feel this can be answered. Its subjective (ie "what is fast?"). It will always be slower than Query2, but with 10 rows that "slowness" is likely milliseconds if even

2. (similar theme) It will be faster than Query 1. Anytime you can do Query2, you should

So with that explaination and your questions, the real answer comes down to how your architect your usage of ATS.

Based on your data set (both current and expected growth) you need to determine a proper scheme so that you can get to your Partition AND to your Row is the fastest way possible. Knowing how the lookup occurs, you can make logical decisions as to what path is going to get you there fast enough, more parts, less rows -vs- less parts, more rows, etc

Answer 2

For #1, it's however fast scanning ten entities is.

For #2, it depends on how many entities there are in that RowKey range. (Specifying the partition key and a range for the row key means we'll do an indexed query over just the entities within that range.) You didn't say how many there are, but if, as an example, there are ten, then it should be the same performance as #1.

Answer 3

Tables are indexed by (PartitionKey, RowKey). Rows with the same partition key are guaranteed to be served from the same partition. Rows with different PartitionKey may or may not be on the same partition. So I don't know how you would know that you have only 10 rows in a partition.

If you have only 10 rows with PartitionKey="123" then the first query will be "fast". The second query will be "fast".

Answer 4

Both should be relatively fast.

Query 1 would have to do a full scan within a single partition (a Range scan in ATS lingo), but that would mean iterating through 10 entities.

Query 2 will also result in a range scan, but using the RowKey as an index within the partition, so it should still be fast.

You can get a VERY detailed blog post with all the performance implications of each of the queries, and how to define an optimum key: http://blogs.msdn.com/b/windowsazurestorage/archive/2010/11/06/how-to-get-most-out-of-windows-azure-tables.aspx

Answer 5

In addition to Taylor's answer, analogous statements also hold for range queries, as discussed here.

In other words, Azure Table Storage can indeed be thought of as just having one index consisting of two parts, the partition key and the range key, in that order.

Answer 6

I think some things might have changed since the WAS paper was written but if you read that, you can draw some conclusions.

For example, a partition can be moved between nodes/physical servers. If you have many partitions that can scale better than a single partition. If you have 1 huge partition you will be limited by the throughput of a single partition.

Note that many small partitions (consecutive in range) can be moved to a single node/physical server. It doesn't have to be slower to scan across partitions if the partitions are logically grouped close together (i.e. sorted).

If you need to partition key to handle more than the 2000 req/sec that is offered you have to figure out a way to split your partition key into multiple partitions, otherwise, it doesn't matter.

Oh, and you can only do entity groups transactions within a single partition key, that might impact your design.

So to recap:

• Do you need more than 2000 req/sec?
• Do you need entity group transactions?

Those are the two questions you need to ask yourself.