DynamoDB LSI vs GSI: Choosing the Right Index for Your Query Pattern

DynamoDB’s core design principle is that you should know your access patterns before you design your table. The primary key handles the most common access pattern efficiently. But almost every application has more than one way it needs to query data, and that is where Local Secondary Indexes (LSI) and Global Secondary Indexes (GSI) come in.

Both types of index create an alternate view of the data that enables queries on non-primary-key attributes. The decision between them comes down to whether you need to query within a partition or across partitions, and whether you know your schema at table creation time.

What Makes an Index “Local” vs “Global”

The names reflect the scope of the index:

Local Secondary Index: the index is local to a partition. It must use the same partition key as the base table and provides an alternate sort key. Every query through an LSI must specify a partition key value — you are asking “within customer C1234’s data, sort by amount instead of date.”

Global Secondary Index: the index is global — it spans all partitions. It can use any attribute as its partition key and any attribute as its sort key. A query on a GSI does not need to specify the base table’s partition key at all.

  LSI vs GSI Scope
  =================

  Base Table: Orders
  PK = CustomerId, SK = OrderDate

  ┌──────────────────────────────────────────────────────────┐
  │ Partition for CustomerId = C1234                         │
  │   Item: C1234 | 2025-01-15 | Amount=$89  | Status=done  │
  │   Item: C1234 | 2025-03-22 | Amount=$245 | Status=pending│
  │   Item: C1234 | 2025-06-01 | Amount=$12  | Status=done  │
  └──────────────────────────────────────────────────────────┘
         │
         LSI (Amount as sort key)
         can only search WITHIN this partition
         "Give me C1234's orders sorted by Amount" ✓
         "Give me all pending orders regardless of customer" ✗

  ┌──────────────────────────────────────────────────────────┐
  │ GSI: Status as PK, OrderDate as SK                       │
  │ Partition for Status = pending                           │
  │   Item: C1234 | 2025-03-22 | $245 | pending             │
  │   Item: C9999 | 2025-05-10 | $500 | pending             │
  │   Item: C7777 | 2025-06-14 | $75  | pending             │
  └──────────────────────────────────────────────────────────┘
         │
         GSI can search ACROSS all customers
         "Give me all pending orders sorted by date" ✓

Local Secondary Index: Details and Constraints

An LSI must be defined at table creation — you cannot add one later without creating a new table and migrating data. This is the most significant constraint.

• Uses the same partition key as the base table
• Defines a different sort key (any non-key attribute)
• Maximum of 5 LSIs per table
• Data is stored in the same partitions as the base table
• Reads from an LSI consume the same RCU capacity as the base table
• Supports strongly consistent reads (unlike GSIs)
• Contributes to the 10 GB per partition limit (base table data + LSI data for that partition key)

  LSI Example: Orders Table
  ==========================

  Base table PK = CustomerId, SK = OrderDate
  LSI-1:     PK = CustomerId, SK = Amount
  LSI-2:     PK = CustomerId, SK = Status

  Queries enabled:
  - Find customer C1234's orders in ascending amount order
  - Find customer C1234's orders filtered by status "pending"

  Queries NOT enabled by these indexes:
  - Find all orders over $100 across all customers (needs GSI)
  - Find all orders placed on 2025-06-15 (needs GSI or scan)

Global Secondary Index: Details and Constraints

GSIs can be created at any time, added to existing tables without downtime, and deleted without affecting the base table.

• Separate partition key from the base table (any attribute)
• Optional separate sort key
• Maximum of 20 GSIs per table
• Has its own provisioned throughput (separate RCUs and WCUs from the base table)
• Data is asynchronously replicated from the base table — slight lag after writes
• Does not support strongly consistent reads
• Does not have the 10 GB per partition key limit (each GSI has its own partitions)
• Items without the GSI’s partition key attribute are not indexed (sparse index behavior)

  GSI Example: Same Orders Table
  ================================

  GSI-1: PK = Status, SK = OrderDate
  Enables: "Find all pending orders sorted by date"
  Query: Status = "pending", OrderDate > "2025-06-01"

  GSI-2: PK = ProductId, SK = OrderDate
  Enables: "Find all orders containing product P100, sorted by date"
  (ProductId must be an attribute on each item for this to work)

  Sparse index: if not all items have ProductId, only items with
  ProductId appear in this GSI. Useful for indexing a subset of items.

Comparison Table

  LSI vs GSI Side by Side
  ========================

  Attribute              | LSI                      | GSI
  -----------------------|--------------------------|------------------------
  Partition key          | Same as base table       | Any attribute
  Sort key               | Different from base table| Any attribute
  When created           | Table creation only      | Anytime
  Storage location       | With base table          | Separate partitions
  Throughput             | Shared with table        | Own RCU/WCU
  Consistent reads       | Yes (strong + eventual)  | Eventual only
  Max per table          | 5                        | 20
  10 GB partition limit  | Yes (shared with table)  | No
  Can delete             | No (would need new table)| Yes, without affecting table
  Good for               | Different sort within    | Cross-partition queries
                         | same user/entity         | Different access patterns

Choosing the Right Index

The decision tree:

1. Does the query always specify the base table’s partition key? → Consider LSI (same partition key, different sort key)
2. Does the query look across different partition key values? → Need GSI (different partition key)
3. Do you need strongly consistent reads from the index? → Must use LSI
4. Is the table already created and you forgot to plan this index? → Only GSI is available
5. Do you have a “sparse” access pattern (only some items have the indexed attribute)? → GSI handles this naturally

Real-World Use Case: Customer Support Tickets

A support system table:

• PK: CustomerId, SK: TicketId
• Primary use: fetch all tickets for a customer

Additional access patterns:

1. “Show me the 5 most recent tickets for customer C1234” → LSI with sort key = CreatedAt
2. “Show me all open tickets assigned to agent A456” → GSI with PK = AssignedAgent, SK = CreatedAt
3. “Show me all critical tickets regardless of customer or agent” → GSI with PK = Priority, SK = CreatedAt

The LSI must be defined at table creation. The GSIs can be added as new access patterns are discovered.

Key Interview Points

• LSI at creation time is non-negotiable — this is the most common reason to choose GSI over LSI; if you are adding an index to an existing table, you must use GSI
• GSI consistency: GSI reads are eventually consistent only — if a user writes and immediately reads from the GSI, they might not see the write
• Sparse indexes with GSI: items without the GSI partition key attribute do not appear in the GSI — useful for indexing only a subset of items efficiently
• Throughput for GSI: writes to the base table that include the GSI’s key attributes consume WCUs on both the table and the GSI — provision the GSI’s throughput accordingly
• LSI storage limit: all items sharing a partition key value (across the base table and all LSIs) must fit within 10 GB. This is rarely a problem for most partition key designs but can bite if one user has millions of items
• Hot GSI partitions: if your GSI uses an attribute with low cardinality as PK (like Status with only 3 values), all writes to “pending” status go to the same GSI partition — a hot partition problem. Choose higher-cardinality attributes or add a shard suffix to distribute.