Hello, I have two questions regarding modelling a schema optimized for performance for Cassandra.
My service does have users who can add a message to a list. The list of message is sorted by date and displayed. When a user changes the message the date is changed and the message moves to the top of the list. At least that is how it suppose to be ;-) With cql i need to define 'date' column as clustered key to be able to sort on date. But by doing this i can't update the value in the 'date' column with a UPDATE query, cause partition and cluster keys can't be updated with cql. A possible solution is to remove row and insert it again, but i suspect this might be not the best solution. Is there a alternative solution? My second question is regarding the number of tables/column families in a keyspace. I can create a table which contains all messages from all users. But i can also create one table for every user which has a name like: messages_[userid], where [userid] is the id of the user. Or i can shard: messages_a (contains messages from user where name starts with a), messages_b (contains messages from user where name starts with b) My users count is around 100.000. And the messages per user are approx around 20.000. What would be the choice: put everything in 1 big table or go with the many small tables option. Thank you for offering the helping hand. Kind regards, Tinus