What are secondary indexes in DynamoDB (GSI vs LSI)?
Understand DynamoDB secondary indexes: how GSIs and LSIs differ in keys, consistency, throughput and projections, with a create-table code example.
Expected Interview Answer
Secondary indexes let you query DynamoDB on attributes other than the table's primary key. A Global Secondary Index (GSI) defines a brand-new partition and sort key, while a Local Secondary Index (LSI) reuses the table's partition key but adds an alternate sort key.
A GSI has its own partition key and optional sort key and its own provisioned throughput, replicates data asynchronously (eventually consistent reads only), can be added or removed at any time, and you can have up to 20 per table. An LSI must share the table's partition key, is created only at table creation, supports strongly consistent reads, shares the table's throughput, and is constrained by a 10 GB per-partition-key item collection limit. Both indexes can project a subset of attributes (KEYS_ONLY, INCLUDE, or ALL) to control storage and read cost.
- Query on non-key attributes without scanning the whole table
- GSI: independent keys and throughput for flexible access patterns
- LSI: strongly consistent reads on an alternate sort key
- Attribute projection trims index size and read cost
- GSIs can be created and dropped on a live table
AI Mentor Explanation
The main scorecard is filed by match, but a secondary index is like keeping extra reference lists so you can look players up differently. An LSI is a sorted appendix inside each match's page, same match, but re-ordered by strike rate. A GSI is a whole separate book organised by player name across all matches, with its own filing cabinet and its own staff to maintain it.
Step-by-Step Explanation
Step 1
Identify the query need
Determine which non-key attribute you need to query or sort by that the base primary key cannot serve.
Step 2
Pick GSI or LSI
Use a GSI for a new partition key; use an LSI to keep the table's partition key with a different sort key.
Step 3
Choose projected attributes
Select KEYS_ONLY, INCLUDE, or ALL to balance index storage and read cost against needing extra fields.
Step 4
Provision throughput (GSI)
A GSI has its own read/write capacity; an LSI shares the base table's capacity.
Step 5
Create the index
Create LSIs only at table creation; create or drop GSIs anytime on a live table.
Step 6
Query the index
Issue Query or Scan against the index name; remember GSI reads are eventually consistent.
What Interviewer Expects
- Clear definition of GSI (new partition + sort key) vs LSI (same partition, new sort key)
- Knowledge that GSIs have their own throughput and only eventual consistency
- That LSIs allow strong consistency but must be created at table creation
- Understanding of attribute projection options
- Awareness of the 10 GB item collection limit for LSIs
Common Mistakes
- Thinking an LSI can be added after the table is created
- Believing a GSI supports strongly consistent reads
- Assuming an LSI has its own separate throughput
- Forgetting that unprojected attributes trigger extra fetches
- Using a GSI when you actually need strong consistency
Best Answer (HR Friendly)
“Secondary indexes are extra lookup paths that let you search DynamoDB by fields other than the main key. A Global Secondary Index gives you a totally new key and its own capacity, while a Local Secondary Index keeps the same partition key but lets you sort by a different attribute.”
Code Example
import boto3
client = boto3.client('dynamodb')
client.create_table(
TableName='Orders',
KeySchema=[
{'AttributeName': 'customerId', 'KeyType': 'HASH'}, # partition key
{'AttributeName': 'orderId', 'KeyType': 'RANGE'}, # sort key
],
AttributeDefinitions=[
{'AttributeName': 'customerId', 'AttributeType': 'S'},
{'AttributeName': 'orderId', 'AttributeType': 'S'},
{'AttributeName': 'orderDate', 'AttributeType': 'S'},
{'AttributeName': 'status', 'AttributeType': 'S'},
],
LocalSecondaryIndexes=[{
'IndexName': 'byOrderDate',
'KeySchema': [
{'AttributeName': 'customerId', 'KeyType': 'HASH'}, # same partition key
{'AttributeName': 'orderDate', 'KeyType': 'RANGE'}, # alternate sort key
],
'Projection': {'ProjectionType': 'ALL'},
}],
GlobalSecondaryIndexes=[{
'IndexName': 'byStatus',
'KeySchema': [
{'AttributeName': 'status', 'KeyType': 'HASH'}, # new partition key
{'AttributeName': 'orderDate', 'KeyType': 'RANGE'},
],
'Projection': {'ProjectionType': 'KEYS_ONLY'},
'ProvisionedThroughput': {'ReadCapacityUnits': 5, 'WriteCapacityUnits': 5},
}],
ProvisionedThroughput={'ReadCapacityUnits': 5, 'WriteCapacityUnits': 5},
)Follow-up Questions
- Why can't a GSI offer strongly consistent reads?
- What are the attribute projection types and when do you use each?
- What is the 10 GB item collection limit and which index does it affect?
- How many GSIs and LSIs can a single table have?
- What happens if a GSI's provisioned write capacity is exhausted?
MCQ Practice
1. Which statement about a Local Secondary Index (LSI) is true?
An LSI reuses the table's partition key and adds an alternate sort key; it also supports strongly consistent reads.
2. Which consistency does a Global Secondary Index support for reads?
GSIs replicate asynchronously from the base table, so they only support eventually consistent reads.
3. When must a Local Secondary Index be created?
LSIs can only be defined at table creation time; GSIs can be added or removed later.
Flash Cards
GSI key schema? — A new partition key plus optional sort key, independent of the base table's key.
LSI key schema? — Same partition key as the table, plus a different sort key.
GSI vs LSI consistency? — GSI: eventual reads only. LSI: supports strongly consistent reads.
When is each created? — LSI only at table creation; GSI can be added or dropped anytime.
Projection types? — KEYS_ONLY, INCLUDE (chosen attributes), or ALL.