Skip to main content

Secondary Indexes: Local vs Global

What This Concept Is​

In a single-node database, a secondary index is a straightforward B-tree: one tree per indexed column, entries point to rows. Once data is partitioned, there are two fundamentally different ways to build that index:

  • Local (document-partitioned) index: each partition maintains its own secondary index covering only its own rows. Writes are fast (one partition, one index update). Reads that filter by the index must scatter-gather -- query every partition, merge results.
  • Global (term-partitioned) index: the index itself is partitioned by the indexed term. All rows where color = 'red' live (in the index) on one partition, regardless of where the row itself lives. Reads are fast (one partition). Writes are slow -- a single row insert must update every index partition whose term it touches, often via a distributed transaction or an async pipeline.
  Local (document-partitioned):

    Partition P0:       Partition P1:       Partition P2:
    rows: {...}         rows: {...}         rows: {...}
    local idx by color  local idx by color  local idx by color
    
    Query "color=red"? Ask P0, P1, P2; merge.

  Global (term-partitioned):

    Partition P0: rows {...}   Partition P1: rows {...}   Partition P2: rows {...}
    
    Index shard A [colors a..h]: "red" -> [row refs across all partitions]
    Index shard B [colors i..p]: ...
    Index shard C [colors q..z]: ...
    
    Query "color=red"? Ask index shard A; read matching rows.

Why It Matters Here​

This is the subtle "why doesn't SELECT * FROM users WHERE country='CA' perform the same in a sharded DB as it did on a single node?" conversation that surprises teams. The answer is: because the index is local, so that query fans out across all shards.

Choosing between local and global determines:

  • Read latency for index-filtered queries (local: slow; global: fast).
  • Write latency and cost (local: cheap; global: cross-partition).
  • Consistency of the index (local: synchronous with the row; global: often async, so stale).

Concrete Example​

A MongoDB collection sharded on user_id, with a secondary index on email.

  • Mongo secondary indexes are local. Each shard has its own email index over its own documents.
  • db.users.find({email: 'alice@ex.com'}): the mongos router does not know which shard holds the email; it fans out to all shards, waits for all responses, and returns the merged result.
  • db.users.find({user_id: '123', email: ...}): shard key is present; routed to one shard only; fast.

Elasticsearch, by contrast, does term-partitioned (global) routing for its lucene indexes when using routing hints, which is why ES often performs faster than Mongo on ad-hoc full-text queries over a sharded dataset.

DynamoDB's Global Secondary Indexes (GSIs) are literally term-partitioned: they are maintained asynchronously, eventually consistent, and allow queries without knowing the partition key of the base table -- at the cost of up-to-seconds-stale reads and additional write throughput cost.

Common Confusion / Misconception​

"Every database's secondary indexes work the same way." They don't. MySQL/InnoDB (sharded via Vitess), Mongo, Cassandra, and Elasticsearch all ship secondary indexes with different partitioning and consistency semantics. Reading the docs for the specific system you use is required.

"Global indexes are strictly better." They buy you read speed and pay with write complexity plus (usually) weaker consistency. If your index lookups are rare, local is cheaper.

"Global indexes are always eventually consistent." Only when implemented asynchronously. Spanner's secondary indexes are globally consistent because Spanner serializes every write with TrueTime -- but that costs latency.

How To Use It​

When designing a partitioned schema:

  1. Enumerate the query patterns. Which filter by the partition key? Those are cheap.
  2. Which filter by a non-partition key? Those require a secondary index.
  3. For each secondary index, decide: local (slow reads, fast writes) or global (fast reads, complex writes, usually async)?
  4. If the query fires often and the partition count is large, pay for a global index. If the query is rare or the partition count is small, a local index's scatter-gather is acceptable.
  5. Document the consistency of the index: "eventually consistent up to 5 s typical" is an honest API statement.

Check Yourself​

  1. Why does a local secondary index require scatter-gather on a non-partition-key query?
  2. Why are global secondary indexes often eventually consistent?
  3. What happens on a write to a single row when the row touches three global index partitions?
  4. When is a local index the right choice even though scatter-gather is slower?

Mini Drill or Application​

For each query pattern, pick local or global and justify:

  1. SELECT * FROM users WHERE user_id = ? (partition key is user_id).
  2. SELECT * FROM users WHERE email = ? (partition key is user_id, email is unique).
  3. SELECT COUNT(*) FROM orders WHERE status = 'pending' (partition by customer_id).
  4. A search box that finds any product by name across all tenants.
  5. SELECT * FROM events WHERE tag = ? in a multi-tenant log store partitioned by tenant, with a small number of distinct tags.

Read This Only If Stuck​