LSI vs GSI comparison in DynamoDB - Visual Side-by-Side Comparison

Concept Flow - LSI vs GSI comparison

Start: Table with Primary Key

↓

Add LSI?

Yes→LSI uses same Partition Key, different Sort Key

↓

Query uses Partition + Sort Key

↓

Add GSI?

Yes→GSI uses different Partition Key and Sort Key

↓

Query uses GSI keys

↓

Use base table keys

Shows decision flow for choosing Local Secondary Index (LSI) or Global Secondary Index (GSI) in DynamoDB based on key usage and query needs.

Execution Sample

DynamoDB

CREATE TABLE Music (
  Artist string,
  SongTitle string,
  AlbumTitle string,
  Year int,
  PRIMARY KEY (Artist, SongTitle),
  LOCAL SECONDARY INDEX AlbumIndex (Artist, AlbumTitle)
);

CREATE GLOBAL SECONDARY INDEX YearIndex ON Music (Year, SongTitle);

Defines a table with a primary key and adds an LSI on AlbumTitle and a GSI on Year.

Execution Table

Step	Action	Index Type	Partition Key Used	Sort Key Used	Query Capability
1	Create base table	None	Artist	SongTitle	Query by Artist and SongTitle
2	Add LSI AlbumIndex	LSI	Artist (same as base)	AlbumTitle (different)	Query by Artist and AlbumTitle
3	Add GSI YearIndex	GSI	Year (different)	SongTitle (different)	Query by Year and SongTitle
4	Query base table	None	Artist	SongTitle	Can only query by Artist and SongTitle
5	Query LSI AlbumIndex	LSI	Artist	AlbumTitle	Can query by Artist and AlbumTitle
6	Query GSI YearIndex	GSI	Year	SongTitle	Can query by Year and SongTitle
7	Attempt query with different Partition Key on LSI	LSI	Year	AlbumTitle	Not possible - LSI partition key fixed
8	Attempt query with different Partition Key on GSI	GSI	Year	SongTitle	Possible - GSI partition key flexible
9	Table size limit check	LSI	N/A	N/A	LSI shares table size limit (10GB)
10	Table size limit check	GSI	N/A	N/A	GSI has separate size limit, scales independently
11	End	N/A	N/A	N/A	Comparison complete

💡 All key differences between LSI and GSI demonstrated, including key usage and query capabilities.

Variable Tracker

Variable	Start	After Step 2	After Step 3	Final
Partition Key	Artist	Artist	Year (for GSI)	Year (for GSI)
Sort Key	SongTitle	AlbumTitle (LSI)	SongTitle (GSI)	SongTitle (GSI)
Query Capability	Artist+SongTitle	Artist+AlbumTitle	Year+SongTitle	Year+SongTitle
Size Limit	10GB (table)	10GB (table shared with LSI)	Separate limit for GSI	Separate limit for GSI

Key Moments - 3 Insights

Why can't we use a different partition key in an LSI?

Because LSI uses the same partition key as the base table, only the sort key changes (see execution_table row 2 and 7).

Can a GSI have a completely different partition key from the base table?

Yes, GSI allows a different partition key and sort key, enabling flexible queries (see execution_table row 3 and 6).

How does the size limit differ between LSI and GSI?

LSI shares the 10GB size limit with the base table, while GSI has its own separate size limit and scales independently (see execution_table rows 9 and 10).

Visual Quiz - 3 Questions

Look at the execution table, at which step is the LSI added and what partition key does it use?

A Step 3, uses Year as partition key

B Step 5, uses AlbumTitle as partition key

C Step 2, uses Artist as partition key

D Step 6, uses SongTitle as partition key

💡 Hint

Check execution_table row 2 for LSI details.

According to variable_tracker, what is the sort key used by the GSI after step 3?

A SongTitle

B Artist

C AlbumTitle

D Year

💡 Hint

Look at the 'Sort Key' row in variable_tracker after step 3.

From the execution table, which index type allows querying by a different partition key than the base table?

A LSI

B GSI

C Both LSI and GSI

D Neither

💡 Hint

Compare rows 2, 3, 6, and 7 in execution_table.

Concept Snapshot

LSI (Local Secondary Index):
- Uses same partition key as base table
- Different sort key
- Shares table size limit (10GB)
- Enables queries on alternate sort keys

GSI (Global Secondary Index):
- Uses different partition and sort keys
- Has separate size and throughput limits
- Enables flexible queries on different keys

Full Transcript

This visual execution compares Local Secondary Index (LSI) and Global Secondary Index (GSI) in DynamoDB. The flow starts with a base table having a primary key composed of partition and sort keys. Adding an LSI keeps the same partition key but changes the sort key, allowing queries on the same partition key but different sort keys. Adding a GSI allows both partition and sort keys to differ, enabling queries on completely different keys. The execution table traces creation steps, key usage, and query capabilities. Variable tracking shows how partition keys, sort keys, query capabilities, and size limits change with each step. Key moments clarify common confusions about partition key restrictions in LSI, flexibility in GSI, and size limits. The quiz tests understanding of index creation steps, key usage, and query flexibility. The snapshot summarizes key differences: LSI shares partition key and size limit with the base table, while GSI allows different keys and scales independently.