Local Secondary Index in DynamoDB: What It Is and How It Works

A Local Secondary Index (LSI) in DynamoDB is an index that uses the same partition key as the main table but allows a different sort key to enable alternative query patterns. It helps you efficiently query data with multiple sort keys without duplicating the partition key.

How It Works

Imagine you have a big filing cabinet where each drawer is labeled with a category (partition key). Inside each drawer, you organize papers by date (sort key). A Local Secondary Index (LSI) lets you keep the same drawer labels but organize the papers inside by a different attribute, like priority or status, instead of date.

In DynamoDB, the LSI shares the partition key with the main table but uses a different sort key. This means you can quickly find items in the same partition but sorted or filtered by another attribute. The LSI is created when you create the table and cannot be added later.

This helps when you want to run queries that need different sorting or filtering on the same group of items without scanning the whole table.

Example

This example shows how to create a DynamoDB table with a Local Secondary Index and query it using AWS SDK for JavaScript (v3).

javascript

import { DynamoDBClient, CreateTableCommand, QueryCommand } from "@aws-sdk/client-dynamodb";

const client = new DynamoDBClient({ region: "us-east-1" });

async function createTable() {
  const params = {
    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" }  // For LSI
    ],
    LocalSecondaryIndexes: [
      {
        IndexName: "OrderDateIndex",
        KeySchema: [
          { AttributeName: "CustomerId", KeyType: "HASH" },
          { AttributeName: "OrderDate", KeyType: "RANGE" }
        ],
        Projection: { ProjectionType: "ALL" }
      }
    ],
    BillingMode: "PAY_PER_REQUEST"
  };

  await client.send(new CreateTableCommand(params));
  console.log("Table created with LSI");
}

async function queryByOrderDate(customerId, orderDate) {
  const params = {
    TableName: "Orders",
    IndexName: "OrderDateIndex",
    KeyConditionExpression: "CustomerId = :cid AND OrderDate = :od",
    ExpressionAttributeValues: {
      ":cid": { S: customerId },
      ":od": { S: orderDate }
    }
  };

  const data = await client.send(new QueryCommand(params));
  console.log("Query result:", JSON.stringify(data.Items, null, 2));
}

// Usage example (uncomment to run):
// createTable();
// queryByOrderDate("C123", "2024-06-01");

Output

Table created with LSI
Query result: [ /* items matching CustomerId and OrderDate */ ]

When to Use

Use a Local Secondary Index when you want to query items in the same partition but need to sort or filter them by a different attribute than the main sort key. This is helpful when you have multiple ways to organize or view related data.

For example, in an e-commerce app, you might store orders by customer ID and order ID but want to query orders by customer ID and order date or status. An LSI lets you do this efficiently without scanning the whole table.

Remember, LSIs must be defined when creating the table and share the partition key with the main table.

Key Points

• An LSI uses the same partition key as the main table but a different sort key.
• It must be created when the table is created; you cannot add it later.
• LSIs help run efficient queries with alternative sorting or filtering on the same partition.
• They share the same storage and throughput as the main table.
• Useful for multiple query patterns on related data grouped by the same partition key.

Key Takeaways

A Local Secondary Index lets you query the same partition with a different sort key for flexible data access.

LSIs must be defined when creating the DynamoDB table and cannot be added afterward.

Use LSIs to support multiple query patterns without scanning the entire table.

LSIs share the partition key with the main table but have their own sort key.

They are ideal for applications needing alternative sorting or filtering within the same data group.