Query all users in DynamoDB with a single-table design-CodePudding

I have a simple single-table design that I want to keep flexible for the future, I currently have 2 entity types: users and videos. Users have a 1:n relationship to videos.

The table's partition key is pk and sort key is sk.

Users: pk=u#<id> and sk=u#<id>, entityType: user

Videos: pk=u#<id> and sk=v#<id>, entityType: video

If I want to fetch all users, does it make sense to create a GSI with PK=entityType and SK=sk?

CodePudding user response：

That is one approach you could take and it would get the job done, but it has a few drawbacks/side effects:

You would also replicate all videos in that GSI, which increases the storage and throughput cost of it
You would create a potentially huge item collection that contains all users, which could lead to a hot partition and may not scale well.

Instead, consider splitting up the huge user partition in the GSI into multiple ones with predictable keys.

If you plan to list your users by username later, you could take the first letter of their username as the partition key and thereby create around 26 (depending on capitalization and character set) different partitions, which would spread out the load a lot better. To list all users, you'd have to issue queries on all the partitions, which is annoying at small sizes, but will be more scalable.

Another option would be to define that you want to spread the users out among n partitions and then use something like hash(user_id) mod n to get a partition key for the GSI. That way you'd have to do n queries to get the values of all partitions.

CodePudding user response：

No, because then all user writes will go to the same PK which isn’t ideal. Instead, setup a GSI with a GSI1PK holding your user ID and you can do a scan against it. Project in the essential attributes. Only set the GSI1PK for user entity types so it’s a sparse GSI.