Partition keys in depth

The partition key is the decision on the container that is hardest to change later. K3 named the limits (20 GB and 10,000 RU/s per logical partition) and broke an office document into items per appointment, partitioned by office and month. This lesson asks deeper: What do you choose by, what follows from the choice, and when does it tip? At the end there is a calculation for three offices.

Prior knowledge. The limits, the repair by office and month and the overview of hierarchical keys are in "K3, lesson 3.6.4" and are not repeated. The item shapes come from lesson 1.3, the maximums per container from lesson 1.1 (F-05: appointments 2,000 RU/s).

What was run. The calculation program was compiled with .NET 10 and ran without an account. Not run against Azure is everything that concerns queries or containers of the service. All numbers on offices, cost per write (6 RU), the index (factor 2) and the size of the activities (300 bytes) are assumptions of the task, not measurements.

What a good key has

For all containers, Learn names five properties: The value does not change (it is immutable; "moving" an item means creating it anew and deleting the old one, not in one transaction across two partitions), it is a string (or a number that fits in a double), it has many distinct values, it distributes RU and storage evenly, and it is at most 2,048 bytes long (101 bytes as long as large keys are not enabled). For containers with many queries there is one more: The key appears in the filters of the frequent queries. A query with the key goes to one partition; without it, to all, and each physical partition costs an additional 2 to 3 RU according to Learn.

Important for the size of the worry: Small containers have only one or two physical partitions, and the cost of cross-partition queries there is barely noticeable. Learn names more than 30,000 provisioned RU/s or more than 100 GB as the point from which you should avoid them.

Hot partitions and the distribution of writes

Provisioned throughput is distributed evenly across the physical partitions (each at most 10,000 RU/s and 50 GB; when exceeded, the service splits them). A logical partition always lies entirely in one. If one key value gets a disproportionate number of requests, it is "hot": It is throttled with 429 although the container as a whole has headroom. The error is easily overlooked because keys with seemingly many values (userId) can in practice be hit by only a few values.

Hierarchical keys

Up to three levels (SDK .NET from 3.33.0). Queries with the first level or a prefix go only to the partitions holding that data. For appointments (key /officeId, /month), applied to Learn's table:

Learn names conditions: Every level needs high cardinality; for write-heavy loads Learn advises at least thousands of values in the first level. If it has few values, writes stay on one physical partition until the first splits (at 50 GB). Hierarchical keys exist only at container creation, they cannot be introduced afterward; the way there is a new container and copying (container copy jobs or the change feed). Permissions of the "Users and permissions" feature can be granted only on the complete key, not on a prefix.

Synthetic, by contrast, means: a property of its own, composed from several.

Synthetic keys

Learn names three techniques: concatenating values (deviceId and date into abc-123-2018), a random suffix (date plus a number from 1 to 400: well-distributed writes, but an item cannot be read in a targeted way) and a computed suffix (hash of another property: evenly distributed and still recomputable). The stumbling block is in the hash: string.GetHashCode() returns different values per process, so a suffix made with it would be different at the next start. A stable hash (here FNV-1a over the UTF-8 bytes):

public static int Bucket(string id, int buckets)
{
    var hash = 2166136261u;
    foreach (var b in Encoding.UTF8.GetBytes(id))
    {
        hash = (hash ^ b) * 16777619u;
    }
    return (int)(hash % (uint)buckets);
}
public static string For(string officeId, string id, int buckets) => $"{officeId}.{Bucket(id, buckets)}";

7360 ids in 10 buckets: smallest 719, largest 747
key of t-00001: buero-nord.1; again: buero-nord.1

The price: The appointments of an office now lie in ten partitions, and "all appointments of the office" is a query across ten. Synthetic pays off when the first level has no high cardinality and a single partition cannot carry the write load.

Anti-patterns

Learn names three:

1. /id for everything. Perfect distribution and cheap point reads, but every query by other properties goes to all partitions. Right when there are almost only point reads.
2. Few values (status, type, country). Uneven and hot. Defensible only for small volumes.
3. Many values that no query names (a random GUID). Writes spread out, every read goes everywhere.

If a single key is missing for several independent queries, Learn names global secondary indexes (additional containers with a different key, fed from the source container and, according to Learn, only eventually consistent; the course does not go deeper into them) as the way, instead of bending the key.

Estimating cost: three offices

The calculation has four parts: size of an item (measured), volumes from F-02, assumptions (6 RU per write, index factor 2, three writes per appointment, 2,000 RU/s maximum) and the question of where it tips.

one appointment item: 221 bytes

office        per year  storage after 5 years   largest month   re-sync
buero-nord       7360         15.51 MB             0.259 MB     44160 RU =  22.1 s
buero-sued       1800          3.79 MB             0.063 MB     10800 RU =   5.4 s
buero-mitte       400          0.84 MB             0.014 MB      2400 RU =   1.2 s

The calculation is: storage = appointments per year × 5 years × 221 bytes × 2 (index assumption), for example for buero-nord 7,360 × 5 × 221 × 2 = 16,265,600 bytes, about 15.5 MB. A re-sync of one year costs 7,360 × 6 = 44,160 RU, which at 2,000 RU/s takes 22.1 seconds. Further:

share of the current writes that hit the hottest logical partition
  /officeId:                  77 % (buero-nord)
  /officeId + /month:         77 % (current month of buero-nord)
  /id:                        0.0105 % (one item per partition)

one logical partition stops at 10000 RU/s = 1667 writes/s of 6 RU;
buero-nord writes on a working day: 96 = 576 RU in a day
20 GB would hold 48,585,603 such items

The shares are calculated, not measured: 7,360 of 9,560 appointments a year belong to buero-nord, and all current writes of an office hit the current month, so the month distributes over time, not over the offices. For the activities (events, key /officeId, /day, 90 days of expiry, F-03) the calculation gave:

events    in the 90-day window   per working day (largest partition)
buero-nord      5444 events     96.0 events =  56.3 KB
buero-sued      1332 events     23.5 events =  13.8 KB
buero-mitte      296 events      5.2 events =   3.1 KB

What follows

• With the numbers from F-02, nothing tips. Even /officeId alone would stay at 16 MB for five years, the limit of 20 GB would be reached only at almost 49 million items, and 10,000 RU/s per partition at 1,667 writes per second. The key /officeId, /month from F-03 stays, because Z1 from lesson 1.3 ("calendar of a month") then reads one partition and because it provides for a larger office. Here it is provision, not necessity; knowing to say so is part of the justification.
• The risk is the first level. Three offices are three values, far below the "thousands" that Learn names for write-heavy loads. At these volumes it has no effect. If the number of offices grows into the thousands, the situation improves by itself; if a single office grows a hundredfold, the second level helps.
• A re-sync can be planned. 22 seconds at the full maximum show: A nightly refresh of all three offices (together just under 29 seconds) stays within the frame of F-05; starting it as a burst uses up the maximum and lets concurrent readers see 429.

What you can do now

• You evaluate a key by immutability, cardinality, distribution and queries.
• You use hierarchical and synthetic keys and know when which fits.
• You recognize the three anti-patterns and know global secondary indexes as a way out.
• You calculate storage, re-sync duration and tipping points for three offices with traceable assumptions.
• You know that a key is changed only through a new container.

Further reading

• Partitioning and horizontal scaling: criteria, anti-patterns, key types compared.
• Hierarchical partition keys: conditions, routing of queries, migration.
• Create a synthetic partition key: concatenating, random and computed suffix.
• Service quotas and default limits: 20 GB, 10,000 RU/s, length of key values.
• Diagnose request rate too large (429): recognizing hot partitions.