When Should You Choose Local vs Global Secondary Indexes?

Choosing between local (document partitioned) and global (term partitioned) secondary indexes hinges on your workload's read/write balance, query selectivity, consistency requirements, and cost tolerance. Local indexes are write optimized: a single partition handles both the base write and index update atomically in one transaction, adding only 10 to 20 percent write overhead. However, reads that query by secondary attributes must scatter to all partitions, suffering high tail latency and coordinator overhead. Use local indexes when writes dominate your workload, when most queries route by the primary key and secondary attribute queries are rare or batch oriented, or when you can tolerate query latency in the 50 to 200 millisecond p99 range due to fan out. For example, DynamoDB LSIs work well for audit logs partitioned by user_id where you occasionally query by timestamp within a user's partition, but would fail for queries across all users. Global indexes are read optimized: queries on the indexed attribute route to one or a few index partitions, achieving 10 to 30 millisecond p99 latency without scatter/gather. The cost is write amplification (each base write that touches indexed attributes generates 1 plus K writes for K indexes), eventual consistency (index updates lag base writes by milliseconds to seconds), and increased storage (indexes are separate partitions consuming additional capacity). Use global indexes when you have frequent, latency sensitive queries on secondary attributes, when you can tolerate eventual consistency, and when you can afford the K times write cost multiplier. Amazon product search is a canonical use case: queries by category or brand are frequent and latency critical, and the application tolerates a 200 millisecond lag where newly listed products do not immediately appear in search results. In practice, many systems combine both patterns. DynamoDB allows up to 5 LSIs plus 20 GSIs per table. A common pattern is to use the primary key for high throughput lookups, LSIs for queries within a partition key (e.g., sort orders or filters scoped to a single customer), and GSIs for global queries across partition keys (e.g., all orders by status or all users by email). When neither index type fits, consider alternatives: denormalize data into multiple tables keyed by different query patterns (materialized views), use a dedicated search system like Elasticsearch for complex multi attribute queries, or push non latency critical queries to an analytics warehouse that periodically ingests from your primary store.

E commerce order table: Primary key is order_id for fast order detail lookups (5 millisecond p99). LSI on user_id, created_at sorts a user's orders by date within their partition. GSI on status queries all pending orders across all users globally in 20 millisecond p99, accepting 200 millisecond lag for eventual consistency.

IoT sensor data: Partition by device_id for high write throughput (1 million writes per second). Local index on timestamp enables time range queries per device. Avoid global index on sensor_value because writes are continuous and index would multiply cost by 2 times with no latency benefit for rare cross device queries; instead, push to analytics warehouse.

User profile service: Primary key is user_id. GSI on email enables login by email in 10 millisecond p99. GSI on country enables admin queries by geography in 25 millisecond p99. Both GSIs project name and avatar_url to cover common queries. Write cost is 3 times base (1 base plus 2 GSIs), acceptable because writes are infrequent (user registration and profile updates).

Audit log: Partition by tenant_id for tenant isolation. LSI on event_type, timestamp enables queries like "show all login events for tenant X in the last 24 hours" without cross partition scatter. GSI would be wasteful because audit queries are always scoped to a tenant.