A local secondary index lives beside the data it indexes, so a write touches one shard and a lookup has to ask all N. A global secondary index is partitioned by the indexed attribute, so a lookup goes to one shard and a write has to touch two. The costs are r·N + w against r + 2w, they are equal at r/w = 1/(N − 1), and which of them is cheaper is a fact about the workload rather than about the designs.
Core ideas
Two designs, one algebraic comparison, and a crossover that depends on the fleet size and on nothing else.
The index entry for a row lives on the same shard as the row, so writing a row writes its index entry locally — one shard touched. But the index is then spread across every shard, so a lookup by the indexed attribute has no idea which shards hold matches and must ask all N. Cost per second: r·N + w.
The index is partitioned by the indexed attribute, so a lookup goes straight to the one shard holding that attribute's entries. The price is that a write now touches two shards — the row's own and the index's — and those are on different machines, which brings the consistency questions of the lesson after next. Cost per second: r + 2w.
Setting r·N + w = r + 2w gives r(N − 1) = w, so the two designs cost the same when r/w = 1/(N − 1). At two shards that is r/w = 1: any workload with more reads than writes wants the global index. At sixteen shards it is 1/15 = 0.0667, so a workload with one indexed read per fifteen writes already prefers the global index. The larger the fleet, the smaller the read share that tips it.
The material
What each design does on a read and on a write, the algebra that puts them in the same units, where they cross, and what the comparison leaves out.
A secondary index maps a non-key attribute to the rows carrying it. In a partitioned store it can be local — each shard indexes only its own rows — or global, partitioned by the indexed attribute so that all entries for one value live together. The primary key's own index is neither; it is the partitioning function.
The two designs are the same data structure placed differently, and the placement decides which operation pays. What makes the comparison tractable is that both costs can be measured in the same unit — shard operations a second, meaning the total number of per-shard requests the fleet performs — so a read that fans out to sixteen shards counts as sixteen.
Read the middle column down and the shape of the trade is obvious. At two shards the two designs meet at equal reads and writes, which is a genuinely balanced decision. By sixty-four shards they meet at one read per sixty-three writes, so any workload that reads the indexed attribute at all is already past the crossover. The local index is a design for small fleets and write-dominated workloads, and the arithmetic says so without any appeal to taste.
Take an audit log partitioned by record id, indexed on a status field, with w = 10 000 /s of writes and r = 200 /s of status lookups across N = 4 shards. The local index costs 200 × 4 + 10 000 = 10 800 ops/s and the global costs 200 + 20 000 = 20 200 — the local index is cheaper by nearly two to one. The ratio is r/w = 0.02, comfortably below the crossover of 1/3, and the same two formulas that chose the global index in the lab choose the local one here.
Three things, and all of them push the same way. A local index read is a scatter-gather, so it also carries the tail of “Scatter-Gather Cost” — N requests and a wait for the slowest — while a global index read is one routed request with one shard's latency. A global index write touches two shards, so unless it is done in a transaction it is briefly inconsistent with the row, and doing it in a transaction is the cross-shard commit of the next lesson. And the global index has its own hot-key problem: all entries for one attribute value live on one shard, so a heavily skewed attribute reproduces “Hot Keys and Salting” inside the index.
So r·N + w against r + 2w is the first cut and not the whole decision. It is worth doing first anyway, because it is arithmetic on two numbers you already have, and when it comes out at nine to one the qualitative arguments are unlikely to reverse it.
Interactive
r·N + w against r + 2w, and the crossover at r/w = 1/(N − 1)
Method
Two rates and a shard count. Compute the crossover before either cost — it is one division and it usually settles the question on its own.
r is reads that use the index, not all reads; a lookup by primary key is routed and is irrelevant here. w is writes that change the indexed attribute, which may be fewer than all writes.
One division each. If r/w is above the crossover the global index is cheaper and if it is below the local one is, and at N = 16 the crossover is 0.0667 against a workload ratio of 2.500 — not a close call.
r·N + w and r + 2w in shard operations a second. The winner's figure is what the fleet has to serve, and the ratio between them — 9.11× at the lab's numbers — is what the decision is worth.
The local index inherits the scatter-gather tail; the global index needs two shards written and can be hot on a skewed attribute. If any of those is decisive the arithmetic has not changed, but the answer might.
Both costs, then the crossover, then the same workload on a fleet small enough to change the answer.
The last block is the useful way to report this. “The global index wins” is a conclusion; “the global index wins unless indexed reads fall below 133 a second” is a conclusion with a margin attached, and it is the version that survives a workload changing after the decision is made.
For a faded rehearsal, take the same workload to a four-shard fleet. The supplied first move is the crossover, 1/(4 − 1) = 1/3. Compute both costs, say which wins and by how much, and then find the read rate at which the answer would flip. Say in one sentence why the margin is so much narrower here than at sixteen shards.
You should be able to write both costs in shard operations a second, derive the crossover 1/(N − 1) from them, report which design wins with the margin that would flip it, and name the three costs the ratio does not include.
Something to change about this lesson? Write it down, tick it off when it is handled, and export the list when you want to act on it. Kept in this browser only: nothing is sent anywhere, and nobody is notified.
No recommendations yet for this lesson.