Why this matters
- VaultCommerce's DynamoDB tables are keyed by
user_id, but support agents search orders bytracking_number— that requires a global secondary index designed upfront. - Local indexes that fan out to every shard turn "simple" lookups into cluster-wide scatter-gather.
- Interviews test GSI vs LSI trade-offs and why you cannot add arbitrary indexes after launch without cost.
Local vs global secondary indexes
Index types in distributed stores
- Local secondary index (LSI) — alternate sort key within the same partition; no extra routing.
- Global secondary index (GSI) — different partition key; spans the cluster like a shadow table.
- Materialised view — precomputed query result maintained asynchronously (Cassandra, Postgres).
- Inverted index — Elasticsearch's core structure for full-text lookup.
Base table partition key: USER#42
├── ORDER#2026-001 (sort key)
└── ORDER#2026-002
GSI partition key: TRACKING#1Z999
└── points to ORDER#2026-001 on USER#42
VaultCommerce's tracking lookup hits the GSI partition TRACKING#1Z999 directly — O(1) routing. Without it, scanning all user partitions is impossible at scale.
Write amplification
Every GSI row duplicates write traffic. Updating an order status writes the base table and each GSI projection:
order_update_cost:
base_table: 1 WCU
gsi_tracking: 1 WCU
gsi_status_by_date: 1 WCU
total: 3 WCU per status change
Design GSIs only for proven access patterns. VaultCommerce limits each table to three GSIs — each added only after query metrics justify the write tax.
| Index type | Read cost | Write cost | VaultCommerce example |
|---|---|---|---|
| LSI | Single partition | Included in base write | Orders by date within user |
| GSI | Single GSI partition | Extra write per index | Lookup by tracking number |
| Scatter-gather local | All partitions queried | Low | Anti-pattern — avoid |
| Elasticsearch | Inverted index lookup | Index on every doc change | Product search |
LSI
Read costSingle partitionWrite costIncluded in base writeVaultCommerce exampleOrders by date within userGSI
Read costSingle GSI partitionWrite costExtra write per indexVaultCommerce exampleLookup by tracking numberScatter-gather local
Read costAll partitions queriedWrite costLowVaultCommerce exampleAnti-pattern — avoidElasticsearch
Read costInverted index lookupWrite costIndex on every doc changeVaultCommerce exampleProduct search
The rest of this guide is part of Pro
Free covers every fresher guide in full. Pro unlocks Senior, Staff and Architect guides across every track.
Still to come · 3 sections
- Query fan-out and hot partitions
- Postgres secondary indexes at scale
- Index lifecycle discipline
Test yourself
Answer these before moving on — recall is what makes it stick.