CODEWITHSUNDEEP
nosqlamazon-dynamodbamazon-dynamodb-index
Safari
Safari

Reputation: 11945

DynamoDB table setup GSI vs composite sort-key

I'm new about DynamoDB and I'm trying to understand some aspects. I have a doubt.

Suppose that I have to store these info related to a Book table:

I need to have these kinds of queries:

Now I'm trying to understand how it's better to setup the table  and the indexes.

Discussing on the web the possible solution could be this:

Partitionkey: AUTHOR#[UUID] SortKey: BOOK#[UUID].

And storing also each uuid as well in their own attributes (author_id, book_id) to make seeing them without having to parse the strings.   And also, adding a  Global Secondary Index that flips the PK and SK. In this way it's possible to use queries: Query PK = AUTHOR#[UUID] or using the GSI with query GSI (pk) = BOOK#UUID to get a specific book

As I wrote I need to make queries like this: "Get all books by author_id and type" So it's necessary to understand how to organize the data/indexes about the type field also. Is it better to add an additional GSI for the type? Is it better to concat the type into the SortKey like: BOOK#UUID#TYPE?

What are pros and cons? If I understood correctly adding multiple GSI indexes it's not a good idea also because the costs will increase, the table will be "duplicated" for each index and these must be kept in sync (by AWS) and this will consume credits.

Any confirmation or different suggestions?

Upvotes: 0

Views: 658

Answers (1)

hunterhacker
hunterhacker

Reputation: 7132

Here's a good design:

Base table has a PK of book#<bookid>. This makes it easy to "Get/Update/Deleted book by ID".

On the base table items, one attribute will naturally be author_id and another will naturally be type. Create a GSI on these two where the author_id is the PK and type is the SK. You can Query specifying the author_id only and it will pull everything by this author. You can optionally add an SK constraint to include the type limitation.

GSIs have costs but they are there for a reason, to provide alternate query mechanisms, which is exactly what you desire.

Upvotes: 0

Related Questions