Secondary indexes (GSI, LSI) in AWS - Commands & Configuration

Introduction

When you store data in a database, sometimes you want to find items quickly using different keys. Secondary indexes let you do that by creating extra ways to look up data without scanning everything.

When you want to query a DynamoDB table by an attribute other than the main key.

When you need to sort or filter data differently without changing the main table structure.

When you want to improve read performance for specific queries.

When your application requires multiple access patterns on the same data.

When you want to add flexibility to your database queries without duplicating data.

Config File - main.tf

main.tf

provider "aws" {
  region = "us-east-1"
}

resource "aws_dynamodb_table" "example" {
  name           = "example-table"
  billing_mode   = "PAY_PER_REQUEST"
  hash_key       = "UserId"
  range_key      = "OrderId"

  attribute {
    name = "UserId"
    type = "S"
  }

  attribute {
    name = "OrderId"
    type = "S"
  }

  attribute {
    name = "Status"
    type = "S"
  }

  attribute {
    name = "CreatedAt"
    type = "S"
  }

  global_secondary_index {
    name               = "StatusIndex"
    hash_key           = "Status"
    range_key          = "CreatedAt"
    projection_type    = "ALL"
  }

  local_secondary_index {
    name               = "OrderDateIndex"
    range_key          = "CreatedAt"
    projection_type    = "ALL"
  }
}

This Terraform file creates a DynamoDB table named example-table with a primary key composed of UserId (hash key) and OrderId (range key).

It defines attributes used in the table and indexes.

The Global Secondary Index (GSI) named StatusIndex lets you query items by Status and sort by CreatedAt.

The Local Secondary Index (LSI) named OrderDateIndex lets you query items with the same UserId but sorted by CreatedAt.

Both indexes project all attributes for flexible queries.

Commands

This command initializes Terraform in the current directory, downloading necessary plugins and preparing to create resources.

Terminal

terraform init

Expected Output

Initializing the backend...



Initializing provider plugins...

- Finding latest version of hashicorp/aws...

- Installing hashicorp/aws v4.0.0...

- Installed hashicorp/aws v4.0.0 (signed by HashiCorp)



Terraform has been successfully initialized!



You may now begin working with Terraform. Try running "terraform plan" to see any changes required for your infrastructure.

This command applies the Terraform configuration, creating the DynamoDB table and its secondary indexes automatically without asking for confirmation.

Terminal

terraform apply -auto-approve

Expected Output

aws_dynamodb_table.example: Creating...

aws_dynamodb_table.example: Creation complete after 20s [id=example-table]



Apply complete! Resources: 1 added, 0 changed, 0 destroyed.

→

-auto-approve - Skips interactive approval before applying changes

This command checks the details of the created DynamoDB table, including its primary key and secondary indexes.

Terminal

aws dynamodb describe-table --table-name example-table

Expected Output

{

    "Table": {

        "TableName": "example-table",

        "KeySchema": [

            {"AttributeName": "UserId", "KeyType": "HASH"},

            {"AttributeName": "OrderId", "KeyType": "RANGE"}

        ],

        "AttributeDefinitions": [

            {"AttributeName": "UserId", "AttributeType": "S"},

            {"AttributeName": "OrderId", "AttributeType": "S"},

            {"AttributeName": "Status", "AttributeType": "S"},

            {"AttributeName": "CreatedAt", "AttributeType": "S"}

        ],

        "GlobalSecondaryIndexes": [

            {

                "IndexName": "StatusIndex",

                "KeySchema": [

                    {"AttributeName": "Status", "KeyType": "HASH"},

                    {"AttributeName": "CreatedAt", "KeyType": "RANGE"}

                ],

                "Projection": {"ProjectionType": "ALL"}

            }

        ],

        "LocalSecondaryIndexes": [

            {

                "IndexName": "OrderDateIndex",

                "KeySchema": [

                    {"AttributeName": "UserId", "KeyType": "HASH"},

                    {"AttributeName": "CreatedAt", "KeyType": "RANGE"}

                ],

                "Projection": {"ProjectionType": "ALL"}

            }

        ]

    }

}

→

--table-name - Specifies the DynamoDB table to describe

Key Concept

If you remember nothing else from this pattern, remember: secondary indexes let you quickly find data using different keys without changing your main table design.

Common Mistakes

Trying to create a Local Secondary Index (LSI) without specifying the same hash key as the main table.

LSIs must use the same partition key as the main table; otherwise, DynamoDB rejects the index.

Always use the main table's hash key for LSIs and only change the sort key.

Using a Global Secondary Index (GSI) with attributes not defined in the table's attribute definitions.

DynamoDB requires all index key attributes to be defined in the table's attribute definitions.

Add all GSI key attributes to the attribute definitions section.

Not projecting the needed attributes in the index, causing queries to miss data.

If attributes are not projected, queries on the index cannot return them, leading to incomplete results.

Use projection_type ALL or specify needed attributes explicitly.

Summary

Use Terraform to define a DynamoDB table with primary keys and secondary indexes.

Apply the configuration to create the table and indexes in AWS.

Verify the table and indexes exist and have the correct keys using AWS CLI.

Practice

1. What is the main difference between a Local Secondary Index (LSI) and a Global Secondary Index (GSI) in DynamoDB?

easy

A. LSI can have a different partition key, but GSI must use the main table's partition key.

B. LSI uses the same partition key as the main table, but GSI can use a different partition key.

C. LSI and GSI both require the same partition and sort keys as the main table.

D. LSI is only for read operations, while GSI is only for write operations.

Solution

1. Step 1: Understand LSI key structure

   LSI shares the same partition key as the main table but allows a different sort key.

2. Step 2: Understand GSI key structure

   GSI can have a completely different partition key and sort key from the main table.

3. Final Answer:

   LSI uses the same partition key as the main table, but GSI can use a different partition key. -> Option B

4. Quick Check:

   LSI shares partition key, GSI can differ [OK]

Hint: LSI shares partition key; GSI can change both keys [OK]

Common Mistakes:

• Thinking LSI can have a different partition key
• Assuming GSI must use main table keys
• Confusing read/write capabilities of indexes

2. Which of the following is the correct syntax to define a Global Secondary Index (GSI) in a DynamoDB table using AWS CloudFormation?

easy

A. "GlobalSecondaryIndexes": [{"IndexName": "GSI1", "KeySchema": [{"AttributeName": "UserId", "KeyType": "HASH"}], "Projection": {"ProjectionType": "KEYS_ONLY"}}]

B. "LocalSecondaryIndexes": [{"IndexName": "GSI1", "KeySchema": [{"AttributeName": "UserId", "KeyType": "HASH"}], "Projection": {"ProjectionType": "ALL"}}]

C. "GlobalSecondaryIndexes": [{"IndexName": "GSI1", "KeySchema": [{"AttributeName": "UserId", "KeyType": "RANGE"}], "Projection": {"ProjectionType": "ALL"}}]

D. "GlobalSecondaryIndexes": [{"IndexName": "GSI1", "KeySchema": [{"AttributeName": "UserId", "KeyType": "HASH"}], "Projection": {"ProjectionType": "ALL"}}]

Solution

1. Step 1: Identify correct index type and key schema

   GlobalSecondaryIndexes is the correct property for GSI, and the partition key must have KeyType "HASH".

2. Step 2: Check projection type and syntax

   ProjectionType "ALL" is valid and commonly used; syntax matches CloudFormation format.

3. Final Answer:

   "GlobalSecondaryIndexes": [{"IndexName": "GSI1", "KeySchema": [{"AttributeName": "UserId", "KeyType": "HASH"}], "Projection": {"ProjectionType": "ALL"}}] -> Option D

4. Quick Check:

   GSI uses GlobalSecondaryIndexes with HASH key [OK]

Hint: GSI uses GlobalSecondaryIndexes with HASH key [OK]

Common Mistakes:

• Using LocalSecondaryIndexes for GSI
• Setting KeyType to RANGE for partition key
• Incorrect projection types or syntax errors

3. Given a DynamoDB table with a Global Secondary Index (GSI) on attribute Category as partition key and Price as sort key, what will the following query return?

Query on GSI where Category = 'Books' and Price between 10 and 20

medium

A. All items with Category 'Books' and Price between 10 and 20

B. All items with Price between 10 and 20 regardless of Category

C. All items with Category 'Books' regardless of Price

D. An error because Price cannot be used in query conditions

Solution

1. Step 1: Understand GSI query conditions

   Query on GSI requires specifying the partition key (Category = 'Books') and can filter by sort key range (Price between 10 and 20).

2. Step 2: Interpret the query result

   The query returns all items matching Category 'Books' with Price in the specified range.

3. Final Answer:

   All items with Category 'Books' and Price between 10 and 20 -> Option A

4. Quick Check:

   GSI query filters partition and sort keys correctly [OK]

Hint: Query GSI with partition key + sort key range filters [OK]

Common Mistakes:

• Ignoring partition key in query
• Thinking sort key can't be filtered
• Expecting error on valid query

4. You created a Local Secondary Index (LSI) on a DynamoDB table but your query on the LSI returns no results even though matching items exist. What is the most likely cause?

medium

A. The LSI was created with a different partition key than the main table.

B. The query used a filter expression instead of a key condition.

C. The query did not specify the same partition key as the main table.

D. LSIs do not support queries, only scans.

Solution

1. Step 1: Recall LSI key requirements

   LSI shares the same partition key as the main table, so queries must specify that partition key.

2. Step 2: Analyze query behavior

   If the query does not specify the partition key, no results will be returned because partition key is mandatory for queries on LSI.

3. Final Answer:

   The query did not specify the same partition key as the main table. -> Option C

4. Quick Check:

   LSI queries require main table partition key [OK]

Hint: LSI queries must include main table partition key [OK]

Common Mistakes:

• Assuming LSI can have different partition key
• Using filter instead of key condition for partition key
• Believing LSI does not support queries

5. You have a DynamoDB table with a partition key UserId and sort key OrderDate. You want to efficiently query all orders by Status and sort them by OrderAmount. Which secondary index should you create and how?

hard

A. Create a Global Secondary Index with partition key Status and sort key OrderAmount.

B. Create a Local Secondary Index with partition key UserId and sort key Status.

C. Create a Global Secondary Index with partition key UserId and sort key Status.

D. Create a Local Secondary Index with partition key Status and sort key OrderAmount.

Solution

1. Step 1: Identify query requirements

   You want to query by Status (different from main partition key) and sort by OrderAmount.

2. Step 2: Choose index type and keys

   Since Status is different from main partition key, use a Global Secondary Index with Status as partition key and OrderAmount as sort key.

3. Final Answer:

   Create a Global Secondary Index with partition key Status and sort key OrderAmount. -> Option A

4. Quick Check:

   GSI allows different partition and sort keys [OK]

Hint: Use GSI when partition key differs from main table [OK]

Common Mistakes:

• Trying to use LSI with different partition key
• Swapping partition and sort keys incorrectly
• Choosing wrong index type for query pattern