TL;DR
When you’re building apps on distributed databases like DynamoDB or Cassandra, you need indexes to query data by something other than the primary key. But here’s the thing: not all indexes are created equal. Local Secondary Indexes (LSIs) live inside your partitions and give you fast, consistent reads for queries scoped to a single partition. Global Secondary Indexes (GSIs) let you query across all partitions with a completely different key, but they’re eventually consistent and cost more to maintain. This article breaks down when to use each one, how they work under the hood, and why choosing the wrong one can sink your performance faster than a badly designed partition key.
🔥 Introduction
Picture this: you’re running a planet-sized library. Books are scattered across hundreds of branches worldwide. Each branch handles its own collection. You’ve got a master catalog sorted by book ID. That works great when someone asks for a specific book. But then a frantic reader rushes in and says, „I need mystery novels by Agatha Christie. Right now.“
Suddenly you’re stuck. Your catalog is organized by book ID, not author. Do you call every single branch and ask them to manually search their shelves? That’s going to take forever. Or do you keep a separate index somewhere that tracks books by author across all branches?
Welcome to the fundamental problem of indexing in distributed databases. When your data is partitioned across multiple nodes, querying by anything other than the partition key becomes a genuine pain. You need secondary indexes. But which kind?
That’s where Local Secondary Indexes (LSIs) and Global Secondary Indexes (GSIs) come in. They’re both sidekicks designed to solve this problem, but they work in completely different ways. One stays local to each partition. The other spans the entire dataset. Picking the wrong one doesn’t just slow down your queries. It can blow up your throughput costs, create hot partitions, or leave you with stale data when you need fresh reads.
Let’s dig into how these two heroes actually work, when you should use each, and what happens when you make the wrong call.
Indexing 101: Searching for Needles in Data Haystacks
An index in a database is like the index in the back of a book. It helps you jump straight to what you’re looking for without reading every page. In a traditional single-machine database, this is straightforward. You build a B-tree or hash map on a column like „author,“ and boom, you can instantly find all books by Agatha Christie without scanning the whole table.
But distributed databases don’t work that way. Your data is split across nodes or shards, partitioned by a primary key. Each node holds a chunk of the data. This is great for scaling and parallelism. It’s terrible for indexing.
Why? Because an index that covers data across multiple partitions either has to live alongside each partition’s data or exist as its own distributed structure. You can’t just slap a B-tree on top and call it a day.
So we get two flavors of secondary indexes:
Local Secondary Indexes (LSIs) are tied to each partition’s data. They use the same partition key as the base table. Think of them as local detectives who only search their own neighborhood.
Global Secondary Indexes (GSIs) span all partitions with their own independent partitioning scheme. They’re like international investigators with jurisdiction everywhere.
Both are called „secondary“ because they index on something other than the primary key. But the local versus global distinction is everything. It’s the difference between searching one neighborhood and searching the whole city.
Meet the Local Secondary Index: The Neighborhood Detective
A Local Secondary Index is a detective who works locally, within the bounds of a single partition. In DynamoDB, an LSI shares the same partition key as your base table, but it uses a different sort key. The index entries are grouped by the exact same partitions as your primary data. Each partition gets its own little index for the items in that partition.
Back to the library analogy: an LSI is like each branch keeping a card catalog of books by author, but only for books in that branch. Walk into the Berlin branch and ask for Agatha Christie books, and the local catalog shows you everything Berlin has. But it won’t tell you about Christie’s books in New York. It doesn’t have global knowledge.
Here’s how it works under the hood. When you add or update an item, the local index for that item’s partition updates automatically. The index entry consists of the partition key (same as the item’s), the new sort key, and a pointer to the full item. Because the LSI doesn’t redistribute data to new partitions, it lives alongside the data in each partition. No separate storage nodes needed.
Querying an LSI requires you to specify the partition key value. You can’t use an LSI to search across partitions. In DynamoDB, you have to provide the partition key upfront. You can ask, „Give me Agatha Christie’s books in the Berlin branch,“ and use a sort key condition on author. But you can’t ask for Christie’s books without specifying a partition. That’s a job for a global index.
Consistency is where LSIs shine. Because the index is local to the partition and updated in tandem with the base data, you can get strongly consistent reads. DynamoDB lets you perform strongly consistent queries on an LSI, just like the base table. This works because the LSI data lives on the same partition (usually the same physical node) as the base item. Your local detective always has the latest gossip from their neighborhood.
Shared Throughput and Storage means the LSI doesn’t get its own provisioned capacity. It shares the read/write throughput of the base table. All index updates and queries consume the table’s capacity units. And all indexed data counts toward the partition’s storage. DynamoDB imposes a 10 GB limit on the total size of items in a single partition when you use LSIs. That includes base items and their index entries. If one partition accumulates too much data (think one prolific author with thousands of books), you could hit that limit fast.
When to Use LSIs: They’re perfect when you have multiple query patterns within the same partition key. Say you have a user data table partitioned by UserID. You store actions for each user, sorted by timestamp by default. But you also need to query a particular user’s data by action type or status. An LSI with a sort key on ActionType lets you quickly get all actions of a certain type for that user. The user ID stays the partition key, so you’re always looking at one user at a time.
This pattern works when your access patterns naturally scope to an entity. Like „all orders for this customer by date“ instead of „all orders by status across all customers.“
Example: The Case of the Actor’s Movies
Let’s get concrete with DynamoDB. Suppose we have a table of actors and their movies. The table’s primary key is Actor (partition key) and MovieTitle (sort key). By default, querying by Actor gives us all their movies sorted by title.
Now we want to query an actor’s movies by year of release. We create an LSI on Year. This LSI uses the same Actor partition key, but with Year as the sort key. Each actor’s partition can now be queried by year.
Tom Hanks wants to see all his movies from the 1990s? We query the LSI on Year for Actor equals „Tom Hanks“ and a Year range of 1990 to 1999. Boom. All of Tom’s ’90s hits appear without scanning all his films. The data for this LSI is local, basically an alternate sorted view of Tom Hanks’s items by Year.
But say we have a new query: „Find all actors who starred in Toy Story.“ This query wants to use MovieTitle as the key and retrieve actors. That’s a different partition key than our base table (which is partitioned by Actor). Can an LSI help? Nope. LSIs can’t change the partition key. Our local detective is helpless outside its home turf.
For this query, we need a Global Secondary Index that indexes by MovieTitle across all actors.
Enter the Global Secondary Index: The Worldwide Investigator
A Global Secondary Index is like an Interpol agent with jurisdiction everywhere. A GSI in DynamoDB uses a different partition key (and optional sort key) than the base table. It’s called „global“ because queries on this index can span all the data in the table, across all partitions. The GSI is essentially a distributed index that can be searched independently of the base table’s partitioning.
Back to the library: a GSI is like a centralized author index that lists every book by Agatha Christie and tells you which branch has it. It’s an online catalog that aggregates data from all branches. When someone asks for mystery novels by Agatha Christie, the global index directly points to all branches holding those books. One index to rule them all.
How GSIs Work: Under the hood, a GSI is basically a separate table. It has its own partition key and sort key schema, and it contains pointers (or copies of attributes) to the original items. When you create a GSI, you specify what data it projects from the main table (all attributes or just some). DynamoDB automatically propagates changes from the base table to the GSI.
This propagation is asynchronous. Usually very fast, but not instantaneous. This is why GSIs only support eventually consistent reads. Your global investigator might be a bit behind on the latest updates. If a new book arrives in the library, the global catalog might take a second or two to catch up and list it.
In DynamoDB, if you query a GSI immediately after a write, there’s a slight chance you won’t see the new item yet. Under normal conditions the delay is tiny (fractions of a second), but it could be longer in rare cases. For most applications, that’s fine. But if you absolutely need read-your-write consistency on that alternate key, an LSI (or a direct table query) is the way. GSIs can’t guarantee that fresh-off-the-press consistency.
Independent Throughput and Unlimited Size: Unlike LSIs, GSIs have their own provisioning for read/write capacity. They don’t share the base table’s throughput. They also aren’t constrained by that 10GB-per-partition limit, because the GSI has its own partitioning. The GSI’s data is stored in separate partitions based on the GSI’s keys, which shuffle the data differently across the system.
You can have up to 20 GSIs on a DynamoDB table by default. Each GSI provides a new way to query the data. Each one is like creating a new lens or view on your data, with its own storage and throughput. This means more flexibility, but also more cost. You pay in throughput and storage for maintaining those extra indexes.
Querying a GSI: You use the GSI’s partition key and optionally sort key to query it, just like a table. The query goes to whichever node holds that partition of the index. If we made a GSI on MovieTitle to Actor index, a query like „MovieTitle equals Toy Story“ would directly go to the partition of the GSI that covers „Toy Story“ and return the items (Tom Hanks, Tim Allen, etc. as actors in that movie). We didn’t need to know which actor’s partition had „Toy Story“ because the GSI reorganized the data by movie.
It’s as if the library made a separate alphabetical catalog by book title, so you just flip to „Toy Story“ and see all the branches and actors associated.
Trade-offs: The Price of Global Vision
GSIs sound powerful. They are. But they come with challenges.
Consistency is eventual. If your application can’t tolerate stale data in that index for even a short time, that’s a problem.
Maintaining GSIs means every write to the base table may trigger writes to one or more GSIs. This increases write latency and cost. If you have a table with 3 GSIs and you insert an item that needs to go into all three, DynamoDB performs three additional writes (one for each index). That’s like the global detective having to file copies of every new clue to three different global databases. More work than just handing it to the local file.
GSIs can be added to existing tables at any time. This is nice for evolving apps. If you realize you need a new query pattern down the line, you can slap on a GSI. But it also means you need to rethink capacity for that index and perhaps backfill data when creating it.
You cannot do strong consistent reads on GSIs. Also, if a GSI’s write provisioning lags behind a sudden spike of writes, it could temporarily back up (though the system tries to prevent that).
Real-World Example: User Email Lookup
A classic GSI use case is maintaining a user table by user ID and wanting to look up users by email address. Your primary key is UserID (partition key) and you have attributes like Email, Name, etc. A common access pattern is to find a user by their email (like when they log in).
You can’t query the main table by Email without scanning every item. Terribly inefficient. So you create a GSI with partition key Email (and maybe no sort key). Now the database keeps an index of Email to UserID mappings. When someone logs in, you query the GSI where Email equals „alice@example.com“, and it directly fetches the matching user’s info.
This GSI is essentially a globally accessible index on the Email attribute, distributed by email. DynamoDB internally handles this by maintaining a hidden table partitioned by the email hash.
Cassandra doesn’t have built-in „global“ secondary indexes that auto-replicate data to a new partition key. You’d handle this scenario by denormalization. Create a separate table yourself, say users_by_email, where you store each user keyed by email. That’s effectively a manual GSI. The downside is you have to transactionally keep it in sync or handle inconsistencies.
Newer versions of Cassandra introduced Materialized Views to automate this pattern, where the database maintains a second table (the view) with a different primary key. A materialized view in Cassandra is basically a globally indexed projection of a base table (Cassandra’s equivalent to a GSI). It automatically updates but is also eventually consistent and was even considered experimental for some time.
With GSIs, we trade some write cost and eventual consistency for the ability to query across all partitions by an entirely new key. It’s a classic trade-off in distributed systems: more flexibility and power, at the cost of more complexity under the hood.
LSIs vs GSIs: Key Differences and When to Use What
Let’s put them head-to-head. Both LSIs and GSIs help you query by alternate keys, but they differ in scope, consistency, and use cases.
Partition Key Constraint: The biggest difference is that an LSI cannot change the partition key of the data, while a GSI can. LSIs are anchored to the original partition. If your query needs to aggregate or search items that reside in different primary partitions, an LSI won’t cut it. You need a GSI. Querying movies within one actor’s list (same actor partition) could use an LSI. Querying across actors by movie requires a GSI.
Data Distribution and Performance: An LSI keeps data co-located. This is great for performance when you know the partition key. The query goes to one partition and uses the local index there (very fast). A GSI distributes the index by a new key, meaning queries on the GSI target only the relevant partition of the GSI. For targeted queries by the indexed attribute, GSIs are efficient. LSI equals efficient if you have the partition key. GSI allows you to efficiently query by a new key (new partitioning).
Consistency Guarantees: LSIs (at least in DynamoDB) can serve strongly consistent reads like the base table. GSIs cannot. They’re eventually consistent only. If your application demands up-to-the-moment accuracy on the indexed data, and you can’t tolerate any replication lag, that leans towards using LSIs (or designing your access pattern around the base table’s key). If you’re running a financial application and absolutely must see the latest transactions by category for a user, you might choose an LSI (partitioned by user) so that you can use strong consistency within that user’s partition. A GSI giving an eventually consistent answer might momentarily miss the most recent transaction.
In practice, many use cases are fine with eventual consistency. But this is crucial for truly critical data freshness.
Capacity and Scaling Limits: LSIs share table throughput and have item size limits per partition (that 10GB rule in DynamoDB). If one partition’s data and its index entries swell too large, you’ve got a problem. DynamoDB limits you to 5 LSIs per table. GSIs have their own throughput and don’t have the 10GB item collection limit. The data can grow because it will just span more partitions of the GSI as needed. DynamoDB allows up to 20 GSIs per table.
In Cassandra terms, an LSI-equivalent (a native secondary index) doesn’t require a separate table and doesn’t have a hard count limit, but it can become impractical beyond a certain scale. A „global“ index in Cassandra (like a materialized view or manual index table) is essentially a full-fledged table on its own, counting toward your total number of tables and using additional storage.
Creation Time: LSIs must be defined at table creation in DynamoDB. They are part of the table’s schema upfront. If you realize later that you need a new LSI, tough luck. You’d have to rebuild the table or plan for it originally. GSIs can be created anytime on an existing table. DynamoDB will backfill the index from existing data. This gives GSIs an edge in schema flexibility.
Cassandra differs: you can add a secondary index (local type) anytime with a CQL CREATE INDEX command on an existing table, and Cassandra will build it in the background. You can also create a materialized view on an existing base table (though doing so on a huge table will put load on the cluster to copy data into the view).
Maintenance and Overhead: Every index is overhead: additional writes, storage, and complexity. An LSI, being tightly coupled with the base partition, might have less overhead in terms of network. It doesn’t have to redistribute data; it just writes an index entry locally. But it still uses capacity and storage. A GSI will have more overhead on writes because it’s writing to different partitions (potentially different physical nodes) and must manage eventually consistent replication. In DynamoDB, that’s abstracted away, but it’s one reason they charge separate throughput for GSIs.
In Cassandra, a local secondary index’s overhead is that each node maintains a hidden index table (like a local copy of that column’s index), which adds write cost but stays local. A materialized view (global index) in Cassandra duplicates the data into another table, effectively doubling writes and storage for that data, similar to a GSI duplicating data in DynamoDB.
Choose indexes wisely. Too many and your writes could slow to a crawl with all the index upkeep.
A Quick Tale of Two Databases
It’s interesting to see how DynamoDB and Cassandra diverged in terminology. DynamoDB cleanly distinguishes LSIs and GSIs. Cassandra historically just had „secondary indexes,“ which are local per node (which equates to local per partition of data) and are notorious if misused.
A common Cassandra community advice is „secondary indexes are not magic, and if you use them without a partition key, you’ll hurt performance.“ That’s because the index isn’t globally distributed. Each node’s index must be queried, leading to a scatter/gather query that can flood the cluster.
To get a „global index“ effect, Cassandra users often denormalize: create a table keyed by email if you need to query by email. This is manual but effective because then you do a single-partition lookup by email in that table.
Cassandra’s newer Materialized Views feature automates that pattern, but with eventual consistency caveats (and it was marked experimental for a long time). ScyllaDB, a Cassandra-compatible database, even introduced something they call „Local Secondary Indexes“ as an optimization on top of Cassandra’s model, allowing a syntax to declare an index that uses the partition key (just like DynamoDB’s LSI concept) for more efficient local searches.
This just shows that as databases evolve, they recognize the need for both local-scope and global-scope indexes in partitioned environments.
Conclusion: Choosing Your Indexing Sidekick
In the grand battle of LSI versus GSI, there’s no absolute winner. Each is a hero in the right context. The choice depends on your data model and query needs.
If your queries are mostly scoped to items that share a partition key (like „all orders of a single customer by different attributes“), and you need the flexibility of different sorting or filtering within that partition, an LSI is a natural fit. It’s efficient, kept in sync in real-time, and leverages the locality of data. Just be mindful of its limitations. You can’t use it to query across partitions, you have to plan it ahead of time, and be wary of any single partition growing too large.
If your application demands querying across the entire dataset by some alternate key (like „find user by email“ or „list all transactions by transaction type irrespective of customer“), you’re looking at a GSI or an equivalent global index strategy. A GSI shines in those situations, essentially giving you another table optimized for that query pattern, maintained automatically. You gain new query superpowers at the cost of eventually consistent reads on that index and extra work on writes.
For engineers using DynamoDB, the good news is AWS handles most of the heavy lifting of maintaining these indexes. You just need to think carefully about your access patterns. Design your LSIs and GSIs up front if possible. Use LSIs for alternate sort orders within the same partition, and GSIs for new lookup keys. Keep an eye on hot partitions and consider GSIs if you find an LSI-constrained scenario.
DynamoDB lets you only have 5 LSIs, which is usually plenty given their narrow use case, but up to 20 GSIs, which suggests AWS expects heavier use of GSIs for most non-trivial applications.
For those working with Cassandra or similar distributed databases, know that an index that isn’t tied to the primary key is likely „local“ on each node and won’t magically know about data across the cluster. You either keep your queries partition-aware or use techniques like materialized views or manual index tables (global indexes by another name) when you need that cross-partition lookup. And always test the performance. A poorly chosen index can make a fast cluster crawl if it forces multi-node operations for each query.
In the end, designing indexes in a partitioned system is a bit of an art. It requires balancing locality (to keep things fast and consistent) and global reach (to support rich query patterns). Sometimes you even use both: a table might have an LSI or two for common within-partition queries and a GSI for one or two critical global queries.
The key takeaway is to align your index choice with your query patterns. Go local when you can, go global when you must.
If you know which branch holds the info, ask the local librarian (LSI) for the quickest answer. If you don’t know where to look, consult the global catalog (GSI) which will guide you but maybe not as instantly. Use the right tool for the mystery you’re trying to solve, and your distributed database will serve up answers faster than Sherlock Holmes with a fresh clue.
Happy indexing. May your queries be ever fast and your servers ever calm, no matter how large your data grows.