What Are Document Partitioned (Local) Secondary Indexes?

Definition
Secondary indexes in partitioned systems enable queries on non-partition-key attributes. The challenge: data is spread across shards by partition key, but you want to find records by other attributes (like email when partitioned by user_id). Two approaches exist: local (index per partition) and global (index partitioned by indexed attribute).
Local Secondary Indexes (Document-Partitioned):

Local secondary indexes store index entries colocated with base data on each partition. Each partition maintains its own independent index covering only its local records. When you write a record to partition A, the index on partition A updates synchronously within the same transaction.

The Scatter-Gather Problem:

When querying by a secondary attribute, the system cannot determine which partition holds matching records. The query coordinator must broadcast to all partitions in a scatter-gather pattern. With 100 partitions where each has p99 latency of 20ms, overall query p99 approaches the slowest responder.

⚠️ Common Pitfall: Probability of hitting at least one slow shard is 1 - 0.99^100 = 63%. Local secondary index queries can see p99 latencies of 50-200ms when fan-out reaches dozens of partitions, compared to single-digit milliseconds for primary key lookups.
Trade-off Summary:

Writes are efficient (10-20% overhead, single partition handles both record and index atomically). Reads suffer from fan-out latency.

💡 Key Takeaways

✓Write efficiency is excellent because both base record and index update occur in a single partition transaction, adding only 10 to 20 percent overhead with no cross partition coordination required.

✓Read queries on secondary attributes require scatter/gather to all N partitions, causing tail latency to approach the slowest shard. With 100 shards at p99 equals 20 milliseconds each, overall p99 often exceeds 50 to 200 milliseconds.

✓DynamoDB Local Secondary Indexes impose a 10 gigabyte item collection limit per partition key, making them unsuitable for partition keys with unbounded cardinality such as status equals active or country equals US.

✓Synchronous updates guarantee that index reads reflect all committed writes to the local partition, avoiding eventual consistency problems within a single partition boundary.

✓Best suited for workloads where most queries route by primary key and secondary attribute queries are rare, offline batch jobs, or can tolerate high tail latency from fan out.

✓Fan out amplification grows linearly with partition count. At 50 partitions, a single secondary query generates 50 remote procedure calls (RPCs) plus coordinator merge overhead, consuming significant network and CPU resources.

📌 Interview Tips

1DynamoDB LSI example: A table partitioned by user_id with an LSI on email. Writing user_id equals 42 updates partition hash(42) and its local email index atomically in under 5 milliseconds. Querying email equals [email protected] fans out to all partitions, each searches its local index, and the coordinator merges results in 80 to 150 milliseconds at p99 with 50 partitions.

2Apache Cassandra secondary indexes are local. A 100 node cluster requires 100 RPCs for a single secondary attribute query. If per node p99 is 15 milliseconds, coordinator p99 often reaches 50 to 100 milliseconds due to stragglers, making Cassandra discourage secondary indexes in favor of denormalized query tables.

3Elasticsearch shards by document (local indexing). A 200 shard index fans queries to all shards, merges top K results, then fetches documents. Systems with 100 plus shards commonly see p99 tail latency degrade to 200 to 500 milliseconds compared to 20 to 50 milliseconds for smaller shard counts.

← Back to Secondary Indexes with Partitioning Overview