A used-cars database sharded by listing id, with secondary indexes on color and make. The read color=red touches 8 shards on a local (document-partitioned) index but 1 shard on a global (term-partitioned) index — then a single-car write touches 1 shard locally and 3 globally. You don't remove the cost; you choose where it lands.
A used-cars database is sharded by listing id (id % 8), with secondary indexes on color and make. You want color = red. Build the secondary index as a local (document-partitioned) index — each shard indexes only its own cars — and the read must scatter to all 8 shards and gather, because a red car can sit on any shard. Build it as a global (term-partitioned) index — the index is sharded by the value, so the term "red" lives on exactly one shard — and the same read touches 1 shard. It looks like the global index simply wins. Then you write one car: the local index touches 1 shard (the car's primary shard), the global index touches 3 (the primary shard, plus the color-term shard, plus the make-term shard). The cost didn't disappear — you moved it. Read-cheap/write-expensive vs write-cheap/read-expensive; you pick which.
Designing Data-Intensive Applications, 2nd ed. (Kleppmann & Riccomini, 2025), Chapter 7 — Sharding, §"Sharding and Secondary Indexes", §"Local Secondary Indexes (Partitioning Secondary Indexes by Document)", §"Global Secondary Indexes (Partitioning Secondary Indexes by Term)":
The advantage of a global (term-partitioned) index over a local (document-partitioned) index is that reads are more efficient: rather than doing scatter/gather over all shards, a client only needs to make a request to the shard containing the term that it wants. However, the downside of a global index is that writes are slower and more complicated, because a write to a single document may now affect multiple shards of the index.
This exercise makes both halves of that sentence into numbers you measure: the read fan-out (8 vs 1) and the write fan-out (1 vs 3), on the book's own used-cars example.
The result is a cost-conservation argument about where a query touches the cluster. These help; each line notes where it shows up.
color=red) to the list of record ids that have it (its postings list). The primary key is the listing id; color and make are secondary. It shows up as the color -> [ids] and make -> [ids] dicts both index types build.1 − 0.99^8 ≈ 7.7% of the time even though each shard is slow only 1%.int(md5(term).hexdigest(), 16) % 8, never Python's per-process-salted hash(). Fixed workload: 10,000 cars, ids 0..9999, colors/makes assigned with random.Random(42); 8 shards.
Same 10,000 cars, same two secondary indexes, same query color=red — only the index partitioning differs.
| LOCAL (document-partitioned) | GLOBAL (term-partitioned) | |
|---|---|---|
| How it's sharded | index co-located with the records: each shard indexes only its own cars | index sharded by the value: term "red" lives on md5("red") % 8 |
READ color=red | scatter/gather all 8 shards (matches are scattered) | 1 shard (the term owner) |
READ color=red AND make=Honda | scatter/gather all 8 shards | 2 shards (the two term owners), then intersect |
| WRITE one car | 1 shard (its primary shard; local index updates in place) | up to 3 shards (primary + color-term + make-term) |
| Good when | writes are hot, reads rare or always carry the partition key | reads are hot, especially single-value equality reads |
The record's primary shard is chosen by its id; a secondary value can appear on any shard. Those are two different partitionings of the same data, and no single index can make both the read and the write single-shard. The whole thing is one script, code/local_vs_global_index.py.
cd study/ddia/ch07
python3 code/local_vs_global_index.pyDo not read the output yet — make each prediction first.
Predict. The cars are sharded by listing id, and roughly a tenth of 10,000 cars are red. For color=red, how many shards must a local index query, and how many must a global index query? (There are 8 shards.)
8 vs 1 — same 980 red cars. Red cars land on every shard, so a local index has no way to know which shards have matches; it must ask all 8. The global index put every red car's id on shard 4, so one request answers the whole query.
Predict. Now insert one car (id=10000, color=red, make=Honda). Its primary shard is 10000 % 8 = 0. How many shards does the local index write touch? How many does the global index write touch?
1 vs 3 — the read cost came back as a write cost. The local index sits on the car's own shard, so one write updates the record and its index entries. The global index scatters this one car's facts across the cluster: its body on shard 0, its "red" onto shard 4, its "Honda" onto shard 2 — three shards for one insert.
Predict. Now color=red AND make=Honda. The global index owns "red" on one shard and "Honda" on another. How many shards does each index type query, and do they agree on the count?
8 vs 2 — still 119 matches either way. The local index has no partition key in the filter, so it's back to all 8 shards. The global index fetches just the two relevant term shards (4 and 2) and intersects the postings lists — a 2-shard read for a 2-term query.
Predict. Suppose each shard is slow 1% of the time, independently. A global single-term read waits on 1 shard. A local scatter read waits on all 8. What fraction of local reads are slow?
~7.7%, not 1%. A scatter read is only as fast as its slowest shard, so the probability of hitting a slow shard compounds with fan-out: 1 − 0.99^8 ≈ 0.077. The local index doesn't just do more work — it amplifies the tail. This is the Ch. 2 effect, arriving through the back door of the index design.
color=red: LOCAL 8 shards vs GLOBAL 1 shard; both return 980 red cars.color=red AND make=Honda: LOCAL 8 shards vs GLOBAL 2 shards ({2, 4}); both return 119.Start from the two partitionings. A record's primary shard is a function of its id (id % 8). A secondary value — color=red — is a property of the record's contents, and red records were assigned ids all over the place, so they land on every shard. These are two independent ways of slicing the same 10,000 rows, and that independence is the whole story.
A local index is organized the same way the records are: it lives on the record's own shard and indexes only that shard's cars. That makes writes cheap — inserting a car updates one shard's records and one shard's index, atomically, in one place (Step 2: 1 shard). But a read for color=red is asking a question the id-partitioning can't answer: "which shards hold red cars?" is unknowable without looking, because red is scattered by id. So the coordinator must scatter/gather across all 8 (Step 1). A global index is organized by the secondary value instead: it collects every red id onto md5("red") % 8, so the single-value read goes to exactly one shard (Step 1: 1 shard). But now one record's terms live apart — its "red" on shard 4, its "Honda" on shard 2, its body on shard 0 — so writing that one car must touch all three (Step 2: 3 shards).
This is a conservation law, not an implementation quirk. You cannot make both the read and the write single-shard, because "group by id" and "group by value" are different partitionings, and an index can only be physically laid out one way. Choosing local vs global is choosing which operation pays: co-locate the index with the records (cheap writes, scattered reads) or co-locate it with the values (cheap reads, scattered writes).
And the scattered read is worse than it looks. Step 4 is the Ch. 2 tail-latency point: an 8-way scatter completes only when its slowest shard replies, so if each shard is independently slow 1% of the time, the read is slow 1 − 0.99^8 ≈ 7.7% of the time. Fan-out multiplies your exposure to the tail; a 1-shard read never does.
color=red AND listing_id=8324 (or "red cars in this seller's shard", if you shard by seller) — and the coordinator knows exactly which shard to ask, so the local index also hits one shard with no scatter. That's why the right question isn't "local or global?" but "will my hot reads know their partition key?" If yes, local wins on both axes; if no (a global filter like color=red across the whole dataset), the global index buys back the read at the cost of the write. Global indexes are also usually updated asynchronously, so a just-written term may not be visible on its term shard immediately — the write fan-out is often eventually consistent, trading the latency for staleness.
color=red AND id=8324 query and confirm the local index drops from 8 shards to 1 — the boundary condition where local's fan-out vanishes.md5(make) % 8 equals red's shard (4) — e.g. try each make — so the global write touches only 2 shards and the AND-query only 1. Watch the write/read costs shift with the hashing.N_SHARDS to 32 and re-measure: the global read stays at 1 shard while the local scatter grows to 32 and its tail-slow probability climbs to 1 − 0.99^32 ≈ 27%. The gap widens with cluster size — which is exactly when global indexes start to pay off.Sources: DDIA 2e, Ch. 7, §"Sharding and Secondary Indexes", §"Local Secondary Indexes (Partitioning Secondary Indexes by Document)", §"Global Secondary Indexes (Partitioning Secondary Indexes by Term)" · Ch. 2, §"Describing Performance" (tail-latency amplification).