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

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 standing ask:** **Priya** (fulfillment): *"every morning I need the full list of orders that are still `processing`, everyone's, so we can pack them. Right now I export the whole table and filter in a spreadsheet. Can you make it a real query?"*

## 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 date
CREATE 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 queue**

_Base table — Scan_

```sql
SELECT OrderId, CustomerName, Placed, Status
FROM "BeanAndBark"
WHERE Status = 'processing'
```

_Executes as:_ Scan · BeanAndBark — `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.

_byStatus index — Query_

```sql
SELECT OrderId, CustomerName, Placed, Status
FROM "BeanAndBark"."byStatus"
WHERE Status = 'processing'
```

_Executes as:_ Query · BeanAndBark.byStatus — On the index, `Status` IS the partition key — DynamoDB goes straight to the `processing` partition and reads only those orders. Same rows, sorted by `Placed`. The read drops from the whole table to just the queue, and the strip turns green.

_Run it live in DynoStudio:_ https://www.kanject.com/dynostudio/

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.

> **Why the index holds only orders:** You keyed `byStatus` on `(Status, Placed)`. Customer profiles and subscriptions have a `Status` too — but they have **no `Placed`**, and DynamoDB only puts an item in a GSI when it has *every* index key attribute. So the index is automatically **sparse**: orders only, no profiles, no subscriptions. Choosing a sort key that only one entity carries is a common single-table trick for a clean, entity-scoped index. One caveat to know: a GSI is **eventually consistent** — an order you just wrote shows up in `byStatus` a beat later, not instantly.

**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 yourself**

**1. Why is `WHERE Status = 'processing'` a **Scan** on `BeanAndBark` but a **Query** on `BeanAndBark."byStatus"`?**

- On the base table `Status` is a plain attribute (filter → Scan); on the GSI it's the partition key (a key condition → Query) ✓ — the index makes `Status` a key, so DynamoDB reads only the matching partition instead of reading the whole table and filtering.
- The GSI stores a pre-computed copy of the results that the base table lacks — a GSI isn't a cached result set — it's an alternate key over the same items. It's cheap because `Status` is a *key* there, not because anything is precomputed.
- Indexes are billed at a lower per-read rate than base tables — there's no discount rate — the saving is entirely that a Query reads only the matched partition while the Scan reads everything.

**2. Your `byStatus` GSI is keyed `(Status, Placed)`. Which items appear in it?**

- Only items that have **both** `Status` and `Placed` — i.e. orders ✓ — a GSI is sparse: an item is indexed only if it carries every index-key attribute. Profiles and subscriptions have `Status` but no `Placed`, so they're left out.
- Every item in the table, since a GSI mirrors the whole table — a GSI only includes items that have all its key attributes — items missing `Placed` never enter the index. That sparseness is a feature here.
- Only orders inserted after the index was created — DynamoDB auto-backfills the index from existing items when you create it — the orders already in the table appear once it's `ACTIVE`.

**Try it yourself**

**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.

_Hint:_ It's one `CREATE GSI` statement — a `PARTITION KEY`, a `SORT KEY`, and `PROJECT ALL`.

_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.

_Hint:_ Query the index by name (`FROM "BeanAndBark"."byStatus"`), key on `Status`, and order by the sort key `Placed` descending.

_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, Status
FROM "BeanAndBark"."byStatus"
WHERE Status = 'processing'
ORDER BY Placed DESC
```

> **Where next?:** You can now **build, question, operate, and index** a DynamoDB table on your own — that's Part 1, all on one coffee-roaster dataset. [CREATE GSI](https://www.kanject.com/docs/dynostudio-create-gsi/) is the full reference (multi-key indexes, `PROJECT` choices, the backfill), and [Tables, indexes & replicas](https://www.kanject.com/docs/dynostudio-partiql-ddl/) sets indexes beside the analytics replica. Ready to *build* Bean & Bark instead of just querying it? [Part 2](https://www.kanject.com/docs/dynostudio-bean-bark-model/) puts it on the Kanject platform, where one annotated model is the schema, the rulebook, and the code.

---
_Source: https://www.kanject.com/docs/dynostudio-bean-bark-gsi/ · Kanject Docs_