GSI overloading technique in DynamoDB

Introduction

GSI overloading helps you use one index to answer many different questions. It saves space and makes your database faster.

When you want to query different types of data using one index.

When you want to reduce the number of indexes to save costs.

When your app needs flexible queries but you want to keep your design simple.

When you want to avoid creating many GSIs for each query pattern.

When you want to improve read performance by reusing indexes.

Syntax

DynamoDB

PartitionKey: <value>
SortKey: <value or composite value>
GSI PartitionKey: <overloaded attribute>
GSI SortKey: <overloaded attribute>

You design your GSI keys to hold different types of data by combining values.

Use prefixes or composite keys to separate different query types in the same GSI.

Examples

Store user metadata and query it by user ID using the GSI.

DynamoDB

PK = USER#123
SK = METADATA
GSI1PK = USER#123
GSI1SK = METADATA

Store orders and query orders by order ID using the same GSI.

DynamoDB

PK = USER#123
SK = ORDER#456
GSI1PK = ORDER#456
GSI1SK = USER#123

Query all orders for a user by using the GSI with user ID as partition key.

DynamoDB

PK = USER#123
SK = ORDER#456
GSI1PK = USER#123
GSI1SK = ORDER#456

Sample Program

This example shows a table with a GSI that stores both user metadata and orders. The query fetches all orders for user 123 using the overloaded GSI.

DynamoDB

CREATE TABLE Orders (
  PK S,
  SK S,
  GSI1PK S,
  GSI1SK S,
  Data S,
  PRIMARY KEY (PK, SK),
  GLOBAL SECONDARY INDEX GSI1 (GSI1PK, GSI1SK)
);

-- Insert user metadata
INSERT INTO Orders VALUE {'PK': 'USER#123', 'SK': 'METADATA', 'GSI1PK': 'USER#123', 'GSI1SK': 'METADATA', 'Data': 'User info here'};

-- Insert an order
INSERT INTO Orders VALUE {'PK': 'USER#123', 'SK': 'ORDER#456', 'GSI1PK': 'USER#123', 'GSI1SK': 'ORDER#456', 'Data': 'Order details here'};

-- Query all orders for user 123
SELECT * FROM Orders.GSI1 WHERE GSI1PK = 'USER#123' AND GSI1SK LIKE 'ORDER#%';

Important Notes

Use clear prefixes like USER# or ORDER# to separate data types in keys.

Overloading GSIs reduces costs but can make queries more complex.

Test your queries to make sure they return the right data from the overloaded index.

Summary

GSI overloading lets you use one index for many query types.

Use prefixes and composite keys to organize data in the GSI.

This technique saves costs and improves query flexibility.