Cassandra update value in one of the clustering column

Question

It is advisable in cassandra to do data modeling around queries. However if I model set a column as clustering column for the purpose of sorting based on it and if that object is dynamic, because it is clustering column, I cannot update its value since it belongs to primary key now for that table. In that case, two options are

1. Sorting on client side (which is bad)
2. Delete complete row and insert new row (which will create tombstone)

Is there other valid way of achieving this in Cassandra data modeling?

Eg. I have table_A and for query of getting all rows with a particular state, table_A_by_state. However since state will be dynamic, and you will need to update state in table_A_by_state which comes with options I mentioned above. Has someone else faced same problem or is there other way of data modeling for this problem?

table_A: Columns: id (K), name, state

table_A_by_state: Columns: id (K), state (C), name

Answer 1

Depending on the number of records you have in each partition, you can try to resort to indexing, and see how far you can scale with them.

Unfortunately there are no other options. As an example, if you are using Cassandra 3.0+ and want to use Materialized Views in order to satisfy you ordering requirement, you are actually falling inside your option 2, because C* hides from you that it will create another table and insert/update/delete that table under the scenes. So, yes, it will create tombstones for you.

However, option 1 is not that bad either (if you manage a relative small amount of records, because fetching a lot of rows is an anti-pattern), assuming you have to perform order somewhere. Yep, storing and retrieving data already ordered is a big plus, but better to sort client-side than having to become a magician with tombstones and write/read timeouts...