Partitioning & Sharding • Secondary Indexes with PartitioningMedium⏱️ ~3 min
What Are Document Partitioned (Local) Secondary Indexes?
Document partitioned secondary indexes, also called local secondary indexes, store index entries colocated with the base data on each partition. Each partition maintains its own independent index covering only the records it stores locally. When you write a record to partition A, the secondary index on partition A is updated synchronously within the same partition transaction. The index entry typically consists of the secondary attribute values plus the primary key, allowing the partition to quickly find matching records.
The critical tradeoff emerges during reads. When you query by a secondary attribute that is not the partition key, the system cannot determine which partition holds matching records, so the query coordinator must broadcast the request to all partitions in a scatter/gather pattern. Each partition searches its local index, returns matching primary keys or partial results, and the coordinator merges these responses. For example, if you partition user data by user_id but query by email address using a local secondary index, a single query fans out to every partition. At Amazon DynamoDB, Local Secondary Indexes (LSIs) follow this pattern and are updated synchronously, but they impose a 10 GB limit per item collection (all items sharing the same partition key), constraining their use for high cardinality scenarios.
The write path is extremely efficient: a single partition handles both the base record write and the index update atomically, typically adding only 10 to 20 percent overhead. However, read latency suffers from the maximum of N problem. With 100 partitions where each has a 99th percentile (p99) latency of 20 milliseconds, the overall query p99 approaches the slowest responder. The probability of encountering at least one slow shard is approximately 1 minus 0.99 raised to the power of 100, which equals roughly 63 percent. In practice, DynamoDB LSI queries can see p99 latencies of 50 to 200 milliseconds when fan out reaches dozens of partitions, compared to single digit milliseconds for primary key lookups.
💡 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.
📌 Examples
DynamoDB 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.
Apache 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.
Elasticsearch 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.