Notespartitioning🌱 seedling

Local vs Global Secondary Index

Also: Local Secondary Index · Global Secondary Index · Document-Partitioned Index · Term-Partitioned Index · LSI · GSI · Scatter-Gather

In a sharded database a [[Secondary Index]] is either local (each shard indexes its own rows, so queries ask every shard) or global (the index is partitioned by the indexed value, so reads hit one partition but writes touch several).

How it works

  • Local (document-partitioned): the index lives beside the rows it covers and is updated in the same local transaction. A query by the indexed column is scatter-gather: send to all shards, merge.
  • Global (term-partitioned): entries are partitioned by the indexed value ("red" lives on one index partition). One row write may update the row's shard plus index partitions elsewhere, so updates are usually asynchronous.

Trade-offs

Local Global
read by indexed value every shard; as slow as the slowest one index partition
write one shard, transactional several partitions, usually async
freshness always consistent with the rows can lag the base table

In practice

  • DynamoDB GSI: its own partitions and capacity, eventually consistent reads only, can be added any time.
  • DynamoDB LSI: same partition key, different sort key; strong reads allowed, defined only at table creation, 10 GB cap per partition key value.
  • Elasticsearch, MongoDB, Cassandra secondary indexes are local: queries fan out.

Pitfalls

  • Scatter-gather on a big cluster: one slow shard sets the latency (Tail Latency Amplification).
  • Reading a global index right after a write and expecting to see it.

Recall

These cards are also in your review deck, scheduled with spaced repetition.