Global Secondary Indexes and Local Secondary Indexes

DynamoDB's primary key defines the only efficient query path on a table. If you need to query items by a different attribute—say, you want to find all orders for a product ID when the table's partition key is CustomerId—you would have to perform an expensive Scan without a secondary index.

Secondary indexes solve this by maintaining a separate, automatically updated copy of the data structured around a different key. DynamoDB offers two types: Global Secondary Indexes (GSI) and Local Secondary Indexes (LSI), each with different trade-offs.

A Global Secondary Index lets you define a completely different partition key (and optionally a sort key) from the base table. The GSI is truly global—it spans all partitions of the base table. You can query a GSI to find items by any attribute that you define as the GSI partition key.

GSIs have their own provisioned throughput (or inherit on-demand mode) independent of the base table. You can create up to 20 GSIs per table and can add or delete GSIs at any time on an existing table.

A Local Secondary Index shares the same partition key as the base table but uses a different sort key. LSIs are 'local' because they only query within a single partition (items with the same partition key). This makes LSIs ideal for querying a specific customer's orders sorted by different attributes.

LSIs must be defined at table creation time—you cannot add or remove them later. Each table can have up to 5 LSIs. LSIs share the base table's provisioned capacity (no separate throughput) and are subject to the 10 GB per-partition-key storage limit for the combination of base table and LSI data.

Here is a side-by-side comparison for exam clarity:

• Partition key: GSI can differ from base table; LSI must match base table
• Sort key: both support a different sort key from the base table
• When created: GSI anytime; LSI only at table creation
• Throughput: GSI has its own; LSI shares base table throughput
• Consistency: GSI reads are eventually consistent only; LSI reads can be strongly consistent
• Limits: up to 20 GSIs; up to 5 LSIs per table

When creating an index, you choose which attributes are projected (copied) into it:

• KEYS_ONLY: only the base table primary key and the index key—smallest index, requires additional GetItem calls for non-key attributes
• INCLUDE: key attributes plus a specific list of additional attributes you name—balances size and access pattern
• ALL: all attributes projected into the index—most flexible, higher storage and write cost

Choose projection type based on which attributes your queries actually need. Over-projecting wastes WCUs on every write; under-projecting forces extra GetItem calls to fetch attributes not in the index.

Querying a GSI uses the same Query API but specifies the --index-name parameter. The query runs against the GSI's key schema rather than the base table's. GSI queries are always eventually consistent—the GSI is updated asynchronously after writes to the base table, so there is a brief lag.

If an item in the base table does not have the GSI's partition key attribute, it is not included in the GSI at all (sparse index pattern). This is a powerful technique for indexing only a subset of items, such as all orders with status PENDING if the GSI partition key is Status.

A sparse index uses the fact that DynamoDB only projects items into a GSI if they have a value for the GSI's partition key. By defining the GSI partition key on an attribute that only some items have, you create an index of just that subset.

Example: in an Orders table, only unshipped orders have a PendingShipmentDate attribute. A GSI on PendingShipmentDate naturally contains only unshipped orders, making it a highly efficient way to query all pending orders without scanning the entire table.

GSI overloading is an advanced single-table design technique where you store different entity types in one table and use a generic GSI key attribute (e.g., GSI1PK and GSI1SK) populated with different patterns depending on the item type. This gives each entity type its own efficient query path through a single GSI.

Example: for User items, set GSI1PK = 'COUNTRY#US' and GSI1SK = username; for Order items, set GSI1PK = 'STATUS#PENDING' and GSI1SK = orderDate. Querying the GSI with the appropriate prefix retrieves the target entity type efficiently.

GSI throttling happens independently from the base table. If the GSI's provisioned WCUs are too low, writes to items that project into the GSI will be throttled—even if the base table has plenty of capacity. Monitor ConsumedWriteCapacityUnits and ThrottledRequests for each GSI separately.

A common pitfall is provisioning the GSI with lower capacity than the base table, then experiencing throttling when a write pattern suddenly increases the GSI write rate. Use Auto Scaling on GSIs or choose On-Demand mode to avoid this.

Decision guidelines for the SAA-C03 exam:

• Need to query by a completely different attribute → GSI
• Need to query within the same partition but sorted differently, and you know at creation time → LSI
• Need strongly consistent reads on an alternate sort key → LSI (only option, since GSIs are eventually consistent)
• Ten or more distinct query patterns → consider single-table design with GSI overloading rather than many separate tables
• Only need to retrieve all items for analysis → reconsider whether DynamoDB is the right database

You can delete a GSI at any time without affecting the base table. When you add a new GSI to an existing table, DynamoDB backfills the index asynchronously by scanning the base table—this can take minutes to hours for large tables. During backfill, the base table remains fully available for reads and writes.

You can monitor backfill progress in the console or via the DescribeTable API by checking the index's IndexStatus field—it will be CREATING during backfill and ACTIVE when complete. Do not query the GSI until it is ACTIVE.

Test your understanding of AWS Solutions Architect (SAA-C03) concepts from this lesson.

In this lesson you learned: GSIs provide alternate partition keys and can be added any time, LSIs share the base partition key and must be defined at creation, and projection types control which attributes are copied to the index. GSIs support sparse index and overloading patterns for advanced query flexibility. Next up we explore DynamoDB Streams and Global Tables.