Ravindra BagaleCourses & study guides मराठी Track your progress

DynamoDB GSI and LSI Deep Dive: Projections, Keys and Costs

By , trainer LinkedIn Last updated:

The CRUD survey introduced GSI vs LSI briefly. Cairo analytics lead Nour Hassan paid for three ALWAYS-projected GSIs that nobody queried. This chapter goes deep: sparse GSIs, projections, LSI limits, backfill, costs. Basics remain in CRUD chapter.

What you'll learn in this chapter

  • Explain with an everyday story
  • Apply Why/What/How
  • Read commands with output+meaning
  • Map across industries
  • Finish lab + cleanup

1. Everyday example: library card catalog drawers

The main shelf is the base table. Extra drawers (GSI) let you find books by author or topic without walking every shelf. A sparse drawer holds only books on hold — few cards, cheap. Projecting ALL copies every page into every drawer wastes paper (cost).

2. Vertical steps

Why -> What -> How -> Lab -> CleanupWhyWhatHowLabCleanup
Cleanup labs

3. Why do we need this?

Why do we need this?

Secondary indexes unlock alternate access patterns. Wrong projections and unused indexes burn money. LSI must be chosen at table create time.

4. What is it?

What is it?

GSI = alternate PK/SK with own partitions. LSI = same PK, different SK, same partition. Projections: KEYS_ONLY, INCLUDE, ALL. Sparse GSI = attribute present only on some items.

5. How to do it (step by step)

How to do it (step by step)

  1. List leftover patterns after base keys.
  2. Prefer sparse GSI over indexing every item.
  3. Choose KEYS_ONLY or INCLUDE before ALL.
  4. Remember LSI only at create; max 10 GB per PK.
  5. Watch GSI throttle metrics separately.

6. Projection cheat sheet

KEYS_ONLY smallest; INCLUDE listed attrs; ALL heaviest. Prefer KEYS_ONLY when you can GetItem after.

7. Backfill behaviour

New GSI backfills existing items that already have key attrs. Sparse items without attrs stay out. Status CREATING until done.

8. Commands with output and meaning

aws dynamodb update-table --table-name lab-dda-shop --attribute-definitions AttributeName=GSI1PK,AttributeType=S AttributeName=GSI1SK,AttributeType=S \
  --global-secondary-index-updates '[{"Create":{"IndexName":"GSI1","KeySchema":[{"AttributeName":"GSI1PK","KeyType":"HASH"},{"AttributeName":"GSI1SK","KeyType":"RANGE"}],"Projection":{"ProjectionType":"KEYS_ONLY"}}}]'
# Output: IndexStatus CREATING then ACTIVE. Meaning: sparse-ready KEYS_ONLY index — put GSI1PK only on items that need it.
aws dynamodb query --table-name lab-dda-shop --index-name GSI1 --key-condition-expression "GSI1PK = :g" --expression-attribute-values '{":g":{"S":"STATUS#NEW"}}'
# Output: only items you stamped with GSI1PK=STATUS#NEW.

update-table adds GSI1 KEYS_ONLY. Query on index returns keys for that alternate pattern. Stamp GSI attributes only on items that belong (sparse).

9. Industry stories

  • Support desk (Aisha Khan, Dubai): sparse GSI STATUS#OPEN tickets only.
  • HR (Rohan Deshmukh, Pune): LSI on employee PK by joinDate.
  • Marketplace (Carlos Rivera): GSI by category + price SK.
  • Fintech (Elena Petrova): avoid ALL projection on PII-heavy items.
  • Gaming (Mei Lin): sparse GSI for active tournaments.

10. Lab

How to do it (step by step)

  1. Add KEYS_ONLY sparse GSI; stamp 3 items; Query index.
  2. Compare describe-table index size vs base.
  3. Delete GSI then table.

Cleanup (stop billing)

  1. Delete lab tables/indexes/triggers.
  2. Confirm describe-table fails or status missing.

11. Access-pattern workshop (paper first)

  1. List every screen or API that reads or writes data.
  2. For each: write the exact key lookup or query shape in plain English.
  3. Mark 1:1, 1:many, many:many relationships.
  4. Circle anything that needs a sort range (time, status, score).
  5. Only then invent PK/SK and optional GSI keys.
  6. Estimate peak items per partition key per second.
  7. Write one sample item JSON for each entity type.
  8. Peer-review with someone who did not design it.

12. Copy-paste DynamoDB CLI starters

# Region habit for this series
export AWS_DEFAULT_REGION=ap-south-1

# Describe + sample
aws dynamodb describe-table --table-name ShopApp
aws dynamodb scan --table-name ShopApp --max-items 3

# Query by PK (replace names)
aws dynamodb query --table-name ShopApp   --key-condition-expression "PK = :pk"   --expression-attribute-values '{":pk":{"S":"USER#u123"}}'

# On-demand capacity mode check
aws dynamodb describe-table --table-name ShopApp   --query 'Table.BillingModeSummary'

# Streams status
aws dynamodb describe-table --table-name ShopApp   --query 'Table.StreamSpecification'

13. More industry stories

  • Retail (Aisha Khan, Dubai): single-table cart+orders; PK=USER#id keeps each shopper hot path local.
  • Gaming (Carlos Rivera, Mexico City): leaderboard uses sparse GSI on score; write shard on hot gameId.
  • IoT (Mei Lin, Shenzhen): sensor#day PK + timestamp SK; TTL 30 days keeps storage flat.
  • Healthcare (Rohan Deshmukh, Pune): appointment slots use conditional Put — no double book.
  • Fintech (Elena Petrova, Warsaw): Streams fan-out to audit table; never update audit in place.
  • Travel (Omar Haddad, Amman): global tables for read-local catalog; writes region-sticky for inventory.

14. Glossary (short)

TermMeaning
PK / Partition keyHash key that places the item in a partition
SK / Sort keyOrders items inside a partition; enables ranges
Item collectionAll items that share the same PK
GSIGlobal Secondary Index — alternate PK/SK, own partitions
LSILocal Secondary Index — same PK, different SK
RCU / WCURead/Write Capacity Units on provisioned mode
Conditional writeWrite succeeds only if a condition is true
Stream view typeKEYS_ONLY / NEW_IMAGE / OLD_IMAGE / NEW_AND_OLD_IMAGES

15. Practice: explain out loud

  1. 60s: why access patterns come before table design.
  2. 60s: hot partition and one fix (write sharding).
  3. 60s: GSI vs LSI in one breath.
  4. 60s: on-demand vs provisioned for a startup.
  5. 60s: optimistic locking with a version attribute.
  6. 60s: Streams + Lambda for an audit trail.
  7. Record once; cut filler; record again.

16. Safe lab hygiene

  • Use ap-south-1 labs with a budget alert (₹/USD).
  • Prefix lab tables with lab- and your initials.
  • Prefer on-demand for tiny labs unless you are learning auto scaling.
  • Delete tables and Lambda triggers after the lab.
  • Never point Streams consumers at production without a DLQ.
  • Tag every resource Owner + Expiry.

18. Single-table sketch pad

Write entities as rows in one mental table before you open the console. Example for a small shop: USER#, ORD#, ORD#|ITEM#, PROD# under carefully chosen PK/SK.

EntityPKSKExample attrs
UserUSER#u1PROFILEname, city
OrderUSER#u1ORD#2026-10-31#o9total, status
Order itemORD#o9ITEM#sku1qty, price
ProductPROD#sku1METAtitle, stock

19. Ten oral drill questions

  1. What is an access pattern?
  2. Why overload PK with entity prefixes?
  3. Name one hot-partition symptom.
  4. Sparse GSI means what?
  5. When is LSI impossible after create?
  6. On-demand bills for what?
  7. What does attribute_not_exists do?
  8. NEW_AND_OLD_IMAGES costs more — why still pick it?
  9. How do you stop a poison Streams message loop?
  10. Which existing chapter do you open for PutItem syntax?

20. Capacity and throttle drill

  1. Open CloudWatch for your table: ConsumedReadCapacityUnits, ConsumedWriteCapacityUnits, ThrottledRequests.
  2. Note whether BillingMode is PAY_PER_REQUEST or PROVISIONED.
  3. If provisioned, write current RCU/WCU and autoscaling min/max.
  4. Name your hottest expected PK in one sentence.
  5. Write one mitigation: shard, cache, or redesign.
  6. Set or verify a budget alert for DynamoDB.
  7. Check GSI list and whether each is still used.
  8. Confirm Streams enabled only where consumers exist.

21. Condition expression cheatsheet

# Create-once
attribute_not_exists(PK)

# Optimistic lock
version = :v

# Stock / seat
stock > :zero
#status = :free

# Combine
attribute_not_exists(PK) AND #type = :user

Meaning: conditions are the seatbelt for concurrent writers. Put them on every race-prone update. Full SDK samples stay in the SDK chapter — here we only list the expressions you will reuse.

22. Streams consumer checklist

  • View type matches need (KEYS_ONLY vs NEW_AND_OLD).
  • Lambda has DLQ and retry policy.
  • Handler idempotent on eventID or keys.
  • IteratorAge alarm before users feel lag.
  • No accidental write-loop back into the same table without markers.
  • Fan-out via SNS/EventBridge when >1 consumer team.
  • Disable stream when lab ends.
  • Ch1 Modeling -> Ch2 Hot keys -> Ch3 GSI/LSI -> Ch4 Capacity -> Ch5 Transactions -> Ch6 Streams.
  • Always link intro + CRUD + CLI + SDK instead of rewriting Put/Get.
  • Projects P1-P7 come in later batches and reuse these ideas.

24. Extra oral drills

  1. Explain single-table to a product manager in 45 seconds.
  2. Draw PK/SK for a chat thread on a whiteboard.
  3. Name three CloudWatch metrics for DynamoDB pain.
  4. Say when you would refuse a new GSI request.
  5. Describe one transaction for seat + payment record.
  6. Explain why Streams are at-least-once.
  7. Convert a Scan-based feature request into a Query design.
  8. List cleanup steps after a DynamoDB lab.

25. Teacher story: the notebook that became a database

Ravindra once watched a shop keep every customer, order and payment in one paper notebook. Finding last Tuesday's unpaid bills meant flipping pages. DynamoDB single-table thinking is that notebook done carefully: one place, clear labels (PK/SK), and an index tab (GSI) only when a second way to find rows is worth the cost. Do not photocopy the notebook into ten unrelated books unless the shop truly has ten separate businesses.

When Meera (clinic), Omar (travel), and Aisha (retail) design together, they still start on paper: screen list, key sketch, sample JSON, peer review. Console clicks come last. That ritual is why this deep series links the intro and CRUD chapters instead of rewriting PutItem on every page.

26. Sixteen mini tasks (self-check, leak-free)

  1. Write three access patterns for a cart app in one minute.
  2. Invent PK/SK for user profile and orders under that user.
  3. Name one hot-partition anti-pattern.
  4. Explain write sharding to a teammate in 40 seconds.
  5. Say GSI vs LSI without notes.
  6. Pick KEYS_ONLY or ALL for a sparse open-orders index — why?
  7. Choose on-demand or provisioned for a weekend flash sale.
  8. List five DynamoDB bill line-item types.
  9. Write a create-once condition in words.
  10. Describe optimistic locking with a version attribute.
  11. When do you wrap two writes in a transaction?
  12. Pick a stream view type for an audit log — justify.
  13. Name two Stream consumer safety rules.
  14. What CloudWatch metric shows stream lag?
  15. Which linked chapter has PutItem CLI syntax?
  16. Write your lab cleanup checklist (table, stream, lambda, alarms).

27. Marathi classroom lines (say out loud)

  • Access patterns आधी, keys नंतर — हे mantra आठवा.
  • Hot partition म्हणजे एक PK वर गर्दी — shard किंवा redesign.
  • GSI लवचिक; LSI create-time — चुकीचा index महाग.
  • On-demand म्हणजे वापरल्यावर पैसे; provisioned म्हणजे आरक्षित units.
  • Condition आणि transaction म्हणजे race साठी seatbelt.
  • Streams at-least-once आहे — handler idempotent ठेवा.

28. Hindi bridge lines

  • Pehle access patterns likho, baad mein PK/SK.
  • Hot partition ek key par zyada load.
  • GSI kabhi bhi; LSI table banate waqt.
  • On-demand spike ke liye; provisioned steady ke liye.
  • Transaction = sab milkar ya kuch nahi.
  • Streams duplicate de sakte hain — idempotent likho.

29. Common errors and fixes

Each row is a real mistake students and juniors hit. Read the fix before you panic.

Error / symptomFix
ALL projection by default habitStart KEYS_ONLY/INCLUDE.
GSI on every item unnecessarilySparse stamp.
Adding LSI after createRecreate table or use GSI.
Ignoring GSI WCU/RCUScale/monitor GSI.
Same hot PK on GSIDesign GSI PK carefully.
Backfill surprise lagExpect CREATING time.
Using GSI for strong consistencyGSI reads eventually consistent.
Ten unused indexesDelete unused.
Projection missing needed attrINCLUDE those attrs.

30. Troubleshooting checklist

  1. Index Query empty: attribute not stamped (sparse).
  2. CREATING long: large backfill.
  3. ValidationException on LSI add: too late.
  4. Throttle on index name: GSI capacity.
  5. Stale GSI read: eventual consistency.

31. FAQ

Q. Should I always use a single table?

Often yes for related access patterns; not a religion. Separate tables are fine for unrelated domains or different capacity/security needs.

Q. Do I still need RDS?

Yes for heavy joins, multi-row SQL reports, and strong relational constraints. See the intro DynamoDB vs RDS chapter.

Q. Is Scan evil?

Scan is a tool for rare admin jobs and small tables. Design so hot paths use Query/GetItem.

Q. Where are Put/Get command details?

In the CRUD survey, CLI and SDK chapters — this deep set links them instead of copying.

Q. Sparse GSI?

Index attr only on some items.

Q. LSI vs GSI?

LSI same PK create-time; GSI alternate PK anytime.

Q. KEYS_ONLY meaning?

Index stores keys; GetItem for more.

Q. GSI strong consistent?

No — eventual.

32. Interview Q&A

Q. (B) Why secondary index?

Alternate access pattern.

Q. (B) Sparse?

Only items with attr appear.

Q. (M) Projection types?

KEYS_ONLY, INCLUDE, ALL.

Q. (M) LSI limit?

Create-time; 10GB/PK.

Q. (S/MNC) Cost control?

Sparse + KEYS_ONLY + delete unused.

Q. (MNC) When second table?

Different lifecycle/IAM/capacity.

33. Tips in English, Marathi and Hindi

Ravindra Bagale's Tip (EN)

Stamp index attributes only when the alternate pattern needs that item.

Ravindra Bagale's Tip (MR)

Index attr फक्त गरज असेल तर.

Tip (HI)

जरूरत हो तभी index attr लिखो।

Try at home

  1. List 2 leftover patterns for your app.
  2. Choose projection for each.
  3. Read CRUD GSI section once.

In short

  1. GSI alternate PK; LSI same PK.
  2. Sparse saves money.
  3. Projection discipline.
  4. Monitor index throttles.

Revision, interview and comparison cards

Revise this lesson in a few minutes. Save the cards on your phone or download them as PNG files. The QR code on each card opens this lesson.

Revision card: GSI vs LSI pick. Wrong index type wastes money or locks you after create. GSI: new PK anytime, eventually consistent. LSI: same PK, create-time only, can be strong. Name access → choose GSI/LSI/second table → pick projection → watch cost. Priya Raman rejects a late LSI request and adds a sparse GSI instead.
Revision card: GSI vs LSI pick · Download PNG · Open card page
Revision card: Projection cost. ALL projections quietly double storage and writes. KEYS_ONLY / INCLUDE / ALL — project only what Query returns need. Sketch needed attrs → choose projection → estimate write amplification. Elena Petrova budgets GSI writes before launch week.
Revision card: Projection cost · Download PNG · Open card page
Comparison: GSI vs LSI. GSI: Own PK/SK anytime; eventually consistent. LSI: Same PK different SK; create-time; can be strong.
Comparison: GSI vs LSI · Download PNG · Open card page
Comparison: KEYS_ONLY vs ALL. KEYS_ONLY: Keys then GetItem. ALL: Every base attr copied.
Comparison: KEYS_ONLY vs ALL · Download PNG · Open card page
Interview card: GSI vs LSI in one minute. Compare. GSI new keys anytime EC; LSI same PK create-time, can be strong. Link CRUD for basics.
Interview card: GSI vs LSI in one minute · Download PNG · Open card page
Interview card: What is a sparse GSI?. Pattern. Item appears only if GSI key attribute exists — great for subsets. Leaderboard / open-orders examples.
Interview card: What is a sparse GSI? · Download PNG · Open card page
Interview card: Which projection to pick?. Cost. KEYS_ONLY / INCLUDE / ALL — project only Query needs. ALL doubles cost risk.
Interview card: Which projection to pick? · Download PNG · Open card page
Interview card: When prefer a second table?. Pragmatism. Different lifecycle, huge/hot index, or separate domain. Single-table is a tool not a religion.
Interview card: When prefer a second table? · Download PNG · Open card page

More cards: Ravi Sir's Cards.

Practice Tasks

About this scenario

London Legal LLP is a law business in London. Oliver Hughes looks after its IT and cloud work. They count money in GBP and run their AWS resources in the eu-west-2 Region. In the tasks below you do real jobs for them using what this lesson teaches. All names, account IDs, IP addresses and numbers are made up for practice.

0 / 16

Easy GSI has its own PK/SK — 1

Why: Def.

What to do: Enter 1 if a GSI has its own partition (and optional sort) key.

How:
  1. Write down the numbers the task gives you.
  2. Work it out step by step on paper.
  3. Type only the final answer in the box.
  4. Click Check answer.

Easy LSI created with table — 1

Why: Timing.

What to do: Enter 1 if LSIs must be defined at table creation.

How:
  1. Write down the numbers the task gives you.
  2. Work it out step by step on paper.
  3. Type only the final answer in the box.
  4. Click Check answer.

Easy KEYS_ONLY projects keys — 1

Why: Proj.

What to do: Enter 1 if KEYS_ONLY stores key attributes in the index.

How:
  1. Write down the numbers the task gives you.
  2. Work it out step by step on paper.
  3. Type only the final answer in the box.
  4. Click Check answer.

Easy Sparse GSI skips missing keys — 1

Why: Sparse.

What to do: Enter 1 if items without the GSI key attribute do not appear in a sparse GSI.

How:
  1. Write down the numbers the task gives you.
  2. Work it out step by step on paper.
  3. Type only the final answer in the box.
  4. Click Check answer.

Easy Describe table to see GSIs

Why: CLI.

What to do: Describe table ShopApp (indexes visible in output)

How:
  1. Read the task and decide which AWS service it is about.
  2. Write aws, then the service name, then the operation (the action word).
  3. Add each needed flag (--name) followed by its value from the task.
  4. Flags can be in any order. Click Check answer.

Easy Projection YAML

Why: Sketch.

What to do: YAML: gsi1: KEYS_ONLY, gsi2: INCLUDE.

How:
  1. Start from the top-level keys the task names.
  2. Indent child keys with 2 spaces (no tabs).
  3. Fill in each property with the value from the task.
  4. Click Check answer. Key order does not matter.

Easy Base table still holds source of truth — 1

Why: Truth.

What to do: Enter 1 if the base table remains the source of truth for attributes.

How:
  1. Write down the numbers the task gives you.
  2. Work it out step by step on paper.
  3. Type only the final answer in the box.
  4. Click Check answer.

Medium GSI adds write amplification — 1

Why: Cost.

What to do: Enter 1 if GSIs add storage and write amplification cost.

How:
  1. Write down the numbers the task gives you.
  2. Work it out step by step on paper.
  3. Type only the final answer in the box.
  4. Click Check answer.

Medium New GSI backfills — 1

Why: Backfill.

What to do: Enter 1 if a new GSI backfills from existing items over time.

How:
  1. Write down the numbers the task gives you.
  2. Work it out step by step on paper.
  3. Type only the final answer in the box.
  4. Click Check answer.

Medium Second table if index hurts — 1

Why: Split.

What to do: Enter 1 if a huge/hot index may justify a second table.

How:
  1. Write down the numbers the task gives you.
  2. Work it out step by step on paper.
  3. Type only the final answer in the box.
  4. Click Check answer.

Medium LSI can be strongly consistent — 1

Why: Consistency.

What to do: Enter 1 if LSI reads can be strongly consistent.

How:
  1. Write down the numbers the task gives you.
  2. Work it out step by step on paper.
  3. Type only the final answer in the box.
  4. Click Check answer.

Medium LSI vs GSI pick

Why: Choose.

What to do: YAML: same_pk_new_sk: LSI, new_pk: GSI.

How:
  1. Start from the top-level keys the task names.
  2. Indent child keys with 2 spaces (no tabs).
  3. Fill in each property with the value from the task.
  4. Click Check answer. Key order does not matter.

Hard ALL projection copies all attrs — 1

Why: Proj.

What to do: Enter 1 if ALL projection copies all base attributes to the index.

How:
  1. Write down the numbers the task gives you.
  2. Work it out step by step on paper.
  3. Type only the final answer in the box.
  4. Click Check answer.

Hard Link CRUD GSI basics — 1

Why: No dup.

What to do: Enter 1 if this deep dive links the CRUD chapter for GSI basics.

How:
  1. Write down the numbers the task gives you.
  2. Work it out step by step on paper.
  3. Type only the final answer in the box.
  4. Click Check answer.

Hard INCLUDE projection picks attrs — 1

Why: Proj.

What to do: Enter 1 if INCLUDE lets you project selected non-key attributes.

How:
  1. Write down the numbers the task gives you.
  2. Work it out step by step on paper.
  3. Type only the final answer in the box.
  4. Click Check answer.

Hard GSI reads are eventually consistent — 1

Why: Consistency.

What to do: Enter 1 if GSI queries are eventually consistent.

How:
  1. Write down the numbers the task gives you.
  2. Work it out step by step on paper.
  3. Type only the final answer in the box.
  4. Click Check answer.

See all practice tasks in the Task Hub →