Skip to main content
PreparationZone Logo
PrepZone

Secondary Indexes at Scale

Global vs local indexes, write amplification, and query fan-out in distributed stores.

Pass checkpoint below

Why this matters

  • VaultCommerce's DynamoDB tables are keyed by user_id, but support agents search orders by tracking_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.
Vertical
usersid, name, email
user_profilesbio, avatar
Horizontal
Shard Auser_id 0–999
Shard Buser_id 1000–1999
Vertical splits tables by column group. Horizontal splits rows across nodes by key.

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.
Java
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:

Java
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 typeRead costWrite costVaultCommerce example
LSISingle partitionIncluded in base writeOrders by date within user
GSISingle GSI partitionExtra write per indexLookup by tracking number
Scatter-gather localAll partitions queriedLowAnti-pattern — avoid
ElasticsearchInverted index lookupIndex on every doc changeProduct search
  • LSI

    Read costSingle partition
    Write costIncluded in base write
    VaultCommerce exampleOrders by date within user
  • GSI

    Read costSingle GSI partition
    Write costExtra write per index
    VaultCommerce exampleLookup by tracking number
  • Scatter-gather local

    Read costAll partitions queried
    Write costLow
    VaultCommerce exampleAnti-pattern — avoid
  • Elasticsearch

    Read costInverted index lookup
    Write costIndex on every doc change
    VaultCommerce 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.