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.