DynamodbHow-ToBeginner · 4 min read

How to Create Local Secondary Index in DynamoDB

To create a Local Secondary Index (LSI) in DynamoDB, define it when creating the table by specifying an alternate sort key with the same partition key. You cannot add an LSI after table creation; it must be included in the CreateTable API call.

📐

Syntax

A Local Secondary Index is defined during table creation using the CreateTable API. It requires specifying the same partition key as the base table but a different sort key. The key parts are:

IndexName: Name of the LSI.
KeySchema: Partition key (same as table) and alternate sort key.
Projection: Attributes to include in the index.

json

CreateTable {
  TableName: string,
  KeySchema: [
    { AttributeName: string, KeyType: 'HASH' },
    { AttributeName: string, KeyType: 'RANGE' }
  ],
  AttributeDefinitions: [
    { AttributeName: string, AttributeType: 'S' | 'N' | 'B' },
    ...
  ],
  LocalSecondaryIndexes: [
    {
      IndexName: string,
      KeySchema: [
        { AttributeName: string, KeyType: 'HASH' },
        { AttributeName: string, KeyType: 'RANGE' }
      ],
      Projection: {
        ProjectionType: 'ALL' | 'KEYS_ONLY' | 'INCLUDE',
        NonKeyAttributes?: [string]
      }
    }
  ],
  BillingMode: 'PAY_PER_REQUEST' | 'PROVISIONED',
  ...
}

💻

Example

This example creates a DynamoDB table named Music with a Local Secondary Index called AlbumTitleIndex. The table's partition key is Artist, and the sort key is SongTitle. The LSI uses the same partition key Artist but a different sort key AlbumTitle. The index projects all attributes.

json

{
  "TableName": "Music",
  "KeySchema": [
    { "AttributeName": "Artist", "KeyType": "HASH" },
    { "AttributeName": "SongTitle", "KeyType": "RANGE" }
  ],
  "AttributeDefinitions": [
    { "AttributeName": "Artist", "AttributeType": "S" },
    { "AttributeName": "SongTitle", "AttributeType": "S" },
    { "AttributeName": "AlbumTitle", "AttributeType": "S" }
  ],
  "LocalSecondaryIndexes": [
    {
      "IndexName": "AlbumTitleIndex",
      "KeySchema": [
        { "AttributeName": "Artist", "KeyType": "HASH" },
        { "AttributeName": "AlbumTitle", "KeyType": "RANGE" }
      ],
      "Projection": {
        "ProjectionType": "ALL"
      }
    }
  ],
  "BillingMode": "PAY_PER_REQUEST"
}

Output

Table 'Music' created with Local Secondary Index 'AlbumTitleIndex'.

⚠️

Common Pitfalls

Adding LSI after table creation: You cannot add a Local Secondary Index after the table is created; it must be defined at creation.
Partition key mismatch: The LSI must use the same partition key as the base table.
Attribute definitions missing: All attributes used in keys must be defined in AttributeDefinitions.
Projection misunderstanding: Choose the right projection type to optimize read performance and storage.

json

/* Wrong: Trying to add LSI after table creation (not allowed) */
UpdateTable {
  TableName: "Music"
  /* LocalSecondaryIndexes cannot be added here */
}

/* Right: Define LSI during CreateTable call as shown in the example above */

📊

Quick Reference

Property	Description
IndexName	Name of the Local Secondary Index
KeySchema	Partition key (same as table) and alternate sort key
Projection	Attributes included in the index (ALL, KEYS_ONLY, INCLUDE)
AttributeDefinitions	Defines data types of all key attributes
Defined at table creation	LSI must be created with the table, cannot be added later

✅

Key Takeaways

Local Secondary Indexes must be defined when creating the DynamoDB table.

LSI uses the same partition key as the base table but a different sort key.

You cannot add or modify LSIs after table creation.

All key attributes must be declared in AttributeDefinitions.

Choose the right projection type to balance performance and storage.