Local Secondary Index (LSI) concept in DynamoDB - Mini Project: Build & Apply

Create and Query a Local Secondary Index (LSI) in DynamoDB

📖 Scenario: You are building a simple product catalog for a small online store. Each product has a unique ProductID and belongs to a Category. You want to store product details and be able to quickly find products by category and sort them by price.

🎯 Goal: Create a DynamoDB table with a Local Secondary Index (LSI) to allow querying products by Category and sorting them by Price. Then write a query to get all products in a specific category sorted by price.

📋 What You'll Learn

Create a DynamoDB table named Products with Category as the partition key and ProductID as the sort key.

Add a Local Secondary Index named CategoryPriceIndex with Category as the partition key and Price as the sort key.

Write a query to get all products in the category Electronics sorted by Price using the LSI.

💡 Why This Matters

🌍 Real World

Online stores often need to find products by category and sort them by price or rating quickly. Using LSIs in DynamoDB helps achieve this efficiently.

💼 Career

Understanding LSIs is important for backend developers and database engineers working with DynamoDB to optimize queries and data access patterns.

1

Create the DynamoDB table with primary keys

Create a DynamoDB table named Products with Category as the partition key and ProductID as the sort key. Define the attribute definitions for Category and ProductID.

DynamoDB

# Your code here to create the Products table with Category as partition key and ProductID as sort key

Hint

Use AttributeDefinitions to define Category and ProductID. Set Category as the partition key (HASH) and ProductID as the sort key (RANGE).

2

Add a Local Secondary Index (LSI) for sorting by Price

Add a Local Secondary Index named CategoryPriceIndex to the Products table. Use Category as the partition key and Price as the sort key. Define the attribute Price as a number type in the attribute definitions.

DynamoDB

CreateTable {
  TableName: "Products",
  AttributeDefinitions: [
    { AttributeName: "Category", AttributeType: "S" },
    { AttributeName: "ProductID", AttributeType: "S" },
    { AttributeName: "Price", AttributeType: "N" }  # Add Price attribute definition
  ],
  KeySchema: [
    { AttributeName: "Category", KeyType: "HASH" },
    { AttributeName: "ProductID", KeyType: "RANGE" }
  ],
  LocalSecondaryIndexes: [  # Add LSI here
    {
      IndexName: "CategoryPriceIndex",
      KeySchema: [
        { AttributeName: "Category", KeyType: "HASH" },
        { AttributeName: "Price", KeyType: "RANGE" }
      ],
      Projection: { ProjectionType: "ALL" }
    }
  ],
  BillingMode: "PROVISIONED",
  ProvisionedThroughput: {  # Required for LSI
    ReadCapacityUnits: 5,
    WriteCapacityUnits: 5
  }
}

Hint

Remember that the LSI uses the same partition key Category but a different sort key Price. Add Price to AttributeDefinitions as type N (number).

3

Write a query to get products in Electronics category sorted by Price

Write a DynamoDB query to get all items from the Products table where Category equals Electronics. Use the CategoryPriceIndex Local Secondary Index to sort the results by Price in ascending order.

DynamoDB

query_params = {
  TableName: "Products",
  IndexName: "CategoryPriceIndex",  # Use the LSI
  KeyConditionExpression: "Category = :cat",  # Filter by Category
  ExpressionAttributeValues: {
    ":cat": {"S": "Electronics"}  # Set category value
  },
  ScanIndexForward: True  # Sort ascending by Price
}
# Your code here to define the query parameters

Hint

Use IndexName to specify the LSI. Use KeyConditionExpression to filter by Category. Set ScanIndexForward to True to sort ascending by Price.

4

Complete the query execution setup

Add the final code line to execute the query using a DynamoDB client method called query with the query_params. Assign the result to a variable named response.

DynamoDB

response = dynamodb.query(query_params)  # Execute the query
# Your code here to assign the query result

Hint

Use the DynamoDB client method query with query_params and assign the result to response.

Practice

1. What is the main purpose of a Local Secondary Index (LSI) in DynamoDB?

easy

A. To store data in a separate table

B. To allow querying data with the same partition key but different sort keys

C. To create a new partition key for faster queries

D. To backup data automatically

Solution

1. Step 1: Understand LSI functionality

   LSI lets you query items with the same partition key but sorted by an alternate sort key.

2. Step 2: Compare options

   Only To allow querying data with the same partition key but different sort keys correctly describes this feature; others describe unrelated features.

3. Final Answer:

   To allow querying data with the same partition key but different sort keys -> Option B

4. Quick Check:

   LSI = alternate sort key with same partition key [OK]

Hint: LSI changes sort key, not partition key [OK]

Common Mistakes:

• Thinking LSI creates new partition keys
• Confusing LSI with Global Secondary Index
• Believing LSI stores data separately

2. Which of the following is the correct way to define a Local Secondary Index (LSI) when creating a DynamoDB table?

easy

A. Define the LSI with a different partition key than the main table

B. Define the LSI with a new partition key and no sort key

C. Define the LSI after the table is created using updateTable API

D. Define the LSI with the same partition key but a different sort key

Solution

1. Step 1: Recall LSI definition rules

   LSIs must use the same partition key as the main table but can have a different sort key.

2. Step 2: Check options

   Only Define the LSI with the same partition key but a different sort key matches this rule; others violate partition key or timing constraints.

3. Final Answer:

   Define the LSI with the same partition key but a different sort key -> Option D

4. Quick Check:

   LSI = same partition key + different sort key [OK]

Hint: LSI shares partition key, differs in sort key [OK]

Common Mistakes:

• Trying to create LSI after table creation
• Using different partition keys for LSI
• Omitting sort key in LSI definition

3. Given a DynamoDB table with partition key UserID and sort key Timestamp, and an LSI with sort key OrderStatus, what will the following query return?

Query: KeyConditionExpression = UserID = '123' AND OrderStatus = 'Pending'

medium

A. All items with OrderStatus 'Pending' regardless of UserID

B. All items with UserID '123' sorted by Timestamp

C. All items with UserID '123' and OrderStatus 'Pending' sorted by OrderStatus

D. Syntax error because OrderStatus cannot be used in KeyConditionExpression

Solution

1. Step 1: Understand query with LSI

   Query on LSI uses same partition key but different sort key (OrderStatus). The query filters UserID = '123' and OrderStatus = 'Pending'.

2. Step 2: Analyze query result

   It returns items with UserID '123' and OrderStatus 'Pending', sorted by OrderStatus.

3. Final Answer:

   All items with UserID '123' and OrderStatus 'Pending' sorted by OrderStatus -> Option C

4. Quick Check:

   Query on LSI filters by partition and LSI sort key [OK]

Hint: LSI query filters by partition key + LSI sort key [OK]

Common Mistakes:

• Assuming LSI changes partition key
• Thinking query returns all items with OrderStatus only
• Believing syntax error occurs using LSI sort key

4. You created a DynamoDB table with an LSI but your query using the LSI's sort key returns no results even though matching items exist. What is the most likely cause?

medium

A. The partition key value in the query is incorrect

B. You used the LSI's partition key instead of the main table's partition key

C. The LSI's sort key attribute is missing in some items

D. The LSI was not defined at table creation time

Solution

1. Step 1: Check LSI query requirements

   Queries on LSI require the correct partition key value; wrong partition key returns no results.

2. Step 2: Evaluate options

   The partition key value in the query is incorrect explains no results due to incorrect partition key; other options are less likely or invalid.

3. Final Answer:

   The partition key value in the query is incorrect -> Option A

4. Quick Check:

   Correct partition key needed for LSI query [OK]

Hint: Always verify partition key value in LSI queries [OK]

Common Mistakes:

• Trying to add LSI after table creation
• Ignoring missing sort key attributes
• Confusing LSI partition key with main table partition key

5. You have a DynamoDB table with partition key CustomerID and sort key OrderDate. You want to query all orders for a customer sorted by OrderStatus instead of OrderDate. How should you design the Local Secondary Index (LSI) to achieve this?

hard

A. Create an LSI with partition key CustomerID and sort key OrderStatus defined at table creation

B. Create an LSI with partition key OrderStatus and sort key OrderDate after table creation

C. Create a Global Secondary Index (GSI) with partition key CustomerID and sort key OrderStatus

D. Add OrderStatus as a new partition key in the main table

Solution

1. Step 1: Understand LSI design rules

   LSI must use the same partition key as the main table but can have a different sort key.

2. Step 2: Apply to problem

   To sort by OrderStatus for the same CustomerID, create an LSI with partition key CustomerID and sort key OrderStatus at table creation.

3. Step 3: Eliminate wrong options

   Create an LSI with partition key OrderStatus and sort key OrderDate after table creation is invalid because LSI cannot be created after table creation; Create a Global Secondary Index (GSI) with partition key CustomerID and sort key OrderStatus is about GSI, not LSI; Add OrderStatus as a new partition key in the main table changes partition key, which is not allowed.

4. Final Answer:

   Create an LSI with partition key CustomerID and sort key OrderStatus defined at table creation -> Option A

5. Quick Check:

   LSI = same partition key + new sort key at creation [OK]

Hint: LSI must be defined at table creation with same partition key [OK]

Common Mistakes:

• Trying to create LSI after table creation
• Confusing LSI with GSI
• Changing partition key in LSI