11.4: Secondary index + the Query API action
A similar pattern for one-to-many relationships is to use a global secondary index and the Query API to fetch multiple items in a single request. This pattern is almost the same as the previous pattern, but it uses a secondary index rather than the primary keys on the main table.
Imagine we use the same dataset as the previous chapter. Lets say our item collection now has organization data, user data and also support ticket data(users can raise support ticket data). If I want to retrieve an Organization and all its Users, I’m also retrieving a bunch of Tickets. And since Tickets are likely to vastly exceed the number of Users, I’ll be fetching a lot of useless data and making multiple pagination requests to handle our original use case.
Instead, let’s try something different. We’ll do three things:
1: We’ll model our Ticket items to be in a separate item collection altogether in the main table. For the PK and SK values, we’ll use a pattern of TICKET#<TicketId> which will allow for direct lookups of the Ticket item.
2: Create a global secondary index named GSI1 whose keys are GSI1PK and GSI1SK.
3: For both our Ticket and User items, add values for GSI1PK and GSI1SK. For both items, the GSI1PK attribute value will be ORG#<OrgName>#USER#<UserName>. For the User item, the GSI1SK value will be USER#<UserName>. For the Ticket item, the GSI1SK value will be TICKET#<TicketId>.
| Primary Key | Attributes | |||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| Partition key: pk | Sort key: sk | OrgName | PlanType | CreatedDate | UserName | UserType | GSI1PK | GSI1SK | ||
| "ORG#MICROSOFT" | "METADATA#MICROSOFT" | "Microsoft" | "Enterprise" | |||||||
| "ORG#MICROSOFT" | "USER#BILLGATES" | "Bill Gates" | "Member" | "ORG#MICROSOFT#USER#BILLGATES" | "USER#BILLGATES" | |||||
| "ORG#MICROSOFT" | "USER#SATYANADELLA" | "Satya Nadella" | "Admin" | "ORG#MICROSOFT#USER#BILLGATES" | "USER#BILLGATES" | |||||
| "ORG#AMAZON" | "METADATA#AMAZON" | "Amazon" | "Pro" | |||||||
| "ORG#AMAZON" | "USER#JEFFBEZOS" | "Jeff Bezos" | "Admin" | "ORG#AMAZON#USER#JEFFBEZOS" | "USER#JEFFBEZOS" | |||||
| "TICKET#123" | "TICKET#123" | "2023-09-05 22:31:54" | "ORG#MICROSOFT#USER#BILLGATES" | "TICKET#123" | ||||||
| "TICKET#456" | "TICKET#456" | "2024-09-05 22:31:54" | "ORG#MICROSOFT#USER#BILLGATES" | "TICKET#456" | ||||||
Notice that our Ticket items are no longer interspersed with their parent Users in the base table. Further, the User items now have additional GSI1PK and GSI1SK attributes that will be used for indexing.
If we look at our GSI1 secondary index, we see the following:
| Primary Key | Attributes | |||||||
|---|---|---|---|---|---|---|---|---|
| Partition key: GSI1PK | Sort key: GSI1SK | pk | sk | CreatedDate | UserName | UserType | ||
| "ORG#MICROSOFT#USER#BILLGATES" | "TICKET#123" | "TICKET#123" | "TICKET#123" | "2023-09-05 22:31:54" | ||||
| "ORG#MICROSOFT#USER#BILLGATES" | "TICKET#456" | "TICKET#456" | "TICKET#456" | "2024-09-05 22:31:54" | ||||
| "ORG#MICROSOFT#USER#BILLGATES" | "USER#BILLGATES" | "ORG#MICROSOFT" | "USER#BILLGATES" | "Bill Gates" | "Member" | |||
| "ORG#MICROSOFT#USER#BILLGATES" | "USER#SATYANADELLA" | "ORG#MICROSOFT" | "USER#SATYANADELLA" | "Satya Nadella" | "Admin" | |||
| "ORG#AMAZON#USER#JEFFBEZOS" | "USER#JEFFBEZOS" | "ORG#AMAZON" | "USER#JEFFBEZOS" | "Jeff Bezos" | "Admin" | |||
This secondary index has an item collection with both the User item and all of the user’s Ticket items. This enables the same access patterns we discussed in the previous chapter.
One last note before moving on—notice that I’ve structured it so that the User item is the last item in the partition. This is because the Tickets are sorted by timestamp (TICKET#123 is older than TICKET#456). It’s likely that I’ll want to fetch a User and the User’s most recent Tickets, rather than the oldest tickets. As such, I order it so that the User is at the end of the item collection, and I can use the ScanIndexForward=False property to indicate that DynamoDB should start at the end of the item collection and read backwards.