Bean & Bark: index a question the key can’t answer

View .md

The morning rush is behind you. This one's a standing need, not a fire: every morning the fulfillment team wants every order still processing — across all customers — so they can pack and ship. On the base table that's a Scan, because Status isn't a key. You already know what a partition key buys you; now you'll learn how to add one — a GSI — for a question the table's own key can't answer.

You'll learn
  • Turn a filtered Scan into a keyed Query by adding a GSI on the attribute you keep filtering by
  • See that a GSI is a second key over the same items — a new access pattern, not a copy of the table
  • Understand a GSI is sparse (only items carrying the index keys appear) and eventually consistent

The base key can’t answer this

A partition key answers "which items belong to this customer?" — one partition, one customer. Priya's question cuts across customers: every processing order, wherever it lives. There's no partition to go to, so WHERE Status = 'processing' on the base table reads every item and filters — the same Scan you met in chapter one, run every single morning. The fix isn't a cleverer query; it's a new key. You index the attribute she filters by, and her filter becomes a key condition.

sql 
-- give the queue a key: index orders by Status, sorted by dateCREATE GSI "byStatus" ON "BeanAndBark"  PARTITION KEY (Status)  SORT KEY (Placed)  PROJECT ALL

That's one statement. It lowers to UpdateTable; DynamoDB auto-backfills the index from the attributes already on your items — no rewrite — and it's queryable the moment it goes ACTIVE. Run it once, and the daily Scan becomes a daily Query. Flip the variants below and watch the strip.

Try it · the fulfillment queueSample
Run it live in DynoStudio →
Executes asScan · BeanAndBark
Scan FilterExpression: Status = 'processing' · reads the whole table, then filters
`Status` isn't a key on the base table — so every item is read and billed, then filtered down to the queue.
Run every morning, this is a full-table read every morning. Flip to the index to pay for only the matches.
#OrderIdCustomerNamePlacedStatus
1Order#9420Milo Nguyen2026-07-03processing
2Order#9452Priya Adeyemi2026-07-03processing
3Order#9478Rosa Costa2026-07-04processing
4Order#9501Theo Sato2026-07-05processing
Fetched 4 items in 372ms · <0.1% efficient · 480 RCU · 22 partitions

Same queue, both ways — but the base-table Scan read the whole table to find four orders, and the index Query read only the four. A GSI is a second key over the same items: you didn't copy the orders anywhere, you gave DynamoDB a second door into them. Priya's morning export is now a keyed read that costs almost nothing.

Recap
  • A GSI turns a non-key filter into a key condition: Status is a Scan filter on the base table, but a cheap Query on byStatus.
  • A GSI is an alternate key over the same items — backfilled for free (UpdateTable, no item rewrite) and queryable once ACTIVE. It answers a question the base key can't.
  • GSIs are sparse (only items carrying every index-key attribute are indexed) and eventually consistent (a fresh write appears a moment later).
Before you ship the index — check yourself2
Why is WHERE Status = 'processing' a Scan on BeanAndBark but a Query on BeanAndBark."byStatus"?
Your byStatus GSI is keyed (Status, Placed). Which items appear in it?
Try it yourself 2
1 Create the index
Write the statement that gives the fulfillment queue a key: index BeanAndBark orders by Status, sorted by Placed, with the whole item projected.
It's one CREATE GSI statement — a PARTITION KEY, a SORT KEY, and PROJECT ALL.
Show solution
CREATE GSI "byStatus" ON "BeanAndBark" PARTITION KEY (Status) SORT KEY (Placed) PROJECT ALL — it lowers to UpdateTable, backfills for free, and is queryable once ACTIVE.
sql 
CREATE GSI "byStatus" ON "BeanAndBark"  PARTITION KEY (Status)  SORT KEY (Placed)  PROJECT ALL
2 Read the queue, newest first
Now use the index: pull every processing order across all customers, most recently placed first.
Query the index by name (FROM "BeanAndBark"."byStatus"), key on Status, and order by the sort key Placed descending.
Show solution
Key on Status = 'processing' against byStatus and sort on the index's Placed sort key — a keyed Query that reads only the queue, not the table.
sql 
SELECT OrderId, CustomerName, Placed, StatusFROM "BeanAndBark"."byStatus"WHERE Status = 'processing'ORDER BY Placed DESC
Was this page helpful?