Sorting DynamoDB on a Changing Attribute

#dynamodb #aws #database #nosql

You model a sort key around an attribute so you can query items in its order — then the
attribute changes. A ticket's status, an order's state, a task's priority. Here's the
catch DynamoDB throws: you cannot update a key attribute in place. A primary key is
immutable for the life of the item. Change a value that's part of the key and you're not
editing an item — you're moving it, which DynamoDB makes you do explicitly.

Can you change a DynamoDB sort key?

No. A sort key is part of the primary key, and DynamoDB key attributes are immutable — UpdateItem cannot edit a partition or sort key value, and there is no "move item" operation. To change it, delete the old item and put a new one, or keep the volatile value on a GSI sort key instead.

Key attributes are immutable. You can't UpdateItem a partition or sort key value — DynamoDB has no "move item" operation.
To change a key value you delete the old item and put a new one — ideally in a transaction so it's atomic.
Better: keep the volatile value off the base-table key and put it on a GSI sort key instead — GSI keys can change, because updating the base item just re-propagates the index entry.
Choose sort keys that don't change (timestamps, immutable ids) whenever the access pattern allows.

The problem: a status you want to sort by, that keeps changing

Say you run a support desk and want to list a team's tickets ordered by status, so you
put status in the sort key:

PK: TEAM#7   SK: STATUS#open#TICKET#8842

Now the ticket moves to pending. You'd like to just UpdateItem the sort key to
STATUS#pending#TICKET#8842 — but DynamoDB rejects any write that changes a key
attribute. The key is the item's address; you can't edit the address in place. The
status you chose to sort by is exactly the thing that won't sit still.

Option 1: delete and recreate (atomically)

If the value must live in the base-table key, changing it means removing the old item
and writing the new one:

1. DeleteItem  PK=TEAM#7  SK=STATUS#open#TICKET#8842
2. PutItem     PK=TEAM#7  SK=STATUS#pending#TICKET#8842  (same attributes)

Do it inside a TransactWriteItems so the delete and
the put either both succeed or both fail — otherwise a crash between them loses the
ticket or duplicates it. This works, but every status change is now two writes plus a
transaction; fine for occasional changes, costly for hot ones.

Option 2: keep the mutable value off the base key (preferred)

The cleaner design: make the base-table key something immutable (the ticket id) and
put the volatile, sortable value on a GSI sort key.

Base:  PK: TICKET#8842   status: "open"   teamId: TEAM#7
GSI:   GSI1PK: TEAM#7    GSI1SK: STATUS#open#TICKET#8842

Now changing status is a plain UpdateItem on the base item's status attribute —
which DynamoDB allows, because status isn't a base-table key. DynamoDB then
re-propagates the GSI entry automatically to its new sorted position. One API call,
with atomicity handled for you — no transaction, no delete dance (under the hood DynamoDB
still deletes the old index entry and writes the new one, so an indexed change costs ~3
write units against the ~4 of a transactional delete-and-put).

The trade-off: the GSI is eventually consistent
and costs extra storage/writes — but for a value that changes often, that's somewhat
cheaper (~3 vs 4 write units) and much simpler than delete-and-recreate on every change.

Designing the keys in DynoTable

Build and preview the key conditions for both the base read and the GSI read in the
DynamoDB expression builder.

In DynoTable, you then pick which index a query runs through and watch the volatile
value sort on the GSI while the base item keeps its immutable key — both reads side by
side on real data.

Pitfalls + next steps

Never try to UpdateItem a key attribute — it's rejected; key values are fixed for the item's life.
If you must move it, do delete+put in a transaction — never as two unguarded writes.
Prefer immutable base keys + a GSI for any attribute you sort by and mutate.
Don't forget GSI eventual consistency — the re-sorted entry appears after a brief propagation delay.
Related: sort-key strategies, GSI vs LSI, transactions.