CREATE GSI — add an alternate read path

View .md

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 for the statement, Targeted reads for the multi-key Query rule.

Use cases

Give a Scan its index Lowered

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 asUpdateTable · GlobalSecondaryIndexUpdates: Create byEmail · Projection ALL
A multi-key GSI — typed attributes, not concatenated strings Lowered

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 asUpdateTable · 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.
Project only what the read needs Lowered

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 asUpdateTable · 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.

Try it

Try it · CREATE GSISample
Run it live in DynoStudio →
Executes asUpdateTable · stage.Users (+1 GSI)
UpdateTable GlobalSecondaryIndexUpdates: [ Create byEmail · key (email) · Projection ALL ]
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.
Index byEmail · CREATING → ACTIVE · no item rewrite

Related: Tables, indexes & replicas · Targeted reads · SELECT for reading an index · Marker indexes for constraints a GSI can't enforce.

Was this page helpful?