In a partitioned datastore where the primary key determines which partition a row lives on, what's the difference between a local secondary index and a global secondary index on some other (non-partition-key) attribute, and what does each cost you on reads versus writes?

## The problem a secondary index solves Once a table is partitioned by a primary (partition) key, the system knows exactly which partition to check for any query that includes that key. The problem arises for queries on a different attribute that isn't the partition key: 'find all orders with `status=shipped`' in a table partitioned by `order_id` gives you no way to know which partition holds matching rows. A secondary index exists to answer exactly this kind of query efficiently, and the two dominant designs — **local** and **global** — differ in where the index data physically lives relative to the base data, driving a direct trade-off between write cost and read fan-out cost. ## The local secondary index A local secondary index is built and stored **within each partition**, indexing only the rows that already live on that partition. Concretely, partition 3 maintains its own local index mapping `status` to the `order_ids` on partition 3 with that status, and partition 7 maintains a separate, independent local index for its own rows. - **Writes are cheap.** Because the index entry for a row always lives on the exact same partition as the row itself, writing or updating a row only ever touches one partition — the base row and its local index entry update together, atomically, without cross-partition coordination. Single-partition writes are cheap and low-latency, and don't need distributed transaction machinery. - **The cost lands on reads.** Since there's no global knowledge of which partitions contain matching rows, a query on the index has to be sent to every partition (scatter-gather), each partition searches its own local index, and the coordinator merges partial results. Query cost scales with the number of partitions regardless of how selective the query actually is. ## The global secondary index A global secondary index takes the opposite approach: it's built as an entirely separate index structure, **partitioned independently** — typically partitioned by the indexed attribute itself rather than by the base table's primary key. - **Reads go straight to the answer.** Now a query like '`status=shipped`' can hash or range on `status` directly and go straight to the specific index partition(s) holding matching entries. This gives read performance that scales with query selectivity rather than cluster size. - **The cost moves to the write path.** Any write to the base table must also propagate an update to the global index, which — because it's partitioned differently — usually lives on a different physical partition/node. Keeping both in sync synchronously would require a distributed transaction on every write, hurting write latency and availability. In practice, most systems make this propagation asynchronous, accepting **eventual consistency**: the global index catches up shortly after the write, but a read against the index immediately after a write can miss it or see stale data. | Design | Where the index lives | Write path | Read path | |---|---|---|---| | local | within each partition | one partition, together, atomically | sent to every partition (scatter-gather) | | global | an entirely separate structure, partitioned independently | propagate an update to a different physical partition/node | straight to the specific index partition(s) | ## Where you meet this: DynamoDB The concrete, named example most engineers meet this in is **DynamoDB**: - its **Local Secondary Indexes** are constrained to share the same partition key as the base table (only the sort key differs) and are strongly consistent, because index and data are physically co-located and updated together; - its **Global Secondary Indexes** can be built on any attribute, are independently partitioned, support querying efficiently across the whole table's keyspace, but are explicitly documented as eventually consistent — a write to the base table might not be visible in a GSI query for a short window afterward. This isn't a DynamoDB quirk; it's the direct structural consequence of the local-vs-global design. The same trade-off shows up in other partitioned systems (e.g., Cassandra's now-deprecated built-in secondary indexes were local-index-style with the same scatter-gather read cost, part of why Cassandra users are steered toward denormalized tables or external indexing like Elasticsearch instead). ## The two failure modes in production - **The local-index failure mode** shows up as query latency and load that scales linearly with cluster size no matter how selective the filter is — adding nodes to handle more data actually makes local-secondary-index queries slower, since more partitions must be scanned, a counter-intuitive and easy-to-miss operational trap. - **The global-index failure mode** shows up as stale-read bugs: an application writes a row, immediately queries the global index expecting to find it, and doesn't — a class of bug invisible in local testing (near-zero replication lag) but appearing intermittently in production under load. ## The decision rule The practical decision rule: 1. use local secondary indexes when write cost and consistency matter more and the cluster is small enough (or queries selective enough within a partition) that fan-out is tolerable; 2. use global secondary indexes when read selectivity across the whole dataset matters and the application can tolerate eventual consistency on the indexed view.