I'm designing a new AppSync model for a Manufacturing Executing System and one of the most important access patterns is "Get all items in a given production date interval" and "Get all items in location X in a given production date interval", more specifically for a calendar/planning view (with date range).
type Project @model {
id: ID!
name: String
items: [Item] @hasMany
}
type Item @model {
id: ID!
ref: String
projectID: ID
location: String
productionDate: AWSDate
}
I've been trying different composite pk/sk keys and indexes but mostly my problem relies on the high frequency that an item can change its production date or location, making it harder to be a pk or sk (maybe I'm wrong here?).
One idea that occurred to me was to delete the item and rewrite it every time the productionDate or location changed, that way I would be able to keep similar production dates together (using a partition key, also items with productionDate < today will be less accessible as intended)
Is there a way to design this model to make these access patterns more efficient and avoid scans?
For context, I'm using AWS amplify for this prototype
Wanting to do a request based on time can be tricky in DynamoDB. One way to do it is to use a GSI where you place a static value as partition key, and date as the sort key:
| GSIPK | GSISK | Data |
|---|---|---|
| 1 | 2022-03-05T12:00:000Z | some data |
| 1 | 2023-02-06T13:00:000Z | some data |
| 1 | 2024-03-07T14:00:000Z | some data |
| 1 | 2024-04-08T15:00:000Z | some data |
Now when you do a Query for all of the items since Jan 2024:
SELECT * FROM myTable.myGsi WHERE GSIPK=1 AND GSISK BETWEEN '2024-01-01T00:00:000Z' AND '2024-04-10T14:00:000Z'
That is now efficient. There are some caveats to this design, and I explain it all in detail in this blog post.