GSI Overloading: One Index to Rule Them All

The Problem with Specific Indexes

A Global Secondary Index (GSI) is a separate, automatically maintained copy of your table data, projected onto a different key structure. It lets you query by attributes other than the primary key.

The naive approach: create one GSI per query pattern. Need to find users by email? Create EmailIndex. Need to find orders by status? Create StatusIndex. Need to find products by category? Create CategoryIndex.

This approach has two problems:
1. Cost: Each GSI replicates data and consumes its own read/write capacity.
2. Limits: DynamoDB allows a maximum of 20 GSIs per table.

GSI Overloading: Generic Index Attributes

The solution is to create generic, reusable GSI attributes — typically named GSI1PK, GSI1SK, GSI2PK, GSI2SK — and populate them with entity-specific values at write time.

Setup: Create one GSI named GSI1 with hash key GSI1PK and range key GSI1SK.

Usage across different entity types:

# User item — searchable by email via GSI1
{
  PK: "USER#123",
  SK: "PROFILE",
  GSI1PK: "EMAIL#[email protected]",
  GSI1SK: "USER#123",
  Name: "Alice"
}

# Order item — searchable by status via GSI1
{
  PK: "USER#123",
  SK: "ORDER#001",
  GSI1PK: "STATUS#PENDING",
  GSI1SK: "2024-06-15T10:00:00Z",   ← ISO timestamp enables date-range queries
  Total: 99.00
}

# Product item — searchable by category via GSI1
{
  PK: "PRODUCT#abc",
  SK: "DETAILS",
  GSI1PK: "CATEGORY#SHOES",
  GSI1SK: "PRODUCT#abc",
  Price: 89.99
}

Queries against GSI1:

# Find user by email
Query GSI1 WHERE GSI1PK = "EMAIL#[email protected]"

# Find all pending orders
Query GSI1 WHERE GSI1PK = "STATUS#PENDING"

# Find pending orders in June 2024
Query GSI1 WHERE GSI1PK = "STATUS#PENDING"
  AND GSI1SK BETWEEN "2024-06-01" AND "2024-06-30"

# Find all shoes
Query GSI1 WHERE GSI1PK = "CATEGORY#SHOES"

One GSI answers four completely different business questions across three entity types.

Important nuance: GSI reads are eventually consistent by default — you cannot request strong consistency on a GSI query. If your access pattern requires reading your own writes immediately after writing, you must query the base table (which supports strong consistency) or architect around this limitation.