Overloaded GSI

In relational databases, you create a new index for every column you want to query. If you have 10 query patterns, you create 10 indexes.

In DynamoDB, indexes are expensive. Each Global Secondary Index (GSI) increases your storage cost and, more importantly, your Write Capacity Unit (WCU) consumption. Every time you write to the base table, DynamoDB has to replicate that write to every GSI where the item appears.

Index Overloading is a design pattern that allows you to use a single GSI to satisfy multiple access patterns across different entity types. This is the cornerstone of Single Table Design.

1. How It Works

Instead of creating specific attribute names like EmailAddress or OrderId for your GSI keys, you use generic names:

• Partition Key: GSI1PK
• Sort Key: GSI1SK

You then map different attributes to these generic keys depending on the type of item (Entity) you are storing.

Example: E-Commerce System

Imagine we have Users, Orders, and Products in a Single Table.

1. Access Pattern A: Get User by Email.
2. Access Pattern B: Get Orders by Status.
3. Access Pattern C: Get Products by Category.

Instead of creating 3 GSIs (EmailIndex, StatusIndex, CategoryIndex), we create one GSI (GSI1) and overload it.

Entity	Base Table PK	Base Table SK	GSI1PK (Partition)	GSI1SK (Sort)
User	USER#123	PROFILE	EMAIL#alice@ex.com	USER#123
Order	ORDER#999	META	STATUS#SHIPPED	2023-10-27
Product	PROD#555	META	CAT#ELECTRONICS	PRICE#99.99

Now, we can support all three queries using the same index:

• Find User by Email: Query GSI1 where PK = "EMAIL#alice@ex.com"
• Find Shipped Orders: Query GSI1 where PK = "STATUS#SHIPPED"
• Find Electronics: Query GSI1 where PK = "CAT#ELECTRONICS"

2. Interactive: GSI Overload Simulator

See how different entities map to the same underlying index structure.

Entity Type	GSI1PK (Partition Key)	GSI1SK (Sort Key)	Query Use Case

No items in the Global Secondary Index yet. Add some!

3. The “Adjacency List” Pattern

This concept is often used to model Many-to-Many relationships, known as the Adjacency List Pattern.

If User and Group have a many-to-many relationship:

1. Base Table: Stores the relationship.
   • PK: USER#123
   • SK: GROUP#456
2. Inverted Index (GSI1): Flips the relationship.
   • GSI1PK: GROUP#456 (from SK)
   • GSI1SK: USER#123 (from PK)

Now you can query:

• Base Table: “What groups does User 123 belong to?”
• GSI: “What users belong to Group 456?”

4. Code Implementation

Go: Structuring for Overloading

Here is how you structure your struct to support overloading. Note the GSI1PK and GSI1SK fields are used for different purposes depending on the entity.

package main

import (
	"context"
	"fmt"
	"github.com/aws/aws-sdk-go-v2/aws"
	"github.com/aws/aws-sdk-go-v2/service/dynamodb"
	"github.com/aws/aws-sdk-go-v2/service/dynamodb/types"
)

// GenericItem represents any entity in our Single Table
type GenericItem struct {
	PK     string // Base Table Partition Key
	SK     string // Base Table Sort Key
	Type   string // "User", "Order", "Product"

	// Overloaded Index Keys
	GSI1PK string
	GSI1SK string

	// Data Attributes
	Email    string
	Price    float64
	Category string
}

func PutUser(client *dynamodb.Client, table string, email, userId string) {
	// For User: GSI1PK = Email, GSI1SK = UserId
	item := map[string]types.AttributeValue{
		"PK":     &types.AttributeValueMemberS{Value: fmt.Sprintf("USER#%s", userId)},
		"SK":     &types.AttributeValueMemberS{Value: "PROFILE"},
		"Type":   &types.AttributeValueMemberS{Value: "User"},
		"GSI1PK": &types.AttributeValueMemberS{Value: fmt.Sprintf("EMAIL#%s", email)},
		"GSI1SK": &types.AttributeValueMemberS{Value: fmt.Sprintf("USER#%s", userId)},
		"Email":  &types.AttributeValueMemberS{Value: email},
	}

	_, err := client.PutItem(context.TODO(), &dynamodb.PutItemInput{
		TableName: aws.String(table),
		Item:      item,
	})
	if err != nil { panic(err) }
    fmt.Printf("Put User: %s\n", email)
}

Java: Querying the Overloaded Index

When querying, you simply provide the key pattern that matches the entity you are looking for.

import software.amazon.awssdk.services.dynamodb.DynamoDbClient;
import software.amazon.awssdk.services.dynamodb.model.*;
import java.util.HashMap;
import java.util.Map;

public class OverloadedQuery {

    public static void findUserByEmail(DynamoDbClient ddb, String tableName, String email) {
        Map<String, AttributeValue> attrValues = new HashMap<>();
        attrValues.put(":email", AttributeValue.builder().s("EMAIL#" + email).build());

        QueryRequest queryReq = QueryRequest.builder()
            .tableName(tableName)
            .indexName("GSI1")
            .keyConditionExpression("GSI1PK = :email")
            .expressionAttributeValues(attrValues)
            .build();

        QueryResponse response = ddb.query(queryReq);
        System.out.println("User found: " + response.count());
    }

    public static void findOrdersByStatus(DynamoDbClient ddb, String tableName, String status) {
        Map<String, AttributeValue> attrValues = new HashMap<>();
        attrValues.put(":status", AttributeValue.builder().s("STATUS#" + status).build());

        QueryRequest queryReq = QueryRequest.builder()
            .tableName(tableName)
            .indexName("GSI1")
            .keyConditionExpression("GSI1PK = :status")
            .expressionAttributeValues(attrValues)
            .build();

        QueryResponse response = ddb.query(queryReq);
        System.out.println("Orders found: " + response.count());
    }
}

[!TIP] Pro Tip: Always project your Type attribute into your GSI. This helps when debugging or if you ever accidentally mix entity types in the same GSI partition.