Cassandra update value in a single clustering column

Question

Cassandra recommends modeling data around queries. However, if I model the column as a clustering column for sorting on it, and if this object is dynamic because it is a clustered column, I cannot update its value, since now it refers to the primary key for this table. In this case, two parameters:

• Client side sorting (which is bad)
• Delete a full line and insert a new line (which will create a tombstone)

Is there another effective way to achieve this in Cassandra data modeling?

Eg. I have table_A and for requesting to get all rows with a certain state table_A_by_state. However, since the state will be dynamic, and you will need to update the state in the table_A_by_state, which comes with the options mentioned above. Has someone else encountered the same problem, or is there another way to model the data 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 that you have in each section, you can try to apply indexing and see how far you can scale them.

Unfortunately, there are no other options. For example, if you use Cassandra 3.0+ and want to use Materialized Views to satisfy your order requirements, you actually get inside your option 2, because C * hides from you that it will create another table and inserts / updates / deletes this table under the scenes . So yes, it will create gravestones for you.

However, option 1 is also not so bad (if you are managing a relatively small number of records, because fetching multiple rows is an anti-pattern), assuming you need to execute an order somewhere. Yes, storing and retrieving already ordered data is a big plus, but it's better to sort the client side than becoming a wizard with tombstones and write / read timeouts ...