Just starting out with NoSql in general and DynamodB in particular, and I've been asked the following question:
There is an existing table with a large number of rows with fields something like:
RecordId (Guid = partition key)
RecordType (Guid = sort key)
UserId (Guid)
CreationDateTime
... and some other attributes ...
There is also a GSI (say GSI1) with:
UserId = partition key
RecordType = sort key
Some users generate a large number of rows with the same RecordType (maybe hundreds or thousands per day), so this index typically has a large number of rows with the same UserId+RecordType.
I want to be able to efficiently execute a paged query for results that match a specific UserId+RecordType, and have a CreationDateTime within a range of dates.
What's the best way to achieve this?
It's been suggested to me that I can create a second GSI (say GSI2) with partitionKey = Userid and Sort key = CreationDateTime. The idea being that this would be an efficient way to find the first row for UserId+RecordType that is in the required date range, and that then this would be used as a starting point (PaginationToken) for searching in GSI1. Which would only work if the rows in GSI1 with a duplicate key are stored in order of creation.
Does this make sense? And if not, what would be a better approach?
if your query is: "specific UserId+RecordType, and CreationDateTime range" so you answered to yourself:
create GSI:
hash = UserId_RecordType (1234_type1)
range = CreationDateTime
in this way you can get all docs for user with RecordType, sorted by CreationDateTime
be careful with hot keys.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments