Skip to content
Tutorials Center
My learning

Topics

Lesson 19 of 37 ยท Queries, Scans and Indexes

Local Secondary Indexes (LSI)

Learn local secondary indexes: same partition key, alternate sort key, strong consistency, create-at-table-time rule, 10 GB collection limit, and how an LSI compares with a GSI.

A local secondary index (LSI) gives you a second sort order within each partition key. The table sorts a customer's orders by order date. An LSI sorts the same orders by total, or by status, or by delivery date. The partition key stays the same, which is the "local" part: the index lives next to the data it indexes, inside the same partition.

LSIs are a narrower tool than global secondary indexes, with a rule that catches many people out: you can only create an LSI when you create the table. This lesson shows what they offer, where they fit and why many teams reach for a GSI instead.

Properties of an LSI

  • Same partition key as the base table, but a different sort key. The table must have a composite key to have an LSI.
  • Created only at table creation time. It cannot be added or removed later (you must recreate the table).
  • At most 5 per table.
  • Supports strongly consistent reads (ConsistentRead=true), because the index is updated together with the base item, in the same partition.
  • Can fetch attributes from the base table that were not projected, at an extra read cost. GSIs cannot do this.
  • Shares the table's throughput: no separate capacity setting. Reads and writes on an LSI consume the table's capacity.
  • Index keys: the LSI sort key must be a scalar string, number or binary attribute.
  • Adds an item collection size limit of 10 GB per partition key value, counting the table items and all their LSI entries together. Writes to a full collection fail with ItemCollectionSizeLimitExceededException.
flowchart TB
    subgraph PK["Partition key: CUSTOMER#amina"]
    direction LR
    T["Table sorted by SK:<br/>ORDER#2026-02-11, ORDER#2026-03-01, ORDER#2026-03-14"]
    L["LSI sorted by Total:<br/>8, 12, 35"]
    end

Same items, two sort orders inside one partition key.

Creating a table with an LSI

Terminal
aws dynamodb create-table \
  --table-name Orders \
  --attribute-definitions \
      AttributeName=CustomerId,AttributeType=S \
      AttributeName=OrderId,AttributeType=S \
      AttributeName=Total,AttributeType=N \
  --key-schema \
      AttributeName=CustomerId,KeyType=HASH \
      AttributeName=OrderId,KeyType=RANGE \
  --local-secondary-indexes '[{
    "IndexName": "ByTotal",
    "KeySchema": [
      {"AttributeName": "CustomerId", "KeyType": "HASH"},
      {"AttributeName": "Total", "KeyType": "RANGE"}
    ],
    "Projection": {"ProjectionType": "ALL"}
  }]' \
  --billing-mode PAY_PER_REQUEST

Querying it looks like any query, with IndexName:

Terminal
aws dynamodb query --table-name Orders --index-name ByTotal \
  --key-condition-expression "CustomerId = :c AND Total >= :min" \
  --expression-attribute-values '{":c": {"S": "amina"}, ":min": {"N": "20"}}' \
  --no-scan-index-forward --limit 1

That finds Amina's most expensive order: the partition key is still CustomerId, and the sort key is now Total.

A model of an LSI

This stand-in stores items under a partition key and maintains a second sort order. The index answers range and top-N questions on Total without scanning.

Python Stand-in: a second sort order inside a partition

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Strong consistency and base-table fetches

Because an LSI lives in the same partition as its base items, DynamoDB can update both in one step, so a strongly consistent read of the index sees the latest write. If a query asks for an attribute that is not projected, DynamoDB fetches it from the base table, charging an extra read per item. A GSI would not return it at all.

Python Stand-in: unprojected attributes cost an extra fetch

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

LSI or GSI?

Question LSI GSI
Same partition key as the table? Yes, required No, any key
Create after the table exists? No Yes
Strongly consistent reads? Yes No
Own throughput? No, shares the table's Yes
Fetch unprojected attributes? Yes, from the table No
Item collection size limit? 10 GB per partition key None
Limit per table 5 20 by default
Cost impact Extra storage and writes Extra storage, writes and capacity

Guidance: use an LSI when you need an alternate sort order within a partition key, need strong consistency on the index, you know it from day one, and no collection will approach 10 GB. In most other cases a GSI is more flexible. Many teams use GSIs exclusively because the table-time-only rule and the 10 GB cap are hard to live with.

The 10 GB item collection limit is permanent once an LSI exists. If one partition key (a busy tenant, a popular product) can grow beyond that, do not use an LSI on that table.

Common mistakes

  • Planning an LSI "for later". It cannot be added to an existing table.
  • Ignoring the 10 GB cap on a collection that grows without bound.
  • Expecting a different partition key. For that you need a GSI.
  • Projecting ALL by habit, doubling storage and write cost.
  • Forgetting the extra read cost of base-table fetches for unprojected attributes.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. When can a local secondary index be created?

    LSIs must be defined at table creation. Adding one later means creating a new table.
  2. 2. What is the key difference in the key schema of an LSI?

    An LSI re-sorts the items inside each partition key using another attribute.
  3. 3. Which read option does an LSI support that a GSI does not?

    Since an LSI is co-located with the base item, it can be read with strong consistency.
  4. 4. What limit does an LSI introduce?

    Table items plus their LSI entries sharing one partition key can total at most 10 GB.

Summary

  • An LSI gives each partition key an alternate sort order and must be created with the table.
  • It supports strongly consistent reads and can fetch unprojected attributes from the table.
  • It shares the table's capacity and caps each item collection at 10 GB.
  • At most 5 LSIs per table; GSIs are the more flexible choice in most designs.
  • Choose it for a known, bounded, strongly consistent alternate sort order.

Next: Design From Access Patterns