Course contentsShow
← System Design
Lesson 1455 of 1,91936. Data Partitioning Strategies in DepthFree lesson

Secondary Indexes and Partitioning

Challenges of maintaining secondary indexes across partitions: local indexes per partition vs global distributed indexes.

Secondary Indexes and Partitioning

What you'll learn: How maintaining secondary indexes becomes complex when data is partitioned, and the tradeoffs between local and global index approaches.

The Challenge

When you partition data by a primary key (like user_id), queries on that key are fast—you know exactly which partition to check. But what if you need to query by a different field, like email or created_date? That's where secondary indexes come in, and they create a dilemma in partitioned systems.

Two Approaches

Local Indexes Per Partition (Document-Partitioned)

Each partition maintains its own secondary index covering only the documents it stores. To query by email, you must scatter the query to all partitions and gather results. This is called "scatter-gather."

Analogy: Imagine a library with multiple branches. Each branch catalogs only its own books by author. To find all books by "Smith," you must call every branch.

  • Pros: Writes are simple—index updates stay local to one partition
  • Cons: Reads scanning secondary indexes touch all partitions (high latency, amplified load)

Global Distributed Indexes (Term-Partitioned)

A single secondary index spans all partitions, but the index itself is partitioned separately (often by hash of the indexed field). Queries hit only relevant index partitions.

Continuing the analogy: One centralized author catalog covering all branches, but split alphabetically across multiple catalog servers.

  • Pros: Efficient reads—query only necessary index partitions
  • Cons: Writes become distributed transactions (updating data partition and remote index partitions), adding complexity and latency

When to Use What

Local indexes suit write-heavy workloads where scatter-gather reads are acceptable. Global indexes optimize read performance when secondary-key queries are frequent and latency-critical.

Key Takeaway: Secondary indexes in partitioned systems force a choice: accept slow scatter-gather reads with local indexes, or embrace complex distributed writes with global indexes. Your workload pattern determines the right tradeoff.