I designed this chat demo app where i have a fairly simple use case: i query conversations using AppSync with GraphQL from my DynamoDB table and i have my PK set as conversationID and lastActive or lastMessageDate used as the sort key.
As you can guess, i would like to use this lastMessageDate attribute to sort the results of my query, so everytime a new message is sent in that conversation, i can display that conversation as the most recent in the list.
I know you cannot modify a sort key (or PK) in dynamoDB and it doesn't make to because the SK and PK make up the primary key in an item. So my question is, are there ways to achieve this using LSIs or GSIs? There MUST be a way to modify an attribute that you sort by. Sort key fields can't remain static throughout their entire lifecycles, can they?
What have i tried?
You're correct that you cannot update the primary keys of a DynamoDB table. However, there are workarounds:
You could simply delete then put. However, you could run into consistency issues (in case of failure). So this leads to using Transactions, which you alluded to. You mention they consume too much RCU, but TransactionWrites do not consume any RCU, only WCU (not sure if thats a typo on your end). For transactions you pay double the WCU. So if a Delete then Put costs you 2 WCU, then you will pay 4 WCU with Transactions. I like this method the most, as you get strong consistency. But for cost purposes, it doesn't scale well when there are lots of updates to keys.
While I would always advise against an LSI, if you require strong consistency it will also work. But let's discuss a GSI. You can set your base table primary key to have a different sort key, one that will still make sense to your access patterns.
Your index will now have the schema that your base table currently has. You can update your index sort key now. Behind the scenes DynamoDB does a Delete then Put of the item in your index. This operation will cost you 1 WCU for your base table update, and 2 WCU for your index Delete then Put -- 3 WCU in total (not much different that the transaction) and you still have to maintain an index.
With both of these options, it really depends on the frequency of your key updates. Less frequent updates will suit transactions, while an index can handle larger throughput from a cost perspective.