DynamoDB Local Secondary Index

By default, you can only query a DynamoDB table using its primary key. A Local Secondary Index (LSI) gives you an additional way to query items within the same partition — using a different sort key. It is called "local" because it shares the same partition key as the base table.

The Problem LSI Solves

Suppose you have an Orders table with UserID as the partition key and OrderDate as the sort key. You can query all orders for a user sorted by date. But your application also needs to query orders for a user sorted by Amount.

Without an LSI, retrieving a user's orders by amount requires fetching all their orders and sorting them in your application code. With an LSI on Amount, DynamoDB can do this directly.

What an LSI Is

An LSI is an alternate sort key for an existing partition. Think of the base table as a book with chapters sorted by date. The LSI is the same book but with chapters reorganized by amount — both use the same table of contents (partition key).

Structure of an LSI

Base Table:
  Partition Key: UserID
  Sort Key:      OrderDate

LSI (named: AmountIndex):
  Partition Key: UserID        ← Same as base table (mandatory)
  Sort Key:      Amount        ← Different — this is what the LSI changes

Creating an LSI

You must create all LSIs at table creation time. You cannot add an LSI to an existing table. This is a hard limitation — plan your LSIs before you create the table.

Each table can have a maximum of 5 LSIs.

Example: Orders Table with LSI

TABLE: Orders
  Partition Key: UserID
  Sort Key:      OrderDate
  LSI:           AmountIndex (Sort Key: Amount)

Data:
┌──────────┬────────────┬──────────┬────────┬───────────┐
│ UserID   │ OrderDate  │ Product  │ Amount │ Status    │
├──────────┼────────────┼──────────┼────────┼───────────┤
│ U001     │ 2024-01-05 │ Laptop   │ 85000  │ Delivered │
│ U001     │ 2024-03-12 │ Mouse    │  1200  │ Delivered │
│ U001     │ 2024-06-20 │ Monitor  │ 22000  │ Pending   │
└──────────┴────────────┴──────────┴────────┴───────────┘

Query using AmountIndex:
  UserID = "U001", sort by Amount ascending

Result:
┌──────────┬──────────┬────────┐
│ UserID   │ Product  │ Amount │
├──────────┼──────────┼────────┤
│ U001     │ Mouse    │  1200  │
│ U001     │ Monitor  │ 22000  │
│ U001     │ Laptop   │ 85000  │
└──────────┴──────────┴────────┘

Projected Attributes

When you create an LSI, you decide which attributes it stores alongside the key. This is called projection. You have three options:

KEYS_ONLY

The index stores only the partition key, sort key, and LSI sort key. To retrieve other attributes, DynamoDB automatically fetches them from the base table. This uses less index storage but requires extra reads for non-key attributes.

INCLUDE

The index stores key attributes plus the specific attributes you name. Use this when you always query specific fields alongside the keys.

ALL

The index stores all attributes from the base table item. This uses the most storage but eliminates any need to fetch from the base table. Ideal when you frequently read many attributes from the index.

Storage and Capacity

An LSI shares the read and write capacity of its base table. It does not have its own separate capacity settings. This is a key difference from Global Secondary Indexes (which have separate capacity).

Every item written to the base table that has the LSI's sort key attribute also creates an entry in the LSI. Items missing the LSI sort key attribute are simply not included in the index.

Item Collection Size Limit

This is one of the most important LSI constraints. All items in a table that share the same partition key value form an item collection. When an LSI exists, the total size of an item collection (base table + all LSIs combined) cannot exceed 10 GB.

Item Collection: UserID = "U001"
  Base Table items:   2.0 GB
  AmountIndex (LSI):  1.8 GB
  StatusIndex (LSI):  1.8 GB
  Total:              5.6 GB — within 10 GB limit ✓

If total exceeds 10 GB → DynamoDB rejects new writes for that partition key

For tables where a single partition key might accumulate huge amounts of data (like a very active user or a busy device), this limit can become a problem. Global Secondary Indexes do not have this restriction — they are a better choice in those cases.

LSI vs Base Table Query: Consistency

LSI reads support both eventually consistent and strongly consistent reads — the same as the base table. This is another advantage over GSIs, which only support eventual consistency.

When to Use an LSI

• You need to sort items in the same partition by a different attribute.
• You always know the partition key before querying.
• The total data per partition key will stay comfortably below 10 GB.
• You need strongly consistent reads on the index.
• You are planning the table from scratch (LSIs cannot be added later).

LSI vs GSI — Quick Comparison

Summary

An LSI gives you a second sort key for querying items within a partition. It is powerful for tables where you know the partition key but need to sort or filter by different attributes. Plan LSIs carefully at table creation time, and monitor your item collection sizes to stay within the 10 GB limit.