The full picture
| Local (document-partitioned) | Global (term-partitioned) | |
|---|---|---|
| Partitioned by | the document's partition key | the indexed term |
| Write cost | local, atomic with the row | cross-shard, usually async |
| Read cost | scatter-gather across all shards | single shard |
| Consistency | index matches the row | eventually consistent |
| Examples | Elasticsearch shards, Cassandra 2i | DynamoDB GSI, term-partitioned search |
- Scatter-gather latency is governed by the slowest shard, so p99 read latency degrades as you add shards — the counter-intuitive result that growing the cluster makes these queries worse. With 100 shards, a query is only as fast as the unluckiest of 100 GC pauses. This is the single most important consequence to state.
- DynamoDB GSIs are the concrete case to reason about: a GSI is a separate table maintained asynchronously, with its own partition key, its own throughput, and its own hot-partition risk. It can be behind the base table, and — the operational trap — if the GSI throttles, writes to the base table can be throttled too. Real constraints like this beat abstract comparisons.
- Cassandra's built-in secondary indexes are the classic anti-pattern: local indexes that scatter to every node, so they only perform when combined with the partition key (which largely defeats the purpose). The idiomatic answer there is a manually maintained inverted table — write the data twice, keyed differently. Denormalization is the index.
- The write-twice pattern generalizes far beyond Cassandra: maintaining your own query-shaped table (or a CQRS read model, or a search index fed by CDC) is how most large systems handle secondary access paths. The cost moves from query time to write time, plus the eventual-consistency window and the reconciliation job to detect drift.
- Bounded fan-out is the middle ground: if a query can be narrowed to a few shards (routing by tenant, or a coarse hash bucket), scatter-gather stays cheap. Elasticsearch's routing key does exactly this — same index design, dramatically better tail latency, at the cost of skew risk.