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

Secondary Indexes in Partitioned Systems

The complexity of maintaining indexes when data is partitioned: local vs global secondary indexes.

Secondary Indexes in Partitioned Systems

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

When you partition data by one key (say, user_id), querying by a different attribute (like email or country) becomes challenging. You need secondary indexes—but where do they live?

The Two Approaches

Local Secondary Indexes (Document-Partitioned)

Each partition maintains its own secondary index covering only the data it holds. To find all users with email="alice@example.com", you must query every partition (scatter-gather). This is called a fan-out query.

Analogy: Like asking every library branch to search its own card catalog for books by a specific author—you get complete results, but slowly.

  • ✅ Writes are fast and isolated (update only one partition)
  • ❌ Reads touching the index are slow (query all partitions)

Global Secondary Indexes (Term-Partitioned)

The secondary index itself is partitioned across nodes, separate from the primary data. All entries for email="alice@example.com" live on one partition of the index.

Analogy: Like a central library catalog that's split alphabetically by author name—fast lookups, but cataloging a new book might update a different location than where the book is shelved.

  • ✅ Reads are fast (query only relevant index partitions)
  • ❌ Writes are slower (must update both data partition AND remote index partition)
  • ❌ Risk of inconsistency if index updates lag behind data writes

Choosing Your Poison

Local indexes favor write throughput and simplicity. Global indexes favor read efficiency on secondary attributes but require distributed transactions or eventual consistency.

Key Takeaway: Secondary indexes on partitioned data force a choice—either scatter-gather reads with local indexes, or cross-partition writes with global indexes. Neither is free.