DynamoDB GSI and LSI

A DynamoDB table is a collection of items, each uniquely identified by a primary key. The primary key can be a simple partition key (hash key) or a composite partition key + sort key (range key). Without indexes, you can only query items using the primary key or scan the entire table—both inefficient for non-key attributes. Secondary indexes provide alternative access patterns by allowing queries on other attributes.

There are two types: Global Secondary Index (GSI) and Local Secondary Index (LSI). A GSI has a different partition key and optionally a different sort key from the base table. An LSI uses the same partition key as the base table but a different sort key.

When you create a GSI, DynamoDB automatically replicates data from the base table into the index. The index is a separate data structure with its own partition key and sort key. Each item in the index is a projection of the base table item—you choose which attributes to copy (KEYS_ONLY, INCLUDE, or ALL). The index is partitioned by its own partition key, which can be any scalar attribute (String, Number, Binary).

When you write to the base table, DynamoDB asynchronously propagates the change to all GSIs. This means GSIs are eventually consistent—there is a propagation delay, typically sub-second but not guaranteed. You cannot achieve strong consistent reads from a GSI. The write capacity consumed by a GSI write is separate from the base table's write capacity. Each GSI has its own provisioned read/write capacity or can use on-demand mode.

Queries against a GSI work just like table queries: you specify the partition key equality condition and optionally a sort key condition (begins_with, between, >, <, etc.). The query returns items from the index, not the base table. If you projected all attributes, the response contains full items; otherwise, you may need to fetch missing attributes from the base table (costing additional read capacity).

An LSI is created at table creation time and cannot be added later. It shares the same partition key as the base table but uses a different sort key. The LSI is stored in the same partition as the base table items—actually, each partition stores items sorted by sort key, and the LSI adds an additional sort order within that partition. This means the LSI is co-located with the base table data, so reads can be strongly consistent (if you choose). LSI writes consume the same write capacity as the base table write—no additional capacity is needed. However, LSI storage counts against the table's storage limit (10 GB per partition key value).

To create a GSI using the AWS CLI:

aws dynamodb update-table --table-name mytable --attribute-definitions AttributeName=Author,AttributeType=S --global-secondary-index-updates 'Create={IndexName=AuthorIndex,KeySchema=[{AttributeName=Author,KeyType=HASH},{AttributeName=Title,KeyType=RANGE}],Projection={ProjectionType=ALL},ProvisionedThroughput={ReadCapacityUnits=5,WriteCapacityUnits=5}}'

To create an LSI during table creation:

aws dynamodb create-table --table-name mytable --attribute-definitions AttributeName=PK,AttributeType=S AttributeName=SK,AttributeType=S --key-schema AttributeName=PK,KeyType=HASH AttributeName=SK,KeyType=RANGE --local-secondary-indexes IndexName=LSI1,KeySchema=[{AttributeName=PK,KeyType=HASH},{AttributeName=Date,KeyType=RANGE}],Projection={ProjectionType=ALL}

To verify indexes:

aws dynamodb describe-table --table-name mytable

Look for GlobalSecondaryIndexes and LocalSecondaryIndexes in the output.

A large e-commerce platform stores orders in a DynamoDB table with primary key (OrderID). The application needs to query orders by CustomerID (partition key) sorted by OrderDate (sort key). The base table does not have CustomerID as partition key. The solution: create a GSI with CustomerID as partition key and OrderDate as sort key, projecting all attributes. This allows efficient queries for a customer's order history. In production, the table handles 10,000 writes/second, and the GSI must be provisioned with sufficient write capacity to avoid throttling. A common mistake is underestimating GSI write capacity—if the GSI write capacity is 5000 WCU but base table writes are 8000 WCU, the base table will be throttled. The team set up auto-scaling on the GSI to match base table write patterns. They also chose to project only frequently accessed attributes (OrderStatus, TotalAmount) to reduce storage costs.

A manufacturing company collects sensor readings from thousands of devices. The base table uses (DeviceID, Timestamp) as primary key. They need to query by sensor type (e.g., temperature) across all devices within a time range. This requires a different partition key (SensorType). They create a GSI with SensorType as partition key and Timestamp as sort key. However, they also need to occasionally query all readings for a specific device sorted by a different attribute, like reading value. For this, they create an LSI on the base table with sort key = ReadingValue. The LSI is created at table creation time. The team must plan ahead because LSIs cannot be added later. They also note that the LSI shares the base table's write capacity, which is acceptable since write volume is moderate. A misconfiguration: they initially projected all attributes to the LSI, causing storage to exceed the 10 GB per partition limit for devices with many readings. They changed to KEYS_ONLY and fetch missing attributes from the base table when needed.

A mobile game tracks player scores in a table with (PlayerID, GameID) as primary key. They need a global leaderboard sorted by score. They create a GSI with a constant partition key (e.g., 'leaderboard') and Score as sort key. This ensures all items land in one partition, allowing efficient range queries. However, this creates a hot partition—all writes and reads go to the same partition. With high write volume (100,000 writes/second), the single partition cannot handle the load. They must redesign: use a sharded GSI where partition key is a combination of score range (e.g., score bucket) to distribute writes. For reads, they query each shard and merge results. This is a common exam scenario—avoid single partition GSIs for high-write workloads.