# CREATE GSI — add an alternate read path

Add a **GSI** — an alternate key shape over the same table, and the standing fix for a read that keeps flagging amber: index the attribute you filter by, and the filter becomes a key condition. Lowers to `UpdateTable`; DynamoDB **auto-backfills** the index from existing attributes (no item rewrite), and it's queryable once `ACTIVE`.

## Syntax

```sql
CREATE GSI "name" ON "table"
  PARTITION KEY (attr[, attr…])          -- up to 4, types default STRING
  [SORT KEY (attr[, attr…])]             -- up to 4; per-attr type: createdAt N
  PROJECT ALL | KEYS | INCLUDE (a, b)

-- accepted spellings: CREATE GLOBAL SECONDARY INDEX · CREATE INDEX
```

`PROJECT` chooses what rides along: `ALL` (every attribute), `KEYS` (just the keys), or `INCLUDE (a, b)` (keys plus the named attributes). Guides: [Tables, indexes & replicas](https://www.kanject.com/docs/dynostudio-partiql-ddl/) for the statement, [Targeted reads](https://www.kanject.com/docs/dynostudio-partiql-reads/) for the multi-key Query rule.

## Use cases

### Give a Scan its index

Reads filtering `stage.Users` by `email` keep flagging amber — index it, and `FROM "stage.Users"."byEmail" WHERE email = …` becomes a key-condition Query.

```sql
CREATE GSI "byEmail" ON "stage.Users"
  PARTITION KEY (email)
  PROJECT ALL
```

_Executes as:_ UpdateTable · GlobalSecondaryIndexUpdates: Create byEmail · Projection ALL

_Quote this example:_ https://www.kanject.com/docs/dynostudio-create-gsi/#gsi-by-email

### A multi-key GSI — typed attributes, not concatenated strings

Region + tenant as partition attributes, status + date as sort attributes — kept separate and typed, not mashed into one `region#tenant#status` string you parse back out.

```sql
CREATE GSI "byRegionStatus" ON "stage.Orders"
  PARTITION KEY (region, tenant)
  SORT KEY (status, createdAt N)
  PROJECT ALL
```

_Executes as:_ UpdateTable · Create byRegionStatus · keys (region, tenant)/(status, createdAt)

- A Query on it needs **equality on every partition attribute** and sort attributes bound **left-to-right** with at most one trailing range — the rule from [Targeted reads](https://www.kanject.com/docs/dynostudio-partiql-reads/#multi-key-gsis).

_Quote this example:_ https://www.kanject.com/docs/dynostudio-create-gsi/#gsi-multi-key

### Project only what the read needs

An index that answers "list titles by author" doesn't need the whole item — `INCLUDE` keeps the index lean and its storage bill smaller.

```sql
CREATE INDEX "byAuthor" ON "stage.Posts"
  PARTITION KEY (authorId)
  SORT KEY (publishedAt N)
  PROJECT INCLUDE (title, slug)
```

_Executes as:_ UpdateTable · Create byAuthor · Projection INCLUDE: title, slug

- A read that asks for a non-projected attribute can't be served by the index alone — project what the access pattern actually reads.

_Quote this example:_ https://www.kanject.com/docs/dynostudio-create-gsi/#gsi-include-projection

## Try it

**Try it · CREATE GSI**

```sql
CREATE GSI "byEmail" ON "stage.Users"
  PARTITION KEY (email)
  PROJECT ALL
```

_Executes as:_ UpdateTable · stage.Users (+1 GSI) — DynamoDB auto-backfills the index from existing attributes — no item rewrite. Queryable once it goes ACTIVE; the strip shows the control-plane call before consent.

_Result:_ Index byEmail · CREATING → ACTIVE · no item rewrite

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

> **Not sure what to index?:** Run [`PROFILE TABLE`](https://www.kanject.com/docs/dynostudio-profile-table/) first — its coverage column tells you which attributes are present consistently enough to key a dependable read path (and which would make a deliberately sparse index).

Related: [Tables, indexes & replicas](https://www.kanject.com/docs/dynostudio-partiql-ddl/) · [Targeted reads](https://www.kanject.com/docs/dynostudio-partiql-reads/) · [SELECT](https://www.kanject.com/docs/dynostudio-select/#gsi-lookup) for reading an index · [Marker indexes](https://www.kanject.com/docs/dynostudio-marker-indexes/) for constraints a GSI can't enforce.

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