1

I have Messages table in DynamoDB. It has four columns sender, timestamp, message, recipient. I was wondering instead of creating a partition key using any one of the four columns, why not create another columns for partitioning purposes concatenating sender&timestamp&recipient.

So this column will hold data like JohnSmithID1461754484307SallyMcDonaldID.

By doing this, when searching for message from a particular sender&recipient combo, I can query by just using this one column using query like (begin with & end with). And there are a few other ways of utilizing this column.

Question 1. Am I being over complicating things here by trying to use one column instead of spreading my query into a few columns?

Question 2. Is there a noticeable performance benefit by taking this direction?

Question 3. Is this design pattern only worthwhile if I eliminate column SenderId & RecipientID for data size purposes? (I need timestamp column for sort key)

Community
  • 1
  • 1
shle2821
  • 1,856
  • 1
  • 19
  • 26

2 Answers2

4

I think you have to read again how DynamoDB partition keys work. You are not able to do queries like "begin with" or "end with" on partition keys because you have to provide the full partition key for a query. You may only provide such a condition on the sort key (but note that there is a begins_with function, but no ends_with function).

Your idea might be based on using scans instead of queries but (regarding question 2.) this would result in a lot more used capacity and bad performance because DynamoDB has to take a look at every item in the table. If you want to have more query flexibility you could define one or more secondary indexes.

You can answer question 3 by yourself: DynamoDB volume is quite expensive but we are talking about a difference of maybe 20 byte per entry. If you may end up with >10.000.000 entries in your table this might become an issue, otherwise ignore it.

birnbaum
  • 4,718
  • 28
  • 37
1

Your particular example will not work, because you cannot have conditions on the Partition Key when querying. You can only have such conditions on the Sort Key.

Although, this sort of structure might come in handy at times. An example would be if you have three attributes that you want to query by. DynamoDB allows for at most two (Partition Key + Sort Key), so one of them could be a combination of two or more attributes in that case.

Nick Pestov
  • 571
  • 5
  • 15
  • Sir, are u saying if i wanted to query using three filter parameters I wouldn't because to do it with a dynamodb table, despite having secondary indices placed in all three respective columns? – shle2821 Apr 28 '16 at 23:29
  • @shle2821I'm talking about a scenario when you would need to query an item which could be defined by the combination of three or more attributes. DynamoDB allows for Primary Keys to be formed using at most two attributes (Partition Key + Sort Key). More info [here](http://stackoverflow.com/questions/32620215/3-fields-composite-primary-key-unique-item-in-dynamodb) – Nick Pestov Apr 28 '16 at 23:39
  • Thanks for the info. Then, let me ask you this. While leaving the primary key alone, I create a sort key based on senderID&timestamp&recipientID? So the user's device will concatenate the three attributes together and send to DynamoDB. Since it's a sort key, I'm able to query with "begin with", "end with" etc. What do you think about this design pattern? – shle2821 Apr 30 '16 at 06:41
  • @shle2821, there's some terminology that's not quite right in your question. I'd suggest you have a look at the [Primary Key](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/HowItWorks.CoreComponents.html#HowItWorks.CoreComponents.PrimaryKey) page in their docs. Basically, they support 2 kinds of Primary Keys - `Partition Key` and `Partition Key + Sort Key` Back to your question: That design is good for when you need to query based on 3 or more different attributes in one go. In your particular example setting up multiple Global Secondary Indexes would suit better I think. – Nick Pestov May 01 '16 at 07:24