A table can have a maximum of 25 secondary indexes, which are comprised of 20 Global Secondary Indexes and 5 Local Secondary Indexes.
Understanding Secondary Index Limits
When designing your database schema, particularly in NoSQL databases, understanding the constraints on secondary indexes is crucial for efficient data retrieval and application performance. Secondary indexes provide alternative access paths to data beyond the primary key, but their numbers are capped to ensure system stability and performance.
There are two distinct types of secondary indexes, each with its own specific limit per table:
Global Secondary Indexes (GSIs)
Global Secondary Indexes offer significant flexibility by allowing you to query data using an alternate partition key and, optionally, an alternate sort key that can be different from the table's primary key. This is particularly useful for queries that require filtering or sorting on attributes not part of the main primary key.
- Maximum Allowed: You may create up to 20 Global Secondary Indexes per table.
GSIs are "global" because they span across all partitions of the base table, making them suitable for queries that need to retrieve items across the entire dataset. They are separate, sparsely populated tables that are automatically maintained by the database service.
Local Secondary Indexes (LSIs)
Local Secondary Indexes provide an alternative sort key for a given partition key that is the same as the base table's partition key. They are "local" because their scope is limited to the items that share the same partition key as the base table.
- Maximum Allowed: You may create up to 5 Local Secondary Indexes per table.
LSIs are useful when you need to query items within a single partition using different sorting attributes, or when you need to project different attributes for items within that partition. They are physically stored alongside the base table's data within the same partition.
Summary of Secondary Index Limits Per Table
For clarity, here's a summary of the maximum number of each type of secondary index allowed per table:
| Index Type | Maximum Allowed |
|---|---|
| Global Secondary Index | 20 |
| Local Secondary Index | 5 |
In total, this means a table can be augmented with a combination of up to 25 secondary indexes to facilitate various query patterns. Thoughtful planning of your secondary indexes is vital to optimize data access patterns and manage costs effectively for your application.
