Partitioning & Sharding • Secondary Indexes with PartitioningMedium⏱️ ~3 min
What Are Term Partitioned (Global) Secondary Indexes?
Term partitioned secondary indexes, commonly called global secondary indexes, treat the index as a separate logical dataset that is itself partitioned by the indexed attribute value or its hash. Unlike local indexes, the index is not colocated with base data partitions. Instead, each index partition holds entries for a specific range or hash bucket of term values across all base records. For example, if you index user records by country, all index entries for country equals US live in one index partition, country equals UK in another, and so on. An index entry typically stores the term value, the primary key of the base record, and optionally projected attributes to avoid fetching the base record.
Reads become highly efficient because a query on the indexed attribute routes to one or a small number of index partitions, avoiding scatter/gather. The index partition returns matching primary keys (and projected attributes if available), and the system then fetches full base records if needed. For selective queries, this is a dramatic improvement: a single index partition lookup at 5 to 10 milliseconds plus a base record fetch at another 5 to 10 milliseconds totals 10 to 20 milliseconds, compared to 100 plus milliseconds for scatter/gather with local indexes. Amazon DynamoDB Global Secondary Indexes (GSIs) follow this pattern and can return results from a single index partition in low single digit milliseconds at p50 and 10 to 30 milliseconds at p99 when the term is not hot.
The cost emerges on the write path. A base record write often must update multiple partitions: the base data partition plus one or more index partitions corresponding to the indexed attributes. Most systems avoid distributed transactions by making index updates asynchronous. DynamoDB propagates base writes to GSIs via durable change streams, introducing eventual consistency. Index updates lag base writes by milliseconds to seconds under normal load, and longer during backfills or traffic spikes. Write amplification is significant: with K global indexes and assuming all indexed attributes change, each base write generates 1 plus K write operations. At 100,000 base writes per second and 3 GSIs, the system performs roughly 400,000 write operations per second total (100k base plus 300k index writes), multiplying cost and capacity requirements by 4 times.
💡 Key Takeaways
•Read latency is low and predictable for selective queries, typically 5 to 30 milliseconds at p99 for a single index partition lookup plus optional base record fetch, avoiding scatter/gather fan out.
•Write amplification scales with the number of indexes. With K global indexes, each base write that modifies indexed attributes generates approximately 1 plus K write operations, multiplying capacity cost and replication traffic by K plus 1.
•Eventual consistency is standard because synchronous cross partition updates require distributed transactions. DynamoDB GSI updates lag base writes by milliseconds to seconds, causing false negatives in queries until propagation completes.
•Hot term skew can bottleneck a single index partition. If country equals US receives 80 percent of traffic, that index partition becomes a hotspot even when overall table capacity is provisioned high. Mitigation requires salting the term into B buckets and querying all B buckets.
•DynamoDB supports up to 20 GSIs per table, but each GSI consumes separate write capacity. At 50,000 writes per second with 5 GSIs, the system performs roughly 300,000 index write operations per second, significantly increasing cost.
•Backfilling a new GSI on a multi terabyte table can take hours, throttled by provisioned capacity. During backfill, queries on the GSI return incomplete results for older records not yet indexed.
📌 Examples
DynamoDB GSI example: A table with 200,000 write operations per second adds a GSI on status attribute. Index updates propagate asynchronously via change streams, lagging by 100 to 500 milliseconds under load. Queries on status equals active hit a single index partition in 8 milliseconds at p50 and 25 milliseconds at p99, then fetch base records, totaling 15 to 40 milliseconds end to end.
Creating a new DynamoDB GSI on a 5 terabyte table with 10,000 write capacity units allocated triggers a backfill. The system indexes existing items at roughly 3,000 to 5,000 items per second, taking 4 to 6 hours. Queries during backfill miss items not yet indexed, requiring application logic to handle incomplete results.
Amazon product catalog indexes by category using a global index. Category equals electronics receives 60 percent of queries, concentrating load on one index partition. Engineers salt the term into 16 buckets: index key equals hash(electronics) mod 16, electronics, product_id. Queries must fan to all 16 buckets, trading off read fan out for write distribution.