# CREATE GLOBAL INDEX / DROP GSI — native index lifecycle

Add or remove a **GSI** — an alternate key shape over the same table. `CREATE GLOBAL INDEX` is the standing fix for a read that keeps flagging amber: index the attribute you filter by, and the filter becomes a key condition. `DROP GSI` removes that read path without deleting any base-table items. Both lower to `UpdateTable`.

## Syntax

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

-- compatibility aliases: CREATE GSI · CREATE GLOBAL SECONDARY INDEX · CREATE INDEX

DROP GSI "name" ON "table";
-- aliases: DROP GLOBAL SECONDARY INDEX · DROP INDEX
```

For create, `PROJECT` chooses what rides along: `ALL` (every attribute), `KEYS` (just the keys), or `INCLUDE (a, b)` (keys plus the named attributes). For drop, `DROP GSI` is the preferred DynamoDB-specific spelling; `DROP GLOBAL SECONDARY INDEX` and `DROP INDEX` are accepted aliases. Guides: [Tables, indexes & replicas](https://dynostudio.dev/docs/dynostudio-partiql-ddl/) for the lifecycle, [Targeted reads](https://dynostudio.dev/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 GLOBAL INDEX "byEmail" ON "stage.Users"
  PARTITION KEY (email)
  PROJECT ALL
```

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

_Quote this example:_ https://dynostudio.dev/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 GLOBAL INDEX "byRegionStatus" ON "stage.Orders"
  PARTITION KEY (region, tenant)
  SORT KEY (status, createdAt)
  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://dynostudio.dev/docs/dynostudio-partiql-reads/#multi-key-gsis).

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

### Project only what the read needs

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

```sql
CREATE GLOBAL INDEX "byStatus" ON "stage.Outbox"
  PARTITION KEY (Status)
  SORT KEY (CreatedAt)
  PROJECT INCLUDE (Type, Attempts)
```

_Executes as:_ UpdateTable · Create byStatus · Projection INCLUDE: Type, Attempts

- 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://dynostudio.dev/docs/dynostudio-create-gsi/#gsi-include-projection

### Remove an index, keep the table

The `byStatusV1` access path has no callers after a migration. Remove its index storage while preserving every item in `stage.Orders`.

```sql
DROP GSI "byStatusV1" ON "stage.Orders";
```

_Executes as:_ UpdateTable · GlobalSecondaryIndexUpdates: Delete byStatusV1

- The first Run arms the drop; the second unchanged Run confirms it. Index queries fail immediately after acceptance, deletion finishes in the background, and recreating it later requires a fresh backfill.

_Quote this example:_ https://dynostudio.dev/docs/dynostudio-create-gsi/#drop-gsi

## Try it

**Try it · CREATE GLOBAL INDEX**

```sql
CREATE GLOBAL INDEX "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://dynostudio.dev/dynostudio/

> **Not sure what to index?:** Run [`PROFILE TABLE`](https://dynostudio.dev/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).

> **DROP GSI removes a read path immediately:** Dropping a GSI preserves the base table, but callers cannot keep querying the index while DynamoDB deletes it. Migrate every reader first. A read-only stage refuses the drop.

Related: [Tables, indexes & replicas](https://dynostudio.dev/docs/dynostudio-partiql-ddl/) · [Targeted reads](https://dynostudio.dev/docs/dynostudio-partiql-reads/) · [SELECT](https://dynostudio.dev/docs/dynostudio-select/#gsi-lookup) for reading an index · [Marker indexes](https://dynostudio.dev/docs/dynostudio-marker-indexes/) for Kanject's model-declared access paths and uniqueness rules.

---
_Source: https://dynostudio.dev/docs/dynostudio-create-gsi/ · DynoStudio Docs_